Created
April 10, 2020 21:02
-
-
Save georgepaoli/7c2e0837dc0514e6d59eebd36e86868f to your computer and use it in GitHub Desktop.
Identificar Lock no MSSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Fonte: https://www.dirceuresende.com/blog/sql-server-como-identificar-locks-blocks-e-sessoes-bloqueadoras/ | |
| IF (OBJECT_ID('dbo.stpVerifica_Locks') IS NULL) EXEC('CREATE PROCEDURE stpVerifica_Locks AS SELECT 1') | |
| GO | |
| ALTER PROCEDURE [dbo].[stpVerifica_Locks] | |
| AS | |
| BEGIN | |
| SET NOCOUNT ON | |
| DECLARE @Monitoramento_Locks TABLE | |
| ( | |
| [nested_level] INT, | |
| [session_id] SMALLINT, | |
| [wait_info] NVARCHAR(4000), | |
| [wait_time_ms] BIGINT, | |
| [blocking_session_id] SMALLINT, | |
| [blocked_session_count] INT, | |
| [open_transaction_count] INT, | |
| [sql_text] XML, | |
| [sql_command] XML, | |
| [total_elapsed_time] INT, | |
| [deadlock_priority] INT, | |
| [transaction_isolation_level] VARCHAR(50), | |
| [last_request_start_time] DATETIME, | |
| [login_name] NVARCHAR(128), | |
| [nt_user_name] NVARCHAR(128), | |
| [original_login_name] NVARCHAR(128), | |
| [host_name] NVARCHAR(128), | |
| [program_name] NVARCHAR(128) | |
| ) | |
| INSERT INTO @Monitoramento_Locks | |
| SELECT | |
| NULL AS nested_level, | |
| A.session_id AS session_id, | |
| '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE | |
| WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999) | |
| WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']' | |
| ELSE '' | |
| END), '') AS wait_info, | |
| COALESCE(E.wait_duration_ms, B.wait_time) AS wait_time_ms, | |
| NULLIF(B.blocking_session_id, 0) AS blocking_session_id, | |
| COALESCE(F.blocked_session_count, 0) AS blocked_session_count, | |
| A.open_transaction_count, | |
| CAST('<?query --' + CHAR(10) + ( | |
| SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE | |
| WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2) | |
| ELSE B.statement_end_offset | |
| END | |
| ) - B.statement_start_offset | |
| ) / 2 + 1 | |
| ) | |
| ) + CHAR(10) + '--?>' AS XML) AS sql_text, | |
| CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command, | |
| A.total_elapsed_time, | |
| A.[deadlock_priority], | |
| (CASE B.transaction_isolation_level | |
| WHEN 0 THEN 'Unspecified' | |
| WHEN 1 THEN 'ReadUncommitted' | |
| WHEN 2 THEN 'ReadCommitted' | |
| WHEN 3 THEN 'Repeatable' | |
| WHEN 4 THEN 'Serializable' | |
| WHEN 5 THEN 'Snapshot' | |
| END) AS transaction_isolation_level, | |
| A.last_request_start_time, | |
| A.login_name, | |
| A.nt_user_name, | |
| A.original_login_name, | |
| A.[host_name], | |
| (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name] | |
| FROM | |
| sys.dm_exec_sessions AS A WITH (NOLOCK) | |
| LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id | |
| LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10) | |
| LEFT JOIN ( | |
| SELECT | |
| session_id, | |
| wait_type, | |
| wait_duration_ms, | |
| resource_description, | |
| ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking | |
| FROM | |
| sys.dm_os_waiting_tasks | |
| ) E ON A.session_id = E.session_id AND E.Ranking = 1 | |
| LEFT JOIN ( | |
| SELECT | |
| blocking_session_id, | |
| COUNT(*) AS blocked_session_count | |
| FROM | |
| sys.dm_exec_requests | |
| WHERE | |
| blocking_session_id <> 0 | |
| GROUP BY | |
| blocking_session_id | |
| ) F ON A.session_id = F.blocking_session_id | |
| LEFT JOIN sys.sysprocesses AS G WITH(NOLOCK) ON A.session_id = G.spid | |
| OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], G.[sql_handle])) AS X | |
| WHERE | |
| A.session_id > 50 | |
| AND A.session_id <> @@SPID | |
| AND ( | |
| (NULLIF(B.blocking_session_id, 0) IS NOT NULL OR COALESCE(F.blocked_session_count, 0) > 0) | |
| OR (A.session_id IN (SELECT NULLIF(blocking_session_id, 0) FROM sys.dm_exec_requests)) | |
| ) | |
| ------------------------------------------------ | |
| -- Gera o nível dos locks | |
| ------------------------------------------------ | |
| UPDATE @Monitoramento_Locks | |
| SET nested_level = 1 | |
| WHERE blocking_session_id IS NULL | |
| DECLARE @Contador INT = 2 | |
| WHILE(EXISTS(SELECT NULL FROM @Monitoramento_Locks WHERE nested_level IS NULL) AND @Contador < 50) | |
| BEGIN | |
| UPDATE A | |
| SET | |
| A.nested_level = @Contador | |
| FROM | |
| @Monitoramento_Locks A | |
| JOIN @Monitoramento_Locks B ON A.blocking_session_id = B.session_id | |
| WHERE | |
| A.nested_level IS NULL | |
| AND B.nested_level = (@Contador - 1) | |
| SET @Contador += 1 | |
| END | |
| UPDATE @Monitoramento_Locks | |
| SET nested_level = @Contador | |
| WHERE nested_level IS NULL | |
| SELECT * | |
| FROM @Monitoramento_Locks | |
| ORDER BY nested_level, blocked_session_count DESC, blocking_session_id, wait_time_ms DESC | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment