up vote 1 down vote favorite
share [g+] share [fb]

Is it in principle possible for the CPU to be the bottleneck on a SQL Server if the Performance Counter Processor:Processor Time is constantly under 30% on all cores? Or does low Processor Time automatically allow me to rule out the CPU as a potential trouble source?

I am asking this because SQL Nexus lists CPU as the top bottleneck on a server with low Processor Time values.

link|improve this question
feedback

3 Answers

up vote 1 down vote accepted

In cases where there are a lot of synchronous operations in a query the cpu can become the bottle neck even when the cpu is reporting to have cycles to spare. There's only so much multi-threading that can happen. As cpu core count continues to increase the more you'll likely see this scenario. About the only thing you can do is get faster CPU cores or look into what sort of optimization can be done to the query/database.

link|improve this answer
Shouldn't this sort of thing de-crease with the number of cores (assuming constant load of course)? Why would in in-crease? – Sleepless Mar 30 '10 at 14:47
The percentage of under utilized cores will increase with the core count. So the percentage of CPU utilization you'll see will decrease as a result. Say a query can only use 1 thread. If you have 4 core cpu then your cpu utilization will be 25%. Same query on the new 12 core Opterons and your cpu utilization will be 8.33%. – 3dinfluence Mar 30 '10 at 14:57
OK, now I see. You're right, of course. What I failed to mention is that on my server both cores are underutilized individually. – Sleepless Mar 30 '10 at 15:00
I should point out that on a busy server that's running multiple queries at the same time will benefit from having more cores. As it can run them in parallel. You just can't expect each individual query to take advantage of all the cores. But I'm not a database expert by any stretch of the imagination and I'm not familiar with the SQL Nexus test/benchmark. But like with any benchmark/test you'll have to understand how to interperate the results. – 3dinfluence Mar 30 '10 at 15:04
feedback
up vote 0 down vote

If you have a high Processor Queue Length then you could theoretically have some CPU bottleneck.

link|improve this answer
It's zero almost constantly. So that means no, I guess? I do have quite a lot of SOS_SCHEDULER_YIELD wait types though, which, according to [1] is a strong indicator for CPU pressure. [1] Wort et al: Professional SQL Server 2005 Performance Tuning, Wrox 2006, pg. 70 – Sleepless Mar 30 '10 at 14:35
If your % Processor Time and Processor Queue Length are low I'd assume that CPU would not be a major bottleneck for you. There may be other factors to consider but those are the big two I look at in terms of CPU. I'm not familiar with the wait types you're experiencing so I can't offer much advice on those unfortunately. – Sean Howat Mar 30 '10 at 14:41
feedback
up vote 0 down vote

The thing to look for are SQL wait times. There are DMV's that can sow you what the system is waiting for. Look at Brent Ozar's site for more details.

Things to look out for: SQL server will (by default) use multi-threaded parallel processing for queries that it expects to take longer than 10 seconds or so. Sometimes this is just not appropriate for the type of query being performed and you get very high CX_WAIT times - where the different threads need to talk and syncronise with each other. You can get better throughput (more transactions per second, even though each transaction takes longer) by setting this value higher (on the server) or via a hint on the query. (See Google for more details!)

Does your queries have lots of complicated string manipulation? Or CLR functions?

SQL is most likely to be DISK bound, then RAM speed bound before being pure CPU bound.

link|improve this answer
I don't have any significant CX_WAIT times, so that's probably not the problem. Thanks anyway! As to what the queries do, I'll have to dig a little deeper into that. – Sleepless Mar 30 '10 at 15:39
feedback

Your Answer

 
or
required, but never shown
discard