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?