Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DenisSun
Starting Member
2 Posts |
 Posted - 2011-10-05 : 11:58:28
|
| How can I know if there are any queries that are using any template plan guides in a database?I was able to answer above question regarding object or sql plan guide with the following query--------------------------------------SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS sql_txt , qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@PlanGuideDB)[1]' , 'varchar(100)') AS [PlanGuideDB], qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@PlanGuideName)[1]' , 'varchar(100)') AS [PlanGuideName],qp.query_plan, total_worker_time/execution_count AS AvgCPU , total_elapsed_time/execution_count AS AvgDuration , (total_logical_reads+total_physical_reads)/execution_count AS AvgReads , execution_count FROM sys.dm_exec_query_stats qscross apply sys.dm_exec_sql_text(sql_handle) stcross apply sys.dm_exec_query_plan(plan_handle) qpWHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[(@PlanGuideName)]') = 1ORDER BY [PlanGuideDB], [PlanGuideName] DESCgo------------------------------------------But the problem with template plan guide is there won't be any attributes like 'PlanGuidename' appear in the xml plan ( at least in 2008R2 which I tested). Any idea?Denis |
|
|
DenisSun
Starting Member
2 Posts |
|
|
|
|
|
|
|
template plan guide