Awesome.. I love SQL Server and can't wait to here from you guys.

Shaun Bedingfield
blogsb.blogspot.com
shaunbed@swbell.net

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

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% :)

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

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

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

What is a SAN? Or a LUN or HBA for that matter?

At least spell out the acronym once somewhere in the article. Or was this a coded message not intended for public consumption?

Sorry, I couldn't help it :-)

Glad to see you're going to be writing about SANs and SQL. I've got a question for you in this area:

I've heard that multiple logical drives are better than one when holding everything else constant because Windows will maintain separate queues for each. Example: 3x 10-disk LUNs, striped metaLUN or metaLUNs across all 30. Is it better to have a single large stripe that produces one logical drive, or 3 smaller ones?

I can see the advantage of separate queues, but will it produce a significant difference in IOs? Alternately, can it be more efficient to have a single queue that can be better optimized by write-reordering?

My testing so far has been inconclusive, so I'd like to get your take on the relative performance of single vs. multiple drives. Thanks - hope to read more SAN columns in this blog!

salt prevents dictionary attacks (they can't precompute hashes for all possible salt values), right?
so as long as I use a different salt value for each SSN, why would I need to hide the salt?

Bob -- If you used a **different** salt for each SSN, somewhere you would need to keep track of the salt values associated with each SSN. Otherwise, any new incoming 'transaction' data that you want to store for the SSN could not be associated with the hashed SSN. That list of salt values could be vulnerable to discovery and would allow the same kind of attack as discovering the single salt.

Hello Scott: Fair comments. We wrongly assumed that readers would know the abbreviations. Here they are: SAN - Storage Area Network, LUN - Logical Unit Number, HBA - Host Bus Adapter.

Ira: You raise an interesting question. We plan on addressing this topic in a future Blog. Meanwhile, there are some operations in SQL Server where having multiple logical devices (either through mount volumes or LUNs) can result is better performance under some conditions. Generally, the impact is small.

Did I miss Deploying SQL Server 2005 with SAN #2?

Please post a link to #1 and #2.

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]