| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2006-10-09 : 11:40:58
|
| Hello everyoneThis is my first question in ages, but here goes.We've got SQL Server 2000 SP3a running on Windows 2003 in a clustered environment. SQL Server is the only resource on the node in question.We are running into some performance issues. Looking at the Disk statistics, we know we need more disks. We've got some disk queuing going on, but nothing too terrible. We've got a 75GB database, and the machine has 4GB of memory, with 2GB allocated to SQL Server. Adding /3GB will be one of our next steps. The one thing that I see that I don't know how to troubleshoot is CPU utilization with SQL Server. We're running dual 3Ghz Xeons hyperthreaded and SQL Server stays at 60-80% CPU utilization 24/7. The system is 25% write, 75% read according to the disk io, with this IO pattern happening pretty much 24/7. My guess is that Indexes and Statistics are constantly being rebuilt. How do I prove or disprove my theory? How do I determine what is eating up the CPU?Thanks for your help in advance everyone! I look forward to your thoughts on this matter.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-09 : 17:23:47
|
| This seems very unusual:"SQL Server stays at 60-80% CPU utilization 24/7."Most systems have some kind of peak hours, like when everyone is in the office doing work. What is the application?Is there some kind of process that is running all the time to keep the CPU level so high?CODO ERGO SUM |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-09 : 19:16:41
|
| Thing might to reconsider is by creating a baseliner for performance monitor to check the disc, memory, database etc and also do checking top 20 performance queries etc. I know this too late already but there at least you something to look as baseline before going to further investigation cause it could be just a busy day than normal day. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-10-09 : 20:10:09
|
| 1st thing I would do is just run a trace which filters queries that run for a long tim (>5000ms).or uses a lot of cpu (>1000).(Rough numbers)Just to see what's hitting the database and takes a lot of resources.Quite often, when the db is under heavy load, I can quickly get a grasp of what's sinking it.And the culprits can be spotted within minutes.It can be some queries that run often and are using a bad execution plan (typically check indexes and fragmentation).rockmoose |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-10 : 01:36:06
|
quote:
Originally posted by rockmoose
1st thing I would do is just run a trace which filters queries that run for a long tim (>5000ms).or uses a lot of cpu (>1000).(Rough numbers)
this is always my first step. I also throw in a trace looking for queries that do more than 5000 reads.You won't need to run the traces for long if your system is constantly busy. These should give you the targets that you will need to tune. Hopefully you have some low hanging fruit here that will be easy to knock out. btw, the fact that you are heavy CPU means that yor disk subsystem is probably ok for now. The logic here is that your disk is able to feed the CPUs at a rate that keeps them constantly busy. If disk were an issue, then the CPU activity would probably be low. I say probably, because this is just a general rule of thumb.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-10 : 01:38:28
|
| also, disable the hyperthreading. -ec |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-10-10 : 02:16:42
|
quote:
Originally posted by eyechart
also, disable the hyperthreading. -ec
Interesting remark, but can that apply as a broad rule?And will it make the box "faster"?With hyperthreading I set the max degree of parallellism to:(#physical processors-1). In this case since there are 2, I would set to 2.This is to reduce locking issues caused by parallellism.MichaelP, is there a resaon for running sp3a and not sp4? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-10 : 02:25:02
|
| i have found hyperthreading to be crap on busy SQL boxes. imho, it is worth disabling to test to see if there is any benefit.I also run with max parallelism limited to the number of physical cores on most of my hyperthreaded systems. We have a few in particular that are so busy that we had to just disable the hyperthreading alltogether. ymmv of course.-ec |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-10-10 : 12:46:14
|
| Lots of good info here guys and gals. I'll give it a go!Thanks!Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-10-10 : 16:14:20
|
| Not sure if they still do a free trial or not but Quest Software's Speed Coeffecient sounds like what you need. It will do a trace for you and then spit out the top worst calls in terms of CPU or Duration. It will also just spit right out for you any missing joins, table scans, index scans etc. That could help you identify the queries/stored procs that are the ones causing your problems. (No I'm not a salesmen trying to hock the product.) I use it regularly and just happen to love it. When it says "this query uses 15% of the overall CPU", after I stop choking, I grab the command it shows me and slap it right down in the Query Analyzer with the showplan on. SQL then shows me the CPU bottleneck and I figure out what my options are. Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
Troubleshooting CPU utilization