2

We have a server with 2 quad xeon 5550 processors. The only application installed on the server is SQL server. CPU usage rarely goes above 40%, and the transaction rate is 2,000 transactions per second.

Processor queue length is also about 2. Everything seems normal, except context switches which has an average of 88,000 and max of 232,000 during work hours.

I've read in SQL server documents that a context switch number larger than 15,000 is indicative of a CPU problem. Is this actually a problem, even though the CPU counters seem normal?

Jeff Atwood
  • 12,994
  • 20
  • 74
  • 92
Parissa Bakhshi
  • 21
  • 1
  • 2

2 Answers2

1

I see that's a Nehalem processor with hyperthreading.

The last time we asked the general consensus was to disable hyperthreading by default on a SQL Server specific box (and we disabled it on ours, with good results). Browsing around a few other sites, I'm hearing that advice echoed by other reputable folks.

So, you might try turning off hyperthreading first, then re-measuring.

Community
  • 1
Jeff Atwood
  • 12,994
  • 20
  • 74
  • 92
  • I think the testing both ways is the most important step. From what I recall of the discussion and from what we've seen ourselves it varies based on workload - some things do well with hyperthreading, others... not so much. – Rob Moir Feb 07 '11 at 09:19
1

I'm sure you guys have figured this out already, but I wanted to share the answer here because it's Server Fault.

SQL Server has a timer function all its own, and uses it by default rather than the OS/BIOS timer. You can disable this, but I do not know if this will jack with your ability to have SQL Server enter granular time data (down to 12ms, iirc) into time fields.

Disable the SQL Server timer by going into SQL Server Configuration Manager, go into SQL Server Services and right-click the SQL Server instance and go to Properties.

In the instance properties go to the Startup Parameters tab and add -t8038. This enables a trace flag which disables the timer for that instance. If you have multiple instances, you will have to do this for each instance, or you will still encounter high context switching.

MikeB
  • 11
  • 1