Suspended query causing high CPU usage

Question

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_sessions 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?

Answer 1

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)

Answer 2

Dont kill the SPID's instead try to see you can stop from your end to see.

Also the suspend means it is waiting for some resources as in your case it is cxpacket- Firstly the query which is running from the SPID's is normal(consider it is running many days with the same ) or did any changes happened like any logic has changed or any outdated statistics(if so update the statistics then you see) or dataload get increased?

second the SPID shows an suspended status means it is requesting access to a resource that is currently not available to processes hence according to your shows an waittype as cxpacket.

see is there any other activity is perofming during that time & what was the load, if its really taking this SPIDs then please check out as said above in firstly(like outdated statistics,too fragmentaion,modification performed,missing indexes). if not take the execution plan and see for the query along with maxdop set.

also see by running counters as well for sql server :stattisitcs -compilation/sec,batch/sec,recompliation/sec -to see how it is involving & increasing.

also see the below one-Reducing SQL Server CXPACKET Wait Type http://dbadiaries.com/reducing-sql-server-cxpacket-wait-type

Parallelism: Hurry up and wait http://blog.idera.com/sql-server/parallelism-hurry-up-and-wait/