May 1, 2015 at 1:19 pm
Hi,
In sp_create_plan_guide documentation, it's written:
When SQL Server matches the value of statement_text to batch_text and @parameter_name data_type [,...n ], or if @type = 'OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:
White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.
Comments (-- or /* */).
Trailing semicolons
On SQL Server 2008 SP3, I created a plan guide for a query. Now, if I execute the query exactly how it was defined in the plan guide, SQL Server match it and use the plan guide to optimize the query.
However, if I add just a space between a column name and an operator in the WHERE clause, the plan guide is ignored. How come it doesn't ignore the extra space, like mentionned in the documentation?
Thank you
May 1, 2015 at 3:32 pm
That is specifically for the OBJECT type plan guide. For the other 2 types (SQL, TEMPLATE) they must match exactly.
https://msdn.microsoft.com/en-us/library/ms179880.aspx
For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter_name data_type [,...n ] must be provided in exactly the same format as their counterparts submitted by the application. This means you must provide the batch text exactly as the SQL Server compiler receives it. To capture the actual batch and parameter text, you can use SQL Server Profiler.
May 3, 2015 at 5:35 am
And it really has to match 100% exactly in order for this to work successfully. It's somewhat fragile in that regard.
"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
May 4, 2015 at 9:27 am
Thank you for your replies, I guess I misundertood the documentation.
I'll make sure query text is 100% identical.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply