Locks


Наиболее часто встречающиеся слова в статье:

[varchar] [nvarchar] [waitresource] [blocked] [lastwaittype] [program_name] [loginame] [hostname] [waittime] [DECLARE]


Статья:

CREATE TABLE [dbo].[Locks](
    [Описание] [varchar](30) NOT NULL,
    [spid] [int] NOT NULL,
    [status] [varchar](100) NULL,
    [ID блокирующего] [int] NULL,
    [loginame] [varchar](50) NULL,
    [hostname] [varchar](50) NULL,
    [Запрос] [nvarchar](max) NULL,
    [Date] [datetime] NOT NULL,
    [BlockCount] [int] NULL,
    [Подзапрос] [nvarchar](max) NULL,
    [program_name] [varchar](150) NULL,
    [waittype] [binary](2) NULL,
    [waittime] [bigint] NULL,
    [waitresource] [nvarchar](300) NULL,
    [lastwaittype] [nvarchar](40) NULL,
    [stmt_start] [int] NULL,
    [stmt_end] [int] NULL,
    [open_tran] [int] NULL,
    [cpu] [int] NULL,
    [dbid] [smallint] NULL,
    [exec_ms] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
DECLARE @bcount int;
DECLARE @CurDate datetime;
 
SELECT @CurDate = GETDATE();
 
IF OBJECT_ID(''tempdb..#ttWhoIsActive'') IS NOT NULL
BEGIN
    DROP TABLE #ttWhoIsActive
END 
 
CREATE TABLE #ttWhoIsActive 
    (
        session_id int
        , sql_text xml
    )
 
SELECT DISTINCT @bcount = count(*)
FROM 
    master.dbo.sysprocesses sp
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) gs
WHERE 
    blocked <> 0
 
exec [dbo].[sp_WhoIsActive] @output_column_list = ''[session_id][sql_text]'' , @destination_table = ''#ttWhoIsActive''
 
INSERT INTO [DB_TEMP].dbo.Locks ([Описание], [spid], [status], [ID блокирующего], [loginame], [hostname], [Запрос], [Date], [BlockCount], [Подзапрос], program_name, waittype, waittime, waitresource, lastwaittype, stmt_start, stmt_end, open_tran, cpu, dbid, exec_ms)
SELECT DISTINCT
    [Описание] = ''Блокированные''
    , [spid]
    , status = CAST(sp.status as varchar(20)) 
    , [ID блокирующего] = blocked
    , loginame = CAST(loginame as varchar(20))
    , hostname = CAST(hostname as varchar(10))
    , [Запрос] = gs.text
    , @CurDate as Date
    , NULL
    , [Подзапрос] = ISNULL(CAST(tt.sql_text AS varchar(max)), '''')
    , sp.program_name
    , sp.waittype
    , sp.waittime
    , sp.waitresource
    , sp.lastwaittype
    , sp.stmt_start
    , sp.stmt_end
    , sp.open_tran
    , sp.cpu
    , sp.dbid
    , DATEDIFF(millisecond, SP.last_batch, CURRENT_TIMESTAMP) AS "exec_ms"
FROM 
 
    master.dbo.sysprocesses sp
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) gs
    LEFT OUTER JOIN #ttWhoIsActive tt ON (sp.spid = tt.session_id)
WHERE 
    blocked <> 0
OPTION (MAXDOP 1)
 
INSERT INTO [DB_TEMP].dbo.Locks ([Описание], [spid], [status], [ID блокирующего], [loginame], [hostname], [Запрос], [Date], [BlockCount], [Подзапрос], program_name, waittype, waittime, waitresource, lastwaittype, stmt_start, stmt_end, open_tran, cpu, dbid, exec_ms)
SELECT DISTINCT
    [Описание] = ''Блокирующий''
     , spid
     ,status = CAST(sp.status as varchar(20))
     , [ID блокирующего] = NULL
     ,loginame   
     ,hostname   
     ,[Запрос] = gs.text
     , @CurDate as Date
     , @bcount
    , [Подзапрос] = ISNULL(CAST(tt.sql_text AS varchar(max)), '''')
    , sp.program_name
    , sp.waittype
    , sp.waittime
    , sp.waitresource
    , sp.lastwaittype
    , sp.stmt_start
    , sp.stmt_end
    , sp.open_tran
    , sp.cpu
    , sp.dbid
    , DATEDIFF(millisecond, SP.last_batch, CURRENT_TIMESTAMP) AS "exec_ms"
FROM 
    master.dbo.sysprocesses sp
    LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) gs
    LEFT OUTER JOIN #ttWhoIsActive tt ON (sp.spid = tt.session_id)
WHERE 
    spid IN (SELECT blocked FROM master.dbo.sysprocesses)
    AND blocked = 0
OPTION (MAXDOP 1)
 
IF OBJECT_ID(''tempdb..#ttWhoIsActive'') IS NOT NULL
BEGIN
    DROP TABLE #ttWhoIsActive
END 
 
 
---- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE [dbo].[Locks2](
    [type] [varchar](20) NULL,
    [tree] [varchar](100) NULL,
    [spid] [int] NULL,
    [loginame] [nvarchar](128) NULL,
    [hostname] [sysname] NOT NULL,
    [program_name] [nvarchar](256) NULL,
    [lastwaittype] [nvarchar](32) NULL,
    [Запрос] [nvarchar](max) NULL,
    [Подзапрос] [nvarchar](max) NULL,
    [waitresource] [nvarchar](256) NULL,
    [waitresourcedesc] [nvarchar](200) NULL,
    [dbccbuffer] [nvarchar](100) NULL,
    [dbname] [nvarchar](100) NULL,
    [objectname] [nvarchar](100) NULL,
    [cpu] [bigint] NULL,
    [physical_io] [bigint] NULL,
    [memusage] [bigint] NULL,
    [status] [nvarchar](30) NULL,
    [login_time] [datetime] NULL,
    [last_batch] [datetime] NULL,
    [waittime] [bigint] NULL,
    [blocked] [int] NULL,
    [Date] [datetime] NULL,
    [sort] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
use master
create proc [dbo].[sp_tree_locks]
(
    @IsExtend int = 0, 
    @SaveTableName nvarchar(100) = ''''
)
--with exec as self
AS
 
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF
 
IF OBJECT_ID(''tempdb..#locks'') IS NOT NULL DROP TABLE #locks;
CREATE TABLE #locks
(
    spid smallint not null
    , kpid smallint not null
    , blocked smallint not null
    , lastwaittype nchar(32) not null
    , waitresource nchar(256) not null
    , waitresourcedesc nvarchar(200) not null
    , dbccbuffer nvarchar(100) null
    , dbname nvarchar(128) null
    , objectname nvarchar(100) null
    , cpu int not null
    , physical_io bigint not null
    , memusage int not null
    , [status] nchar(30)
    , loginame nchar(128) not null
    , login_time datetime not null
    , last_batch datetime not null
    , waittime bigint not null
    , hostname sysname
    , program_name nvarchar(256)
    , sql_handle binary(20)
    , tree varchar(8000) not null
    , dl_num int not null
    , sort int not null
    , full_statement_code varchar(max)
    , executing_statement varchar(max)
 
    , primary key clustered (dl_num, sort)
);
 
IF OBJECT_ID(''tempdb..#process'') IS NOT NULL DROP TABLE #process 
 
CREATE TABLE #process
(
    spid smallint not null
    , kpid smallint not null
    , blocked smallint not null
    , lastwaittype nchar(32) not null
    , waitresource nchar(256) not null
    , dbname nvarchar(128) null
    , cpu int not null
    , physical_io bigint not null
    , memusage int not null
    , status nchar(30)
    , loginame nchar(128) not null
    , login_time datetime not null
    , last_batch datetime not null
    , waittime bigint not null
    , hostname sysname
    , program_name nvarchar(256)
    , sql_handle binary(20)
--    , primary key clustered (spid, kpid, cpu, physical_io)
);
 
IF OBJECT_ID(''tempdb..#LockTmp'') IS NOT NULL DROP TABLE #LockTmp 
CREATE TABLE #LockTmp (
    [type] [varchar](20) NULL,
    [tree] [varchar](100) NULL,
    [spid] [int] NULL,
    [loginame] [nvarchar](128) NULL,
    [hostname] [sysname] NOT NULL,
    [program_name] [nvarchar](256) NULL,
    [lastwaittype] [nvarchar](32) NULL,
    [Запрос] [nvarchar](max) NULL,
    [Подзапрос] [nvarchar](max) NULL,
    [waitresource] [nvarchar](256) NULL,
    [waitresourcedesc] [nvarchar](200) NULL,
    [dbccbuffer] [nvarchar](100) NULL,
    [dbname] [nvarchar](100) NULL,
    [objectname] [nvarchar](100) NULL,
    [cpu] [bigint] NULL,
    [physical_io] [bigint] NULL,
    [memusage] [bigint] NULL,
    [status] [nvarchar](30) NULL,
    [login_time] [datetime] NULL,
    [last_batch] [datetime] NULL,
    [waittime] [bigint] NULL,
    [blocked] [int] NULL,
    [sort] [int] NULL
);
 
INSERT #process
SELECT DISTINCT
    spid
    , kpid
    , blocked
    , lastwaittype
    , waitresource
    , db_name(sp.dbid) as dbname
    , cpu
    , physical_io
    , memusage
    , sp.[status]
    , loginame
    , login_time
    , last_batch
    , waittime
    , hostname
    , program_name
    , sp.sql_handle
FROM 
    sys.sysprocesses sp
 
/*
DECLARE @q varchar(max) = ''create index [''+cast(newid() as varchar(200))+''] on #process (blocked)''
EXEC (@q)
*/
CREATE CLUSTERED INDEX IDX_spid ON #process (spid)
CREATE INDEX IDX_Block ON #process (blocked)
 
DELETE 
FROM 
    #process 
WHERE 
    blocked = 0 
    AND NOT EXISTS
        (
            SELECT * 
            FROM 
                #process P
            WHERE 
                #process.spid = P.blocked
        );
--    and spid not in (select blocked from #process);
 
WITH mcte AS
(
    SELECT *,
        min(blocked) over (partition by spid) min_blocked,
        row_number() over (partition by spid order by blocked) rn
    FROM #process
)
, cte AS
(
    SELECT
        spid
        , kpid
        , blocked
        , lastwaittype
        , waitresource
        , waitresourcedesc = ''''
        , dbccbuffer = ''''
        , dbname
        , cpu
        , physical_io
        , memusage
        , status
        , loginame
        , login_time
        , last_batch
        , waittime
        , hostname
        , program_name
        , sql_handle
        , convert(varchar(8000),spid) list
        , 0 lev
        , min_blocked
        , rn
    FROM 
        mcte
    WHERE 
        min_blocked = 0
    UNION ALL
    SELECT
        m.spid
        , m.kpid
        , m.blocked
        , m.lastwaittype
        , m.waitresource
        , waitresourcedesc = ''''
        , dbccbuffer = ''''
        , m.dbname
        , m.cpu
        , m.physical_io
        , m.memusage
        , m.status
        , m.loginame
        , m.login_time
        , m.last_batch
        , m.waittime
        , m.hostname
        , m.program_name
        , m.sql_handle
        , c.list+case when m.blocked = m.spid then '''' else '';''+convert(varchar(8000),m.spid) end
        , case when m.blocked = m.spid then c.lev else c.lev+1 end
        , m.min_blocked
        , m.rn--, m.ds, m.qte
    FROM 
        cte c 
        JOIN mcte m ON (c.spid = m.blocked)
        AND c.rn = 1
)
INSERT #locks
SELECT
    spid
    , kpid
    , blocked
    , lastwaittype
    , waitresource
    , waitresourcedesc = ''''
    , dbccbuffer = ''''
    , dbname
    , objectname = ''''
    , cpu
    , physical_io
    , memusage
    , status
    , loginame
    , login_time
    , last_batch
    , waittime
    , hostname
    , program_name
    , sql_handle
    , replicate('' '',5*lev)+case when blocked=spid then ''.'' else '''' end+convert(varchar(8000),spid)
    , 0
    , row_number() over (order by list, -abs(blocked-spid))
    , NULL
    , NULL
FROM 
    cte 
OPTION (maxrecursion 10000);
 
CREATE INDEX IX_spid ON #locks (spid)
 
UPDATE l
SET 
    [full_statement_code] = DEST.[text] 
    , [executing_statement] = 
        CASE   
            WHEN SDER.[statement_start_offset] > 0 THEN  
            --The start of the active command is not at the beginning of the full command text 
            CASE SDER.[statement_end_offset]  
                WHEN -1 THEN  
                  --The end of the full command is also the end of the active statement 
                    SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647) 
                ELSE   
                  --The end of the active statement is not at the end of the full command 
                    SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2 + 2)   
                END  
            ELSE  
            --1st part of full command is running 
                CASE SDER.[statement_end_offset]  
                    WHEN -1 THEN  
                  --The end of the full command is also the end of the active statement 
                        RTRIM(LTRIM(DEST.[text]))  
                    ELSE  
                  --The end of the active statement is not at the end of the full command 
                        LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)  
            END  
        END 
        , objectname = OBJECT_SCHEMA_NAME(DEST.ObjectID, DEST.dbid) + ''.'' + OBJECT_NAME(DEST.ObjectID, DEST.dbid)
FROM 
    #locks l
    LEFT OUTER JOIN sys.dm_exec_requests SDER ON (l.spid = SDER.session_id)
    CROSS APPLY sys.dm_exec_sql_text(SDER.[sql_handle]) DEST  
 
IF @IsExtend = 1
BEGIN
    DECLARE @spid int
    DECLARE @waitresource varchar(50)
    DECLARE @waitresourcedesc varchar(200)
    DECLARE @dbccbuffer varchar(100)
    DECLARE @sql nvarchar(2000)
 
    DECLARE spid_curs CURSOR FOR   
    SELECT DISTINCT spid, waitresource
    FROM 
        #locks
    WHERE 
        spid NOT IN
            (
            SELECT DISTINCT spid FROM #locks
            where 
                spid = blocked
            )
 
    OPEN spid_curs
 
    FETCH NEXT FROM spid_curs   
    INTO @spid, @waitresource
 
    WHILE @@FETCH_STATUS = 0  
    BEGIN
 
        SET @waitresourcedesc = ''''
--        SET @sql = N''[master].[dbo].[sp_dbcc_page] @Res = N'''''' + @waitresource + '''''', @objstr = @objstr OUTPUT''
--        EXECUTE sp_executesql @sql, N''@objstr varchar(200) OUTPUT'', @objstr = @ OUTPUT
/*------------------------------------------*/
--        EXECUTE [master].[dbo].[sp_dbcc_page] @Res = @waitresource, @objstr = @waitresourcedesc OUTPUT
 
        DECLARE @Res varchar(50)
        DECLARE @objstr varchar(200) 
        DECLARE @i int, @ip int
        DECLARE @tmpRes varchar(50)
        DECLARE @dbname varchar(100), @dbid int, @fileid int, @pageid int
        DECLARE @objectid int, @indexid int, @keyid bigint
        DECLARE @objectname int, @indexname int
        DECLARE @Result varchar(200)
 
        SET @Res = @waitresource
        SET @objstr = ''''
 
        IF CHARINDEX(''PAG'', LEFT(@Res, 5)) > 0 
        BEGIN
 
            IF OBJECT_ID(''tempdb.dbo.#DBCCPAGE'') IS NOT NULL DROP TABLE #DBCCPAGE;
            CREATE TABLE #DBCCPAGE (
                   ParentObject VARCHAR(255),
                   [OBJECT]     VARCHAR(255),
                   Field       VARCHAR(255),
                   [VALUE]     VARCHAR(255) );
 
            SET @i = CHARINDEX('':'', @Res, 3) + 1;
            SET @ip = CHARINDEX('':'', @Res, @i) ;
            SELECT @dbid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
            SET @i = @ip + 1
            SET @ip = CHARINDEX('':'', @Res, @i);
            SELECT @fileid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
            SET @i = @ip + 1
            IF RIGHT(@Res, 1) = '',''
            BEGIN
                SET @ip = CHARINDEX('','', @Res, @i);
            END 
            ELSE
            BEGIN
                SET @ip = LEN(@Res) + 1;
            END
 
            SELECT @pageid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
 
            DBCC TRACEON(3604) WITH NO_INFOMSGS
 
            INSERT INTO #DBCCPAGE
            EXEC(''DBCC PAGE('' + @dbid + '', '' + @fileid + '', '' + @pageid + '') WITH TABLERESULTS, NO_INFOMSGS'');
            DBCC TRACEOFF(3604) WITH NO_INFOMSGS
 
            SELECT 
                @objectid = VALUE 
            FROM 
                #DBCCPAGE
            WHERE 
                Field = ''Metadata: ObjectId''
 
            SELECT 
                @indexid = VALUE 
            FROM 
                #DBCCPAGE
            WHERE 
                Field = ''Metadata: IndexId''
 
            SET @sql = ''
                USE '' + DB_NAME(@dbid) + '';
 
                SELECT TOP 1
                    @ostr = o.name + '''' (Object), '''' + ISNULL(i.name, '''''''')  + '''' (Index)'''' 
                FROM 
                    sys.objects o WITH (NOLOCK)
                    LEFT OUTER JOIN sys.indexes i  WITH (NOLOCK) ON (o.object_id = i.object_id)
                WHERE 
                    o.object_id = '' + CAST(@objectid as varchar(10)) + ''
                    and i.index_id = '' + CAST(@indexid as varchar(3))+ '';     
 
                    ''
        --    EXEC(@sql)
            EXECUTE sp_executesql @sql, N''@ostr varchar(200) OUTPUT'',  @ostr = @objstr OUTPUT
 
            IF OBJECT_ID(''tempdb.dbo.#DBCCPAGE'') IS NOT NULL DROP TABLE #DBCCPAGE;
        END -- PAG
 
        IF CHARINDEX(''KEY'', LEFT(@Res, 5)) > 0 
        BEGIN
            SET @i = CHARINDEX('':'', @Res, 3) + 1;
            SET @ip = CHARINDEX('':'', @Res, @i) ;
            SELECT @dbid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
            SET @i = @ip + 1
            SET @ip = CHARINDEX(''('', @Res, @i);
            SELECT @keyid = CAST(SUBSTRING(@Res, @i, @ip - @i) as bigint)
 
            SET @sql = ''
                USE '' + DB_NAME(@dbid) + '';
 
                SELECT TOP 1
                    @ostr = o.name + '''' (Object), '''' + ISNULL(i.name, '''''''')  + '''' (Index)'''' 
                FROM 
                    sys.partitions p  WITH (NOLOCK)
                    INNER JOIN sys.objects o  WITH (NOLOCK) ON p.object_id = o.object_id 
                    INNER JOIN sys.indexes i  WITH (NOLOCK) ON (p.object_id = i.object_id AND p.index_id = i.index_id)
                WHERE 
                    p.hobt_id = '' + CAST(@keyid AS varchar(30)) + '';
                ''
 
            EXECUTE sp_executesql @sql, N''@ostr varchar(200) OUTPUT'',  @ostr = @objstr OUTPUT
        END --KEY
 
        IF CHARINDEX(''TAB'', LEFT(@Res, 5)) > 0 
        BEGIN
            SET @i = CHARINDEX('':'', @Res, 3) + 1;
            SET @ip = CHARINDEX('':'', @Res, @i) ;
            SELECT @dbid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
            IF DB_NAME(@dbid) != ''tempdb''
            BEGIN
                SET @i = @ip + 1
                SET @ip = CHARINDEX('':'', @Res, @i);
                SELECT @objectid = CAST(SUBSTRING(@Res, @i, @ip - @i) as int)
 
                SELECT @objstr = OBJECT_NAME(@objectid, @dbid) + '' (Object)''
            END
        END -- TAB
 
        SET @waitresourcedesc = @objstr;
 
/*---------------------------------------------*/
 
 
 
/*---------------------------------*/
        SET @dbccbuffer = ''''
/*
        SET @sql = N''[master].[dbo].[sp_inputbuffer] @spid = N'''''' + CAST(@spid as varchar(10)) + '''''', @objstr = @objstr OUTPUT''
        EXECUTE sp_executesql @sql, N''@objstr varchar(200) OUTPUT'', @objstr = @dbccbuffer OUTPUT
*/
 
 
        BEGIN TRY;
            IF OBJECT_ID(''tempdb..#dbccinputbuffer'') IS NOT NULL DROP TABLE #dbccinputbuffer
            CREATE TABLE #dbccinputbuffer
                (
                    EventType nvarchar(30), 
                    [Parameters] smallint, 
                    EventInfo nvarchar(4000)
                )
            SET @sql = ''DBCC INPUTBUFFER('' + CAST(@spid as varchar(10)) + '') WITH NO_INFOMSGS;''
            INSERT INTO #dbccinputbuffer EXEC(@sql)
 
            SELECT TOP 1 @dbccbuffer = CAST(EventInfo as varchar(100)) FROM #dbccinputbuffer
 
            IF OBJECT_ID(''tempdb..#dbccinputbuffer'') IS NOT NULL DROP TABLE #dbccinputbuffer
        END TRY
        BEGIN CATCH
            IF OBJECT_ID(''tempdb..#dbccinputbuffer'') IS NOT NULL DROP TABLE #dbccinputbuffer
        END CATCH;
 
 
/*
        BEGIN TRY;
 
        DECLARE @dbccinputbuffer TABLE 
            (
                EventType nvarchar(30), 
                [Parameters] smallint, 
                EventInfo nvarchar(4000)
            )
            INSERT @dbccinputbuffer
            (
                EventType,
                Parameters,
                EventInfo
            )
            EXEC sp_executesql
                N''DBCC INPUTBUFFER(@spid) WITH NO_INFOMSGS;'',
                N''@spid SMALLINT'',
                @spid;
 
        SELECT TOP 1 @dbccbuffer = CAST(EventInfo as varchar(100)) FROM @dbccinputbuffer
 
        END TRY
        BEGIN CATCH
        END CATCH;
*/
/*---------------------------------*/
 
        UPDATE #locks
        SET 
            waitresourcedesc = @waitresourcedesc
            , dbccbuffer = @dbccbuffer
        WHERE spid = @spid
 
        FETCH NEXT FROM spid_curs   
        INTO @spid, @waitresource
    END 
 
    CLOSE spid_curs;  
    DEALLOCATE spid_curs; 
END --IF @IsExtend = 1
 
 
DELETE FROM #process
WHERE spid in (SELECT spid FROM #locks);
 
--SELECT * FROM #locks
 
    WITH selflock 
    AS
    (
        SELECT DISTINCT 
            spid
            , (SELECT lastwaittype + '','' 
                       FROM (SELECT DISTINCT spid, RTRIM(LTRIM(lastwaittype)) as lastwaittype FROM #locks) l1
                      WHERE l1.spid = l.spid
                      ORDER BY lastwaittype
                        FOR XML PATH('''') ) AS lastwaittypes
            , (SELECT waitresource + '','' 
                       FROM (SELECT DISTINCT spid, RTRIM(LTRIM(waitresource)) as waitresource FROM #locks) l1
                      WHERE l1.spid = l.spid
                      ORDER BY waitresource
                        FOR XML PATH('''') ) AS waitresources
            , count(*) as cou
        FROM 
            #locks l
        WHERE 
            spid = blocked
        GROUP BY spid
    ), chainlock
    AS (
        SELECT DISTINCT 
            spid
            , (SELECT lastwaittype + '','' 
                       FROM (SELECT DISTINCT spid, RTRIM(LTRIM(lastwaittype)) as lastwaittype FROM #locks) l1
                      WHERE l1.spid = l.spid
                      ORDER BY lastwaittype
                        FOR XML PATH('''') ) AS lastwaittypes
            , (SELECT waitresource + '','' 
                       FROM (SELECT DISTINCT spid, RTRIM(LTRIM(waitresource)) as waitresource FROM #locks) l1
                      WHERE l1.spid = l.spid
                      ORDER BY waitresource
                        FOR XML PATH('''') ) AS waitresources
            , count(*) as cou
        FROM 
            #locks l
        WHERE 
            spid != blocked
        GROUP BY spid
    )
 
--    select * from selflock
 
    INSERT INTO #LockTmp 
    SELECT 
        [type]
        , tree
        , spid
        , loginame
        , hostname
        , 
        CASE 
            WHEN CHARINDEX(''SQLAgent - TSQL JobStep'', program_name) = 1
            THEN 
                (SELECT 
                    [name] 
                FROM 
                    msdb.dbo.sysjobs 
                WHERE 
                    job_id = CAST(CONVERT(binary(16), SUBSTRING(program_name, 30, 34), 1) as uniqueidentifier)) + ''  /'' + program_name + ''/''
            ELSE
                program_name
        END as program_name
        , lastwaittype
        , [Запрос]
        , [Подзапрос]
        , waitresource
        , waitresourcedesc
        , dbccbuffer
        , dbname
        , objectname = ISNULL(objectname, '''')
        , cpu
        , physical_io
        , memusage
        , status
        , login_time
        , last_batch
        , waittime
        , blocked
        , sort
    FROM
    (
        SELECT 
            [type]
            , tree
            , spid
            , loginame
            , hostname
            , program_name
            , lastwaittype
            , ISNULL([full_statement_code], '''') as [Запрос]
            , ISNULL([executing_statement], '''') as [Подзапрос]
            , waitresource
            , waitresourcedesc
            , dbccbuffer
            , dbname
            , objectname
            , cpu
            , physical_io
            , memusage
            , status
            , login_time
            , last_batch
            , waittime
            , blocked
            , sort
        FROM
            (
            SELECT
                CASE WHEN l.dl_num = 0
                    THEN ''locks''
                    ELSE ''dlock '' + right(''000000''+convert(varchar,l.dl_num),2)
                END [type]
                , l.tree
                , l.spid
                , l.loginame
                , l.hostname
                , l.program_name
                , l.lastwaittype
                , chainlock.waitresources as waitresource
                , l.waitresourcedesc
                , l.dbccbuffer
                , l.dbname
                , l.objectname
                , l.cpu
                , l.physical_io
                , l.memusage
                , l.status
                , l.login_time
                , l.last_batch
                , l.waittime
                , l.blocked
                , [full_statement_code]
                , [executing_statement]
                , l.sort
                , ROW_NUMBER() OVER (PARTITION BY l.spid ORDER BY full_statement_code DESC, l.sql_handle DESC, loginame DESC, physical_io DESC) AS rn
            FROM 
                #locks l
                LEFT OUTER JOIN chainlock ON (l.spid = chainlock.spid)
            ) B
        WHERE 
            spid NOT IN (SELECT spid FROM selflock)
            AND rn = 1
        UNION ALL
        SELECT 
            [type]
            , tree
            , spid
            , loginame
            , hostname
            , program_name
            , lastwaittype
            , ISNULL([full_statement_code], '''') as [Запрос]
            , ISNULL([executing_statement], '''') as [Подзапрос]
            , waitresource
            , waitresourcedesc
            , dbccbuffer
            , dbname
            , objectname
            , cpu
            , physical_io
            , memusage
            , status
            , login_time
            , last_batch
            , waittime
            , blocked
            , sort
        FROM
        (
            select
                case when l.dl_num = 0
                    then ''self''
                    else ''dlock ''+right(''000000''+convert(varchar,l.dl_num),2)
                end [type]
                , l.tree
                , l.spid
                , l.kpid
                , l.blocked
                , l.lastwaittype
                , selflock.waitresources AS waitresource
                , '''' as waitresourcedesc
                , '''' as dbccbuffer
                , '''' as objectname
                , l.dbname
                , l.cpu
                , l.physical_io
                , l.memusage
                , l.status
                , l.loginame
                , l.login_time
                , l.last_batch
                , l.waittime
                , l.hostname
                , l.program_name
                , [full_statement_code]
                , [executing_statement]
                , l.sort
                , ROW_NUMBER() OVER (PARTITION BY l.spid ORDER BY full_statement_code DESC, l.sql_handle DESC, loginame DESC, physical_io DESC) AS rn
            from 
                #locks l
                INNER JOIN selflock ON (l.spid = selflock.spid)
            ) A
        WHERE rn = 1
    ) TMP
--    ORDER BY [type], sort
 
IF @SaveTableName = ''''
BEGIN
    SELECT * FROM #LockTmp     ORDER BY [type], sort
END
ELSE
BEGIN
 
    set @sql = ''
    DECLARE @CurDate datetime
    SET @CurDate = GETDATE()
    INSERT INTO '' + @SaveTableName + ''
    SELECT 
        [type] 
        , tree 
        , spid 
        , loginame 
        , hostname 
        , program_name 
        , lastwaittype 
        , апрос] 
        , одзапрос] 
        , waitresource 
        , waitresourcedesc
        , dbccbuffer
        , dbname 
        , objectname
        , cpu 
        , physical_io 
        , memusage 
        , [status] 
        , login_time 
        , last_batch 
        , waittime 
        , blocked 
        , @CurDate
        , sort
    FROM 
        #LockTmp
    ''
    EXEC (@sql);
 
    SELECT * FROM #LockTmp ORDER BY [type], sort
END
 
IF OBJECT_ID(''tempdb..#process'') IS NOT NULL DROP TABLE #process
IF OBJECT_ID(''tempdb..#locks'') IS NOT NULL DROP TABLE #locks;
IF OBJECT_ID(''tempdb..#LockTmp'') IS NOT NULL DROP TABLE #LockTmp 
GO
 
exec sp_tree_locks 
        @IsExtend = 1
        , @SaveTableName = ''[DB_TEMP].[dbo].[Locks2]''