March 31, 2017 at 8:14 am
Hello.
I have read all of the posts I can find about setting up a plan guide for some SQL, and the gotchas about exact matching, including white space etc.
I have also read posts about taking the SQL from the plan cache to ensure it matches.
However, whatever I try the query does not use the plan guide created, as confirmed by looking at the XML and at the Select Properties in the Execution Plan,
and running a DMV to show executed plan guides for the instance (none returned).
The SQL has one parameter to it and I have catered for this in the plan guide - here is what gets scripted from the created plan guide :
EXEC sp_create_plan_guide @name = N'[SELECT_VLIBRARY_MAxDOP_Fix]', @stmt = N'SELECT * FROM [vLibrary] WITH(nolock) WHERE [Libr_CommunicationId]=@1', @type = N'SQL',
@module_or_batch = N'SELECT * FROM [vLibrary] WITH(nolock) WHERE [libr_communicationId]=@1', @params = N'@1 int', @hints = N'OPTION (RECOMPILE, MAXDOP 1)'
GO
It isn't a stored procedure, but even when I pass a NULL to the crate it populates the @module_or_batch with the SQL text passed to @stmt
I have tried replacing the @1 with a different name but it makes no difference.
All I am trying to add is turn off parallelism and parameter sniffing - so adding query hints as this is vendor application generated code.
Is there anything less obvious that would prevent a plan guide from being used - or can I trace the process somehow to figure this out, or am I just
not understanding this somehow?
The only thing that I could see that was 'odd' was that the column used in the parameter has a name that is all lower case in the table (which differs from all
others in the table) - whereas the SQL has a mix of upper and lowercase for the column. However I did try various combinations of UC and LC to no avail.
Appreciate any input.
Thanks
Steve O.
March 31, 2017 at 8:59 am
Usually the biggest issue with plan guides is that your T-SQL in the guide has to match the T-SQL it's seeking to replace perfectly, every possible way, all the capitalization and the white space. Any difference at all, and all bets are off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2017 at 9:50 am
Thanks Grant.
I had read that on many posts (some by yourself IIRC) and as a result I copy the select from the XML or the plan guide into SSMS query to test it.
All I do is replace the parameter (@1 int) with a 4 digit Integer to test.
I will carry on testing - it's now more about not being beaten by plan guides than fixing the problem, as I added an Index for the time being......
Regards
Steve O.
April 1, 2017 at 2:28 am
SteveOC - Friday, March 31, 2017 9:50 AMThanks Grant.I had read that on many posts (some by yourself IIRC) and as a result I copy the select from the XML or the plan guide into SSMS query to test it.
All I do is replace the parameter (@1 int) with a 4 digit Integer to test.I will carry on testing - it's now more about not being beaten by plan guides than fixing the problem, as I added an Index for the time being......
Regards
Steve O.
But that modification could be the issue. Is the query going through simple parameterization?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2017 at 3:31 am
Thanks Grant!
You are quite correct and if I create an unparameterized plan guide that exactly matches the select, with a hardcoded integer,
and execute the select, it does use the plan guide.
Of course, I need to use a parameterized plan guide, since the integer changes - so the next question is, how do I test it, since
my method failed.
FYI - I started with this and am very confused - I first though I needed to set up a Template and a SQL Plan Guide but.....
Thanks
Steve O.
April 1, 2017 at 8:24 am
Got there in the end - back to Plan A - that I couldn't get to work in the beginning.
I had to create a Template Plan Guide to Force parameterisation
I then had to create a SQL Plan Guide (on the force parameterized version of the query) to add the Query Hints
In combination with my Index I now get sub-second response in my test query (started as somewhere between 10-19 seconds)
Looks like things get easier when we move over to 2016 and have Database Scoped Configurations to set MAXDOP at DB level.
Hopefully we can start looking at that next week,
Thanks again Grant.
April 1, 2017 at 1:23 pm
SteveOC - Saturday, April 1, 2017 8:24 AMGot there in the end - back to Plan A - that I couldn't get to work in the beginning.
I had to create a Template Plan Guide to Force parameterisation
I then had to create a SQL Plan Guide (on the force parameterized version of the query) to add the Query Hints
In combination with my Index I now get sub-second response in my test query (started as somewhere between 10-19 seconds)Looks like things get easier when we move over to 2016 and have Database Scoped Configurations to set MAXDOP at DB level.
Hopefully we can start looking at that next week,Thanks again Grant.
And don't forget plan forcing which changes all of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply