For sometime now we were receiving alerts for high CPU usage on one of the servers running SQL Server 2005 instance. The CPU utilization would continuously be spiking and sqlservr.exe was the one using the maximum CPU. Even though the number of user connecting to this instance is relatively high but not a particular query or a database was contributing to it. SQL Server instance as a whole was spiking the CPU. Here is the snapshot of the Task Manager.
Not a pretty sight for a DBA. CPU at 99% and Memory usage at 90%. Closer look at the memory value told me that something was wrong with the Memory Setting. Here is how I arrived at that conclusion.
The Physical Memory (K) section in Task Manager gives information about the Physical Memory (RAM) installed on the server.
The Total is 46067232 KB (43.9 GB).
The Commit Charge (K) section looked like this.
The Total value in this section is the Total Memory in use currently (Physical Memory + Virtual Memory). In this case it was 42413400 KB = 40.4 GB. The Limit value is the Total Memory available to the server ( Physical Memory + Virtual Memory). It had a value of 47467864 KB = 45.3 GB.
This was the key value which made me suspect that something was wrong with the Virtual Memory setting. I remembered that sometime back the DBAs had informed the System Administrators team that the Virtual Memory (Paging File) on this server was not correctly set. Usually the Virtual Memory is set at 1.5 times the Physical Memory installed on the server. Based on that request the System Administrators had added a new page file on a dedicated drive.
To verify that I navigated to Computer Properties –> Advanced –> Performance –> Settings to check the Page File settings.
On the top of the above screen shot we can see that 2 page files have been configured on the system. One of C drive (2 GB) and the other one on F drive (58 GB). The catch is at the bottom of the screen. The Currently allocated value shows only 2 GB, which means it does not detect the 58 GB page file located on the F drive. Gotcha! This is the reason why the Task Manager is showing a Commit Charge Limit of only 45.3 GB instead of the configured 104 GB.
Inadequate Virtual Memory can also be a major contributor to the poor performance of a server. On this server it definitely seems so. Team had a tough time in making the System Admins understand that the server is not recognizing the configured Virtual memory. Yesterday night they understood what we were trying to emphasize (after a lot of e-mails and phone calls) and they will be reconfiguring the Page File on the F drive.
I am curious to see the performance of the server after this issue is fixed.