SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Troubleshooting CPU utilization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 10/09/2006 :  11:40:58  Show Profile  Visit MichaelP's Homepage  Reply with Quote
Hello everyone
This 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

USA
36661 Posts

Posted - 10/09/2006 :  16:19:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run a trace for a several hours, then use a tool like Bill's to analyze the data:

http://weblogs.sqlteam.com/billg/archive/2006/06/19/10271.aspx

Once you have the normalized data, see what queries are using the most CPU time.

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/09/2006 :  17:23:47  Show Profile  Reply with Quote
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
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 10/09/2006 :  19:16:41  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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.

Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/09/2006 :  20:10:09  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/10/2006 :  01:36:06  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/10/2006 :  01:38:28  Show Profile  Reply with Quote
also, disable the hyperthreading.



-ec
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/10/2006 :  02:16:42  Show Profile  Reply with Quote
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?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/10/2006 :  02:25:02  Show Profile  Reply with Quote
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
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 10/10/2006 :  12:46:14  Show Profile  Visit MichaelP's Homepage  Reply with Quote
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.
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 10/10/2006 :  16:14:20  Show Profile  Reply with Quote
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,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000