Interview Question, MS SQL

Finding Blocking Session in MS SQL Server.

Execute this query in SSMS against the same SQL Server instance:

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS RunningSQL
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) txt
WHERE s.is_user_process = 1
ORDER BY r.wait_time DESC;

Look for the SSIS session and check these columns:
blocking_session_id greater than 0: another session may be blocking the task.
wait_type beginning with LCK_M_: waiting for a lock.
wait_type such as ASYNC_NETWORK_IO: SQL Server is waiting for the client to consume results.
No corresponding active request: SSIS might be waiting elsewhere, using another connection, or waiting on transaction completion.

Leave a Reply

Prabhakaran Jayaraman