High plan count on query without literal values

Question

High plan count on query without literal values

chris.nilsson

SSC Veteran

Points: 289
More actions
February 19, 2010 at 3:12 pm

#211305

SQL 2008
In Activity Monitor's Recent Expensive Queries, this query shows up with a Plan Count value showing around 130:
SELECT TOP 1 sMeasureNum, sMeasureDesc, DenomDesc, sQuestion1, sQuestion2, sQuestion3,
sQuestion4, sQuestion5
FROM Default_PQRI_Settings WITH (Nolock)
WHERE sGroupID=@sGroupID AND sStatusRepRowID IS NULL AND sMeasureID=@sMeasureID
Since it is a single-table query without any literal values (unless IS NULL counts as a literal value), I am trying to figure out what is causing the high Plan Count, so we can take action to correct it.
Any ideas?
Thanks.
Chris Nilsson
Altos Solutions

Viewing 9 posts - 1 through 9 (of 9 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Chris,

Parameterization is not the only reason for multiple cached plans, though it would be interesting to know whether the statement in question is submitted ad-hoc or as part of a stored procedure, function, trigger, or other similar object.

Different user contexts and connection settings are candidates for investigation too - all can cause a fresh plan to be cached.

Use the sys.dm_exec_plan_attributes dynamic management function, passing the plan_handles, to reveal the differences between the cached plans. This is normally enough to solve the mystery 😉

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chris.nilsson SSC Veteran Points: 289 More actions · Answer 2

Thanks Paul:

It is ad-hoc--it comes in as a query passed in from the web front end, rather than as a stored proc or the like.

I will try the sys.dm_exec_plan_attributes approach and see what bubbles to the surface. Thanks!

Chris Nilsson

Paul White SSC Guru Points: 150468 More actions · Answer 3

chris.nilsson (2/21/2010)
Thanks Paul:
It is ad-hoc--it comes in as a query passed in from the web front end, rather than as a stored proc or the like.
I will try the sys.dm_exec_plan_attributes approach and see what bubbles to the surface. Thanks!
Chris Nilsson

No worries, thanks Chris.

Using sys.dm_exec_plan_attributes can involve a bit of decoding work, but it is absolutely worth the effort, and you should be able to put something useful and re-usable together to handle future problems of the same sort.

You might like to encourage your web developers to examine the possibilities for sending parameterized or prepared statements to SQL Server rather than ad-hoc stuff. You also have an option in Forced Parameterization, but that's a whole separate, and complex, issue... 😉

Thanks for posting an interesting problem in a format that was easy to work with and understand.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 4

chris.nilsson (2/19/2010)
SQL 2008
In Activity Monitor's Recent Expensive Queries, this query shows up with a Plan Count value showing around 130:
SELECT TOP 1 sMeasureNum, sMeasureDesc, DenomDesc, sQuestion1, sQuestion2, sQuestion3,
sQuestion4, sQuestion5
FROM Default_PQRI_Settings WITH (Nolock)
WHERE sGroupID=@sGroupID AND sStatusRepRowID IS NULL AND sMeasureID=@sMeasureID
Since it is a single-table query without any literal values (unless IS NULL counts as a literal value), I am trying to figure out what is causing the high Plan Count, so we can take action to correct it.
Any ideas?
Thanks.
Chris Nilsson
Altos Solutions

Have you determined if it uses any indexes or whether or not the table involved even has any indexes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 5

Jeff Moden (2/21/2010)
Have you determined if it uses any indexes or whether or not the table involved even has any indexes?

Interesting point. I rather focussed on the need to identify why there were so many cached plans for the query, rather than wondering why it appeared in the high-cost query list. Not that the Activity Monitor high-cost list is a particularly good guide...

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chris.nilsson SSC Veteran Points: 289 More actions · Answer 6

Yes, it needed indexes, and I am working on that at the same time. They are in place now.

I am also trying to figure out what triggered the high plan count. 🙂

Thanks for the input.

Chris

Paul White SSC Guru Points: 150468 More actions · Answer 7

It would be interesting to know what the cause of the high plan count turned out to be in your case -, if you get chance to post back about it.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GetheBeber Valued Member Points: 63 More actions · Answer 8

Hi all,

I have the same problem ...

Any solution to resolve it ? :crazy:

Thx !