Contents
Why is MY SQL Server in suspended status?
Issue: My online DB server was experiencing high CPU usage. Using the following query I was able to determine there are 2 queries (Select command) in suspended status (wait type – CXPACKET) for nearly 10 minutes and going on. I believe this is the cause of high CPU usage.
What causes high CPU usage in SQL Server?
When multiple CPU threads keep spinning (in a while loop) for a resource instead of yielding, this causes high CPU usage and performance issues. This problem was fixed in the following cumulative updates for SQL Server:
How to kill a SQL query in suspended state?
Get the session_id returned from your queries above and use the KILL command to kill the queries that are hung. For example if the session_id of the suspended query was 86, then you would run the following.
How to check CPU utilization in SQL Server?
The Processes tab of the Task Manager will show this information: Note that to see all processes you should select Show processes from all user. In this case, SQL Server (sqlserver.exe) is consuming 99% of the CPU (a normal benchmark for max CPU utilization is about 50-60%). Next we examine the scheduler data.
What to do when SQL is using high CPU?
Once the database maintenance activity is performed (like Index rebuild and Stats update), if SQL is still using high CPU, execute the query mentioned in Step 10. Check if the Top CPU query has changed. If the query has changed, then follow the action mentioned in Step 13. If the query is still the same, then go to next step.
Why is cxpacket causing high CPU workload?
CXPACKET is not the cause of high CPU workload. CXPACKET simply means that the query has gone parallel and that the thread which is reporting CXPACKET is waiting for another thread of the query to run. When I see queries waiting on CXPACKET this tells me that there’s probably some index or query tuning which needs to happen.