SQL Server查询计划sys.dm_exec_text_query_plan

SQL Server Manager Studio中执行语句，有选项可以显未该语句的“估计的查询计划”和“实际的执行计划”，以及统计信息。

 

但一般在生产环境trouble-shooting时，你必须看当前db正在执行语句的查询计划，是不能使用上面这种方法的。此时需要用到动态管理视图sys.dm_exec_text_query_plan. 

A. 检索运行速度缓慢的 Transact-SQL 查询或批查询的缓存查询计划

如果 Transact-SQL 查询或批查询在 SQL Server 的特定连接上运行的时间很长，请检索该查询或批查询的执行计划，以查找导致延迟的原因。以下示例显示的是如何检索运行速度缓慢的查询或批查询的显示计划。

        首先，使用 sp_who或sp_who2 存储过程检索正在执行查询或批查询的进程的服务器进程 ID (SPID)。

   EXEC sp_who;
   由 sp_who 返回的结果集指示 SPID 为 54。可以在 sys.dm_exec_requests 动态管理视图中使用该 SPID，以便使用以下查询来检索计划句柄：

SELECT * FROM sys.dm_exec_requests WHERE session_id = 54;

       由  sys.dm_exec_requests 返回的表指示缓慢运行的查询或批查询的计划句柄为 0x06000100A27E7C1FA821B10600。以下示例返回特定计划句柄的查询计划，并使用默认值 0 和 -1 来返回查询或批查询中的所有语句。

SELECT query_plan FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);

B. 从计划缓存中检索每个查询计划

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);

C. 检索服务器已从计划缓存中收集了其查询统计信息的每个查询计划

SELECT * FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);

D. 按平均 CPU 时间检索有关前五个查询的信息

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],Plan_handle, query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;