以下查询支持SQL Server 2014以及更高版本，部分不兼容低版本
1.逻辑读+物理读高的TOP SQL
SQL Server Logical/Physical Reads
逻辑读：从缓存中读取数据页
物理读：从磁盘上读取数据页
Buffer cache的命中率：(logical reads – physical reads)/logical read * 100%
过度的逻辑会增加内存的使用，减少逻辑读的方法：使用正确的索引、或查询改写
SELECT TOP
( 10 ) DB_NAME( t.[dbid] ) AS [Database],
REPLACE( REPLACE( LEFT ( t.[text], 255 ), CHAR ( 10 ), '' ), CHAR ( 13 ), '' ) AS [ShortQueryTXT],
qs.total_logical_reads AS [TotalLogicalReads],
qs.min_logical_reads AS [MinLogicalReads],
qs.total_logical_reads/ qs.execution_count AS [AvgLogicalReads],
qs.max_logical_reads AS [MaxLogicalReads],
qs.total_physical_reads AS [TotalPhysicalReads],
qs.min_physical_reads AS [MinPhysicalReads],
qs.total_physical_reads/ qs.execution_count AS [AvgPhysicalReads],
qs.max_physical_reads AS [MaxPhysicalReads],
qs.min_worker_time AS [MinWorkerTime],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.max_worker_time AS [MaxWorkerTime],
qs.min_elapsed_time AS [MinElapsedTime],
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time AS [MaxElapsedTime],
qs.execution_count AS [ExecutionCount],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
qs.creation_time AS [CreationTime],
t.[text] AS [Complete Query Text],
qp.query_plan AS [QueryPlan]
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
(total_logical_reads + total_logical_writes) / Execution_count DESC OPTION ( RECOMPILE )
2.cpu消耗高的TOP SQL
SELECT TOP
( 10 ) DB_NAME( t.[dbid] ) AS [Database],
REPLACE( REPLACE( LEFT ( t.[text], 255 ), CHAR ( 10 ), '' ), CHAR ( 13 ), '' ) AS [ShortQueryText],
qs.total_worker_time AS [Total Worker Time],
qs.min_worker_time AS [MinWorkerTime],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.max_worker_time AS [MaxWorkerTime],
qs.min_elapsed_time AS [MinElapsedTime],
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time AS [MaxElapsedTime],
qs.min_logical_reads AS [MinLogicalReads],
qs.total_logical_reads/ qs.execution_count AS [AvgLogicalReads],
qs.max_logical_reads AS [MaxLogicalReads],
qs.execution_count AS [ExecutionCount],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
qs.creation_time AS [CreationTime],
t.[text] AS [Query Text],
qp.query_plan AS [QueryPlan]
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 )
3.执行次数多的TOP SQL
SELECT TOP
( 10 ) LEFT ( t.[text], 50 ) AS [ShortQueryText],
qs.execution_count AS [ExecutionCount],
qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/ qs.execution_count AS [AvgLogicalReads],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.total_elapsed_time AS [TotalElapsedTime],
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
qs.creation_time AS [CreationTime],
t.[text] AS [CompleteQueryText],
qp.query_plan AS [Query Plan]
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
WHERE
t.dbid = DB_ID( )
ORDER BY
[ExecutionCount] DESC OPTION ( RECOMPILE )
4.存储过程运行的平均时间较高的TOP SQL
SELECT TOP
( 10 ) p.name AS [SPName],
qs.min_elapsed_time,
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time,
qs.last_elapsed_time,
qs.total_elapsed_time,
qs.execution_count,
ISNULL( qs.execution_count/ DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ), 0 ) AS [Calls/Minute],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
FORMAT( qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [LastExecutionTime],
FORMAT( qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [PlanCachedTime],
qp.query_plan AS [QueryPlan]
FROM
sys.procedures AS p WITH ( NOLOCK )
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK ) ON p.[object_id] = qs.[object_id] CROSS APPLY sys.dm_exec_query_plan ( qs.plan_handle ) AS qp
WHERE
qs.database_id = DB_ID( )
AND DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ) > 0
ORDER BY
[AvgElapsedTime] DESC OPTION ( RECOMPILE )
5.存储过程消耗CPU高的TOP SQL
低版本不实用
SELECT TOP
( 10 ) p.name AS [SPName],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.execution_count AS [ExecutionCount],
ISNULL( qs.execution_count/ DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ), 0 ) AS [Calls/Minute],
qs.total_elapsed_time,
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
FORMAT ( qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [LastExecutionTime],
FORMAT ( qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [PlanCachedTime],
qp.query_plan AS [Query Plan]
FROM
sys.procedures AS p WITH ( NOLOCK )
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK ) ON p.[object_id] = qs.[object_id] CROSS APPLY sys.dm_exec_query_plan ( qs.plan_handle ) AS qp
WHERE
qs.database_id = DB_ID( )
AND DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ) > 0
ORDER BY
qs.total_worker_time DESC OPTION ( RECOMPILE )
6.存储过程执行次数较多的TOP SQL
SELECT TOP
( 10 ) p.name AS [SPName],
qs.execution_count AS [ExecutionCount],
ISNULL( qs.execution_count/ DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ), 0 ) AS [Calls/Minute],
qs.total_elapsed_time/ qs.execution_count AS [AvgElapsedTime],
qs.total_worker_time/ qs.execution_count AS [AvgWorkerTime],
qs.total_logical_reads/ qs.execution_count AS [AvgLogicalReads],
CASE
WHEN CONVERT ( nvarchar ( MAX ), qp.query_plan ) LIKE N'%%' THEN
1 ELSE 0
END AS [HasMissingIX],
FORMAT ( qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [LastExecutionTime],
FORMAT ( qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US' ) AS [PlanCachedTime],
qp.query_plan AS [QueryPlan]
FROM
sys.procedures AS p WITH ( NOLOCK )
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK ) ON p.[object_id] = qs.[object_id] CROSS APPLY sys.dm_exec_query_plan ( qs.plan_handle ) AS qp
WHERE
qs.database_id = DB_ID( )
AND DATEDIFF( MINUTE, qs.cached_time, GETDATE( ) ) > 0
ORDER BY
[ExecutionCount] DESC OPTION ( RECOMPILE )
7.存储过程IO高TOP SQL
SELECT TOP
( 10 ) OBJECT_NAME( qt.objectid, dbid ) AS [SPName],
( qs.total_logical_reads + qs.total_logical_writes ) / qs.execution_count AS [AvgIO],
qs.execution_count AS [ExecutionCount],
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 [QueryText]
FROM
sys.dm_exec_query_stats AS qs WITH ( NOLOCK ) CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) AS qt
WHERE
qt.[dbid] = DB_ID( )
ORDER BY
[AvgIO] DESC OPTION ( RECOMPILE )
