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')
SUM(refcounts) AS TotalRefCounts,
SUM(usecounts) AS TotalUseCounts
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideDB)', 'varchar(128)') AS dbName,
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideName)', 'varchar(128)') AS PlanGuideName,
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
) 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.