In MS SQL Server, how do I determine what database processes are blocking others.

book

Article ID: 52535

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

During periods of high load, SQL Server can experience an excessive locking behavior. This results in slow system response in Clarity.

 

 

Environment

All Clarity versions

Resolution

The following queries were provided by Microsoft for isolating issues with blocking database processes:

  1. This query shows the root blocker. In theory the process running this query could be one holding up the other processes.
    SELECT sp.spid,sp.sql_handle,(select text from sys.sysprocesses as p --- get sql for blockercross apply sys.dm_exec_sql_text(p.sql_handle)where p.spid = sp.spid) AS sql_statementFROM SYS.sysprocesses spWHERE sp.spid IN (SELECT blockedFROM SYS.sysprocesses WHERE spid IN(SELECT blockedFROM SYS.sysprocesses WHERE blocked <> 0))
  2. This query shows all blocking SPID's (database processes) and their corresponding SQL.
    select t1.resource_type,db_name(resource_database_id) as [database],t1.resource_associated_entity_id as [blk object],t1.request_mode,t1.request_session_id -- spid of waiter,(select text from sys.dm_exec_requests as r --- get sql for waitercross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text,t2.blocking_session_id -- spid of blocker,(select text from sys.sysprocesses as p --- get sql for blockercross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as blocker_textfromsys.dm_tran_locks as t1,sys.dm_os_waiting_tasks as t2wheret1.lock_owner_address = t2.resource_addressgo

These queries will return no results in a dormant database.