January 14, 2011 at 3:58 pm
When a stored procedure is altered, does the execution for the stored procedure become invalid and a new one created when the altered SP is executed for the first time? Also can a stored procedure have different plans because of parameters differences?
January 17, 2011 at 12:05 pm
Yes and yes.
When you alter a stored procedure, the plan in cache, if there is one, is marked as invalid, so a new one is created the next time the procedure runs.
Depending on the parameters sent to a procedure, different statistics can be access within the tables in question, which can lead to differences in execution plans.
"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 17, 2011 at 1:34 pm
A stored procedure can only have one plan cached at a time (baring different set options), so if you have a case like Grant describes, where different parameters can result in different plans, it's the parameter values when the procedure is first compiled that shape the plan that goes into cache. All other executions have to make do and, if the plan is not optimal for them, will perform badly.
SQL will not recompile a procedure simply because the parameter values at execution time differ from what they were at compile time.
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
January 17, 2011 at 4:05 pm
GilaMonster (1/17/2011)
A stored procedure can only have one plan cached at a time (baring different set options), so if you have a case like Grant describes, where different parameters can result in different plans, it's the parameter values when the procedure is first compiled that shape the plan that goes into cache. All other executions have to make do and, if the plan is not optimal for them, will perform badly.SQL will not recompile a procedure simply because the parameter values at execution time differ from what they were at compile time.
Yes, all true. Sorry, I should have clarified.
Also, in most cases, you don't want the optimizer to recompile just because the values in the parameters have changed. For most queries, this is a huge benefit.
"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 18, 2011 at 7:32 am
Thank you guys for the confirmation on this. We had a case of a new stored procedure that was put into production. Within a week we started getting complaints about the ASP.NET web app that uses the proc. From testing several different ways (different servers), we determined the problem only occurred when the proc was called by ADO.NET. The actual problem was filling a .NET data adapter with the results of the proc call. If you ran the proc in SSMS, it ran just fine. But when it was called using the .NET code, it would either really slow or .NET would timeout. It didn't make sense. As a last resort, we recompiled the proc and it began performing again just fine. In our research, we found several people complain of this type of behavior, but none seemed to have a definitive answer.
January 18, 2011 at 8:10 am
Lee Forst (1/18/2011)
Thank you guys for the confirmation on this. We had a case of a new stored procedure that was put into production. Within a week we started getting complaints about the ASP.NET web app that uses the proc. From testing several different ways (different servers), we determined the problem only occurred when the proc was called by ADO.NET. The actual problem was filling a .NET data adapter with the results of the proc call. If you ran the proc in SSMS, it ran just fine. But when it was called using the .NET code, it would either really slow or .NET would timeout. It didn't make sense. As a last resort, we recompiled the proc and it began performing again just fine. In our research, we found several people complain of this type of behavior, but none seemed to have a definitive answer.
Hmmm... from the sounds of that, I'd double check the ANSI settings coming from the .NET code. It's possible that someone slipped up in that bit and is changing the way NULLS behave or something. Changing those settings could affect the plan created and that's why when you recompiled it cleared up. Also, could just be a case of parameter sniffing gone wrong.
"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 18, 2011 at 8:36 am
Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major IF statement based on one of the parameters. If the parameter is set to one value, a certain SELECT is done. But if the parameter is anything else, then a different SELECT is done. Keep in mind the result set looks the same (same columns) either way. What happens is the execution plan for the proc is built on one side of the IF condition? Maybe this is the reason? Still doesn’t explain why the .NET code had a problem but no problem when running the proc from SSMS.
January 18, 2011 at 9:01 am
You'll get a plan for each query in the branch. I wouldn't think that would be the cause, but it might be related in some way.
"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 18, 2011 at 10:11 am
Lee Forst (1/18/2011)
Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major IF statement based on one of the parameters. If the parameter is set to one value, a certain SELECT is done. But if the parameter is anything else, then a different SELECT is done.
This happens:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
The different plans is probably different SET options. You may need to run a trace to see what options the .Net is setting that's different from SSMS.
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
January 18, 2011 at 10:25 am
GilaMonster, thank you so much for the info!
April 5, 2011 at 2:41 pm
GilaMonster (1/18/2011)
Lee Forst (1/18/2011)
Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major IF statement based on one of the parameters. If the parameter is set to one value, a certain SELECT is done. But if the parameter is anything else, then a different SELECT is done.This happens:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
The different plans is probably different SET options. You may need to run a trace to see what options the .Net is setting that's different from SSMS.
How can i find what SET options are being issued from the application using a trace? I don't see any event to capture SET options?
April 5, 2011 at 2:54 pm
sqldba_icon (4/5/2011)
GilaMonster (1/18/2011)
Lee Forst (1/18/2011)
Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major IF statement based on one of the parameters. If the parameter is set to one value, a certain SELECT is done. But if the parameter is anything else, then a different SELECT is done.This happens:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
The different plans is probably different SET options. You may need to run a trace to see what options the .Net is setting that's different from SSMS.
How can i find what SET options are being issued from the application using a trace? I don't see any event to capture SET options?
The connection events have that information. Existing connection or new connection show the SET statements in the text field.
"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 6, 2011 at 6:41 am
GilaMonster (1/18/2011)
Lee Forst (1/18/2011)
Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major IF statement based on one of the parameters. If the parameter is set to one value, a certain SELECT is done. But if the parameter is anything else, then a different SELECT is done.This happens:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
The different plans is probably different SET options. You may need to run a trace to see what options the .Net is setting that's different from SSMS.
As Gail points out, nested sprocs is probably solution to the conditional query branching problem. BTW, kudos to you for even doing the IF branching in the same sproc. Can't count the number of times I have fixed things like "WHERE (myField = @myVar OR @myVar IS NULL) . . . constructs!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply