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:
- Target SQL Server instance --> the instance has the locking event
- Blocked session id --> session id for the locking victim
- Blocked script --> the script is being blocked
- Blocked database --> the target database for the session\script being blocked
- Blocking session id --> session id for the locking owner
- Blocking login --> the login for the blocking session
- Blocking host --> the machine name for the blocking session
- Blocking application --> the application used by the blocking session for connecting to SQL Server
- Blocking script --> the script that caused the locking
- Wait type --> the wait type for the locking event
- Waite time (min) --> the locking duration in minuets
- Wait resource --> information about the locked object(s)
- 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:
- sys.dm_exec_requests
- sys.sysprocesses
- sys.dm_exec_sessions
- 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"
0 comments:
Post a Comment