sqlservr.exe high cpu usage, ran query, now what?
Posted on 2011-09-27
I have XP sp3 and have been running one database for two years, now just recently the cpu usage is bouncing between 70 and 99 percent all the time. Have scanned for viruses, rebooted, defragged, backed up the entire database and log file.
I found this answer below to another post and ran the query. I just don't know what to do with the results. I am running SQL Server 2005 SP2 but think the below information is good for me too. Please advise if this query would have to be different with 2005 version.
Otherwise, the query shows one result, session_52, status suspended. I just don't know where to go from here.
I also have trace results from the sql profiler that I ran yesterday. Don't know what to do with those either.
Any help with be greatly appreciated.
Answer that I got query from:
1. You should start by checking what processes are currently executing on the server. If you run the statement below in SQL Management studio, it will display a list of user processes currently executing:
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid
2. If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)
RPC: Completed (Under stored procedures)
SQL: BatchCompleted (Under TSQL)
Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.