SQL Plan Cache

Question

SQL Plan Cache

Viewing 9 posts - 16 through 24 (of 24 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

SQLSACT (8/16/2012)
Shouldn't the usecount for the parameterised plan be 3 because there are 3 queries?

Probably. Why don't you investigate and figure out why it's not 3?

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster (8/16/2012)
SQLSACT (8/16/2012)
Shouldn't the usecount for the parameterised plan be 3 because there are 3 queries?
Probably. Why don't you investigate and figure out why it's not 3?

Thanks so much Gail!!

These ad-hoc entries are shell queries, cached just to make the parameterised form of the query easier to find. All the execution plan contains for these shell queries is a pointer to the plan for the parameterised version of the query

Taken from http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/

Thanks for your help!!

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

GilaMonster (8/16/2012)
SQLSACT (8/16/2012)
Shouldn't the usecount for the parameterised plan be 3 because there are 3 queries?
Probably. Why don't you investigate and figure out why it's not 3?

Thanks again for you help with this

Another Question, is there a way that I can query the DMV and group all of the unparameterized shells to it's parameterized plan? So that it's easy to figure out which unparameterized shells stem from which parameterized plan. Or maybe get an exact count of how many times the auto-parameterized plan was used

Also, does the 'Prepared' objtype always mean that Auto-Parameterization has happened?

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Not that I'm aware of, and no. Prepared are parameterised statements, auto, forced or manually parameterised.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

GilaMonster (8/17/2012)
Not that I'm aware of, and no. Prepared are parameterised statements, auto, forced or manually parameterised.

Thanks

To get an accurate count of how many times the parameterized plan was used - I would need to sum up the usecount for the parameterized plan and sum up the usecount of unparameterized plan shells stemming from that parameterized plan and add the 2 together.

Does that sound right?

Thanks

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

No. It should just be the use count of the parameterised query. That's the actual 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

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

GilaMonster (8/17/2012)
No. It should just be the use count of the parameterised query. That's the actual plan.

Ok

Everytime the unparameterized shell query gets executed, the usecount for the parameterized plan doesn't increment. Only the usecount for the unparameterized shell query increments.

I've noticed that the only time the usecount parameterized plan increments is when I change the value in the where clause.

I don't understand why I would only need to use the usecount from the parameterised query to get a count of how many times the plan was used

Thanks

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

Taking a couple steps back... This whole thing is only valid for very, very, very simple queries that qualify for autoparameterisation. Emphasis 'very simple'. It is not at all the case for all ad-hoc queries and if you're investigating ad-hoc queries you're better off making your test queries more complex so that you don't get auto-parameterisation.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 9

GilaMonster (8/17/2012)
Taking a couple steps back... This whole thing is only valid for very, very, very simple queries that qualify for autoparameterisation. Emphasis 'very simple'. It is not at all the case for all ad-hoc queries and if you're investigating ad-hoc queries you're better off making your test queries more complex so that you don't get auto-parameterisation.

Great - Thanks