January 21, 2009 at 1:03 pm
We have a rather good size server (4 CPU x 3.0Mhz, 4 Gig RAM, etc.) running SQL Server 2000 SP4 on Windows 2000 Server. All windows updates are up to date. It has been working just fine until a month ago.. Lately, the CPU has been working at 100% (all taken by sqlserver.exe) at very different times of the day and at varying intervals. Sometimes it lasts for 5 min, sometimes for 20 minutes, which is how much we can tolerate until we restart SQL Service. When we do, things go back to normal.. until same thing happens again, sometimes in 10 minutes, sometimes hours later.. We rebooted the server many times to no avail.. played with many SQL jobs to see if anything is causing this problem to no avail.. When this happens, it is impossible to browse or troubleshoot on the server since it is soooo slow, hence it is driving us crazy. Any ideas? Please help..
January 21, 2009 at 1:13 pm
The first thing that you need to do is find out what is running in SQL Server during that time. A great link on getting together a trace that can be run from a QA window is http://stackoverflow.com/questions/257906/ms-sql-server-2008-how-can-i-log-and-find-the-most-expensive-queries
This should allow you to collect the expensive queries at that time and start to look at what might be throttling the instance. The post is for 2008 but I believe it will work on 2000 with a bit of work potentially.
I would not recommend using profiler during the time of high cpu as you will most likely bring the instance down to a crawl.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 21, 2009 at 1:33 pm
Thanks David.. however, when we run the profiler, there are many things that are taking up a lof ot time, not because they are the problem, but because the CPU is at 100% at that time.. The CPU works at 2% to 40% 99% of the time.. Something triggers this problem and drives it nuts.. That is what we need to find out.. and not sure how..
January 21, 2009 at 1:45 pm
Did you read that article / posting? The configuration of profiler is filtering down to those expensive queries that are running at that time and it is using a trace opened from QA dumping to a file rather than actually using profiler which can be costly.
Outside of that you can look at the perfmon counters to see if you can see what other processes aside from SQL Server are using up CPU. Outside of that you are going to have a hard time pinpointing what is going on. For that you would use the Process counter % Processor Time for sqlservr process.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 21, 2009 at 2:09 pm
If you can configure your trace with the necessary output data, then I would advise using something like "cleartrace" which will collate the results and you can filter by host/user/CPU usage/writes etc
Will give you an instant idea of the root cause.
January 21, 2009 at 4:07 pm
David, thanks so much.. We think we got it following your advice.. There was a function eating up amazing CPU time.. we followed it to Google crawls calling a function from an unmaintained table that had grown like crazy.. Let's keep our fingers crossed but we are pretty sure this was it..
Great help.. thx..
January 21, 2009 at 4:17 pm
Awesome! I know being able to catch that data with traces like that are great and if you keep using it you can find other ones that need to be tuned. 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply