Environment detail: SQL Server 2008 R2.
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 - CXPACKETwait 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?
