1 1、查找目前SQL Server所执行的SQL语法，并展示资源情况： 33 SELECT s2.dbid , 34 DB_NAME(s2.dbid) AS [数据库名] , 35 --s1.sql_handle , 36 ( SELECT TOP 1 37 SUBSTRING(s2.text, statement_start_offset / 2 + 1, 38 ( ( CASE WHEN statement_end_offset = -1 39 THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text)) 40 * 2 ) 41 ELSE statement_end_offset 42 END ) - statement_start_offset ) / 2 + 1) 43 ) AS [语句] , 44 execution_count AS [执行次数] , 45 last_execution_time AS [上次开始执行计划的时间] , 46 total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] , 47 last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] , 48 min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] , 49 max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] , 50 total_logical_reads AS [总逻辑读] , 51 last_logical_reads AS [上次逻辑读] , 52 min_logical_reads AS [最少逻辑读] , 53 max_logical_reads AS [最大逻辑读] , 54 total_logical_writes AS [总逻辑写] , 55 last_logical_writes AS [上次逻辑写] , 56 min_logical_writes AS [最小逻辑写] , 57 max_logical_writes AS [最大逻辑写] 58 FROM sys.dm_exec_query_stats AS s1 59 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 60 WHERE s2.objectid IS NULL 61 ORDER BY last_worker_time DESC 62 63 2、展示耗时查询： 96 -- List expensive queries 97 DECLARE @MinExecutions int; 98 SET @MinExecutions = 5 99 100 SELECT EQS.total_worker_time AS TotalWorkerTime 101 ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO 102 ,EQS.execution_count As ExeCnt 103 ,EQS.last_execution_time AS LastUsage 104 ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS 105 ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count 106 AS AvgLogicalIO 107 ,DB.name AS DatabaseName 108 ,SUBSTRING(EST.text 109 ,1 + EQS.statement_start_offset / 2 110 ,(CASE WHEN EQS.statement_end_offset = -1 111 THEN LEN(convert(nvarchar(max), EST.text)) * 2 112 ELSE EQS.statement_end_offset END 113 - EQS.statement_start_offset) / 2 114 ) AS SqlStatement 115 -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! 116 --,EQP.[query_plan] AS [QueryPlan] 117 FROM sys.dm_exec_query_stats AS EQS 118 CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 119 CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP 120 LEFT JOIN sys.databases AS DB 121 ON EST.dbid = DB.database_id 122 WHERE EQS.execution_count > @MinExecutions 123 AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) 124 ORDER BY AvgLogicalIo DESC 125 ,AvgCPUTimeMiS DESC 126 127 3、当前进程及其语句： 149 -- Current processes and their SQL statements 150 SELECT PRO.loginame AS LoginName 151 ,DB.name AS DatabaseName 152 ,PRO.[status] as ProcessStatus 153 ,PRO.cmd AS Command 154 ,PRO.last_batch AS LastBatch 155 ,PRO.cpu AS Cpu 156 ,PRO.physical_io AS PhysicalIo 157 ,SES.row_count AS [RowCount] 158 ,STM.[text] AS SQLStatement 159 FROM sys.sysprocesses AS PRO 160 INNER JOIN sys.databases AS DB 161 ON PRO.dbid = DB.database_id 162 INNER JOIN sys.dm_exec_sessions AS SES 163 ON PRO.spid = SES.session_id 164 CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM 165 WHERE PRO.spid >= 50 -- Exclude system processes 166 ORDER BY PRO.physical_io DESC 167 ,PRO.cpu DESC; 168 169 4、存储过程执行情况： 186 -- Stored Procedure Execution Statistics 187 SELECT ISNULL(DBS.name, '') AS DatabaseName 188 ,OBJECT_NAME(EPS.object_id, EPS.database_id) AS ObjectName 189 ,EPS.cached_time AS CachedTime 190 ,EPS.last_elapsed_time AS LastElapsedTime 191 ,EPS.execution_count AS ExecutionCount 192 ,EPS.total_worker_time / EPS.execution_count AS AvgWorkerTime 193 ,EPS.total_elapsed_time / EPS.execution_count AS AvgElapsedTime 194 ,(EPS.total_logical_reads + EPS.total_logical_writes) 195 / EPS.execution_count AS AvgLogicalIO 196 FROM sys.dm_exec_procedure_stats AS EPS 197 LEFT JOIN sys.databases AS DBS 198 ON EPS.database_id = DBS.database_id 199 ORDER BY AvgWorkerTime DESC; 200 201 5、开销较大的查询： 225 /* 226 开销较大的查询 227 */ 228 SELECT ss.SUM_execution_count , 229 t.text , 230 ss.SUM_total_elapsed_time , 231 ss.sum_total_worker_time , 232 ss.sum_total_logical_reads , 233 ss.sum_total_logical_writes 234 FROM ( SELECT S.plan_handle , 235 SUM(s.execution_count) SUM_Execution_count , 236 SUM(s.total_elapsed_time) SUM_total_elapsed_time , 237 SUM(s.total_worker_time) SUM_total_worker_time , 238 SUM(s.total_logical_reads) SUM_total_logical_reads , 239 SUM(s.total_logical_writes) SUM_total_logical_writes 240 FROM sys.dm_exec_query_stats s 241 GROUP BY S.plan_handle 242 ) AS ss 243 CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t 244 ORDER BY sum_total_logical_reads DESC 245