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?