June 3, 2007 at 4:11 pm
I am curious to know how to identify SQL Server whether it needs to upgrade for additional processor or problem is with Application/SQL code when i see my SQL Server CPU utilization is 80-90% full all the time.
Performance counter is good answer but still to narrow down or find root cause is kind of difficult. any input or links will be highly appreciated. thank you in advance
June 3, 2007 at 5:01 pm
Is it actually the sqlserver.exe process?
Does the activity regardless of the type of queries being sent? Some indices on your tables may help - I would check the statements being sent to SQL using SQLProfiler to see which ones have a high CPU cost and start seeing if you can optimise them.
Of course, if this server just runs SQL and nothing else, and your throughput is acceptable, then a high CPU could be ok. SQL tends to take over the resources of the machine unless you specifically limit it.
June 3, 2007 at 5:38 pm
If your CPU is often that high then you have to do an emergency analysis at the sql instance level. Firstly monitor blocking issues, then look for excesive compilation/re-compilation, and inefficient query plans; other things to be monitored would be intra-query parallelism in case your db server has >1 processors; poor coursor usage would be other cause for high CPU utilisation.
If all of the above have been fixed, then you can say that more CPU is needed.
Hope this helps.
June 5, 2007 at 9:44 am
Is this a single or multiple CPU server ? If it is a single then upgrade immediately. If it has 2 CPUs see if you can go to 4 CPUs. How much RAM do you have ? Hopefully 2 Gb. Well that's the quick tune up (throw money at it).
The better tune-up is a 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
You also might want to pay a visit to:
http://www.sql-server-performance.com
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply