Suspended query causing high CPU usage 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 &lt; 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 &gt;= 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?
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  :-)