-- ----------------------------------------------------------------------------------- -- --1. 数据库 ----------------------------------------------------------------------------------- -- --1.查看数据库版本信息 select @@version --2.查看所有数据库名称及大小 exec sp_helpdb --3.查看数据库所在机器的操作系统参数 exec master..xp_msver --4.查看数据库启动的参数 exec sp_configure --5.查看数据库启动时间 select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 --6.查看数据库服务器名 select 'Server Name:'+ltrim(@@servername) --7.查看数据库实例名 select 'Instance:'+ltrim(@@servicename) --8.数据库的磁盘空间呢使用信息 exec sp_spaceused --19.查看所有数据库用户登录信息 exec sp_helplogins --20.查看所有数据库用户所属的角色信息 exec sp_helpsrvrolemember --21.查看链接服务器 exec sp_helplinkedsrvlogin --22.查看远端数据库用户登录信息 exec sp_helpremotelogin --23.获取网络数据包统计信息 select @@pack_received [输入数据包数量], @@pack_sent [输出数据包数量], @@packet_errors [错误包数量], getdate() [当前时间] --24.检查数据库中的所有对象的分配和机构完整性是否存在错误 dbcc checkdb --25.收缩数据库 dbcc shrinkdatabase(DB) --26.查看系统信息（操作系统、硬件、数据库） SP_CONFIGURE 'xp_cmdshell', '1' RECONFIGURE exec xp_cmdshell 'systeminfo' --27.所有数据库的状态 select name, user_access_desc, --用户访问模式 state_desc, --数据库状态 recovery_model_desc, --恢复模式 page_verify_option_desc, --页检测选项 log_reuse_wait_desc --日志重用等待 from sys.databases --28.对某个数据库,显示目录视图中的页数和行数错误并更正 DBCC UPDATEUSAGE('DB') --29.重命名数据库用的SQL EXEC sp_renamedb 'DB', 'DB1' --30.Tells you the date and time that SQL Server was installed SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date] FROM sys.server_principals WITH (NOLOCK) WHERE name = N'NT AUTHORITYSYSTEM' OR name = N'NT AUTHORITYNETWORK SERVICE' OPTION (RECOMPILE); --31.Get selected server properties (SQL Server 2012) (Query 3) (Server Properties) SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID], SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly], SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus]; --32. Windows information (SQL Server 2012) (Query 7) (Windows Info) SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE); --33. SQL Server NUMA Node information (Query 9) (SQL Server NUMA Info) -- Gives you some useful information about the composition and relative load on your NUMA nodes -- You want to see an equal number of schedulers on each NUMA node SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state FROM sys.dm_os_nodes WITH (NOLOCK) WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE); --34. Hardware information from SQL Server 2012 (Query 10) (Hardware Info) -- (Cannot distinguish between HT and multi-core) -- Gives you some good basic hardware information about your database server -- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM -- It merely indicates that you have a hypervisor running on your host SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)], committed_target_kb/1024 AS [Committed Target Memory (MB)], max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type], sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type] FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE); --35. Get System Manufacturer and model number from (Query 11) (System Manufacturer) -- SQL Server Error log. This query might take a few seconds -- if you have not recycled your error log recently -- This can help you determine the capabilities -- and capacities of your database server EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer'; --36. Get socket, physical core and logical core count from (Query 12) (Core Counts) -- SQL Server Error log. This query might take a few seconds -- if you have not recycled your error log recently -- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not -- It can also help you confirm your SQL Server licensing model -- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing" which means grandfathered Server/CAL licensing -- Note: If you recycle your error logs frequently and your instance has been running long enough, -- this query may not return any results, since the original startup information from the first error log -- when SQL Server was last started will have been overwritten EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket'; --37. Get processor description from Windows Registry (Query 13) (Processor Description) -- Gives you the model number and rated clock speed of your processor(s) -- Your processors may be running at less that the rated clock speed due -- to the Windows Power Plan or hardware power management EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWAREDESCRIPTIONSystemCentralProcessor ', N'ProcessorNameString'; -- You can skip the next four queries if you know you don't -- have a clustered instance --38. Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured (Query 14) (SQL Server Error Log) -- Knowing this information is important for troubleshooting purposes -- Also shows you the location of other error and diagnostic log files SELECT is_enabled, [path], max_size, max_files FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE); --39. Get information about your OS cluster (if your database server is in a cluster) (Query 15) (Cluster Properties) -- You will see no results if your instance is not clustered SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath, SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE); --40. Get information about your cluster nodes and their status (Query 16) (Cluster Node Properties) -- (if your database server is in a failover cluster) -- Knowing which node owns the cluster resources is critical -- Especially when you are installing Windows or SQL Server updates -- You will see no results if your instance is not clustered SELECT NodeName, status_description, is_current_owner FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE); --41. Get information about any AlwaysOn AG cluster this instance is a part of (Query 17) (AlwaysOn AG Cluster) -- You will see no results if your instance is not using AlwaysOn AGs -- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters -- https://support.microsoft.com/kb/2920151 SELECT cluster_name, quorum_type_desc, quorum_state_desc FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE); --42. Get configuration values for instance (Query 18) (Configuration Values) -- Focus on these settings: -- backup compression default (should be 1 in most cases) -- clr enabled (only enable if it is needed) -- cost threshold for parallelism (depends on your workload) -- lightweight pooling (should be zero) -- max degree of parallelism (depends on your workload) -- max server memory (MB) (set to an appropriate value, not the default) -- optimize for ad hoc workloads (should be 1) -- priority boost (should be zero) -- remote admin connections (should be 1) SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced FROM sys.configurations WITH (NOLOCK) ORDER BY name OPTION (RECOMPILE); --43. Get information about TCP Listener for SQL Server (Query 19) (TCP Listener States) -- Helpful for network and connectivity troubleshooting SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_time FROM sys.dm_tcp_listener_states WITH (NOLOCK) ORDER BY listener_id OPTION (RECOMPILE); --44. Get information on location, time and size of any memory dumps from SQL Server (Query 20) (Memory Dump Info) -- This will not return any rows if you have -- not had any memory dumps (which is a good thing) SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)] FROM sys.dm_server_memory_dumps WITH (NOLOCK) ORDER BY creation_time DESC OPTION (RECOMPILE); --45. Calculates average stalls per read, per write, and per total input/output for each database file (Query 25) (IO Stalls by File) -- Helps determine which database files on the entire instance have the most I/O bottlenecks -- This can help you decide whether certain LUNs are overloaded and whether you might -- want to move some files to a different location or perhaps improve your I/O performance SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms], CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms], CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads, fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE); --46. Recovery model, log reuse wait description, log file size, log usage size (Query 26) (Database Properties) -- and compatibility level for all databases on instance -- Things to look at: -- How many databases are on the instance? -- What recovery models are they using? -- What is the log reuse wait description? -- How full are the transaction logs ? -- What compatibility level are the databases on? -- What is the Page Verify Option? (should be CHECKSUM) -- Is Auto Update Statistics Asynchronously enabled? -- Make sure auto_shrink and auto_close are not enabled! SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.state_desc, db.log_reuse_wait_desc AS [Log Reuse Wait Description], CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)], CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled FROM sys.databases AS db WITH (NOLOCK) INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_name WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE N'Log File(s) Size (KB)%' AND ls.cntr_value > 0 OPTION (RECOMPILE); --47. Get VLF Counts for all databases on the instance (Query 28) (VLF Counts) -- (adapted from Michelle Ufford) -- High VLF counts can affect write performance -- and they can make database restores and recovery take much longer -- Try to keep your VLF counts under 200 in most cases CREATE TABLE #VLFInfo ( RecoveryUnitID int, FileID int, FileSize bigint, StartOffset bigint, FSeqNo bigint, [Status] bigint, Parity bigint, CreateLSN numeric(38) ); CREATE TABLE #VLFCountResults ( DatabaseName sysname, VLFCount int ); EXEC sp_MSforeachdb N' Use [DB]; INSERT INTO #VLFInfo EXEC sp_executesql N''DBCC LOGINFO([DB])''; INSERT INTO #VLFCountResults SELECT DB_NAME(), COUNT(*) FROM #VLFInfo; TRUNCATE TABLE #VLFInfo; ' SELECT DatabaseName, VLFCount FROM #VLFCountResults ORDER BY VLFCount DESC; DROP TABLE #VLFInfo; DROP TABLE #VLFCountResults; -- ----------------------------------------------------------------------------------- -- --2. 数据文件 ----------------------------------------------------------------------------------- -- --1.查看某个数据库中的所有文件及大小 sp_helpfile --2.查询文件组和文件 select df.[name], df.physical_name, df.[size], df.growth, f.[name][filegroup], f.is_default from sys.database_files df join sys.filegroups f on df.data_space_id = f.data_space_id --3.查看所有文件所在数据库、路径、状态、大小 select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小（MB）' from sys.master_files --4.按区extent计算空间，没有性能影响，基本准确,把TotalExtents*64/1024,单位为MB --同时也适用于计算tempdb的文件大小，但不包括日志文件 dbcc showfilestats --5.日志文件大小及使用情况 dbcc sqlperf(logspace) --6.查看日志文件所在数据库、路径、状态、大小 select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小（MB）' from sys.master_files where type_desc = 'LOG' --8. File names and paths for TempDB and all user databases in instance (Query 21) (Database Filenames and Paths) -- Things to look at: -- Are data files and log files on different drives? -- Is everything on the C: drive? -- Is TempDB on dedicated drives? -- Is there only one TempDB data file? -- Are all of the TempDB data files the same size? -- Are there multiple data files for user databases? -- Is percent growth enabled for any files (which is bad)? SELECT DB_NAME([database_id]) AS [Database Name], [file_id], name, physical_name, type_desc, state_desc, is_percent_growth, growth, CONVERT(bigint, growth/128.0) AS [Growth in MB], CONVERT(bigint, size/128.0) AS [Total Size in MB] FROM sys.master_files WITH (NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE); --9. Volume info for all LUNS that have database files on the current instance (Query 22) (Volume Info) --Shows you the total and free space on the LUNs where you have database files SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)], CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)], CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE); --10. Drive level latency information (Query 24) (Drive Level Latency) -- Based on code from Jimmy May -- Shows you the drive-level latency for reads and writes, in milliseconds -- Latency above 20-25ms is usually a problem SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point], CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms/num_of_reads) END AS [Read Latency], CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (io_stall_write_ms/num_of_writes) END AS [Write Latency], CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END AS [Overall Latency], CASE WHEN num_of_reads = 0 THEN 0 ELSE (num_of_bytes_read/num_of_reads) END AS [Avg Bytes/Read], CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (num_of_bytes_written/num_of_writes) END AS [Avg Bytes/Write], CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) END AS [Avg Bytes/Transfer] FROM ( SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads, SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes, SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read, SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point ) AS tab ORDER BY [Overall Latency] OPTION (RECOMPILE); --11. Inpidual File Sizes and space available for current database (Query 45) (File Sizes and Space) -- Look at how large and how full the files are and where they are located -- Make sure the transaction log is not full!! SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB], CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name] FROM sys.database_files AS f WITH (NOLOCK) LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE); --12. I/O Statistics by file for the current database (Query 46) (IO Stats By File) -- This helps you characterize your workload better from an I/O perspective for this database -- It helps you determine whether you has an OLTP or DW/DSS type of workload SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.physical_name AS [Physical Name], vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms, CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct], CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct], (vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads], CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written], CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct], CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct], CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct], CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct] FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs INNER JOIN sys.database_files AS df WITH (NOLOCK) ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE); -- ----------------------------------------------------------------------------------- -- --3. I/O统计信息，内存，CPU ----------------------------------------------------------------------------------- -- --1.获取磁盘读写情况 select @@total_read [读取磁盘次数], @@total_write [写入磁盘次数], @@total_errors [磁盘写入错误数], getdate() [当前时间] --2.获取I/O工作情况 select @@io_busy, @@timeticks [每个时钟周期对应的微秒数], @@io_busy*@@timeticks [I/O操作毫秒数], getdate() [当前时间] --3.查看CPU活动及工作情况 select @@cpu_busy, @@timeticks [每个时钟周期对应的微秒数], @@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)], @@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)], getdate() [当前时间] --4. 查看SQL Server的实际内存占用 select * from sysperfinfo where counter_name like '%Memory%' --5.显示所有数据库的日志空间信息 dbcc sqlperf(logspace) --6.数据和日志文件的I/O统计信息,包含文件大小 select database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数 from sys.dm_io_virtual_file_stats(db_id('Landa_MMI_3'),1 ) --数据库id, 数据文件id union all select database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数 from sys.dm_io_virtual_file_stats( db_id('Landa_MMI_3'),2 ) --数据库id, 日志文件id --7. Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs (Query 23) (IO Warnings) -- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of -- poor I/O performance (which might have many different causes) CREATE TABLE #IOWarningResults ( LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000) ); INSERT INTO #IOWarningResults EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds'; INSERT INTO #IOWarningResults EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds'; INSERT INTO #IOWarningResults EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds'; INSERT INTO #IOWarningResults EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds'; INSERT INTO #IOWarningResults EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds'; SELECT LogDate, ProcessInfo, LogText FROM #IOWarningResults ORDER BY LogDate DESC; DROP TABLE #IOWarningResults; --8. Get CPU utilization by database (Query 29) (CPU Usage by Database) -- Helps determine which database is using the most CPU resources on the instance ;WITH DB_CPU_Stats AS ( SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY ( SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid' ) AS pa GROUP BY DatabaseID ) SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank], [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent] FROM DB_CPU_Stats WHERE DatabaseID <> 32767 -- ResourceDB ORDER BY [CPU Rank] OPTION (RECOMPILE); --9. Get I/O utilization by database (Query 30) (IO Usage By Database) -- Helps determine which database is using the most I/O resources on the instance ;WITH Aggregate_IO_Statistics AS ( SELECT DB_NAME(database_id) AS [Database Name], CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS] GROUP BY database_id ) SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)], CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent] FROM Aggregate_IO_Statistics ORDER BY [I/O Rank] OPTION (RECOMPILE); --10. Get total buffer usage by database for current instance (Query 31) (Total Buffer Usage by Database) -- This make take some time to run on a busy instance -- Tells you how much memory (in the buffer pool) -- is being used by each database on the instance ;WITH AggregateBufferPoolUsage AS ( SELECT DB_NAME(database_id) AS [Database Name], CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize] FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) WHERE database_id <> 32767 -- ResourceDB GROUP BY DB_NAME(database_id) ) SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)], CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent] FROM AggregateBufferPoolUsage ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE); --11. Get top total worker time queries for entire instance (Query 38) (Top Worker Time Queries) -- Helps you find the most expensive queries from a CPU perspective across the entire instance SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 255) AS [Short Query Text], qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time], qs.total_worker_time/qs.execution_count AS [Avg Worker Time], qs.max_worker_time AS [Max Worker Time], qs.execution_count AS [Execution Count], qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads], qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads], qs.creation_time AS [Creation Time] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE); --12. Good basic information about OS memory amounts and state (Query 39) (System Memory) -- You want to see "Available physical memory is high" -- This indicates that you are not under external memory pressure SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], available_physical_memory_kb/1024 AS [Available Memory (MB)], total_page_file_kb/1024 AS [Total Page File (MB)], available_page_file_kb/1024 AS [Available Page File (MB)], system_cache_kb/1024 AS [System Cache (MB)], system_memory_state_desc AS [System Memory State] FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE); --13. SQL Server Process Address space info (Query 40) (Process Memory) -- (shows whether locked pages is enabled, among other things) -- You want to see 0 for process_physical_memory_low -- You want to see 0 for process_virtual_memory_low -- This indicates that you are not under internal memory pressure SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)], large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, memory_utilization_percentage, available_commit_limit_kb, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE); --14. Page Life Expectancy (PLE) value for each NUMA node in current instance (Query 41) (PLE by NUMA Node) -- PLE is a good measurement of memory pressure. -- Higher PLE is better. Watch the trend over time, not the absolute value. -- This will only return one row for non-NUMA systems. SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy] FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances AND counter_name = N'Page life expectancy' OPTION (RECOMPILE); --15. Memory Grants Pending value for current instance (Query 42) (Memory Grants Pending) -- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending] FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE); --16. Memory Clerk Usage for instance (Query 43) (Memory Clerk Usage) -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) -- MEMORYCLERK_SQLBUFFERPOOL is new for SQL Server 2012. It should be your highest consumer of memory -- CACHESTORE_SQLCP SQL Plans -- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers -- Watch out for high values for CACHESTORE_SQLCP -- CACHESTORE_OBJCP Object Plans -- These are compiled plans for stored procedures, functions and triggers SELECT TOP(10) mc.[type] AS [Memory Clerk Type], CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)] FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK) GROUP BY mc.[type] ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE); --17. Breaks down buffers used by current database by object (table, index) in the buffer cache (Query 59) (Buffer Usage) -- Note: This query could take some time on a busy instance -- Tells you what tables and indexes are using the most memory in the buffer cache -- It can help identify possible candidates for data compression SELECT OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)], COUNT(*) AS [BufferCount], p.Rows AS [Row Count], p.data_compression_desc AS [Compression Type] FROM sys.allocation_units AS a WITH (NOLOCK) INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id WHERE b.database_id = CONVERT(int,DB_ID()) AND p.[object_id] > 100 GROUP BY p.[object_id], p.index_id, p.data_compression_desc, p.[Rows] ORDER BY [BufferCount] DESC OPTION (RECOMPILE); -- ----------------------------------------------------------------------------------- -- --4. 对象，包括：表、索引、索引视图等 ----------------------------------------------------------------------------------- -- --1.表的磁盘空间使用信息 exec sp_spaceused 'T1' --2.查看数据库中所有表的条数 select b.name as tablename , a.rowcnt as datacount from sysindexes a , sysobjects b where a.id = b.id and a.indid < 2 and objectproperty(b.id, 'IsMSShipped') = 0 --3.得到最耗时的前10条T-SQL语句 ;with maco as ( select top 10 plan_handle, sum(total_worker_time) as total_worker_time , sum(execution_count) as execution_count , count(1) as sql_count from sys.dm_exec_query_stats group by plan_handle order by sum(total_worker_time) desc ) select t.text , a.total_worker_time , a.execution_count , a.sql_count from maco a cross apply sys.dm_exec_sql_text(plan_handle) t --4.准确:但有性能影响 exec sp_spaceused @objname ='T1', @updateusage ='true' --5.按页统计，没有性能影响，有时不准确 /*====================================================== 一次计算多个对象的空间使用情况 sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息 ========================================================*/ select o.name, sum(p.reserved_page_count) as reserved_page_count, --保留页，包含表和索引 sum(p.used_page_count) as used_page_count, --已使用页，包含表和索引 sum(case when p.index_id <2 then p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count else p.lob_used_page_count + p.row_overflow_used_page_count end) as data_pages, --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据 sum(case when p.index_id < 2 then p.row_count else 0 end) as row_counts --数据行数，包含表中的数据行数，不包含索引中的数据条目数 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id = o.object_id where p.object_id= object_id('T1') group by o.name --6.按页或区统计，有性能影响，准确 --显示当前数据库中所有的表或视图的数据和索引的空间信息 --包含：逻辑碎片、区碎片(碎片率)、平均页密度 dbcc showcontig(T1) --7.SQL Server推荐使用的动态性能函数，准确 select * from sys.dm_db_index_physical_stats ( db_id('test'), --数据库id object_id('test.dbo.temp_lock'), --对象id null, --索引id null, --分区号 'limited' --default,null,'limited','sampled','detailed',默认为'limited' --'limited'模式运行最快，扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页 --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息，如果少于1000页，那么用'detailed'代替'sampled' --'detailed'模式会扫描所有页,返回所有统计信息 ) --8.查找哪些对象是需要重建的 use DB go if OBJECT_ID('extentinfo') is not null drop table extentinfo go create table extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size int, obj_id int, index_id int, partition_number int, [partition_id] bigint, iam_chain_type varchar(50), pfs_bytes varbinary(10) ) go /*==================================================================== 查询到的盘区信息是数据库的数据文件的盘区信息，日志文件不以盘区为单位 命令格式: DBCC EXTENTINFO（dbname,tablename,indexid） DBCC EXTENTINFO('[test]','extentinfo',0) ======================================================================*/ insert extentinfo exec('dbcc extentinfo(''DB'') ') SELECT * FROM extentinfo go --9.每一个区有一条数据 select file_id, obj_id, --对象ID index_id, --索引id page_id, --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号 pg_alloc, --这个盘区分配的页面数量 ext_size, --这个盘区包含了多少页 partition_number, partition_id, iam_chain_type, --IAM链类型:行内数据,行溢出数据,大对象数据 pfs_bytes from extentinfo order by file_id, OBJ_ID, index_id, partition_id, ext_size /*===================================================================================================== 数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大, 那就应该重建对象. 每一条记录就是一个区 如果pg_alloc比ext_size小，也就是实际每个区分配的页数小于理论上这个区的页数， 那么就会多一条记录，把本应该属于这个区的页放到多出来的这条记录对应的区中， 那么原来只有一条记录(也就是一个区)，现在就有2条记录(也就是2个区)， 导致实际的区数量2大于理论上的区数量1. ========================================================================================================*/ select file_id, obj_id, index_id, partition_id, ext_size, count(*) as '实际区的个数', sum(pg_alloc) as '实际包含的页数', ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理论上的区的个数', ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理论上的区个数 / 实际区的个数' from extentinfo group by file_id, obj_id, index_id, partition_id, ext_size having ceiling(sum(pg_alloc)*1.0/ext_size) < count(*) --过滤: 理论上区的个数 < 实际区的个数,也就是百分比小于100%的 order by partition_id, obj_id, index_id, [file_id] --10.修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 ,更改某个数据对象的用户属主 ,注意: 更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 EXEC sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner' --11.查看某数据库下某个数据对象的索引信息 sp_helpindex T1 --12.查看数据库里所有的存储过程和函数 sp_stored_procedures --13.查看存储过程和函数的源代码 sp_helptext '@procedure_name' --11. Missing Indexes for all databases by Index Advantage (Query 27) (Missing Indexes All Databases) -- Getting missing index information for all of the databases on the instance is very useful -- Look at last user seek time, number of user seeks to help determine source and importance -- Also look at avg_user_impact and avg_total_user_cost to help determine importance -- SQL Server is overly eager to add included columns, so beware -- Do not just blindly add indexes that show up from this query!!! SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], migs.last_user_seek, mid.[statement] AS [Database.Schema.Table], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY index_advantage DESC OPTION (RECOMPILE); --12. Get Table names, row counts, and compression status for clustered index or heap (Query 60) (Table Sizes) -- Gives you an idea of table sizes, and possible data compression opportunities SELECT OBJECT_NAME(object_id) AS [ObjectName], SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType] FROM sys.partitions WITH (NOLOCK) WHERE index_id < 2 --ignore the partitions from the non-clustered index if any AND OBJECT_NAME(object_id) NOT LIKE N'sys%' AND OBJECT_NAME(object_id) NOT LIKE N'queue_%' AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%' AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%' AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%' AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%' AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%' GROUP BY object_id, data_compression_desc ORDER BY SUM(Rows) DESC OPTION (RECOMPILE); --13. Get some key table properties (Query 61) (Table Properties) -- Gives you some good information about your tables SELECT [name], create_date, lock_on_bulk_load, is_replicated, has_replication_filter, is_tracked_by_cdc, lock_escalation_desc FROM sys.tables WITH (NOLOCK) ORDER BY [name] OPTION (RECOMPILE); --14. When were Statistics last updated on all indexes? (Query 63) (Statistics Update) -- Helps discover possible problems with out-of-date statistics -- Also gives you an idea which indexes are the most active SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS [Object Name], o.type_desc AS [Object Type], i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], s.auto_created, s.no_recompute, s.user_created, s.is_temporary, st.row_count, st.used_page_count FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id] WHERE o.[type] IN ('U', 'V') AND st.row_count > 0 ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE); --15. Look at most frequently modified indexes and statistics (Query 64) (Volatile Indexes) SELECT o.name AS [Object Name], o.[object_id], o.type_desc, s.name AS [Statistics Name], s.stats_id, s.no_recompute, s.auto_created, sp.modification_counter, sp.rows, sp.rows_sampled, sp.last_updated FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.stats AS s WITH (NOLOCK) ON s.object_id = o.object_id CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE o.type_desc NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE') AND sp.modification_counter > 0 ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE); --16. Get fragmentation info for all indexes above a certain size in the current database (Query 65) (Index Fragmentation) -- Note: This query could take some time on a very large database -- Helps determine whether you have framentation in your relational indexes -- and how effective your index maintenance strategy is SELECT DB_NAME(ps.database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], i.name AS [Index Name], ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id WHERE ps.database_id = DB_ID() AND ps.page_count > 2500 ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE); ---17. Index Read/Write stats (all tables in current DB) ordered by Reads (Query 66) (Overall Index Usage - Reads) -- Show which indexes in the current database are most active for Reads SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, user_seeks + user_scans + user_lookups AS [Reads], s.user_updates AS [Writes], i.type_desc AS [IndexType], i.fill_factor AS [FillFactor], i.has_filter, i.filter_definition, s.last_user_scan, s.last_user_lookup, s.last_user_seek FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY user_seeks + user_scans + user_lookups DESC OPTION (RECOMPILE); -- Order by reads ---18. Index Read/Write stats (all tables in current DB) ordered by Writes (Query 67) (Overall Index Usage - Writes) -- Show which indexes in the current database are most active for Writes SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, s.user_updates AS [Writes], user_seeks + user_scans + user_lookups AS [Reads], i.type_desc AS [IndexType], i.fill_factor AS [FillFactor], i.has_filter, i.filter_definition, s.last_system_update, s.last_user_update FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes -- ----------------------------------------------------------------------------------- -- --5. tempdb数据库 ----------------------------------------------------------------------------------- -- --1.tempdb数据库的空间使用 /*====================================================== tempdb中包含的对象： 用户对象：是用户显式创建的，这些对象位于用户会话的作用域， 可以位于创建对象的例程(存储过程、触发器、函数)的作用域中。 1.用户定义的表、索引 2.系统表、索引 3.全局临时表、索引 4.局部临时表、索引 5.表变量 6.表值函数中返回的表 内部对象：是根据需要由SQL Server数据库引擎创建的，用于处理SQL Server语句， 内部对象可以在语句作用域中创建、删除。 每个内部对象至少需要9个页面，一个IAM页，一个区包含了8个页。 1.游标、假脱机操作、临时的大型对象(LOB)，存储的工作表 2.哈希联接、哈希聚合操作的工作文件 3.如果设置了sort_in_tempdb选项，那么创建、重新生成索引的重建排序结果存放在tempdb； group by、order by、union操作的中间结果。 版本存储区：是数据页的集合，包含了支持行版本控制功能的所需的数据，主要支持快照事务隔离级别， 以及一些其他的提高数据库并发性能的新功能。 1.公用版本存储区：在使用快照隔离级别、已提交读隔离级别的数据库中，由数据修改事务生成的行版本。 2.联机索引生成版本存储区：为了实现联机索引操作而为数据修改事务生成的行版本， 多个活动结果集，after触发器生成的行版本。 上面也提到了，由于sys.allocation_units和sys.partitions视图没有记录tempdb中的内部对象、版本存储区 所以这2个视图和sp_spaceused，不能准确反应出tempdb的空间使用。 分析tempdb现有的工作负载: 1.设置tempdb的自动增长 2.通过模拟单独的查询、工作任务，监控tempdb空间使用 3.通过模拟执行一些系统维护操作(重新生成索引),监控tempdb空间使用 4.根据2和3中tempdb的空间使用量,预测总工作负荷会使用的空间,并针对任务的并发度调整这个值. 5.根据4得到的值,设置生成环境中tempdb的初始大小,并开启自动增长. 另外,tempdb的文件个数和大小,不仅需要满足实际使用需要,还要考虑性能优化. 监控tempdb的空间使用方法: 1.可以通过SQL Trace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行, 而且SQL Trance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用. 2.轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数， 把结果记录在文件中，这对于很繁忙的系统是不错的选择。 ========================================================*/ Select DB_NAME(database_id) as DB, max(FILE_ID) as '文件id', SUM (user_object_reserved_page_count) as '用户对象保留的页数', ----包含已分配区中的未使用页数 SUM (internal_object_reserved_page_count) as '内部对象保留的页数', --包含已分配区中的未使用页数 SUM (version_store_reserved_page_count) as '版本存储保留的页数', SUM (unallocated_extent_page_count) as '未分配的区中包含的页数', --不包含已分配区中的未使用页数 SUM(mixed_extent_page_count) as '文件的已分配混合区中:已分配页和未分配页' --包含IAM页 From sys.dm_db_file_space_usage --Where database_id = 2 group by DB_NAME(database_id) --能够反映当时tempdb空间的总体分配,申请空间的会话正在运行的语句 SELECT t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count, t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count, t.text from sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请 inner join sys.dm_exec_sessions as t2 on t1.session_id = t2.session_id inner join sys.dm_exec_requests t3 on t2.session_id = t3.session_id cross apply sys.dm_exec_sql_text(t3.sql_handle) t where t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0 --返回tempdb中页分配和释放活动， --只有当任务正在运行时，sys.dm_db_task_space_usage才会返回值 --在请求完成时，这些值将按session聚合体现在SYS.dm_db_session_space_usage select t.session_id, t.request_id, t.database_id, t.user_objects_alloc_page_count, t.internal_objects_dealloc_page_count, t.internal_objects_alloc_page_count, t.internal_objects_dealloc_page_count from sys.dm_db_task_space_usage t inner join sys.dm_exec_sessions e on t.session_id = e.session_id inner join sys.dm_exec_requests r on t.session_id = r.session_id and t.request_id = r.request_id -- ----------------------------------------------------------------------------------- -- --6. 锁 ----------------------------------------------------------------------------------- -- --1.检查锁与等待 exec sp_lock --2.检查死锁 exec sp_who_lock --自己写个存储过程即可 /* create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sys.sysprocesses where blocked>0 ) a where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sys.sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end */ --3.查看阻塞信息 select spid, loginame, waitresource from master..sysprocesses where blocked <> 0 --4.查看语句 dbcc inputbuffer(53) --5.查看锁的信息 exec sp_lock @spid1 = 53 --6.打开数据库 select * from sysdatabases where dbid = 1 --7.根据锁信息中的ObjId列：1335727861，找到了这个xx表 select * from sysobjects where id = 1335727861 --8. Clear Wait Stats with this command -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); -- Isolate top waits for server instance since last restart or wait statistics clear (Query 32) (Top Waits) -- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure -- The SQL Server Wait Type Repository -- https://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx -- Wait statistics, or please tell me where it hurts -- https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ -- SQL Server 2005 Performance Tuning using the Waits and Queues -- https://technet.microsoft.com/en-us/library/cc966413.aspx -- sys.dm_os_wait_stats (Transact-SQL) -- https://msdn.microsoft.com/en-us/library/ms179984(v=sql.120).aspx ;WITH [Waits] AS ( SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS], (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS], signal_wait_time_ms / 1000.0 AS [SignalS], waiting_tasks_count AS [WaitCount], 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum] FROM sys.dm_os_wait_stats WITH (NOLOCK) WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND waiting_tasks_count > 0 ) SELECT MAX (W1.wait_type) AS [WaitType], CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec], CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec], CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec], MAX (W1.WaitCount) AS [Wait Count], CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage], CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec], CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec], CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec] FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold OPTION (RECOMPILE); --9. Signal Waits for instance (Query 33) (Signal Waits) -- Signal Waits above 10-15% is usually a confirming sign of CPU pressure -- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure -- Resource waits are non-CPU related waits SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Signal (CPU) Waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Resource Waits] FROM sys.dm_os_wait_stats WITH (NOLOCK) WHERE wait_type NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') OPTION (RECOMPILE); --10. Detect blocking (run multiple times) (Query 62) (Detect Blocking) -- Helps troubleshoot blocking and deadlocking issues -- The results will change from second to second on a busy system -- You should run this query multiple times when you see signs of blocking SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database], t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], --- lock requested t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], -- spid of waiter ( SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK) -- get sql for waiter CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) WHERE r.session_id = t1.request_session_id ) AS [waiter_batch], ( SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, ( CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2 ) FROM sys.dm_exec_requests AS r WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt WHERE r.session_id = t1.request_session_id ) AS [waiter_stmt], -- statement blocked t2.blocking_session_id AS [blocker sid], -- spid of blocker ( SELECT [text] FROM sys.sysprocesses AS p -- get sql for blocker CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) WHERE p.spid = t2.blocking_session_id ) AS [blocker_stmt] FROM sys.dm_tran_locks AS t1 WITH (NOLOCK) INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE); --11. Get lock waits for current database (Query 68) (Lock Waits) -- This query is helpful for troubleshooting blocking and deadlocking issues SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number, SUM(ios.row_lock_wait_count) AS [total_row_lock_waits], SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms], SUM(ios.page_lock_wait_count) AS [total_page_lock_waits], SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms], SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms] FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios INNER JOIN sys.objects AS o WITH (NOLOCK) ON ios.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ios.[object_id] = i.[object_id] AND ios.index_id = i.index_id WHERE o.[object_id] > 100 GROUP BY o.name, i.name, ios.index_id, ios.partition_number HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0 ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE); --12. Look at recent Full backups for the current database (Query 69) (Recent Full Backups) -- Are your backup sizes and times changing over time? -- Are you using backup compression? SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model, CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)], CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)], CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) / CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)], bs.backup_finish_date AS [Backup Finish Date] FROM msdb.dbo.backupset AS bs WITH (NOLOCK) WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 AND bs.backup_size > 0 AND bs.[type] = 'D' -- Change to L if you want Log backups AND database_name = DB_NAME(DB_ID()) ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE); -- These three Pluralsight Courses go into more detail about how to run these queries and interpret the results -- SQL Server 2014 DMV Diagnostic Queries ? Part 1 -- htt
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/621423.html
数据库教程:SQLServerFullCheck
— ———————————————————————————–
absoluteaccessAdreNALEXappArchastbatbottleCCODcsEAENCEFailformgeohHRhttpHyperIDEIEigninputLPLMODMsdnsOGOriginOWproppsRdbRioRWSEselectstreamtopVRWHOWindows Serverwow创建对象数据数据库文件个数服服务器系统系统维护链接服务器
赞 (0)
数据库教程:SQL语言之DQL、DML、DDL、DCL的概念与区别
上一篇
2021年5月22日
数据库教程:C#语言和SQLServer数据库结合使用，通过数据库中的表实现存储数据
下一篇
2021年5月22日