Procedure Plans & Execution Contexts Low

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben Anderson - 28 Feb 2005 16:15 GMT

Hi,

I'm trying to to troubleshoot why the two counters for Procedure Plans &
Execution Contexts are low (they stay at 50%). because these two counters
are low they cause the cache hit ratio to stay at 50%. All other cache
ratios are close to 100%. This includes the buffer cache hit ratio which
stays at 99%.

I have had many suggestions of memory shortages but I'm not completely
convinced, as the target server memory is the same as the total, and the
buffer cache ratio is nice and high. There are also no recompiles happening,
and only a few compiles happening (max 10 per sec) but considering this is a
fairly busy biztalk server this should be expected.

There is very little info anywhere on these two counters so I wondered if
anyone out there may know more?

What can make these counters so low consistently, and can these be made
lower by thigns such as slow disks, too much ad-hoc SQL or others?

Reply to this Message

Andrew J. Kelly - 28 Feb 2005 16:44 GMT

I have found those to be less than ideal. I basically never use those as an
indication of how well they are doing. You can get a better feel by looking
at the # of compiles and recompiles per second counters. Plus have a look
at the syscacheobjects system table and see which sp's have low usecounts.

Signature

Andrew J. Kelly SQL MVP

> Hi,
>
[quoted text clipped - 17 lines]
> What can make these counters so low consistently, and can these be made
> lower by thigns such as slow disks, too much ad-hoc SQL or others?

Reply to this Message