Procédure sp_tran_locks – Scripts Center

La DMV sys.dm_db_tran_locks décodée…

/*-------------------------------------------------------------------
[SCRIPT] dbo.sp_tran_locks system procedure
[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)
		, resource_subtype 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
			, resource_subtype
            , 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
			      , resource_subtype
                  , 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
          , resource_subtype
          , 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';

-- sample : 
-- EXEC dbo.sp_tran_locks @BlockedOnly = 1;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

/*-------------------------------------------------------------------

[SCRIPT] dbo.sp_tran_locks system procedure

[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;

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET NOCOUNT ON;

DECLARE @locktable TABLE

(

resource_type NVARCHAR(60)

, resource_subtype 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

, resource_subtype

, 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

, resource_subtype

, 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

, resource_subtype

, 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;

-- mark as system object

EXEC sp_MS_marksystemobject 'sp_tran_locks';

-- sample :

-- EXEC dbo.sp_tran_locks @BlockedOnly = 1;