[ms sql] 잠금현상 조회

USE master

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

CREATE procedure sp_nrSpidByStatus

@status varchar(20) = 'runnable'

as

 

/*

exec sp_nrSpidByStatus                        -- all spids whith status runnable

exec sp_nrSpidByStatus 'sleeping'        -- all spids whith status sleeping

exec sp_nrSpidByStatus 'background'        -- all spids whith status background

exec sp_nrSpidByStatus 'sleeping'        -- all spids whith status sleeping

exec sp_nrSpidByStatus 'all'                -- all spids

exec sp_nrSpidByStatus 'blk'                -- all blocked or blocking spids

exec sp_nrSpidByStatus '74'                -- an individual spid - also gives subthreads

select * from master..sysprocesses where spid = 56

*/

set transaction isolation level read uncommitted

set nocount on

 

declare        @cmd varchar(3000)

 

declare @buf varchar(3000) ,

        @id int ,

        @spid int ,

        @maxSpid int

        create table #spid (spid int, command varchar(2000) null)

        create table #temp (x varchar(100), y int, s varchar(2000), id int identity (1,1))

        create table #spids (spid int)

        if isnumeric(@status) = 1

        begin

                insert #spids select @status

        end

        else if @status = 'blk'

        begin

                insert        #spids 

                select spid from master..sysprocesses where blocked <> 0

                union

                select blocked from master..sysprocesses where blocked <> 0

        end

        else

        begin

                insert        #spids 

select spid from master..sysprocesses 

where (status = @status or @status = 'all') and ecid = 0

        end

        select         @spid = 0 ,

                @maxSpid = max(spid)

        from        #spids

 

        while @spid < @maxSpid

        begin

                select        @spid = min(spid) from #spids where spid > @spid

 

                select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'

 

                delete #temp

 

                insert #temp

                exec (@cmd)

 

                select         @id = 0 ,

                        @buf = ''

                select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')

 

                from #temp

 

                insert         #spid

                select        @spid, @buf

        end

 

        select         blk = case 

when s.blocked <> 0 then convert(varchar(3),s.blocked) 

else ' ' 

end ,

                spid = convert(varchar(4),#spid.spid) ,

                s.physical_io ,

                status = left(s.status,12) ,

                last_batch = convert(varchar(23),s.last_batch,121) ,

                s.cmd ,

                #spid.command ,

                login_time = convert(varchar(23),s.login_time,121) ,

                s.HostName

        from        #spid ,

                master..sysprocesses s

        where        s.spid = #spid.spid

        and         (ecid = 0 or isnumeric(@status) = 1)

        order by s.status, #spid.spid

 

        drop table #spid

        drop table #temp

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON 

GO

출처 : http://www.phpschool.com/bbs2/inc_view.html?id=10977&code=tnt2&start=0&mode=&field=&search_name=&operator=&period=&category_id=&s_que=

 

SELECT s.session_id AS spid
    ,s.[status]
,t.[text] AS lastSQLText
    ,s.login_name AS loginName
    ,s.[host_name] AS hostName
    ,r.blocking_session_id AS blkBy
    ,r.wait_time
    ,r.wait_type
    ,r.last_wait_type  
    ,r.percent_complete
    ,DB_NAME(s.database_id) AS dbName
    ,r.command
    ,s.cpu_time AS cpuTime
    ,s.reads + s.writes AS diskIO
    ,s.last_request_end_time AS lastBatch
    ,s.[program_name] AS programName
    ,r.request_id
    ,CASE
        WHEN s.transaction_isolation_level = 0 THEN 'Unspecified'
        WHEN s.transaction_isolation_level = 1 THEN 'ReadUncommitted'
        WHEN s.transaction_isolation_level = 2 THEN 'ReadCommitted'
        WHEN s.transaction_isolation_level = 3 THEN 'Repeatable'
        WHEN s.transaction_isolation_level = 4 THEN 'Serializable'
        WHEN s.transaction_isolation_level = 5 THEN 'Snapshot'
    END AS transactionIsolationLevel
    ,OBJECT_NAME(t.objectid) AS objectName 
FROM sys.dm_exec_sessions AS s
    LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
    LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.is_user_process = 1
AND s.[program_name] = 'Internet Information Services'
AND DB_NAME(s.database_id) = 'DBNAME'