Sqlserver统计语句

  1 --查看被缓存的查询计划
  2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
  3 SELECT TOP 20 
  4     st.text AS [SQL] 
  5     , cp.cacheobjtype 
  6     , cp.objtype 
  7     , COALESCE(DB_NAME(st.dbid), 
  8         DB_NAME(CAST(pa.value AS INT))+'*', 
  9         'Resource') AS [DatabaseName] 
 10     , cp.usecounts AS [Plan usage] 
 11     , qp.query_plan 
 12 FROM sys.dm_exec_cached_plans cp                       
 13 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
 14 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp 
 15 OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa 
 16 WHERE pa.attribute = 'dbid' 
 17   AND st.text LIKE '%sales%'   
 18 
 19 --查看数据库中跑的最慢的前20个查询以及它们的执行计划
 20 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 21 SELECT TOP 20 
 22   CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) 
 23                                      AS [Total Duration (s)] 
 24   , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
 25                                AS DECIMAL(28, 2)) AS [% CPU] 
 26   , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
 27         qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
 28   , qs.execution_count 
 29   , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 
 30                 AS DECIMAL(28, 2)) AS [Average Duration (s)] 
 31   , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
 32     ((CASE WHEN qs.statement_end_offset = -1 
 33       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
 34       ELSE qs.statement_end_offset 
 35       END - qs.statement_start_offset)/2) + 1) AS [Individual Query 
 36   , qt.text AS [Parent Query] 
 37   , DB_NAME(qt.dbid) AS DatabaseName 
 38   , qp.query_plan 
 39 FROM sys.dm_exec_query_stats qs 
 40 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
 41 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
 42 WHERE qs.total_elapsed_time > 0 
 43 ORDER BY qs.total_elapsed_time DESC                
 44 
 45 --被阻塞时间最长的前20个查询以及它们的执行计划
 46 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 47 SELECT TOP 20 
 48   CAST((qs.total_elapsed_time - qs.total_worker_time) /      
 49         1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)] 
 50   , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
 51         AS DECIMAL(28,2)) AS [% CPU] 
 52   , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
 53         qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
 54   , qs.execution_count 
 55   , CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000000.0 
 56     / qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)] 
 57   , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     
 58   ((CASE WHEN qs.statement_end_offset = -1 
 59     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
 60     ELSE qs.statement_end_offset 
 61     END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
 62   , qt.text AS [Parent Query] 
 63   , DB_NAME(qt.dbid) AS DatabaseName 
 64   , qp.query_plan 
 65 FROM sys.dm_exec_query_stats qs 
 66 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
 67 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
 68 WHERE qs.total_elapsed_time > 0 
 69 ORDER BY [Total time blocked (s)] DESC                       
 70 
 71 --最耗费CPU的前20个查询以及它们的执行计划 
 72 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 73 SELECT TOP 20 
 74   CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) 
 75                                            AS [Total CPU time (s)] 
 76   , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
 77                                       AS DECIMAL(28,2)) AS [% CPU] 
 78   , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
 79            qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
 80              , qs.execution_count 
 81   , CAST((qs.total_worker_time) / 1000000.0 
 82     / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)] 
 83   , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
 84     ((CASE WHEN qs.statement_end_offset = -1 
 85       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
 86       ELSE qs.statement_end_offset 
 87       END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
 88   , qt.text AS [Parent Query] 
 89   , DB_NAME(qt.dbid) AS DatabaseName 
 90   , qp.query_plan 
 91 FROM sys.dm_exec_query_stats qs 
 92 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
 93 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
 94 WHERE qs.total_elapsed_time > 0 
 95 ORDER BY [Total CPU time (s)] DESC          
 96 
 97  --最占IO的前20个查询以及它们的执行计划
 98 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 99 SELECT TOP 20 
100   [Total IO] = (qs.total_logical_reads + qs.total_logical_writes) 
101   , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) / 
102                                             qs.execution_count 
103   , qs.execution_count 
104   , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
105   ((CASE WHEN qs.statement_end_offset = -1 
106     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
107     ELSE qs.statement_end_offset 
108     END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
109   , qt.text AS [Parent Query] 
110   , DB_NAME(qt.dbid) AS DatabaseName 
111   , qp.query_plan 
112 FROM sys.dm_exec_query_stats qs 
113 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
114 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
115 ORDER BY [Total IO] DESC 
116 
117 --查找被执行次数最多的查询以及它们的执行计划
118 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
119 SELECT TOP 20 
120     qs.execution_count 
121     , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,   
122     ((CASE WHEN qs.statement_end_offset = -1 
123       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
124       ELSE qs.statement_end_offset 
125       END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
126     , qt.text AS [Parent Query] 
127     , DB_NAME(qt.dbid) AS DatabaseName 
128     , qp.query_plan 
129 FROM sys.dm_exec_query_stats qs 
130 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
131 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
132 ORDER BY qs.execution_count DESC;  
133 
134 --特定语句的最后运行时间
135 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
136 SELECT DISTINCT TOP 20 
137     qs.last_execution_time 
138     , qt.text AS [Parent Query] 
139     , DB_NAME(qt.dbid) AS DatabaseName 
140 FROM sys.dm_exec_query_stats qs                       
141 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
142 WHERE qt.text LIKE '%特定语句的部分%' 
143 ORDER BY qs.last_execution_time DESC
posted @ 2015-09-09 13:41 も不秃不秃阅读(515) 评论(0) 编辑收藏举报
刷新页面返回顶部
Sqlserver统计语句

公告
