SQL Server falling below 5000 free pages

Question

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?

Answer 1

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.