I have a SQL 2000 Server, dual processer with hyperthreading. 2Gb of Ram. The machine is only being used as a SQL Server
Normally the server runs at about 15-20% usage. I have now noticed a problem where the SQL server suddenly jumps to 100%, this can happen after a week or a couple of days. It requires a server reboot to fix the problem. Stopping the SQL server and restarting will not work.
I don't know if this is part of the problem, but I have noticed that after a day or so the processor usage climbs by about 10%, if I stop the SQL service and restart, the processor usage drop by the 10%.
Also I have set the memory usage to be 1.5Gb, but it takes a day or two for the SQL server to consume this amount of memory. Don't know if this has anything to do with it.
I ran profiler after the last problem and I had the same issue again. It seems three particular queries may be causing the problem. All three queries are fairly simple, although they do query a large amount of rows .
I've taken a sample of three reading before and after the problem. All other queries seem to be ok. But the website is inaccessable when the problem manifests.
I was looking at the profiler and heres what I found on the querys before the problem
I can get rid of Query 2 from the website as its something I added last night (and I've had 2 occurances of the problem today). But it still leaves me with the problemof what is happening, as I've had 2 previous problems (approx 1 week apart) prior to query 2.
Don't know if this has any revelance but I stopped the sql server and did a reboot, the SQL server went straight back up to 100%. I then did another reboot (without stopping SQL) and the SQL server went back to normal when the machine went back up.
Sorry, I meant to say when I stop the SQL Server, usage drop to 0% but as soon as I restart the SQL service it goes straight back to 100% usage. It takes a reboot of the server to bring SQL back to a normal level. i.e. stopping and starting the service does not fix the problem of usage.
Originally posted by Pat Phelan
Since you can stop SQL Server and have the usage continue, I would suspect that the problem lies elsewhere!