Collecting Locking Information

Case:

Imagine the situation of a user who is trying to insert his\her information into a website and after clicking the submit button they wait 3 minutes to get the operation completed, one of the reasons for this bad situation can be a waiting “locking” case, the insert statement is being locked by another statement that locked the targeted table.

So, it is very important for DBAs to know how to deal with locking events occurs on SQL Server because this is one of the critical cases that affect the database\application operations and reliability.

Scope:

The scope of this post is to show how we can collect locking events for interactive troubleshooting and further investigations and analysis, in other separate posts will focus on locking definition, terminologies and how to get alerted with the locking accidents.

Solution:

The information we need to collect here is the following:

1. Target SQL Server instance --> the instance has the locking event
2. Blocked session id --> session id for the locking victim
3. Blocked script --> the script is being blocked
4. Blocked database --> the target database for the session\script being blocked
5. Blocking session id --> session id for the locking owner
6. Blocking login --> the login for the blocking session
7. Blocking host --> the machine name for the blocking session
8. Blocking application --> the application used by the blocking session for connecting to SQL Server
9. Blocking script --> the script that caused the locking
10. Wait type --> the wait type for the locking event
11. Waite time (min) --> the locking duration in minuets
12. Wait resource --> information about the locked object(s)
13. Date\Time --> date and time for the locking event

Please note that any column with the word blocked refers to the session being blocked and wait for the target object to be released and columns with the word blocking refers to the session causing blocking.

This information is located in the following catalog views, dynamic management views and functions:

1. sys.dm_exec_requests
2. sys.sysprocesses
3. sys.dm_exec_sessions
4. sys.dm_exec_sql_text()

sys.sysprocesses is a catalog view that contains information about processes running on SQL instance and used here to get the script for the blocking session.

sys.dm_exec_sessions & sys.dm_exec_requests are dynamic management views that contain information about the current sessions and requests being executed on SQL instance and used here to get most of information like blocked sessions id, host name, program name …etc.

sys.dm_exec_sql_text() is a dynamic management function that contains information about the SQL script being executed for a specific session, it converts the SQL handle into a text valued column that contain the SQL script being executed and used here to get both blocking and blocked scripts.

Then the full SQL script should be as below:

SELECT

@@servername AS 'sql_instance',

r.session_id AS 'blocked_spid',

sqltext2.text AS 'blocked_script',

db_name(database_id) AS 'blocked_database',

blocking_session_id AS 'blocking_spid',

s.login_name AS 'blocking_login',

s.host_name AS 'blocking_host',

s.program_name AS 'blocking_application',

sqltext.text AS 'blocking_script',

wait_type,

(wait_time/1000)/60 AS 'wait_time (m)',

wait_resource, CONVERT(smalldatetime,getdate()) AS 'Date\Time'

FROM sys.dm_exec_requests r join sys.sysprocesses p ON r.blocking_session_id=p.spid

left outer join sys.dm_exec_sessions s ON (s.session_id = r.blocking_session_id)

OUTER APPLY sys.dm_exec_sql_text(p.sql_handle) AS sqltext

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sqltext2

WHERE blocking_session_id > 0

ORDER BY wait_time DESC

And this is a sample for the result set of the query

Now this is fine on the interactive troubleshooting level, but in case we need to keep the locking information for further analysis then we have to store it in a separate table and automate the collecting process.

Then, the first step is to create the locking table in our dbatoolsdb inside schema “lck”.

USE dbatoolsdb

CREATE TABLE [lck].[lockdata](

[sql_instance] [nvarchar](128) NULL,

[blocked_spid] [smallint] NOT NULL,

[blocked_script] [nvarchar](max) NULL,

[blocked_database] [nvarchar](128) NULL,

[blocking_spid] [smallint] NULL,

[blocking_login] [nvarchar](128) NULL,

[blocking_host] [nvarchar](128) NULL,

[blocking_application] [nvarchar](128) NULL,

[blocking_script] [nvarchar](max) NULL,

[wait_type] [nvarchar](60) NULL,

[wait_time (m)] [int] NULL,

[wait_resource] [nvarchar](256) NOT NULL,

[date\time] [smalldatetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

After that we have to create a SQL Agent job and create a single job step with the locking information insert statement as shown below, "we just added this line to the original script in order to insert the data 

INSERT INTO dbatoolsdb.lck.lockdata"

The final step is to define the schedule for the SQL job, it is recommended to run the job with low interval (every 5 or 10 min for example)

Next Step:

We will work in the next post on the alerting section and how to send alert for the locking events.