April 25, 2012 at 6:24 am
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?
April 25, 2012 at 7:27 am
Free pages isn't really a great performance counter for monitoring the server. It just indicates how the buffer manager is moving stuff around. You'd be better off looking at page life expectancy, page faults, lazy writes and some of the queues and wait stats. Then you can figure out if you have a real problem or not.
BTW, this counter is removed in SQL Server 2012.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply