question

Grant Fritchey avatar image
Grant Fritchey asked

Buffer Manager: Free Pages counter

Buffer Manager: Free Pages is removed as a performance monitor counter in SQL Server 2012. Are any of you using this counter, and if so, what for? Since it's gone, what do you plan to use in it's place?
sql-server-2012performanceperformance-metricsperformance-monitor
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
I plan to just keep tabs on PLE. I would assume that Free Pages is sort of like Buffer Cache Hit Ratio, sorta useless. If Buffer Cache Hit Ratio drops below 99.999% it typically means FLE is taking a hit. If Free Pages gets really low, wouldn't it also mean PLE is taking a hit as well? Curious to see the rest of the responses.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I agree with @TRAD here, the metric on its own doesn't mean a great deal IMO. I would be more concerned with PLE nosediving in conjunction with I/O waits rising. Funny that Free Pages counter has been removed, but the Free list stalls/sec is still there. I wonder if the former was dropped as it is not a useful counter and the latter just got forgot
2 Likes 2 ·
This makes me think, if it is used somewhere what would be the purpose? Whatever situation I thought of, other more often used metrics (seems more useful to me) can replace it easily. Also, we do know that this metric will only work for different combinations. One specific uncommon combination could be to compare drops in free pages to any disk queuing in order to see if LazyWriter is having problems keeping the free buffer steady, or at least above zero, it could mean that the disk subsystem is not able to provide LazyWriter with the disk I/O performance that LazyWriter needs. But this again could be easily replaced by other DISK I/O metrics combination. So my understanding is that such metrics were in earlier versions when other more useful metrics were not present. Like in SQL SERVER 7.0 it was FREE BUFFERS (Changed to FREE PAGES in SQL 2000), but metrics like PLE were not there. So, to me it should have been replaced by more useful metrics in the production since SQL 2000. It only comes to the surface now because it is removed in SQL 2012. Hope I am making sense.
2 Likes 2 ·
yeah, I'm not enamored of this counter either. But the question I had is, what's the use of it and what's replacing it. I don't use it. Others do, but I can't figure out what for exactly. @AaronBertrand has a post up on it here: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/08/sql-server-v-next-denali-changes-to-performance-counters.aspx But doesn't exactly describe what it's for or what's replacing it, if anything. Thanks for the feedback guys. And +1, but I'm going to hold out on whether this is the answer or not.
0 Likes 0 ·
Have you asked Aaron for his opinion at all?
0 Likes 0 ·
DaveRowland avatar image
DaveRowland answered
I have a monitor that I developed which displays Buffer Hit Ratio, PLE and uses the Free Pages as a way to show memory use. The two I keep an eye on most is BHR and PLE however having the memory graph displayed does come in handy. I will use this information to adjust the memory depending on stats like stolen memory free up more to the OS or other processes if I have to. I rarely rely on SQL Server to determine how much memory it grabs dynamically, however most of our installations of SQL Server we have kind of have a massive overkill on available memory anyway. Although the PLE and BHR are important, it is also interesting sometimes to see the memory timeline change as processes run on the server over time.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.