Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have set alerts on two SQL Server 2005 instances I have in production. One of those alerts is sent whenever the Free Pages performance counter falls below the 5000 pages mark.

Both servers normally operate with more than 300000 free pages, but sometimes one of these servers momentarily goes well below 2000 or even 1000 free pages and then goes up again. Again, this only happens with one of the servers.

As far as I know, this behavior is not degrading the performance of this server, because it looks to me as if whenever the server is going to starve on free pages, it frees up older data pages from the buffers and populates the Free Buffer List. Theoretically speaking, at least.

What I don't understand is why the lazywriter isn't maintaining a larger free buffer list and is 'waiting' for SQL Server to get so short on free pages before freeing up more buffers.

So my question is actually twofold:
1. How can I determine what/who is causing SQL Server to become short on free pages?
2. How can I inspect what is actually going on here?

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

So what can happen that the free lists shrinks? For most purposes you can consider the formula free = total - (max(database, reserved)+stolen). So simply collect all the counters in the SQL Server, Buffer Manager Object and then see what the pattern is:

  • total stay the same, but stolen has grown. This indicate some memory consumption from code and caches. and usually does not heal itslef. You would investigate this by checking the memory clerks, see How to use the DBCC MEMORYSTATUS command to monitor memory usage. A possible explanation for a sudden spike in stolen that disapears quickly is a complex query compilation. Collect SQL Server, Memory Manager Object for additional info, look at Optimizer Memory.
  • database, reserved and stolen stay the same, but total shrinks. Lets just consider this as it doesn't happen. If you see this, post here and we can further digest.
  • total stays the same, database grows. Probably a scans on a large table. Look for a query with large logical_reads in sys.dm_exec_query_stats
  • total stays the same, reserved grows. Indicates a query that has a large memory grant request. Collect SQL Server, Memory Manager Object for additional info, look at Memory Grants Outstanding.
share|improve this answer
    
Thank you! I'll take a closer look :-) –  ivanmp Apr 26 '12 at 13:32
    
It looks like it's a complex query compilation. I'll take a look at the additional counters you said. Thank you again! –  ivanmp Apr 26 '12 at 17:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.