SQL Server Performance Forum – Threads Archive

Pages/Sec counter

We have 18 servers, all running Windows 2003 R2, with SQL Server 2005 SP1. We have approximately 40 databases per server (give or take, plus or minus), where the average size is about 1.5 GB. All the servers have with 8 GB RAM, with MinServerMemory = 0, and MaxServerMemory = 6144. The Total and Target Server memory counters all run at about 6 GB. Each server is identical. Each database setup, options, and configurations are identical. The tables within the databases have identical schemas, but containing different data. Our SQL Servers are dedicated SQL Servers, with no other applications. My Buffer Cache Hit ratio averages 99%. In looking at Memory Object Pages/Sec, two of the 16 (identical) servers, the average Pages/Sec is slightly over 300. However, the remaining 16 servers, are in the range of 10-15 Pages/Sec. In reading onhttp://www.sql-server-performance.com/qdpma/inst_3_pmlogs.asp, I came across the following: "The SQL Server process Working Set should track closely with Private Bytes, the SQL Server:Buffer Manager->Total Pages counters and the SQL Server:Memory Manager->Total Server Memory(KB) counters. It is not necessary to log all of these counters. If the working set suddenly drops much below private bytes, then this could indicate that the OS had to taken memory from SQL Server for another application." When running Private Bytes and Working Set on one of the servers with Pages/Sec over 300, the Private Bytes averages 280300544 and stays rather steady. However, the Working Set averages 116593746 and every 4 to 5 minutes dips down to near zero. Why the sudden drop? Since SQL Server is not running any other applications, and all 18 servers from the table schema on up are identical, save the data, I find it interesting that so much paging occurs on two servers, versus the other 16. Any suggestions on how to track down why the Pages/Sec are over 300 on two, but the other 16 are in the 10-15 range? Why the sudden drop of the Working Set counter?

---

average counter values are hard to interpret usually what happens is the pages/s sits at a steady state value
then jumps during a surge
if this is the case,
then it is important state that this is the case
and what is the steady state value and the duration of the peaks

---

Thanks Joe. I will attempt to clarify. The Buffer Cache Hit ratio of 99% was during the High CPU episode. I did not have a Pages/Sec counter running during the high CPU episode, so the 300+ Pages/Sec is during normal CPU usage. In looking at Pages/Sec this morning (and my CPU is averaging 27%), the Pages/Sec is averaging 600 Pages/Sec, with a maximum of 3531.354 and a minimum of 102.576. There are surges taking place all the time, and nothing steady about it. The duration of the peaks are fairly small. Additionally, any insight on the sudden drop in the Working Set counter, as per my initial thread?

---

]]>