I concur with Shanky. Instead of BCHR, use Page Life Expectancy to check for memory pressureSatish Kartan www.sqlfood.com
I would like to add here that if the system is NUMA again PLE should not be looked upon but PLE for each NUMA node should be looked upon this is becuase PLE as such on NUMA node is avarage of PLE's of all NUMA nodes. The reason I am asking to avoid is becase if server is having NUMA nodes and if there is memory pressure PLE corresponding to that node(Node facing memory crunch) will dip down but avrage PLE would remain high and this would gibe user feeling that PLE is fine when actually there was memory pressure. Remember in NUMA configuration each node beahves as it ha its own memory management. The details are explaiined by Paul in his article PLE Is not what you think
If you really want to test memory pressure there are other Perfmon counters
- SQLServer:Buffer Manager--Page Life Expectancy(PLE) for EACH NUMA nodes.
- SQLServer:Buffer Manager--CheckpointPages/sec:
- SQLServer:Memory Manager--Memory Grants Pending:
- SQLServer:memory Manager--Target Server Memory:
- SQLServer:memory Manager--Total Server memory
- Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design
- Free Pages – Total number of pages on all free lists (free lists track all of the pages in the buffer pool that are not currently allocate to a data page, and are therefore available for usage immediately)
- Free List Stalls/sec – Number of requests per second that had to wait for a free page
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
My Technet Wiki Article
MVP
No comments:
Post a Comment