info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
T-SQL - Find Lead Blocking SPID
by BF (Principal Consultant; Architecture; Engineering)
2015-06-28
MSDN:
Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.
The duration and transaction context of a query determine how long its locks are held and, thereby, their impact on other queries. If the query is not executed within a transaction (and no lock hints are used), the locks for SELECT statements will only be held on a resource at the time it is actually being read, not for the duration of the query. For INSERT, UPDATE, and DELETE statements, the locks are held for the duration of the query, both for data consistency and to allow the query to be rolled back if necessary.
For queries executed within a transaction, the duration for which the locks are held are determined by the type of query, the transaction isolation level, and whether or not lock hints are used in the query. For a description of locking, lock hints, and transaction isolation levels, see the following topics in SQL Server Books Online:
• Locking in the Database Engine
• Customizing Locking and Row Versioning
• Lock Modes
• Lock Compatibility
• Row Versioning-based Isolation Levels in the Database Engine
• Controlling Transactions (Database Engine)
When locking and blocking increase to the point where there is a detrimental effect on system performance, it is usually due to one of the following reasons:
•A SPID holds locks on a set of resources for an extended period of time before releasing them. This type of blocking resolves itself over time, but can cause performance degradation.
•A SPID holds locks on a set of resources and never releases them. This type of blocking does not resolve itself and prevents access to the affected resources indefinitely.
In the first scenario above, the blocking problem resolves itself over time as the SPID releases the locks. However, the situation can be very fluid as different SPIDs cause blocking on different resources over time, creating a moving target. For this reason, these situations can be difficult to troubleshoot using SQL Server Enterprise Manager or individual SQL queries. The second situation results in a consistent state that can be easier to diagnose.
T-SQL Script:
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD - '
ELSE '|------ ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
--T-SQL: Find Lead Blocking SPID - TotalBlocked and InputBuffer
select * from sysprocesses where blocked <> 0
select blocked as IsBlocking, COUNT(blocked) as TotalBlocked from sysprocesses where blocked <> 0 group by blocked order by TotalBlocked desc
declare @blocked int
select @blocked = t1.blocked from (
select top 1 blocked, COUNT(blocked) as blockedcount from sysprocesses where blocked <> 0 group by blocked order by blockedcount desc
) t1
If @blocked is not null
Begin
DBCC INPUTBUFFER(@blocked)
End
Image #1:
--T-SQL #2: Find Lead Blocking SPID(s)
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD - '
ELSE '|------ ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
(Source: blog.sqlauthority.com)
--T-SQL: Find Lead Blocking SPID(s)
--Find Blocking SPID
IF EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses where blocked<> 0))
SELECT
'BLOCKING' as 'MSG',
sp.spid, sp.[status], sp.loginame, sp.hostname, sp.[program_name], sp.blocked, sp.open_tran, dbname=db_name(sp.[dbid]),
sp.cmd, sp.waittype, sp.waittime, sp.last_batch, st.[text]
FROM master.dbo.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.[sql_handle]) st
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
ELSE
SELECT 'No blocking processes found!'
--Find SPIDS blocked:
Select
'BLOCKED' as 'MSG'
,* from sysprocesses where blocked <> 0
--T-SQL: Find Blocked SPIDs Resource Info
SELECT
DTL.[resource_type] AS [resource type]
,CASE
WHEN DTL.[resource_type] IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ('KEY', 'PAGE', 'RID') THEN (SELECT
(CASE
WHEN s.name IS NOT NULL THEN s.name + '.'
ELSE ''
END) + OBJECT_NAME(p.[object_id])
FROM sys.partitions p
INNER JOIN sys.objects o
ON o.object_id = p.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.[hobt_id] = DTL.[resource_associated_entity_id])
ELSE 'Unidentified'
END AS [Parent Object]
,DTL.[request_mode] AS [Lock Type]
,DTL.[request_status] AS [Request Status]
,wt.*
,dtl.*
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_tran_locks DTL
ON DTL.lock_owner_address = WT.resource_address
WHERE wt.blocking_session_id IS NOT NULL;
(Source: sqlservercentral.com)
Example:
keylock hobtid=72057594044219392
dbid=10
id=lock2037a4cc80
mode=X
associatedObjectId=72057594044219392
SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594044219392
--TSQL: Find Blocked SPIDs Info
WITH [Blocking]
AS (SELECT w.[session_id]
,s.[original_login_name]
,s.[login_name]
,w.[wait_duration_ms]
,w.[wait_type]
,r.[status]
,r.[wait_resource]
,w.[resource_description]
,s.[program_name]
,w.[blocking_session_id]
,s.[host_name]
,r.[command]
,r.[percent_complete]
,r.[cpu_time]
,r.[total_elapsed_time]
,r.[reads]
,r.[writes]
,r.[logical_reads]
,r.[row_count]
,q.[text]
,q.[dbid]
,p.[query_plan]
,r.[plan_handle]
FROM [sys].[dm_os_waiting_tasks] w
INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
WHERE w.[session_id] > 50
AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
,b.[blocking_session_id] AS [BlockingSessionID]
,b.[login_name] AS [WaitingUserSessionLogin]
,s1.[login_name] AS [BlockingUserSessionLogin]
,b.[original_login_name] AS [WaitingUserConnectionLogin]
,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
,b.[wait_duration_ms] AS [WaitDuration_ms]
,b.[wait_type] AS [WaitType]
,t.[request_mode] AS [WaitRequestMode]
,UPPER(b.[status]) AS [WaitingProcessStatus]
,UPPER(s1.[status]) AS [BlockingSessionStatus]
,b.[wait_resource] AS [WaitResource]
,t.[resource_type] AS [WaitResourceType]
,t.[resource_database_id] AS [WaitResourceDatabaseID]
,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
,b.[resource_description] AS [WaitResourceDescription]
,b.[program_name] AS [WaitingSessionProgramName]
,s1.[program_name] AS [BlockingSessionProgramName]
,b.[host_name] AS [WaitingHost]
,s1.[host_name] AS [BlockingHost]
,b.[command] AS [WaitingCommandType]
,b.[text] AS [WaitingCommandText]
,b.[row_count] AS [WaitingCommandRowCount]
,b.[percent_complete] AS [WaitingCommandPercentComplete]
,b.[cpu_time] AS [WaitingCommandCPUTime]
,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
,b.[reads] AS [WaitingCommandReads]
,b.[writes] AS [WaitingCommandWrites]
,b.[logical_reads] AS [WaitingCommandLogicalReads]
,b.[query_plan] AS [WaitingCommandQueryPlan]
,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
Order By [WaitDuration_ms] desc
GO
|
|
|
|
|
|
|
|
|
|