Execution Plan Aging

Question

Execution Plan Aging

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
May 25, 2011 at 9:48 am

#250797

I have been looking around for a way to find out how long and exeuction plan for a specific proc is staying in memory. Can anyone help me with this.
Thanks,
Fraggle

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Just query the plan cache for that proc every x minutes. Then poll the history table you just filled to find out when the plan reuse goes down to 1 (1st use).

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

That's a good idea to know exactly how long it was there. To just get an idea of how long it's been there at a moment in time, take a look at sys.dm_exec_query_stats and you'll see the date and time the plan was created. What you won't be able to see is when it went away, so to get a complete picture you need to do what the Ninja says.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you want the exact time it goes away, profiler (server side trace better) the performance statistics event (under Performance)

Hmm, that event needs a blog post.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

Grant Fritchey (5/26/2011)
That's a good idea to know exactly how long it was there. To just get an idea of how long it's been there at a moment in time, take a look at sys.dm_exec_query_stats and you'll see the date and time the plan was created. What you won't be able to see is when it went away, so to get a complete picture you need to do what the Ninja says.

Grant,

Thanks for this. An additional followup question. When my company writes procedures, it often passes in a QueryType to a stored procedure. This is so that they can keep code in one place for the same section of the application. Think about a proc that you can both insert and update a customer depending upon passing in a QueryType. In this type of scenario, would we have 2 different execution plans in cache (1 for each query type)? Or would it have to recompile for each QueryType if the last QueryType that was run wasn't the same as the current QueryType being passed in.

Thanks,

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

GilaMonster (5/26/2011)
If you want the exact time it goes away, profiler (server side trace better) the performance statistics event (under Performance)
Hmm, that event needs a blog post.

I would love to read the post. Would it be possible to get me a preview by letting me know which profiler events would be used to tell me this for a specific procedure?

Thanks,

Fraggle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Well I haven't even started to think about such a post so...

The profiler event is called "Performance Statistics" It's a single event.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Fraggle-805517 (5/26/2011)
GilaMonster (5/26/2011)
If you want the exact time it goes away, profiler (server side trace better) the performance statistics event (under Performance)
Hmm, that event needs a blog post.
I would love to read the post. Would it be possible to get me a preview by letting me know which profiler events would be used to tell me this for a specific procedure?
Thanks,
Fraggle

+1M, but no pressure! 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Fraggle-805517 (5/26/2011)
When my company writes procedures, it often passes in a QueryType to a stored procedure. This is so that they can keep code in one place for the same section of the application. Think about a proc that you can both insert and update a customer depending upon passing in a QueryType. In this type of scenario, would we have 2 different execution plans in cache (1 for each query type)? Or would it have to recompile for each QueryType if the last QueryType that was run wasn't the same as the current QueryType being passed in.

Neither. There's one execution plan, created when the procedure runs that contains the query plans for both branches of the query.

http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

GilaMonster (5/26/2011)
Fraggle-805517 (5/26/2011)
When my company writes procedures, it often passes in a QueryType to a stored procedure. This is so that they can keep code in one place for the same section of the application. Think about a proc that you can both insert and update a customer depending upon passing in a QueryType. In this type of scenario, would we have 2 different execution plans in cache (1 for each query type)? Or would it have to recompile for each QueryType if the last QueryType that was run wasn't the same as the current QueryType being passed in.
Neither. There's one execution plan, created when the procedure runs that contains the query plans for both branches of the query.
http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

What she said. I'm assuming QueryType is a parameter, so it'll work as Gail outlined.

"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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 10

GilaMonster (5/26/2011)
Neither. There's one execution plan, created when the procedure runs that contains the query plans for both branches of the query.

Gale,

So if the query plan does the branches, can I assume that it used the variables that are passed into the procedure to generate that plan? Also, if those variables have default values, that those values would get used for generating the plan? Lastly, if that is the case, then could this lead to bad execution plans being generated upon compile?

Thanks,

Fraggle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Fraggle-805517 (5/26/2011)
So if the query plan does the branches, can I assume that it used the variables that are passed into the procedure to generate that plan? Also, if those variables have default values, that those values would get used for generating the plan? Lastly, if that is the case, then could this lead to bad execution plans being generated upon compile?

Go read the second post that I linked to. Answers all of those questions.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass