June 6, 2003 at 5:53 am
Hi
As a novice sql administrator, I am having troubles identifying what is causing the mssql 7 service to use >99% CPU usage.
It is happening on a semi-regular basis, where it skyrockets from anywhere between 10+ minutes to a couple of hours. Since it goes up and down, I don't think it is due to a software bug.
May I know what sort of steps I can take to determine the user, database, sql procedure/statement that could be causing the problem?
Thanks for any advice.
June 6, 2003 at 8:45 am
Execute system proc. sp_who2. as follows
Exec Master..sp_who2
This proc. will report all current sessions on any given SQL server,
including CPU Time, Disk IO, Client Source (ex. IIS, Query Analyzer) and many more.
Once you identify the process (spid) that is consuming much of the server resource
You can use your DBCC command to find out command issued by this (spid)
Your DBCC command would look like this:
DBCC INPUTBUFFER (SPID) where SPID is the numerical value of the process id.
NOTE: The DBCC command is not helpful sometimes, therefore pay attention to
the result from the system proc. You can identify username, program name and so on
which will help you isolate the issue.
I hope this helps
MW
MW
June 6, 2003 at 8:55 am
If you don’t get much info with DBCC INPUTBUFFER, find out the SPID as mworku suggested. And launch the trace flag with SPID or userid criteria. And check your t-sql is creating any temp tables?
Also what’s your RAID configuration? Where is your log file? And tempdb files?
Shas3
June 6, 2003 at 8:31 pm
I would suggest using profiler to capture, in the first instance, completion events for RPCs and Batches. Let it run for a good number of minutes (depending on the amount of data being captured) during a high CPU period and then save it to a SQL table.
Do the same for a low/normal CPU period.
Construct some queries to analyse the results, for instance grouping by substring(textdata, 1, 30) and summing CPU.
This may or may not reveal the culprit, but I have at times been able to use this method to identify the problem. It could be a high CPU query, or even a relatively low CPU query being issued thousands of times.
(PS. I'm not quite sure whether SQL7 profiler allows you to save to a table: I usually use SQL2000 clients against SQL7)
Cheers,
- Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply