【SQLServer】使用DMVs查找慢查询

以下查询支持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 )

　　

原文地址：

https://red9.com/blog/how-to-find-slow-queries-using-dmvs-in-sql-server/