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 |
|
Mathias
Posting Yak Master
119 Posts |
 Posted - 2009-03-18 : 11:34:30
|
| I have some have store procedure that do a lot of calculation and I was wondering why the CPU% is moving only up to 25% (on a 4 CPU server - SQL Std 2005). It may go sometimes up to 100% but it is really rare and unpredictable. On the other hand, 25% is always reached when a heavy load is under go. What should I do to have it more predictable and use more CPU? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-18 : 13:38:37
|
| It is likely that the server is waiting on some other resource so it cannot use all the CPU power.Disk IO is something that it could be waiting for. It is possible that adding more memory would reduce the IO work load by allowing more data to stay in memory. You can use the Performance Monitor to look at key SQL Server metrics, like page life expectancy and page reads per second, to see it it could benefit from more memory.Another possibility is that adding indexes would reduce the need for IO and also reduce the need for memory and CPU time. You can use the Database Tuning Wizard to look for indexes that might be useful to add.CODO ERGO SUM |
 |
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2009-03-19 : 03:49:06
|
| I use DB monitor to the server (which is very good to my view). When running a heavy calculation store procedure, the CPU gets stuck at about 23 %, meanwhile the disk access is about 5 access/s in write and almost none in read. The server has 3000 GB of RAM wich is not bad. Let's put it in another way : could you provide a code example to use more than 25% CPU? Something predictableThanks |
|
|
|
|
|
|
CPU % - most of the time 25 percent why?