查看被缓存的查询计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 st. text AS [ SQL ] , cp.cacheobjtype , cp.objtype , COALESCE( DB_NAME(st.dbid), DB_NAME( CAST(pa.value AS INT)) + ' * ', ' Resource ') AS [ DatabaseName ] , cp.usecounts AS [ Plan usage ] , qp.query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid' AND st.text LIKE '%这里是查询语句包含的内容%'
结果是:
可以根据查询字段来根据关键字查看缓冲的查询计划。
查看某一查询是如何使用查询计划的
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20 SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1)
LIKE '%指定查询包含的字段%'
结果是:
查看数据库中跑的最慢的前20个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL( 28, 2)) AS [ Total Duration (s) ] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % CPU ] , CAST((qs.total_elapsed_time - qs.total_worker_time) * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % Waiting ] , qs.execution_count , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL( 28, 2)) AS [ Average Duration (s) ] , SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query , qt.text AS [Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER BY qs.total_elapsed_time DESC
查看数据库中哪个查询最耗费资源有助于你解决问题
被阻塞时间最长的前20个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL( 28, 2)) AS [ Total time blocked (s) ] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % CPU ] , CAST((qs.total_elapsed_time - qs.total_worker_time) * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % Waiting ] , qs.execution_count , CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL( 28, 2)) AS [ Blocking average (s) ] , SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC
结果如图:
找出这类查询也是数据库调优的必须品
最耗费CPU的前20个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL( 28, 2)) AS [ Total CPU time (s) ] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % CPU ] , CAST((qs.total_elapsed_time - qs.total_worker_time) * 100.0 / qs.total_elapsed_time AS DECIMAL( 28, 2)) AS [ % Waiting ] , qs.execution_count , CAST((qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL( 28, 2)) AS [ CPU time average (s) ] , SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0
ORDER BY [Total CPU time (s)] DESC
原理同上，就不上图了
最占IO的前20个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 [ Total IO ] = (qs.total_logical_reads + qs.total_logical_writes) , [ Average IO ] = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count , qs.execution_count , SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC
结果如图:
能帮助找出占IO的查询
查找被执行次数最多的查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20 qs.execution_count , SUBSTRING (qt. text,(qs.statement_start_offset / 2) + 1, (( 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) + 1) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.execution_count DESC;
结果如图:
可以针对用的最多的查询语句做特定优化。
特定语句的最后运行时间
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DISTINCT TOP 20 qs.last_execution_time , qt. text AS [ Parent Query ] , DB_NAME(qt.dbid) AS DatabaseName FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt WHERE qt. text LIKE ' %特定语句的部分% '
ORDER BY qs.last_execution_time DESC
结果如图: