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 :
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, butstolen
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 atOptimizer Memory
.database
,reserved
andstolen
stay the same, buttotal
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 largelogical_reads
insys.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 atMemory Grants Outstanding
.