--DBA 需要知道N种对数据库性能的监控SQL语句 -- IO问题的SQL内部分析 下面的DMV查询可以来检查当前所有的等待累积值。 Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type --可以通过运行下面的查询得到每个文件的信息，了解哪个文件经常要做读(num_of_reads/ num_of_bytes_read)， --哪个经常要做写(num_of_writes/ num_of_bytes_written)，哪个文件的读写经常要等待(io_stall_read_ms/ io_stall_write_ms/ io_stall)。 select db.name as database_name, f.fileid as file_id, f.filename as file_name, i.num_of_reads, i.num_of_bytes_read, i.io_stall_read_ms, i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms, i.io_stall, i.size_on_disk_bytes from sys.databases db inner join sys.sysaltfiles f on db.database_id = f.dbid inner join sys.dm_io_virtual_file_stats(NULL, NULL) i on i.database_id = f.dbid and i.file_id = f.fileid --SQLOS的任务调度算法 --SQL 2005和SQL 2008有个动态管理视图sys.dm_os_schedulers，可以反映当前每个scheduler的状态。 SELECT scheduler_id, cpu_id, parent_node_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count FROM sys.dm_os_schedulers; -- SQL CPU 100%问题 --使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前50名。 select highest_cpu_queries.*,q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.* from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc go --我们也可以找到最经常做重编译的存储过程。 select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc go -- 返回最经常运行的100条语句 SELECT TOP 100 cp.cacheobjtype ,cp.usecounts ,cp.size_in_bytes ,qs.statement_start_offset ,qs.statement_end_offset ,qt.dbid ,qt.objectid ,SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as statement FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle where cp.plan_handle=qs.plan_handle and cp.usecounts>4 ORDER BY [dbid],[Usecounts] DESC -- 返回最经常被修改的100个索引 -- 通过它们的Database_id, object_id, index_id和partition_number -- 可以找到它们是哪个数据库上的哪个索引 SELECT top 100 * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) order by leaf_insert_count+leaf_delete_count+leaf_update_count desc GO -- 返回做IO数目最多的50条语句以及它们的执行计划 select top 50 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset, substring(sql_text.text, (statement_start_offset/2), case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000 else (statement_end_offset -statement_start_offset)/2 end) as exec_statement, sql_text.text, plan_text.* from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) as sql_text cross apply sys.dm_exec_query_plan(plan_handle) as plan_text order by (total_logical_reads + total_logical_writes) /Execution_count Desc go -- CPU -- 计算signal wait占整wait时间的百分比 select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms)) from Sys.dm_os_wait_stats --性能计数对象SQLServer:SQL Statistics下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是： --Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec --执行计划重用率= (Batch requests/sec – Initial Compilations/sec) / Batch requests/sec -- 计算'Cxpacket'占整wait时间的百分比 declare @Cxpacket bigint declare @Sumwaits bigint select @Cxpacket = wait_time_ms from Sys.dm_os_wait_stats where wait_type = 'Cxpacket' select @Sumwaits = sum(wait_time_ms) from Sys.dm_os_wait_stats select convert(numeric(5,4),@Cxpacket/@Sumwaits) -- 阻塞： -- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率 declare @dbid int select @dbid = db_id() Select dbid=database_id, objectname=object_name(s.object_id) , indexname=i.name, i.index_id --, partition_number , row_lock_count, row_lock_wait_count , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)) , row_lock_wait_in_ms , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i where objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id order by row_lock_wait_count desc -- 返回当前数据库所有碎片率大于25%的索引 -- 运行本语句会扫描很多数据页面 -- 避免在系统负载比较高时运行 declare @dbid int select @dbid = db_id() SELECT * FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) where avg_fragmentation_in_percent>25 order by avg_fragmentation_in_percent desc GO -- 当前数据库可能缺少的索引 select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc go ---查找未使用过的索引 SELECT TOP 1000 o.name AS 表名 , i.name AS 索引名 , i.index_id AS 索引id , dm_ius.user_seeks AS 搜索次数 , dm_ius.user_scans AS 扫描次数 , dm_ius.user_lookups AS 查找次数 , dm_ius.user_updates AS 更新次数 , p.TableRows as 表行数 , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS '删除语句' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 and o.name='t_goods' --根据实际修改表名 ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC /* user_updates很大，而发现user_seeks和user_scans很少或者就是0，那就说明该索引一直在更新， 但是从来不被使用，仅仅创建和修改，没有为查询提供任何帮助，就可以考虑删除了 */ --得到按照执行时间排序的前10 的存储过程的执行信息： SELECT TOP 10 a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name', a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time], a.execution_count, a.total_physical_reads/a.execution_count avg_physical_reads, a.total_logical_writes, a.total_logical_writes/ a.execution_count avg_logical_reads, a.last_elapsed_time, a.total_elapsed_time / a.execution_count avg_elapsed_time, b.text,c.query_plan FROM sys.dm_exec_procedure_stats AS a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c ORDER BY [total_worker_time] DESC; --100个io读取开销最大的语句 SELECT TOP 100 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC