SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Buffer Manager Free Pages


SQL Server Buffer Manager Free Pages

Author
Message
BU69
BU69
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 283
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
sql_lock
sql_lock
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3343 Visits: 1996
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)
Attachments
greenshot_2009-07-10_11-59-10.jpg (378 views, 63.00 KB)
BU69
BU69
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 283
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
sql_lock
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3343 Visits: 1996
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
BU69
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 283
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
sql_lock
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3343 Visits: 1996
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
BU69
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 283
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
BU69
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 283
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
Oldman@ds9
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 43
I've the similar Problem on by new SQL-Sever, having a lot of freee memory. I still not found a resolution.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98649 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search