Perf. counters not affected by memory encrease

Question

Perf. counters not affected by memory encrease

Viewing 8 posts - 16 through 22 (of 22 total)

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

Roust_m SSCoach Points: 17410 More actions · Answer 1

quote:
Nothing unusual here, then. Due to all available memory being used for data caching, the lazywriter is aging out query plans, so the Cache Hit Ratio equilibrates to approximately the same value as before the memory addition. I.e. your databases are large enough that they are using all the cache they can get for data caching, including "stolen" pages from the procedure cache.

- Very unusual though, after adding memory from 2GB to 4Gb the Cash Hit Ratio went up from 70% to 75%, but after moving from 4GB to 16GB (four times!!!), it went down from 75% to 72%, with all the same data eating "all the cache they can get for data caching"

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

quote:
- So what it is? Bad application/database design? Is there a way to correct this?
Some MS SQL options?

It's nothing to be concerned about. If there is any pressure from another process needing memory (data caching in this case), then the lazywriter will remove cached query plans that have had their counters decremented to zero. The counter is incremented (by the cost) each time the query plan is used and decremented by one each time the lazywriter sweeps the memory blocks. So any plans that have reached zero are tossed because there are (theoretically) better uses for that memory. If you have enough memory available that lazywriter is not invoked, then no plans will ever be discarded.

These algorithms are meant to ensure that memory (a scarce resource) is used most efficiently. If plans are seldom used and/or have a very low cost associated with their compilation, then that memory would better be used for another purpose that (theoretically) will offer increased performance.

--Jonathan

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
Just to notice:
I have a -T2861 flag turned on, that does not allow to flush out of cache zero-cost plans.

Makes no difference other than:

Creating more work for the system by allocating cache to the zero cost plans.
Creates more work for the lazywriter because it will always free up the cache used for zero cost plans.
Opening you up to potential issues by using an obscure flag on a production system.

--Jonathan

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

After enabling /PAE and /3GB, There is only 1Gb left for OS. System has to allocate part of it to manage the memory beyond 4GB boundary. I am wondering similar thing happening to SQL Server process too, which has to use part of memory it allocated to administrater the memory above 4GB after AWE is turned on.

Which cache hit ratio are you referring to, from cache manager objects or buffer manager objetcs in performance monitor?

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
quote:
Nothing unusual here, then. Due to all available memory being used for data caching, the lazywriter is aging out query plans, so the Cache Hit Ratio equilibrates to approximately the same value as before the memory addition. I.e. your databases are large enough that they are using all the cache they can get for data caching, including "stolen" pages from the procedure cache.
- Very unusual though, after adding memory from 2GB to 4Gb the Cash Hit Ratio went up from 70% to 75%, but after moving from 4GB to 16GB (four times!!!), it went down from 75% to 72%, with all the same data eating "all the cache they can get for data caching"

Again, I do not find this unusual in the least. Unless you can guarantee that the entire application environment (data, queries performed, etc.) is exactly the same as before, the number will vary slightly and approach the same value over time. In all cases the lazywriter is aweeping through the cache pages and freeing procedure cache pages that have decremented to zero. IIRC, the lazywriter will stop a sweep when the available pages reaches its target, so even knowing what pages will be flushed or discarded is subject to chance.

--Jonathan

Roust_m SSCoach Points: 17410 More actions · Answer 6

quote:
Which cache hit ratio are you referring to, from cache manager objects or buffer manager objetcs in performance monitor?

- from cache manager objects of course:

SQLServer:Cache Manager-> Cache Hit Ratio

Roust_m SSCoach Points: 17410 More actions · Answer 7

[/quote]

Again, I do not find this unusual in the least. Unless you can guarantee that the entire application environment (data, queries performed, etc.) is exactly the same as before, the number will vary slightly and approach the same value over time. In all cases the lazywriter is aweeping through the cache pages and freeing procedure cache pages that have decremented to zero. IIRC, the lazywriter will stop a sweep when the available pages reaches its target, so even knowing what pages will be flushed or discarded is subject to chance.

[/quote]

- Ok, let it be so, but is there a way to keep procedure cache size fixed, to prevent it from being occupied by data? Some flags, options, etc.?

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
- Ok, let it be so, but is there a way to keep procedure cache size fixed, to prevent it from being occupied by data? Some flags, options, etc.?

Not that I am aware of. I wouldn't think of the process as "the procedure cache...being occupied by data" but instead that the system is dynamically reallocating buffer pool pages from procedure caching to data caching.

--Jonathan