Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Buffer Manager Free Pages Expand / Collapse
Author
Message
Posted Friday, July 10, 2009 3:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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
Post #750904
Posted Friday, July 10, 2009 5:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,492, Visits: 1,691
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)



  Post Attachments 
greenshot_2009-07-10_11-59-10.jpg (257 views, 63.30 KB)
Post #750957
Posted Friday, July 10, 2009 5:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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.
Post #750971
Posted Friday, July 10, 2009 8:07 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,492, Visits: 1,691
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
Post #751067
Posted Friday, July 10, 2009 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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.
Post #751080
Posted Friday, July 10, 2009 8:34 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,492, Visits: 1,691
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).
Post #751114
Posted Friday, July 10, 2009 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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.
Post #751123
Posted Tuesday, July 14, 2009 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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
Post #752727
Posted Tuesday, June 22, 2010 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:24 AM
Points: 9, Visits: 37
I've the similar Problem on by new SQL-Sever, having a lot of freee memory. I still not found a resolution.
Post #941278
Posted Monday, December 05, 2011 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1216307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse