How to monitor and troubleshoot SQL Server blocking (1) (current blocking)

1. What is "blocking"?

Blocking is a side effect of the SQL database application "lock" mechanism. When an application request is locked for a database object (such as a full table, a row of data, or a data page), the lock blocks other application requests. It's like you lock your home door, your wife doesn't have a key, and you can only wait for you to go home. Her request to enter the room was blocked by you and had to wait until you unlocked the door. For a database, a short-lived block can be accepted. and short-term blocking is the norm for the database. This blocking phenomenon requires the attention of the DBA only when the blocking time exceeds the time tolerated by people.

2. How do I find the "current" "blocking"?

The following tools allow you to discover the current blocking phenomena that are occurring

• Active Monitor Activity Monitors
• Sp_who2
• Dynamic performance View (DMV)

• Session ID: is a unique integer (int) that is assigned to each user connection when the connection is established.
• Wait time (milliseconds): The time, in milliseconds, that this task is waiting for a resource. If the task does not wait, the wait time is 0.
• Wait type: The name of the most recent or current wait type.
• Wait for resource: The name of the resource you want.
• Blocked by: The ID of the session that is blocking the task if there is a blocking session.
• Header blocker: Identifies the session that caused the first blocking condition if there is a blocking session. A value of 1 indicates a header blocking program for another session.

2.2 Sp_who2

Under the master database, run the following statement:

[SQL] View plain copy

1. EXEC Sp_who2

You will see the following information, which resembles the activity monitor, which displays the blocking information requested by the current user. Just tabular, so that we filter out some irrelevant content.

The following statement allows you to import the display information for SP_WHO2 into a temporary table. Of course you can also import into a permanent table.

[SQL] View plain copy

2. CreateTable #sp_who2 (SPID int,status VARCHAR (255),
4. Login varchar (255), HostName varchar (255),
6. Blkby varchar (255), DBName varchar (255),
8. Command VARCHAR (255), CPUTime INT,
10. Diskio int,lastbatch VARCHAR (255),
12. ProgramName VARCHAR (255), SPID2 INT,
14. RequestID INT)
18. Insertinto #sp_who2 Execsp_who2
20. SELECT *
22. From #sp_who2
24. WHERE DBName <> ' master '
26. ORDER by DBName ASC
30. DROP TABLE #sp_who2

2.3 Using dynamic performance Views (recommended)

Dynamic performance views show more and richer information to help DBAs quickly diagnose "current" blocking phenomena. It also captures such things as SQL statements and the CPU time, memory size, total elapsed time, logical readings, etc. that have been used by the current SQL statement.

Use [master]
GO
SELECT
Db_name (blocked.database_id) as ' database ',
blocked.session_id as ' Blocked SPID ',
Blocked_sql. TEXT as ' blocked SQL ',
Waits.wait_type as ' Wait resource ',
blocking.session_id as ' Blocking SPID ',
Blocking_sql. TEXT as ' blocking SQL ',
Sess.status as ' blocking status ',
Sess.total_elapsed_time as ' blocking elapsed time ',
Sess.logical_reads as ' blocking logical reads ',
Sess.memory_usage as ' blocking memory usage ',
Sess.cpu_time as ' Blocking CPU time ',
Sess.program_name as ' blocking program ',
GETDATE () as ' timestamp '
From Sys.dm_exec_connections as Blocking
INNER JOIN sys.dm_exec_requests as Blocked on blocked.blocking_session_id = blocking.session_id
INNER JOIN sys.dm_os_waiting_tasks as Waits on Waits. session_id = blocked.session_id
INNER JOIN sys.dm_exec_sessions sess on sess.session_id = blocking.session_id
Cross APPLY Sys.dm_exec_sql_text (Blocking.most_recent_sql_handle) as Blocking_sql
Cross APPLY Sys.dm_exec_sql_text (Blocked.sql_handle) as Blocked_sql

How to monitor and troubleshoot SQL Server blocking (1) (current blocking)