Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tomrippity
Starting Member
37 Posts |
 Posted - 2012-04-20 : 11:14:13
|
| I've been reading more and more on optimizing SQL and how to evaluate performance. One white paper I came across suggested looking at CPU pressure and wait times.I'm not 100% sure how to use all of this information yet, but Microsoft suggest that CPU pressure be below 25%. If that is the case, then the bottleneck then becomes Memory and Storage performance, correct? It would seem to me, that you would want your CPU pressure to as high as possible, meaning your storage system is saturating your CPU with as much data as it can handle, not because the CPU is weak, but because your IOPS on your storage system is very very high. This assumes that your queries are written well and server configured properly.Is this not true? Maybe my understanding of what CPU pressure "is" is causing me to look at the topic in the wrong way.Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-20 : 11:30:13
|
quote:
It would seem to me, that you would want your CPU pressure to as high as possible, meaning your storage system is saturating your CPU with as much data as it can handle, not because the CPU is weak, but because your IOPS on your storage system is very very high.
You're confusing CPU "pressure" with "utilization". If your system's total CPU is near 100% it means there is not enough processing power to handle the load put on the server, and it's not a good sign. It doesn't mean your I/O and memory are really fast either, it just means your CPU is underpowered.CPU "pressure" is not a particularly useful term, because it doesn't identify where the CPU is busy or where it's waiting for another resource. You may see a single core of a multicore system pegged at 100% while the other 3 or 7 or 31 cores are idle, and your query takes a long time to run. This could be a single-threaded query with lots of computation, or an incorrectly set affinity mask, or a problem with the CPU hardware. The problem is that total CPU utilization in this case could be under 10%.quote:
This assumes that your queries are written well and server configured properly.
That's an awfully big assumption.  If your CPU is pegged it's unlikely that's true, and if it is true, you need faster CPUs.Which whitepaper are you referring to?What you should look at are wait statistics on your SQL Server. These identify exactly what your server is waiting for and is the first step to identifying a bottleneck. You can find a good writeup here: http://www.brentozar.com/sql/wait-stats/ |
 |
|
|
tomrippity
Starting Member
37 Posts |
Posted - 2012-04-20 : 13:46:20
|
| I was trying to find where exactly the link was for the white paper, all I can say for sure is it was from kaminario.com .. I will read on the link you provided me and see what I come up with. I am developing an application currently that we estimate will have a lot of traffic and large queries, so I am trying to eliminate any bottlenecks I can find right now, even though its hard for me to simulate the expected traffic.Thanks so much for your input. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-20 : 14:22:13
|
quote:
I am developing an application currently that we estimate will have a lot of traffic and large queries, so I am trying to eliminate any bottlenecks I can find right now, even though its hard for me to simulate the expected traffic.
Until you can set up a test system with (near-) identical configuration that you can actually run tests on and measure performance, it's pointless to try and tune any queries beyond the basics of no cursors, minimize returned data, normalization, etc. Premature optimization is the root of all evil. Wait until you have hard performance data before you start tuning.There are load testing programs available, including some in Visual Studio. You can also look at the SQL Server RML utilities for stress tests. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-21 : 06:22:32
|
| Some other options for testing are:SQL Server Profiler- using the T-SQL Replay template. DBGEN - The TPC-H tools, dbgen and qgen allow for the development of up to a terabyte databases.Quest Benchmark Factory for Databases – Track the effects of SQL Server changes on the environment Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
CPU Pressure