THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

How many times has that Plan Guide been used?

Plan guides aren't something that we tend to use everyday, and I certainly never think about the ones that I have created in my databases over the last few years.  However, recently someone asked how to measure how many times a plan guide has been used.  I expected to find some DMV/DMF that would have that kind of information aggregated in it (kindof like the index usage stats), but I couldn't find one.  Then I decided to jump into the XML Plan schema and see what it held.  There happened to be an attribute on the StmtSimple node called TemplatePlanGuideName.  Using some XQuery you can query the plan cache to find out how many times the plan guide exists in the cache.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
  
dbName,
  
PlanGuideName,
  
SUM(refcounts) AS TotalRefCounts,
  
SUM(usecounts) AS TotalUseCounts
FROM
(
  
SELECT 
      
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideDB)[1]', 'varchar(128)') AS dbName,
      
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideName)[1]', 'varchar(128)') AS PlanGuideName,
      
refcounts,
      
usecounts
  
FROM sys.dm_exec_cached_plans
  
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
  
WHERE query_plan.exist('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@TemplatePlanGuideName])[1]')=1
) AS tab
GROUP BY dbName, PlanGuideName

This isn't fool proof since the cache changes somewhat, but in most of my servers, the plan cache should be fairly stable over time and I was able to find use counts for all of my plan guides.  Hope it helps someone.

Published Monday, October 26, 2009 11:11 PM by Jonathan Kehayias
Filed under:

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement