Relinquish the CPU BottleNeck on Win Server 2008 64-bit

CPU Performance Bottleneck

Server Specification

Dell PowerEdge T610

Processor Intel Quad Core Xeon COU, 2.8 GHz

Memory 16GB 4x4GB

Operating System: MS Windows Server 2008 Standard x64 Ed.

Database: MS Sql Server 2008 x64

Summary

Initially the Server T610 was running on single instance IDBSVR\INS_ICARE to manage ICARE database. The hardware setup  on CPU and Memory was based on default setting. We only reconfigure the current RAID setup from RAID 5 to RAID 10. The ICARE database was replicated continously to another Windows 2003 Server I.e DBSVR.

We used Spotlight on SQL Server Enterprise to monitor the database performance and track any significant errors.

Symptom

On April ~ May 2011, we started to install Boss.Net application and create another instance IDBSVR\BOSS_NET_DB. This database also was replicated to DBSVR on scheduled basis.

However, the Spotlight monitoring software started to view alarming indicator which shows bottleneck on CPU performance.

Benchmark

Processor Queue Length (CPUs x 2) = 32 (Logical CPU)

                                                            = 16 (Physical CPU)

            Preliminary Trial Setup = 16

Context Switches/Sec                         = 8000

Message Alarms

The IDBSVR started to broadcast Low level alarm indicating high number of Processor Queue Length which is exceeding 16 for more than 10 minutes and Context Switching level has started to reach 2 million counters.

The symptoms started to appear after 48 hours of restarting the server.

Solution and Action

1.      Set Maximum Worker Threads =  704 in Server Properties under Processors Tab on IDBSVR\INS_ICARE*

2.      Restart the server.

3.      After 48 hours, the problem still happened.

4.      Set Maximum Worker Threads =  704 in Server Properties under Processors Tab on IDBSVR\IBOSS_NET_DB*

5.      Restart the server.

*The default value 704 on Max Worker Thread is taken from MSDN blog.