March 29, 2013 at 10:49 am
I have a stored procedure which runs 1000+ times a day. I can see that every time it is creating a new execution plan. I took the stored procedure ,opened two sessions in ssms and executed the sproc. I do see it is creating two new plans.
One thing different about this sproc is it is inserting data into a temp table which is created by another sproc. Any thoughts on how to avoid this? Could this be parameter sniffing?
March 29, 2013 at 12:31 pm
Can you provide some more information about what your procedures are doing. Code would be helpful. Are you using dynamic SQL? What is the Server Advance setting for Optimize for Ad hoc Workloads set to? What is the Database option for Parameterization set to?
March 29, 2013 at 1:19 pm
sqldba_newbie (3/29/2013)
I can see that every time it is creating a new execution plan. I took the stored procedure ,opened two sessions in ssms and executed the sproc. I do see it is creating two new plans.
How are you seeing that?
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
March 29, 2013 at 1:35 pm
DBA_Dom (3/29/2013)
Can you provide some more information about what your procedures are doing. Code would be helpful. Are you using dynamic SQL? What is the Server Advance setting for Optimize for Ad hoc Workloads set to? What is the Database option for Parameterization set to?
Not adhooc, this is executed from the app.
March 29, 2013 at 1:36 pm
GilaMonster (3/29/2013)
sqldba_newbie (3/29/2013)
I can see that every time it is creating a new execution plan. I took the stored procedure ,opened two sessions in ssms and executed the sproc. I do see it is creating two new plans.How are you seeing that?
Couple different ways. Within Confio (Ignite) and also when i run manually, i can see the plan handles are different.
March 29, 2013 at 1:56 pm
Unfortunately, we can't see what you see. You have posted no code (the stored procedure, the CREATE TABLE statements for the table or tables it uses, sample data, expected results). Anything we give you right now will be simply shots in the dark.
March 29, 2013 at 5:02 pm
sqldba_newbie (3/29/2013)
Within Confio (Ignite) and also when i run manually, i can see the plan handles are different.
Can't help you with just that info. There's a lot of things that could be happening here, deferred compile being just one of them, but need a lot more info on what you're seeing and why the conclusion is multiple plans.
Maybe start by having a read through these, at worst they'll give you more things to look at.
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
http://sqlinthewild.co.za/index.php/2010/11/18/recompiles/
http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply