In the first part of this blog series, we looked into the basic plan guide setup, the fact that hints already on the queries will be overwritten by the plan guide and some other intricacies. In this part we will be taking a closer look at somethings you cannot do as well, as how to add a plan guide to procedures, and lastly how you could find queries that are using plan guides.
CREATE TABLE DBO.TABLE1( ID INT IDENTITY(1,1), VALUE NVARCHAR(255));
CREATE TABLE DBO.TABLE2( ID INT IDENTITY(1,1), TABLE1ID INT, VALUE INT);
SET NOCOUNT ON;
DECLARE @I INT = 1
WHILE @I < 10000
BEGIN
INSERT INTO DBO.TABLE1(VALUE)
VALUES('PLAN GUIDE DEMO')
INSERT INTO DBO.TABLE2(TABLE1ID,VALUE)
VALUES(@I,@I)
INSERT INTO DBO.TABLE2(TABLE1ID,VALUE)
VALUES(5,5)
SET @I += 1
END
--CLUSTERED INDEXES
CREATE CLUSTERED INDEX CX_TABLE1_ID
on dbo.TABLE1(ID)
CREATE CLUSTERED INDEX CX_TABLE2_ID
on dbo.TABLE2(ID)
-- SEEK NC INDEX
CREATE INDEX IX_TABLE2_VALUE_TABLE1ID
on dbo.TABLE2(Value,TABLE1ID)
Consider the following query:
DECLARE @values table(id int, val varchar(255))
INSERT INTO @values(id,val)
values(1,'test');
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T2.VALUE IN (select id from @values);
We could try and create a plan guide for it:
EXEC sp_create_plan_guide
@name = 'RecompileForce',
@stmt = '',
@type = 'SQL',
@module_or_batch ='DECLARE @values table(id int, val varchar(255))
INSERT INTO @values(id,val)
values(1,''test'')
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2
T2
ON T1.ID = T2.TABLE1ID
WHERE T2.VALUE IN (select id from @values)',
@hints = N'OPTION(RECOMPILE)';
An error occurs:
Msg 10509, Level 16, State 1, Line 11 Cannot create plan guide ‘RecompileForce’ because the statement specified by @stmt or @statement_start_offset either contains a syntax error or is ineligible for use in a plan guide. Provide a single valid Transact-SQL statement or a valid starting position of the statement within the batch. To obtain a valid starting position, query the ‘statement_start_offset’ column in the sys.dm_exec_query_stats dynamic management function.
Why?
In this case there are two reasons why I can’t create the plan guide. The obvious one being that you can’t do it on two statements, the second reason being that ‘USE PLAN’ can’t use DML. As a result, plan guides also cannot use DML.
An extra example of this:
EXEC sp_create_plan_guide
@name = 'RecompileForce',
@stmt = '',
@type = 'SQL',
@module_or_batch ='update dbo.table1 set VALUE = 1',
@hints = N'OPTION(RECOMPILE)';
Results in the same error.
No parameters
Create proc Dbo.ProcNoParams
as
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
Simply execute it like so:
Exec Dbo.ProcNoParams;
Okay, so how do we add a plan guide for this?
We use the OBJECT @type. But note that the query statement needs to be added, aswell as the proc name.
EXEC sp_create_plan_guide @name = N'[ProcedureNoParamsPlanGuide]',
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID ',
@type = N'OBJECT',
@module_or_batch = N'[Dbo].[ProcNoParams]',
@hints = N'OPTION (RECOMPILE)'
Ok, but how do we make sure that this worked? After all, plan guides are not going to ring any bells when they are being used?
Inside the query plan XML two new records are added, PlanGuideDB and PlanGuideName:
Which makes querying the plan XML possible:
The query we could use to find our ‘ProcedureNoParamsPlanGuide’ in action:
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 10
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time, dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time, dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@PlanGuideNa
me)[1]', 'varchar(max)') ='ProcedureNoParamsPlanGuide'
Houston we have lift-off
Query to get all the plans in cache that use a plan guide
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time, dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE
query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@PlanGuideNa me)[1]', 'varchar(max)') is not null
Depending on your hardware and the amount of plan’s in cache, this might take a while.
Consider the following procedure:
Create proc Dbo.ProcParams
(
@Value int = NULL
)
as
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T2.VALUE = @Value;
Exec Dbo.ProcParams @Value = 1
Create the plan guide
EXEC sp_create_plan_guide @name = N'[ProcedureParamsPlanGuide]',
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID WHERE T2.VALUE = @Value;',
@type = N'OBJECT',
@module_or_batch = N'[Dbo].[ProcParams]',
@hints = N'OPTION (RECOMPILE)'
Note that you do not need to declare any variables in the @stmt, just add all the query text in the stored proc after the ‘as’. Change any single quote (‘) characters to double quotes (‘’).
Executing it once and checking the plan cache to see if the guide was used
Exec Dbo.ProcParams @Value = 1
Executing it again with a different parameter
Exec Dbo.ProcParams @Value = 2
Wait, the execution count remains 1?
– That is because we are using option(recompile), the creation_time did change due to recompilation of the plan.
Multiple queries
CREATE proc Dbo.ProcDoubleQuery
as
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T1.ID = 1;
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T1.ID = 2;
Not possible in its entirety:
Cannot create plan guide ‘ProcedureDoubleQueryPlanGuide’ because parameter @stmt has more than one statement.
Workaround, creating two plan guides:
EXEC sp_create_plan_guide @name = N'[PLanGuide_ProcDoubleQuery_1]',
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T1.ID = 1;',
@type = N'OBJECT',
@module_or_batch = N'[Dbo].[ProcDoubleQuery]',
@hints = N'OPTION (RECOMPILE)'
EXEC sp_create_plan_guide @name = N'[PLanGuide_ProcDoubleQuery_2]',
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
WHERE T1.ID = 2;',
@type = N'OBJECT',
@module_or_batch = N'[Dbo].[ProcDoubleQuery]',
@hints = N'OPTION (RECOMPILE)'
My script is not optimized for multiple plan guides, as in it will always show the first one found.
Looking at the XML however, we will find both plan guides:
CREATE proc Dbo.ProcDeclare
as
declare @blabla int
set @blabla = 5
SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
where t2.value = @blabla
Not possible in its entirety:
Cannot create plan guide ‘ProcedureProcDeclare’ because parameter @stmt has more than one statement.
A Workaround
But, if we omit the declaration of @blabla:
EXEC sp_create_plan_guide @name = N'[PLanGuide_ProcDeclare]',
@stmt = N'SELECT * FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
where t2.value = @blabla;',
@type = N'OBJECT',
@module_or_batch = N'[Dbo].[ProcDeclare]',
@hints = N'OPTION (RECOMPILE)'
The plan guide gets used:
CREATE proc Dbo.ProcCTE
as
WITH CTE
AS
(
SELECT T1.ID FROM DBO.TABLE1 T1 INNER JOIN DBO.TABLE2 T2
ON T1.ID = T2.TABLE1ID
where t2.value = 5 )
SELECT * FROM CTE;
Works from the get go:
Commands completed successfully.
© 2023 Kohera
Crafted by
© 2022 Kohera
Crafted by
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |