sqlserver 查询计划相关

--这个查询为所有缓存的查询计划返回XML显示计划

SELECT qplan.query_plan AS [Query Plan]

FROM sys.dm_exec_query_stats AS qstats

 CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan;

--想这样找到某个查询计划非常困难，因为查询文本被包含在XML显示计划内部。
--下面的查询使用Xquery value方法从显示计划中提取出序列号(No列)和查询文本(Statement Text列)。
--因为每个批处理都有sql_handle，所以用ORDER BY sql_handle可以按这些语句在批处理中的顺序进行排序显示。(有点慢)

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)

SELECT

 C.value('@StatementId', 'INT') AS [No],

 C.value('(./@StatementText)', 'NVARCHAR(MAX)') AS [Statement Text],

 qplan.query_plan AS [Query Plan]

FROM (SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats) AS qstats

 CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan

 CROSS APPLY query_plan.nodes('/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/ sql:Statements/ descendant::*[attribute::StatementText]') AS T(C)

ORDER BY plan_handle, [No];

--强制sqlserver 使用查询计划 (也许只是短期高效率 )
1)set statistics xml on

select top 10 cp.CPContent,uw.ActorName,uw.ActorTime
from CRC_Complaint_CP cp
       inner join CRC_User_WorkOrder_UW uw
       on cp.CPID = uw.uw_wordorderid
       inner join (select distinct * from CRC_Department_DP ) dp on dp.dpid = uw.UWAssignDepartment
set statistics xml off

--根据得到的XML执行计划 使用 use plan 强制使用此执行计划

select top 10 cp.CPContent,uw.ActorName,uw.ActorTime
from CRC_Complaint_CP cp
       inner join CRC_User_WorkOrder_UW uw
       on cp.CPID = uw.uw_wordorderid
       inner join (select distinct * from CRC_Department_DP ) dp on dp.dpid = uw.UWAssignDepartment
      
option (use plan
'复制1)执行计划的xml'
) 

--查看性能统计信息和缓存的查询计划
select t.text,st.total_logical_reads,st.total_physical_reads,
       st.total_elapsed_time,st.total_logical_writes
        from  sys.dm_exec_query_stats st cross apply sys.dm_exec_sql_text(st.sql_handle) t
       
--清空缓存
--dbcc freeproccache    

--创建生成所有表的统计信息
exec sp_createstats

--查看统计信息
dbcc show_statistics('tab_name'| 'view_name',target)

--删除统计信息
drop statistics tab.col_name