--===========================================================================-- -- TABLES temporelles / MS Cloud Summit 2017 / 2017-01-09 version 1.0 -- --===========================================================================-- -- Fred Brouard, SQLpro - http://sqlpro.developpez.com - SQLpro@SQLspot.com -- -- Arian papillon - http://www.datafly.fr/ - a.papillon@datafly.com -- --===========================================================================-- --▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄-- -- Exemple 5b : Métadonnées des verrous -- --▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀-- /*------------------------------------------------------------------- [SCRIPT] dbo.sp_tran_locks [DATABASE] master (system object) [DESCRIPTION] sys.dm_db_tran_locks decoded [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20170122 [EXAMPLE] EXEC dbo.sp_tran_locks @spid = NULL, -- int @type = NULL, -- nvarchar(60) @mode = NULL, -- nvarchar(60) @status = NULL, -- nvarchar(60) @Database = NULL, -- sysname @BlockedOnly = 0 -- bit -------------------------------------------------------------------*/ USE master; GO CREATE OR ALTER PROCEDURE dbo.sp__tran_locks @spid INT = NULL , @type NVARCHAR(60) = NULL , @mode NVARCHAR(60) = NULL , @status NVARCHAR(60) = NULL , @Database sysname = NULL , @BlockedOnly BIT = 0 AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @locktable TABLE ( resource_type NVARCHAR(60) , request_session_id INT , DatabaseName sysname NULL , SchemaName sysname NULL , ObjectName sysname NULL , IndexName sysname NULL , IndexType NVARCHAR(60) , PartitionNumber NVARCHAR(16) , request_mode NVARCHAR(60) , request_status NVARCHAR(60) , blocking_session_id INT , host_name NVARCHAR(128) , program_name NVARCHAR(128) , login_name NVARCHAR(128) , LastRequestingText NVARCHAR(MAX) , Localize NVARCHAR(MAX) , resource_database_id INT , resource_description NVARCHAR(256) , resource_associated_entity_id BIGINT ); INSERT @locktable ( resource_type , request_session_id , DatabaseName , SchemaName , ObjectName , IndexName , IndexType , PartitionNumber , request_mode , request_status , blocking_session_id , [host_name] , [program_name] , login_name , LastRequestingText , Localize , resource_database_id , resource_description , resource_associated_entity_id ) SELECT resource_type , request_session_id , DB_NAME(resource_database_id) , '-' SchemaName , '-' ObjectName , '-' IndexName , '-' IndexType , '-' PartitionNumber , request_mode , request_status , dm_os_waiting_tasks.blocking_session_id , host_name , program_name , login_name , dm_exec_sql_text.text AS LastRequestingText , '' , resource_database_id , dm_tran_locks.resource_description , resource_associated_entity_id FROM sys.dm_tran_locks JOIN sys.dm_exec_sessions ON dm_tran_locks.request_session_id = dm_exec_sessions.session_id JOIN sys.dm_exec_connections ON dm_exec_connections.session_id = dm_tran_locks.request_session_id LEFT JOIN sys.dm_os_waiting_tasks ON dm_tran_locks.lock_owner_address = dm_os_waiting_tasks.resource_address CROSS APPLY sys.dm_exec_sql_text(dm_exec_connections.most_recent_sql_handle) WHERE ( resource_type = @type OR @type IS NULL ) AND ( request_session_id = @spid OR @spid IS NULL ) AND ( request_mode = @mode OR @mode IS NULL ) AND ( request_status = @status OR @status IS NULL ) AND ( DB_NAME(resource_database_id) = @Database OR @Database IS NULL ) AND ( dm_os_waiting_tasks.blocking_session_id IS NOT NULL OR @BlockedOnly = 0 ); DECLARE @resource_type NVARCHAR(60) , @resource_database_id INT , @resource_description NVARCHAR(256) , @resource_associated_entity_id BIGINT; DECLARE @fileid VARCHAR(4) , @pageid VARCHAR(10) , @slotid VARCHAR(10); DECLARE @objectid INT , @indexid INT , @partitionnumber INT; DECLARE @objectname sysname , @indexname sysname , @schemaname sysname , @indextype NVARCHAR(60); DECLARE @sqlcmd NVARCHAR(4000) , @localize NVARCHAR(MAX); DECLARE lockdecode CURSOR FOR SELECT resource_type , resource_database_id , resource_description , resource_associated_entity_id FROM @locktable; OPEN lockdecode; FETCH NEXT FROM lockdecode INTO @resource_type, @resource_database_id, @resource_description, @resource_associated_entity_id; WHILE ( @@FETCH_STATUS = 0 ) BEGIN IF @resource_type IN ( 'KEY', 'PAGE', 'RID' ) BEGIN SET @sqlcmd = 'SELECT @schemaname=s.name, @objectname=t.name' + ', @indexname=i.name, @indextype=i.type_desc, @partitionnumber=p.partition_number ' + 'FROM [' + DB_NAME(@resource_database_id) + '].sys.partitions p ' + 'JOIN [' + DB_NAME(@resource_database_id) + '].sys.indexes i ON p.object_id = i.object_id and p.index_id = i.index_id ' + 'JOIN [' + DB_NAME(@resource_database_id) + '].sys.objects t ON i.object_id = t.object_id ' + 'JOIN [' + DB_NAME(@resource_database_id) + '].sys.schemas s ON t.schema_id = s.schema_id ' + 'WHERE partition_id = ' + CAST(@resource_associated_entity_id AS VARCHAR(32)); EXEC sp_executesql @sqlcmd, N'@schemaname sysname OUT, @objectname sysname OUT, @indexname sysname OUT, @indextype nvarchar(60) OUT, @partitionnumber INT OUT', @schemaname OUT, @objectname OUT, @indexname OUT, @indextype OUT, @partitionnumber OUT; END; IF @resource_type = 'OBJECT' BEGIN SET @sqlcmd = 'SELECT @schemaname=s.name, @objectname=t.name ' + 'FROM [' + DB_NAME(@resource_database_id) + '].sys.objects t ' + 'JOIN [' + DB_NAME(@resource_database_id) + '].sys.schemas s ON t.schema_id = s.schema_id ' + 'WHERE t.object_id = ' + CAST(@resource_associated_entity_id AS VARCHAR(32)); EXEC sp_executesql @sqlcmd, N'@schemaname sysname OUT, @objectname sysname OUT', @schemaname OUT, @objectname OUT; UPDATE @locktable SET SchemaName = @schemaname , ObjectName = @objectname WHERE CURRENT OF lockdecode; END; ELSE IF @resource_type = 'PAGE' BEGIN SET @fileid = PARSENAME(RTRIM(REPLACE(@resource_description, ':', '.')), 2); SET @pageid = PARSENAME(RTRIM(REPLACE(@resource_description, ':', '.')), 1); SET @localize = 'SELECT sys.fn_physlocFormatter(%%physloc%%) PhysicalLocation,%%lockres%% AS LockResHash,' + @objectname + '.* FROM [' + DB_NAME(@resource_database_id) + '].[' + @schemaname + '].[' + @objectname + '] WITH (READUNCOMMITTED, INDEX = ' + ISNULL(@indexname, '0') + ') CROSS APPLY sys.fn_physlocCracker(%%physloc%%) PL WHERE PL.file_id=' + @fileid + ' AND PL.page_id=' + @pageid; UPDATE @locktable SET SchemaName = @schemaname , ObjectName = @objectname , IndexName = ISNULL(@indexname, '-') , Localize = RTRIM(@localize) , IndexType = @indextype , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16)) WHERE CURRENT OF lockdecode; END; ELSE IF @resource_type = 'RID' BEGIN SET @fileid = PARSENAME(RTRIM(REPLACE(@resource_description, ':', '.')), 3); SET @pageid = PARSENAME(RTRIM(REPLACE(@resource_description, ':', '.')), 2); SET @slotid = PARSENAME(RTRIM(REPLACE(@resource_description, ':', '.')), 1); SET @localize = 'SELECT sys.fn_physlocFormatter(%%physloc%%) PhysicalLocation,%%lockres%% AS LockResHash,' + @objectname + '.* FROM [' + DB_NAME(@resource_database_id) + '].[' + @schemaname + '].[' + @objectname + '] WITH (READUNCOMMITTED, INDEX = 0) CROSS APPLY sys.fn_physlocCracker(%%physloc%%) PL WHERE PL.file_id=' + @fileid + ' AND PL.page_id=' + @pageid + ' AND slot_id=' + @slotid; UPDATE @locktable SET SchemaName = @schemaname , ObjectName = @objectname , Localize = RTRIM(@localize) , IndexType = @indextype , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16)) WHERE CURRENT OF lockdecode; END; ELSE IF @resource_type = 'KEY' BEGIN SET @localize = 'SELECT %%lockres%% AS LockResHash,* FROM [' + DB_NAME(@resource_database_id) + '].[' + @schemaname + '].[' + @objectname + '] WITH (READUNCOMMITTED, INDEX = ' + @indexname + ') WHERE %%lockres%% = ''' + RTRIM(@resource_description) + ''''; UPDATE @locktable SET SchemaName = @schemaname , ObjectName = @objectname , IndexName = @indexname , IndexType = @indextype , Localize = RTRIM(@localize) , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16)) WHERE CURRENT OF lockdecode; END; FETCH NEXT FROM lockdecode INTO @resource_type, @resource_database_id, @resource_description, @resource_associated_entity_id; END; CLOSE lockdecode; DEALLOCATE lockdecode; SELECT resource_type , request_session_id , DatabaseName , SchemaName , ObjectName , IndexName , IndexType , PartitionNumber , request_mode , request_status , ISNULL(CAST(blocking_session_id AS VARCHAR(5)), '-') blocking_session_id , [host_name] , [program_name] , login_name , resource_database_id , resource_description , resource_associated_entity_id , LastRequestingText , Localize FROM @locktable; GO -- mark as system object EXEC sp_MS_marksystemobject 'sp_tran_locks'; GO EXEC dbo.sp__tran_locks --@BlockedOnly = 1;