【SQLServer】使用DMVs查找慢查询
2022-08-24 13:42 abce 阅读(105) 评论(0) 编辑 收藏 举报以下查询支持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 )