2

Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver.

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.

 USE MASTER GO

 SELECT scheduler_id ,
        runnable_tasks_count ,
        pending_disk_io_count
 FROM   sys.dm_os_schedulers
 WHERE  scheduler_id < 255

 SELECT qs.percent_complete ,
        qs.session_id ,
        scheduler_id ,
        blocking_session_id ,
        qs.status ,
        command ,
        wait_time ,
        wait_type ,
        last_wait_type ,
        wait_resource ,
        ST.text ,
        host_name ,
        program_name /*   
 ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
     (    (CASE statement_end_offset 
           WHEN -1 THEN DATALENGTH(st.text)
           ELSE QS.statement_end_offset END - QS.statement_start_offset  )/2 ) + 1) AS statement_text   */   --, qp.* 
 FROM   sys.dm_exec_requests qs
        LEFT JOIN sys.dm_exec_sessions es ON ( qs.session_id = es.session_id )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS ST
 --     CROSS APPLY sys.dm_exec_query_plan(QS.sql_handle) as qp where qs.session_id = 50 and qs.session_id != @@SPID

 SELECT CASE WHEN Requests.sql_handle IS NULL THEN ' '
             ELSE SUBSTRING(Statements.text,
                            ( Requests.statement_start_offset + 2 ) / 2,
                            ( CASE WHEN Requests.statement_end_offset = -1
                                   THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text))
                                        * 2
                                   ELSE Requests.statement_end_offset
                              END - Requests.statement_start_offset ) / 2)
        END AS StatementText ,
        QueryPlans.query_plan AS QueryPlan ,
        Statements.text AS Batch_Text ,
        Sessions.session_id ,
        Sessions.Login_Name ,
        Sessions.Host_Name ,
        Sessions.Program_Name ,
        Sessions.Client_Interface_Name ,
        Requests.wait_time ,
        Requests.cpu_time ,
        Requests.total_elapsed_time ,
        Requests.reads ,
        Requests.writes ,
        Requests.logical_reads ,
        Requests.row_count ,
        Requests.granted_query_memory * 8 / 1024 AS Granted_Query_Memory_MB ,
        LEN(Statements.text) AS Batch_Text_Length ,
        Requests.statement_start_offset / 2 AS Statement_Start_Offset ,
        CASE WHEN Requests.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
             ELSE Requests.statement_end_offset
        END / 2 AS Statement_End_Position ,
        ( CASE WHEN Requests.statement_end_offset = -1
               THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
               ELSE Requests.statement_end_offset
          END - Requests.statement_start_offset ) / 2 AS Statement_Text_Length
 FROM   sys.dm_exec_session Sessions
        INNER JOIN sys.dm_exec_requests Requests ON Sessions.session_id = Requests.session_id
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
 WHERE  Sessions.session_id != @@SPID


 SELECT *
 FROM   sys.dm_exec_requests
 WHERE  blocking_session_id != 0

Any suggestions on how I can kill these queries in suspended state?

  • If you are using windows you can simply use task manager. Windows-R then type taskmgr and find the taks consuming CPU and end them. – Saher Ahwal Dec 17 '14 at 19:27
  • 3
    @saher: which is rather pointless - you'd be shutting down sqlserver. – Marc B Dec 17 '14 at 19:28
  • Find the SPID and kill it: KILL [SPID] – Dave.Gugg Dec 17 '14 at 19:33
  • @MarcB: that is if he shuts down the main sql server process. why would that be pointless if the tasks are suspended and no way to stop them? – Saher Ahwal Dec 17 '14 at 19:37
  • 3
    just because 2 queries are wedged doesn't meant the rest of the database is. "Hmm, this door is jammed. guess we'll have to shut down the entire hospital" – Marc B Dec 17 '14 at 19:37
2

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.

KILL 86;

IMPORTANT: if any of the queries run batches of insert, update, or delete statements and are not in a transaction so that all changes get rolled back, then you may run into data consistency problems if you kill the query in the middle of processing.

|improve this answer|||||
6

Although you've accepted an answer that informs you how to kill the session, this is a kind of problem that could repeat itself and so you should probably be aware that the CXPACKET wait type in itself is not a problem, but tends to indicate an issue with the execution of a parallel query.

Brent Ozar has posted a good explanation (with a video) of what a CXPACKET wait is and how you go about reducing them. As an example, if your parallel query runs across 5 CPUs and 4 of them have completed their allocated workload, the waiting on the 5th CPU to complete is what is identified as the CXPACKET wait.

You can start to alleviate CXPACKET waits by looking into setting the Maximum Degree of Parallelism (MAXDOP) on the individual query and/or the Cost Threshold for Parallelism on the instance, both of which are explained in the article.

|improve this answer|||||
3

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. Once the indexes and query have been tuned then you need to look at the other wait types for the other threads for that query and see what the other threads are waiting on. One of them will be waiting on something other than CXPACKET. That's the problem that you need to address.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy