Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Very strange behaviour - Multiple Exec plans - Attached is the complete code Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 6,788, Visits: 13,998
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.


A new plan for each new session, even. There's some useful info here.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1441298
Posted Thursday, April 11, 2013 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
ChrisM@Work (4/11/2013)
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.


A new plan for each new session, even. There's some useful info here.


Still doesn't answer my question. My temp is executed in block of where the condition is never used, but still there are multiple exec plans. Please go to the original post for more details.
Post #1441309
Posted Thursday, April 11, 2013 9:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
curious_sqldba (4/11/2013)
ChrisM@Work (4/11/2013)
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.


A new plan for each new session, even. There's some useful info here.


Still doesn't answer my question. My temp is executed in block of where the condition is never used, but still there are multiple exec plans. Please go to the original post for more details.


It is never used when the procedure is executed, but SQL Server still generates a plan for the SQL statement in the IF block. Compilation does not evaluate the IF clause, that is done during execution, after the plan is generated.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441317
Posted Thursday, April 11, 2013 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 6,788, Visits: 13,998
curious_sqldba (4/11/2013)
ChrisM@Work (4/11/2013)
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.


A new plan for each new session, even. There's some useful info here.


Still doesn't answer my question. My temp is executed in block of where the condition is never used, but still there are multiple exec plans. Please go to the original post for more details.


I'm not quite sure what you mean. The article I referenced stated that for any chance of plan reuse, a temp table used by a stored procedure should be declared within it, not outside.
You may be missing a point about plan generation and conditional statements which is covered very well here: "The optimiser processes all branches of a conditional no matter which branch will be taken during execution, even if the branch can never be executed".


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1441320
Posted Thursday, April 11, 2013 9:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different table in those sessions. The table #test in session 1 is not the same #test in session 2.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441323
Posted Thursday, April 11, 2013 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 6,788, Visits: 13,998
Lynn Pettis (4/11/2013)
And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different table in those sessions. The table #test in session 1 is not the same #test in session 2.



"•If a stored procedure refers to a temporary table not created statically in the procedure, the spid (process ID) gets added to the cache key. This means that the plan for the stored procedure would only be reused when executed again by the same session. Temporary tables created statically within the stored procedure do not cause this behavior."
http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1441350
Posted Thursday, April 11, 2013 10:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
Lynn Pettis (4/11/2013)
And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different table in those sessions. The table #test in session 1 is not the same #test in session 2.


Thanks,makes total sense.
Post #1441359
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse