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_KEYIt 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?"
No comments:
Post a Comment