December 2, 2009 at 2:18 pm
Greetings all!
I have a stored procedure I am developing (for quite a while now). I am sure after testing several thousand times it had generated quite a few plans that are no longer the best suited for what I have now.
If I run it with option(recompile) it generates a much more efficient plan however, taking this option out it uses the old plan again. Is there a way to force this to use the plan it creates with the option(recompile) ?
Link to my blog http://notyelf.com/
December 2, 2009 at 2:21 pm
shannonjk (12/2/2009)
I am sure after testing several thousand times it had generated quite a few plans that are no longer the best suited for what I have now.
There will only ever be at most two plans for a procedure in cache. One serial, one parallel. (ignoring the possibility of different set options from different clients)
If I run it with option(recompile) it generates a much more efficient plan however, taking this option out it uses the old plan again. Is there a way to force this to use the plan it creates with the option(recompile) ?
Post the procedure and the two execution plans please?
If you run sp_recompile 'Procedure Name' and then run the proc again without WITH RECOMPILE, do you get a good plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2009 at 6:48 am
Gail's already focused on the core of the question, you're only going to see two plans in the cache for a given query (assuming the same parameters, yada-yada).
But, you can drop a specific plan. You just need to do this:
--First get the plan handle
SELECT decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%'; -- or some other comparison to identify your query
--then drop the plan
DBCC freeproccache(0x05000E000A70051EB8A0A309000000000000000000000000);
But honestly, that's not applicable to your situation.
"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
December 3, 2009 at 12:04 pm
Sorry for the delayed response on this!
I believe I figured out the problem. Here is what I believe was the issue; The stored proc is used for reporting service reports. There are 8 parameters that can be defined however the script, the first 6 are very high level only slightly shifting the report in a company-wide type reporting structure. The last 2 parameters filter the report down to agencies and people. I realize that 2 plans are needed, 1 to satisfy the high level and a 2nd to satisfy the finite report. If I do the sp_recompile and run the finite report, it runs great, but the high level runs poorly. If I redo the sp_recompile and run the high level report, then it runs great but the finite report runs slow.
Outside of just splitting the stored proc out into 2 separate ones (thus needing to create 2 separate reports), is there a way to work around this? I really don't want to add option(recompile) to my proc...
Link to my blog http://notyelf.com/
December 3, 2009 at 12:19 pm
You've effectively written two different queries, and yeah, each query needs a different execution plan. Recompile is probably your best option.
"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
December 4, 2009 at 12:38 am
Try splitting into two procedures, but no need for two reports. Have the report call a wrapper proc that checks the parameters and calls one of two procedures to do the actual work.
Something like this
CREATE PROCEDURE OuterProcedure (@Param1 .... @Param8)
IF @Param7 IS NULL AND @Param8 IS NULL
EXEC HighLevelReport @Param1 ... @Param6
ELSE
EXEC DetailedReport @Param1 .... @Param8
That's rough semi-pseudocode, but should give you the idea.
These may be worth reading too:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2009 at 12:15 pm
Awesome idea Gail thanks!
Thank you for the links as well, they were very helpful!
Link to my blog http://notyelf.com/
December 4, 2009 at 2:31 pm
Gail,
On your second link when you wrote about the Dynamic SQL; I really like that idea however my code is a little over 10k characters and parametrized
I have been looking for the last hour or so and I can not find an intuitive work around to this.
Any ideas?
Link to my blog http://notyelf.com/
December 4, 2009 at 10:53 pm
Do you really need dynamic SQL? Your question indicated there were two possibilities - the detailed and the high level report. If that's the case, use if - else with two procedures. Dynamic SQL's great when there are two many possible queries to do if - else blocks with
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2009 at 12:57 am
Not this one necessarily, but I have several Stored procedures that are similar to this vien, some of them have multiple variations that I would like to test the viability of dynamic SQL on these more variable stored procs 😀
Link to my blog http://notyelf.com/
December 5, 2009 at 7:37 am
Just be aware that dynamic SQL can lead to the exact same types of problems you were facing with this procedure. It's worth knowing ahead of time. The one way you can minimize the issues, but not eliminate them, is to use parameterized queries in the dynamic SQL, meaning don't build a SQL string and run it using exec. Look up sp_executesql and learn how to pass it parameters. Then you build the dynamic SQL with paramters and you'll get more consistent plans and some plan reuse.
"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
December 7, 2009 at 10:51 am
Thanks Grant 🙂
I know how to use sp_executesql and pass parameters through that. My only issue is getting around the character limit for my more robust queries 🙂
Link to my blog http://notyelf.com/
December 7, 2009 at 11:13 am
shannonjk (12/7/2009)
Thanks Grant 🙂I know how to use sp_executesql and pass parameters through that. My only issue is getting around the character limit for my more robust queries 🙂
Assuming 2005/2008, use VARCHAR(MAX).
"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
January 6, 2010 at 12:37 pm
Varchar(max) isn't acceptable because it has to be nVarchar(Max). But that still has a character limit I have surpassed.
Is there not another way to get around that?
Link to my blog http://notyelf.com/
January 6, 2010 at 12:56 pm
nVarchar(max) only has a size limit, not a character limit and we're talking gb's, so you shouldn't be hitting that limit (not saying you're not, but you sure shouldn't be).
"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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply