How do you measure CPU pressure?

re: How do you measure CPU pressure?

Darryl Dreiling — Wed, 07 Sep 2005 20:14:43 GMT

Tom,

Very interesting and timely in our case! We are right now looking at CPU utilization in our organization and this is sure helpful. One question though, I know it’s hard to give specifics, but in orders of magnitude how much is a significant percentage? Is 80% too high, 50%...more than 30%?

Thanks for the insight!

Darryl

re: How do you measure CPU pressure?

marksou — Thu, 08 Sep 2005 05:42:55 GMT

Hello Darryl,
It is difficult to say what CPU utilization is the right number. For example if you are running an active/active cluster then you definitely want to run less than 45% CPU so when you fail over you have no problem handling the workload. A better thing to do is to use CPU pressure as a guide as Tom Davidson states above. Also you should look at what I call good CPU vs bad CPU. CPU is either user time(good) or privledge time(also known to some as kernal time). User time is the CPU is doing actual work like sorting, etc. Privledge time is CPU wait time. You could be waiting on IO completion, context switching or some other activity outside of user time.
So, look at user time and look at CPU pressure as a better guide. Also , don't run at 100% :)

re: How do you measure CPU pressure?

Darryl Dreiling — Thu, 08 Sep 2005 19:39:05 GMT

Mark,

Thanks for the update and the additional information. I will definitely use this for additional analysis. However, my original comment was misleading, my bad. What I was actually talking about was CPU Pressure, not CPU utilization. Tom writes in his post “if Signal Waits are a significant percentage of total waits” then you may be experiencing CPU Pressure. This is great information, but I am not sure what “significant” means with respect to Signal Waits and Total waits. For example, monitoring my SQL Server with DBCC SQLPERF (WAITSTATS), I see that 31% of my total waits are Signal Waits. How do I tell if that is significant for my system?

Thanks again guys!

Darryl

re: How do you measure CPU pressure?

Stuart Ozer — Tue, 13 Sep 2005 00:34:41 GMT

Darryl --

The percentage of the total wait time is not very helpful in decising whether signal wait is significant. To asses that, compare total signal wait time to total processing time available per cpu in the interval.

You will need to look at the total signal wait time on the system during a specific sample period of time (say, 1 minute or 5 minutes). If you see Signal Wait Time total 100% of the sample time (say, 60,000 msec during a 60-second sample), that tells you that you could fill up an additional CPU with work. If it is 200% then you could probably put 2 additional CPUs to work. Under 10% is probably below the radar.

Stuart

re: How do you measure CPU pressure?

thomasda — Thu, 15 Sep 2005 22:58:56 GMT

Hi Darryl,

Signal waits represent the time everyone waits for CPU and are thus unavoidable to some extent in a high concurrency system.

When you compare signal to total wait time this just establishes how much of your total is completely dependent on CPU availability as opposed to other resources.

The best questions you must ask when looking at cpu waits are:
1. How much cpu latency is acceptable? Say your signal waits are 30% for a given INTERVAL of time. Is it acceptable to have performance variations that AVERAGE 30% based on CPU? What about the interval? If you measure the busiest 15 minute interval, you may have high signal waits. But if you measure over a longer period say 2 hours, you may have a much lower average.

2. Is the total ms or percentage significant?
For example, 50% looks like a high percentage but if the number of milliseconds is very small to start with, you discount the significance of it.

The old sanity test still applies as to whether the percentage is significant or not. It is an interesting question but absolute numbers are hard to make because you have to factor in significance & the time interval.

Regards,
Tom Davidson

re: How do you measure CPU pressure?

mcrowley — Fri, 18 Nov 2005 18:24:03 GMT

For SQL 2000, would the above script be:

[code]
create table #tempwaits
(type varchar(40),
requests int,
waittime numeric(19,3),
signalwaittime numeric(19,3))

dbcc sqlperf (waitstats, clear)
go
waitfor delay '000:01:00'

insert into #tempwaits
exec ('dbcc sqlperf (waitstats)')

select sum(signalwaittime) as "Signal Wait Time",
100.0 * (sum(signalwaittime)/sum(waittime)) as "%cpu waits",
sum(waittime - signalwaittime) as "Resource Wait Time",
100.0 * (sum(waittime - signalwaittime)/sum(waittime)) as "% Resource Waits"
from #tempwaits
[/code]

re: How do you measure CPU pressure?

Magarity Kerns — Fri, 25 Nov 2005 22:11:56 GMT

On a related note, I've heard much conflicting information about Intel CPUs with their Hyperthreading architecture versus SQL Server. Whether to leave it on or disable it, etc. Can you address this in a future posting?

How to Troubleshoot SQL Server 2000 Performance Problems

Practical SQL Server — Tue, 12 Sep 2006 22:07:11 GMT

If you are having trouble performance tuning SQL Server 2000, there is one whitepaper you need.&nbsp;...

CPU Pressure is Huge, But Where is the Wait?

Linchi Shea — Sun, 31 Dec 2006 10:35:06 GMT

SQL Server waits enable you to identify where SQL Server is spending a lot of time doing nothing but

re: How do you measure CPU pressure?

jcarlossaez — Tue, 06 Feb 2007 17:55:54 GMT

And what about the deviation caused by wait types such as LAZYWRITER_SLEEP. Clearly, this wait should not been included in the calculation (as you can read in BOL describing dm_os_wait_stats view).

My question is, how many other wait types should be excluded from this calculation? Perhaps also waits related to backup devices?

SQLServerPedia » Scheduler Yields = CPU Pressure…or do they?

SQLServerPedia » Scheduler Yields = CPU Pressure…or do they? — Sun, 10 Feb 2008 15:36:12 GMT

PingBack from http://sqlserverpedia.com/blog/?p=229

SOS_SCHEDULER_YIELD

Kevin Kline — Wed, 14 May 2008 19:11:36 GMT

You might encounter a situation, under a very heavy workload, where processing for certain transactions

Context Switching Through the Roof | keyongtech

Context Switching Through the Roof | keyongtech — Thu, 22 Jan 2009 04:21:54 GMT

PingBack from http://www.keyongtech.com/2163609-context-switching-through-the-roof

Microsoft SQL Server Development Customer Advisory Team How do you | Shed Kits

Microsoft SQL Server Development Customer Advisory Team How do you | Shed Kits — Wed, 27 May 2009 12:07:51 GMT

PingBack from http://backyardshed.info/story.php?title=microsoft-sql-server-development-customer-advisory-team-how-do-you