Nirav's Diary

SQL Server long running and blocking queries

By nirav on June 3rd, 2013

SQL Server long running and blocking queries

This query helps you to get all running queries including user queries and SQL Server engine queries.

with duration, so you can examine which query running long.

You can see BlockingWith field which shows you query is blocking with another process.

I have ordered query by blocking first so you can get all blocking query at first and you can take necessary actions.

SELECT
DATEDIFF(MI, Start_time, GETDATE()) AS Mins,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END -   Statement_start_offset ) / 2 ) + 1) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Session_id,
Blocking_session_id AS Blockingwith,
Status,
Command
FROM SYS.Dm_exec_requests R
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blockingwith DESC, Mins DESC

This query show you only user queries. any queries running by SQL Server machine will be filtered.

Here are few more things to observe.

CPU field which shows the CPU usage, Reads, Writes, Host_Name which shows query running from which machine, Program_Name Which shows from which program the query executed.

SELECT
R.Cpu_time AS Cpu,
DATEDIFF(MINUTE, Last_request_start_time, GETDATE()) AS Mins,
S.Host_name AS Host,
LEFT(Client_interface_name, 15) AS Client,
S.Session_id AS Sessionid,
Blocking_session_id AS Blocking,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ABS(( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END - Statement_start_offset ) / 2 ) + 1)) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Blocking_session_id AS Blockingwith,
R.Reads,
R.Writes,
S.Session_id,
S.[Program_name],
S.Login_name,
S.Status,
S.Last_request_start_time,
R.Logical_reads
FROM SYS.Dm_exec_requests R
INNER JOIN SYS.Dm_exec_sessions S ON S.Session_id = R.Session_id AND S.Is_user_process = 1
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blocking DESC, Mins DESC

Regards,
Nirav Gajjar