November 7, 2006 at 4:21 pm
We are having a sporadic problem where suddenly the CPU usage for SQL Server on the box spikes up to 100% and stays there. It ends up hanging the server, and we have to basically take it offline and back up for it to get unhung. However, as soon as we reconnect our web servers, the CPU goes right back up again and the server hangs. Help!!!
For all you experienced DBA's, what would you do if this problem happened to you? What would be your first steps in problem resolution? I have no idea where to start; I can sometimes look at the Activity before it hangs up, and it doesn't really seem like anything too exciting is happening. The web servers are using SiteRefresh, but we are running a trace on that side and can't really determine the problem either. The fact that it is sporadic does NOT help.
ANY help at all would be so much appreciated.
Amy
November 7, 2006 at 6:32 pm
Can you run the Profiler and then connect the web servers one at a time? The Profiler trace should tell you what the server is working on.
November 8, 2006 at 10:26 am
I had never used SQL Profiler before, thanks for the advice - seems like it will give us what we're looking for.
November 8, 2006 at 11:04 am
How many CPUs does your SQL Server have ? Is is just dedicatde to SQL Server alone ? Have you tried to use perfmon to see what you can see ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 8, 2006 at 11:44 am
Download a free trial version of a monitoring tool like Quest's Spotlight on SQL Server. It gives you a graphical interface for almost every performance metric you can imagine. Whatever is causing the problem (disks, memory, locking, CPU, etc) will be flashing red and you click on it for an explanation or drilldown for more details. Profiler and Perfmon are powerful tools if you know how to use them and what you're looking for, but Spotlight should help you find the problem quickly.
One caveat though, Spotlight collects a lot of performance info from the server, both from SQL and from Windows. If something has the server spiked at 100% CPU, Spotlight will be as unresponsive as everything else waiting for the server.
November 8, 2006 at 4:43 pm
If you're looking for help with performance monitoring try this free perf mon wizard from Microsoft:
November 9, 2006 at 5:31 am
actually all this advice is useful but to isolate what is actually using the cpu can be quite tricky - be careful of spotlight, I've found that it will degrade a box by up to 10% so can actually be the worst thing to connect when you have problems.
I'm currently tuning a system where the procs all hit 100% for periods of time - it's a long job to isolate the issues - I've pretty well established that it's not memory or the disk sub system, I know the sql isn't too good ( it's all dynamic/embedded )
You need to know if it's a sql process or not , I'd be looking to eliminate areas such as memory/paging , disks, bandwidth etc. etc. I suspect your database may well be blocking ..
Make sure you have out of the box settings on the sql server and o/s.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply