SQL Server Buffer Manager Free Pages

Question

SQL Server Buffer Manager Free Pages

BU69

SSCrazy

Points: 2784
More actions
July 10, 2009 at 3:19 am

#136096

I'm setting up some alerts using the Performance Monitor counters and there is one I'm really struggling to find much documentation about and that's SQL Server Buffer Manager\Free Pages, so far most things I've found say that this should be greater than 640, but I can't find out anything about what to do if this is below 640, the test server I'm using has this at 210, what do I do to improve on this?
Thanks
November 29, 2016 at 4:27 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

I personally dont use this counter as a memory issue indicator.

For my prod environment I monitor and log the Buffer Cache (98-99%) and Page Life Expectancy (Ms recommends over 300, mine is current 2800 sec).

I also keep an eye on a few other counter (I have attached a print screen on my perfmon)

BU69 SSCrazy Points: 2784 More actions · Answer 2

Thanks, I got the a list of counters from the Microsoft PAL tool someone at Microsoft told me to look at the xml config files and there's a list that they use without having to install the app, also the Free Pages counter is always mentioned in articles for which counters, anyway I'm playing about with a monitoring tool, Zabbix, where you can add perfmon counters into it and set alerts up and got a bit worried when this counter was well below the alert levels that were recomended but only since yesterday and I wondered if there is any way to troubleshoot this to find out what has used up all the pages.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 3

Just found an suggestion for this counter (now include it in my alerts)

> 640

http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SQL%20Server.htm

BU69 SSCrazy Points: 2784 More actions · Answer 4

I found that earlier but it's the same as the other documents I found and it doesn't explain what to do to troubleshoot this situation which is what I'm trying to find as the server I'm testing on is below 640.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 5

http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/what-is-allocation-bottleneck.aspx

If you read this article it explains the process of "Store Allocation".

As the article says TempDB is used allocation, therefore improving the performance of TempDB could reduce the allocation bottle neck.

Things like (if you already haven't):

- Place Temp on a different disk array.

- Create x data files for TempDB (x = number of CPU cores).

BU69 SSCrazy Points: 2784 More actions · Answer 6

Thank you

I'd seperated tempdb to the number of processes, I can't put them on a seperate spindle due to resources but I'll give this a read and see if I can do anything else.

BU69 SSCrazy Points: 2784 More actions · Answer 7

I've just seen on a different server low free pages, what I really need to know is how to troublrshoot this and should I be worried, free pages seem to stay about 200 all the time, is that bad? Nothing in the above article frees up anymore free pages and I'm still struggling to find a good explaination of why free pages needs to be above 640 (This figure was take from a Microsoft POD cast and seems to be listed all over as the standard at which free pages should be above but none of the articles have any reasoning for this)

Anyone any ideas?

Thanks

Oldman@ds9 SSC Enthusiast Points: 135 More actions · Answer 8

I've the similar Problem on by new SQL-Sever, having a lot of freee memory. I still not found a resolution.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

Oldman@ds9 (6/22/2010)
I've the similar Problem on by new SQL-Sever, having a lot of freee memory. I still not found a resolution.

I realize this thread is 18 months old, but I'm doing some research on the Free Pages counter (It's gone in SQL Server 2012) and came across this post.

As a single measure, this provides you with almost no information at all. It gives you an indication of free pages, but it doesn't tell you if you actually have pressure on memory. It can be a very low number and everything can be fine. You have to use this in combination with some of the other counters already mentioned such as page life expectancy.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning