------------------------最耗时的sql---------------------------------------DECLARE @n INT; SET @n = 500;WITH cte1 AS ( SELECT a.* , t.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t WHERE t.dbid >= 5 ) SELECT t.dbid , DB_NAME(t.dbid) AS dbname , a.total_worker_time , a.avg_time_ms , a.execution_count , a.cache_count , REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ') FROM ( SELECT TOP ( @n ) plan_handle , SUM(total_worker_time) / 1000 AS total_worker_time , SUM(execution_count) AS execution_count , COUNT(1) AS cache_count , ( SUM(total_worker_time) / SUM(execution_count) ) / 1000 AS avg_time_ms FROM cte1 GROUP BY plan_handle ORDER BY avg_time_ms DESC ) a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t WHERE avg_time_ms > 200 ORDER BY avg_time_ms DESC;GO------------------------最频繁的sql---------------------------------------DECLARE @n INT; SET @n = 500;WITH cte1 AS ( SELECT a.* , t.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t WHERE t.dbid >= 5 ) SELECT t.dbid , DB_NAME(t.dbid) AS dbname , a.execution_count , a.total_worker_time , a.avg_time_ms , a.cache_count , REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ') FROM ( SELECT TOP ( @n ) plan_handle , SUM(total_worker_time) / 1000 AS total_worker_time , SUM(execution_count) AS execution_count , COUNT(1) AS cache_count , ( SUM(total_worker_time) / SUM(execution_count) ) / 1000 AS avg_time_ms FROM cte1 GROUP BY plan_handle ORDER BY avg_time_ms DESC ) a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t ORDER BY execution_count DESC;GO------------------------耗cpu的sql---------------------------------------DECLARE @n INT; SET @n = 500;WITH cte1 AS ( SELECT a.* , t.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t WHERE t.dbid >= 5 ) SELECT t.dbid , DB_NAME(t.dbid) AS dbname , a.total_logical_reads , a.avg_reads , a.total_logical_writes , a.avg_writes , a.execution_count , a.total_worker_time , a.avg_time_ms , a.cache_count , REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ') FROM ( SELECT TOP ( @n ) plan_handle , SUM(total_logical_reads) AS total_logical_reads , ( SUM(total_logical_reads) / SUM(execution_count) ) AS avg_reads , SUM(total_logical_writes) AS total_logical_writes , ( SUM(total_logical_writes) / SUM(execution_count) ) AS avg_writes , SUM(execution_count) AS execution_count , COUNT(1) AS cache_count , SUM(total_worker_time) AS total_worker_time , ( SUM(total_worker_time) / SUM(execution_count) ) / 1000 AS avg_time_ms FROM cte1 GROUP BY plan_handle ORDER BY ( ( SUM(total_logical_reads) / SUM(execution_count) ) + ( SUM(total_logical_writes) / SUM(execution_count) ) ) DESC ) a CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t ORDER BY ( avg_reads + avg_writes ) DESC;GO-----------------------当前数据库可能缺少的索引-------------------------------SELECT d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek , s.unique_compilesFROM sys.dm_db_missing_index_group_stats s , sys.dm_db_missing_index_groups g , sys.dm_db_missing_index_details dWHERE s.group_handle = g.index_group_handle AND d.index_handle = g.index_handleORDER BY s.avg_user_impact DESC;-----------------------当前数据库没用到的索引-------------------------------SELECT tb_name = OBJECT_NAME(a.object_id) , idx_name = b.name , last_user_update , c.colid , c.keyno , col_name = d.nameINTO #tmpFROM sys.dm_db_index_usage_stats a LEFT JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id LEFT JOIN sys.sysindexkeys c ON c.id = a.object_id AND c.indid = a.index_id LEFT JOIN syscolumns d ON d.id = c.id AND d.colid = c.colidWHERE database_id = DB_ID() AND last_user_seek IS NULL AND last_user_scan IS NULL AND last_user_lookup IS NULL AND last_user_update IS NOT NULLORDER BY tb_name , idx_name , keyno;SELECT tb_name , idx_name , last_user_update , keywords = STUFF(( SELECT ',' + col_name FROM #tmp WHERE tb_name = a.tb_name AND idx_name = a.idx_name ORDER BY tb_name , idx_name , keyno FOR XML PATH('') ), 1, 1, '')FROM #tmp aGROUP BY tb_name , idx_name , last_user_update;DROP TABLE #tmp;GO------------------------------当前数据库索引的使用率--------------SELECT OBJECT_NAME(object_id) AS table_name , ( SELECT name FROM sys.indexes WHERE object_id = stats.object_id AND index_id = stats.index_id ) AS index_name , *FROM sys.dm_db_index_usage_stats AS statsWHERE database_id = DB_ID()ORDER BY table_name; --当前数据库指定表的索引使用情况DECLARE @table AS NVARCHAR(100);SET @table = 't_ire_candidate';SELECT ( SELECT name FROM sys.indexes WHERE object_id = stats.object_id AND index_id = stats.index_id ) AS index_name , *FROM sys.dm_db_index_usage_stats AS statsWHERE object_id = OBJECT_ID(@table)ORDER BY user_seeks , user_scans , user_lookups ASC;GO-- 查找阻塞，通过 a.blocking_session_id 查看引起阻塞的进程，text是对应的sql脚本SELECT b.text , -- sql执行计划 b.dbid , DB_NAME(b.dbid) AS dbname , a.session_id , -- 执行会话id a.blocking_session_id , -- 引起阻塞的会话id a.status , a.command , a.wait_time , a.wait_type , a.wait_resource , a.total_elapsed_timeFROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) bWHERE session_id IN ( SELECT DISTINCT request_session_id FROM sys.dm_tran_locks ); 摘录http://www.cnblogs.com/chengxiaohui/articles/6377569.html