SQL Server - Are there any queries using any Plan Guides now?

Plan Guide in SQL Server is equivalent to SQL Profile in Oracle functionally. In Oracle, v$sqlarea has a column called SQL_PROFILE, based on which  I can query this view to check if any queries are using any SQL Profiles. Here is the an example:

select sql_profile, max(sql_text) sample_sql_text, sum(executions) total_executions, 
sum(buffer_gets)/ sum(executions)   gets_per_execs 
from v$sqlarea where sql_profile is not null group by sql_profile;


SQL_PROFILE                      SAMPLE_SQL_TEXT                     TOTAL_EXECUTIONS GETS_PER_EXECS
-------------------------------- ----------------------------------- ---------------- --------------
profile_sfh_user_1               SELECT U.SFH_USER_ID,B.SFH_BTN_ID,U             2254     10.3189885
                                 B.PMT_INDICATOR,U.EMAIL FROM SFH_US
                                 ER U,SFH_BTN B,SFH_USER_BTN UB WHER
                                 E U.SFH_USER_ID = UB.SFH_USER_ID AN
                                 D UB.SFH_BTN_ID = B.SFH_BTN_ID AND
                                 B.CUST_BTN = :B2 AND U.USER_ID = :B
                                 1 AND U.USER_STATUS_CD = 'A' AND UB
                                 .SVC_STATUS ='A' AND B.BTN_STATUS_C
                                 D = 'A'
 
COE_gg3zpxx6j5c8c_4057913317      SELECT COUNT(SESSION_KEY) FROM SFH           638642     7.52062032
                                 _MYACC_SUMMARY WHERE cust_btn=:cust
                                 _btn AND SESSION_KEY like :session_
                                 key_value AND LAST_ACCESS_DATE >= S
                                 YSDATE-1
 
profile_sfh_summary_1            SELECT * FROM SFH_SUMMARY_JOBS WHER             6088     3.84395532
                                 E TTQ_ACCOUNT_ID='999802381'AND CR_
                                 DATE > TRUNC(SYSDATE)
 
COE_1x13vygbj9k9t_142312094      DELETE FROM SFH_MYACC_SUMMARY WHERE             5047     11.3929067
                                  CUST_BTN = :CUST_BTN AND  SESSION_
                                 KEY LIKE :SESSION_KEY

It can been seen that  there are four SQL Profiles being used in this Oracle database (This is one of our production databases. I modified some table names to protect the innocent).


How can I obtain the same information in SQL Server regarding the usage of Plan Guide ? This is what I try to find out in the past few days. Below is what I got so far.


First of all, the output of the following query shows there are three Plan Guids in my AdventureWorks sample database

--  Show Plan Guides I created in AdventureWorks sample database

use AdventureWorks
go
-- usp_p is my own print-rows-as-column procedure
exec usp_p'   
select name,create_date,query_text,scope_type_desc from sys.plan_guides
'
go



OUTPUT ROWS AS COLUMN    
    
----------------------------------------------
name                          : Guide1
create_date                   : Oct  4 2011  4:19PM
query_text                    : SELECT * FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c, Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
            AND t.CountryRegionCode = @Country_region
scope_type_desc               : OBJECT
----------------------------------------------
name                          : MyPlan_Guide_1
create_date                   : Oct  4 2011  6:41PM
query_text                    : SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]
scope_type_desc               : SQL
----------------------------------------------
name                          : Template_Plan_1
create_date                   : Oct  5 2011 11:05AM
query_text                    : select SUM ( LineTotal ) as LineTotal from Sales . SalesOrderHeader H join Sales . SalesOrderDetail D on D . SalesOrderID = H . SalesOrderID where H . SalesOrderID = @0
scope_type_desc               : TEMPLATE
----------------------------------------------

As shown above in the "scope_type_desc" column, in SQL Server, there are three types of Plan Guide: Object, SQL and Template. In summary,

• An OBJECT plan guide matches queries that execute in the context of Transact-SQL stored procedures, user-defined scalar functions, multi-statement user-defined table-valued functions, and DML triggers.
• An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.
• A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Now, I use the following query in SSMS to check whether any Plan Guides are being used

-- Show if any plan guides are in use now:

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 qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[(@PlanGuideName)]') = 1
ORDER BY [PlanGuideDB], [PlanGuideName] DESC

The idea of this query is from this post: http://www.scarydba.com/2009/02/12/missing-index-information-and-query-stats/ ,  which tries to associate missing index info with the query causing it. Basically I used xquery exist funtion to search if there is a keyword 'PlanGuideName' in the XML text.


Here is the sample output screenshot.

As shown in the screenshot,  the query works for the Object and SQL type Plan Guide. When comes to the template Plan Guide, I encountered difficulties.

The template Plan Guide was created by the following script, which is used to force replacing literals with parameters for a specific type query. By default, database PARAMEDTERIZATION is set to Simple, which will not be applicable to this type of query that has a two-table join.

USE AdventureWorks;
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
    N' SELECT  SUM(LineTotal) AS LineTotal
       FROM    Sales.SalesOrderHeader H
       JOIN    Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
      WHERE   H.SalesOrderID = 56000',
    @stmt OUTPUT,
    @params OUTPUT

EXEC sp_create_plan_guide
    N'Template_Plan_1',
    @stmt,
    N'TEMPLATE',
    NULL,
    @params,
    N'OPTION(PARAMETERIZATION FORCED)';
GO

Then I run the following test to see if the template Plan Guide works:

use AdventureWorks
go

SELECT  SUM(LineTotal) AS LineTotal
FROM    Sales.SalesOrderHeader H
JOIN    Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE   H.SalesOrderID = 56001
GO

When checking the estimated plan, in the Show Plan XML window, I can find the following text for the "StmtSimple" node:

StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00658091"
StatementText="SELECT SUM(LineTotal) AS LineTotal FROM Sales.SalesOrderHeader H
JOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56001" StatementType="SELECT"
TemplatePlanGuideDB="AdventureWorks" TemplatePlanGuideName="Template_Plan_1" ParameterizedText="(@0 int)
select SUM ( LineTotal ) as LineTotal from Sales . SalesOrderHeader H join Sales . SalesOrderDetail D on D . SalesOrderID = H . SalesOrderID
where H . SalesOrderID = @0"
QueryHash="0x1170510D7B65D486" QueryPlanHash="0x1B1C9CA6A989FB7C"


 
It can be noticed that there are "TemplatePlanGuideDB" and "TemplatePlanGuideName" attributes presented, which indicates the template Plan Guide will be used. When checking the acutual plan, in the Show Plan XML window, I see different text than above:

StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL"

StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00658091"
StatementText="select SUM ( LineTotal ) as LineTotal from Sales . SalesOrderHeader H
join Sales . SalesOrderDetail D on D . SalesOrderID = H . SalesOrderID
where H . SalesOrderID = @0" StatementType="SELECT"
QueryHash="0x1170510D7B65D486" QueryPlanHash="0x1B1C9CA6A989FB7C"



Basically in the actual execution plan XML text, there is no such keywords as 'PlanGuideDBName' or 'TemplatePlanGuideDBName'. Due to the lacking of such marks,  the query described previously cannot
be used to answer such a question " Are there any queries using any template Plan Guides now?"