April 25, 2012 at 2:23 pm
I'm trying to create a plan guide for a problem SQL statement inside a stored procedure.
The problematic SQL causes the proc to time out from time to time, even though we frequently update the stats of indexes with fullscan and re-index on a daily basis.
Normally the proc executes within seconds, but once or twice a week it times out after 30 sec.
There are no missing indexes or any other obvious ways to improve performance.
So with some reluctance I have decided to look into plan guides and have been reading up on this online:
http://msdn.microsoft.com/en-us/library/ms179880(v=sql.100).aspx
I have tried following example A in above link, but I seem to always end up with this error:
Msg 10507, Level 16, State 1, Procedure procName, Line 3
Cannot create plan guide 'guide__procName' because the statement specified by @stmt and @module_or_batch, or by @plan_handle and @statement_start_offset, does not match any statement in the specified module or batch. Modify the values to match a statement in the module or batch.
Here is how I do it, leaving out the non-essential details:
EXEC sp_create_plan_guide
@name = N'guide__procName',
@stmt = N'
select ...
and col1 = @userid
...
',
@type = N'OBJECT',
@module_or_batch = N'dbo.procName',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@UserId = 4))';
Any ideas anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 25, 2012 at 2:44 pm
The weirdest thing; the SQL stmt is actually enclosed inside a "SELECT * INTO #tempTable FROM ..." statement.
As soon as I included that as well, it worked:
EXEC sp_create_plan_guide
@name = N'guide__procName',
@stmt = N'select *
into #temp_name
from
(
select ...
and col1 = @userid
...
) A where A.rn = 1
',
@type = N'OBJECT',
@module_or_batch = N'dbo.procName',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@UserId = 4))';
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 25, 2012 at 9:21 pm
Very cool. I have considered plan guides to help with issues brought in by third-party apps, but have never had to implement one as a different path to resolve the issue has always presented itself. Thanks for posting the solution. I am inferring that since SQL Server compiles plans at the statement level, and that while the select is a derived table it is not a stand-alone statement, it wanted the entire insert-select in the guide...that's my theory anyway 😀
Could you say why you decided not to add the Optimze for option to the query in the actual proc?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 4:57 am
From what you described, it doesn't sound like you need a plan guid. Just add the query hint directly to the query.
Creating plan guides, every single bit of white space, returns, spaces, etc., matters for matching the statement. That's usually the trickiest part to getting a plan guide to work.
Also, creating the plan guide, doesn't mean it's being used. Check the execution plan, specifically the SELECT operator properties. You'll see whether or not the plan guide was used.
"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 26, 2012 at 6:10 am
Thanks both; yes, I did not consider the obvious option of adding the OPTIMIZE FOR hint directly in the code:
SELECT ...
...
OPTION (OPTIMIZE FOR (@UserId = 4))
I will try that now.
If that fails helping with performance, I will try, as a last resort, adding the WITH RECOMPILE option in the sproc definition itself.
The sproc completes within a few seconds normally, but every now and then the client app times out on it after 30 sec, and when I look at the cached plan from that failure I see a very thick arrow emanating from a key lookup operator (estimated number of rows > 7 billion (!); nowhere near the number of records in that table). In "normal" plans the estimated no. of rows is a few thousand.
The plan guide I created yesterday was actually invalid, as it turned out.
The following statement gave me "Invalid object name '#tempName'." in the message column:
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id)
Not sure why I got this error.
In any case, I will try inserting the OPTIMIZE FOR option directly in the code and see if that helps.
There is some funky XML manipulation (XQUERY) in that SELECT statement, which may have something to do with the poor query plan.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply