USE master
GO
IF OBJECT_ID('dbo.sp_wholock') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_wholock AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_wholock
@Delay TINYINT = 2,
@IgnoreSystem BIT = 1,
@IgnoreSleeping BIT = 1,
@IncludeMemoryGrants BIT = 1,
@IncludeSQLStmt BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
PRINT '
EXEC dbo.sp_wholock @Delay = 2, @IgnoreSystem = 1, @IgnoreSleeping = 1, @IncludeMemoryGrants = 1, @IncludeSQLStmt = 1
'
IF OBJECT_ID('tempdb..#sp_who2') IS NULL
CREATE TABLE #sp_who2 (SPID SMALLINT,
status VARCHAR(30),
LOGIN VARCHAR(128),
hostname VARCHAR(128),
BlkBy VARCHAR(5),
DBName VARCHAR(128),
Command VARCHAR(128),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(30),
ProgramName VARCHAR(256),
SPID2 SMALLINT,
request_id INT,
CommandText VARCHAR(MAX))
ELSE
TRUNCATE TABLE #sp_who2
INSERT #sp_who2
(SPID,
status,
LOGIN,
hostname,
BlkBy,
DBName,
Command,
CPUTime,
DiskIO,
LastBatch,
ProgramName,
SPID2,
request_id)
EXEC sp_who2
UPDATE #sp_who2
SET blkby = NULL
WHERE BLKBY NOT LIKE '%[0-9]%'
IF OBJECT_ID('Util.SE.SQLExecution') IS NOT NULL
UPDATE s2
SET LOGIN = e.UserName,
ProgramName = 'SERVICE_BROKER' + ISNULL(', EGId: ' + CAST(ExecutionGroupId AS VARCHAR), '') + ', EId: ' + CAST(ExecutionId AS VARCHAR),
DBName = e.DatabaseName,
CommandText = e.CommandText,
LastBatch = CONVERT(VARCHAR(5), e.DateAdded, 101) + ' ' + CONVERT(VARCHAR(8), e.DateAdded, 114)
FROM #sp_who2 s2 (NOLOCK)
INNER JOIN master.sys.sysprocesses (NOLOCK) p ON s2.SPID = p.SPID
INNER JOIN Util.SE.SQLExecution (NOLOCK) e ON e.ExecutionId = CAST(CAST(p.CONTEXT_INFO AS BINARY(4)) AS INT)
WHERE ISNULL(p.[hostname], '') = ''
AND ISNULL(p.[program_name], '') = ''
AND p.[loginame] = 'sa'
AND p.sql_handle = 0x0
AND p.CONTEXT_INFO <> 0x0
IF OBJECT_ID('TEMPDB..#temp_buffer') IS NULL
CREATE TABLE #temp_buffer (EventType VARCHAR(30),
PARAMETERS INT,
EventInfo VARCHAR(4000))
IF OBJECT_ID('TEMPDB..#buffer') IS NULL
CREATE TABLE #buffer (SPID SMALLINT,
EventInfo VARCHAR(4000))
ELSE
TRUNCATE TABLE #buffer
DECLARE @BlockList TABLE (SPID SMALLINT PRIMARY KEY CLUSTERED,
SQLStmt VARCHAR(MAX))
IF @IncludeSQLStmt = 0
INSERT @BlockList
(SPID)
SELECT DISTINCT
bl.SPID
FROM #sp_who2 s1
OUTER APPLY (SELECT s1.SPID
UNION ALL
SELECT s2.SPID
FROM #sp_who2 s2
WHERE s2.SPID = s1.blkby) bl
WHERE blkby LIKE '%[0-9]%'
ELSE
IF @IncludeSQLStmt = 1
INSERT @BlockList
(SPID,
SQLStmt)
SELECT SPID,
ISNULL(CommandText, SQLStmt) AS SQLStmt
FROM (SELECT DISTINCT
bl.SPID,
bl.CommandText
FROM #sp_who2 s1
OUTER APPLY (SELECT s1.SPID,
s1.CommandText
UNION ALL
SELECT s2.SPID,
s2.CommandText
FROM #sp_who2 s2
WHERE s2.SPID = s1.blkby) bl
WHERE blkby LIKE '%[0-9]%') a
OUTER APPLY (SELECT TOP 1
LTRIM(RTRIM(CASE WHEN stmt_end IN (0, -1)
AND stmt_start = 0 THEN NULL
WHEN (stmt_end > stmt_start
OR stmt_end = -1)
AND st.TEXT <> ''
THEN LTRIM(RTRIM(SUBSTRING(st.TEXT, stmt_start / 2, (CASE WHEN stmt_end = -1 THEN 9999999
ELSE stmt_end
END - stmt_start) / 2 + 1)))
END)) AS SQLStmt
FROM master.dbo.sysprocesses sp (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) st
WHERE sql_handle <> 0x0
AND st.TEXT <> ''
AND SP.SPID = A.SPID) B
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = Util.dbo.StringConcat(DISTINCT 'TRUNCATE TABLE #temp_buffer
BEGIN TRY
INSERT #temp_buffer(EventType, Parameters, EventInfo)
EXEC(''DBCC INPUTBUFFER(' + CAST(SPID AS VARCHAR) + ') WITH NO_INFOMSGS'')
INSERT #buffer (SPID, EventInfo) SELECT ' + CAST(SPID AS VARCHAR) + ', LTRIM(RTRIM(EventInfo))
FROM #temp_buffer
END TRY
BEGIN CATCH
END CATCH', '
')
FROM @BlockList
IF @SQL <> ''
EXEC(@SQL )
IF EXISTS ( SELECT *
FROM #buffer )
BEGIN
;
WITH comp
AS (SELECT s1.SPID,
s1.BlkBy,
'SPID: ' + CAST(s1.SPID AS VARCHAR) + ', Login: ' + s1.LOGIN + ', DBName: ' + s1.DBName + ', ProcessCount : '
+ CAST(s1.COUNTER AS VARCHAR) + ', Command: ' + s1.Command + ', LastBatchSec: ' + CAST(s1.LastBatchSec AS VARCHAR)
+ ', CPUTime: ' + LTRIM(RTRIM(s1.CPUTime)) + ', DiskIO: ' + LTRIM(RTRIM(s1.DiskIO)) + ', Program: ' + s1.ProgramName
+ ', Host: ' + s1.hostname AS BlockedInfo,
s1.EventInfo AS BlockedEventInfo,
bl.SQLStmt AS BlockedSQLStmt,
'SPID: ' + CAST(s1.blkby AS VARCHAR) + ', Blocking: Count ' + CAST(b3.BlockedCount AS VARCHAR) + ', List '
+ ISNULL(BlockedProcesses, '') + ', Login: ' + b2.LOGIN + ', DBName: ' + b2.DBName + ', ProcessCount : '
+ CAST(b2.COUNTER AS VARCHAR) + ', Command: ' + b2.Command + ', LastBatchSec: ' + CAST(b2.LastBatchSec AS VARCHAR)
+ ', CPUTime: ' + LTRIM(RTRIM(b2.CPUTime)) + ', DiskIO: ' + LTRIM(RTRIM(b2.DiskIO)) + ', Program: ' + b2.ProgramName
+ ', Host: ' + b2.hostname AS BlockingInfo,
b2.EventInfo AS BlockingEventInfo,
bk.SQLStmt AS BlockingSQLStmt
FROM (SELECT s1.SPID,
CAST(ISNULL(MAX(CASE WHEN s1.blkby LIKE '%[0-9]%' THEN s1.blkby
END), 0) AS SMALLINT) AS blkby,
COUNT(*) AS COUNTER,
ISNULL(LTRIM(RTRIM(MAX(s1.LOGIN))), '') AS LOGIN,
ISNULL(LTRIM(RTRIM(MAX(s1.hostname))), '') AS hostname,
ISNULL(LTRIM(RTRIM(MAX(s1.ProgramName))), '') AS ProgramName,
ISNULL(LTRIM(RTRIM(MAX(s1.DBName))), '') AS DBName,
ISNULL(Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(s1.Command)), ', '), '') AS Command,
ISNULL(SUM(CAST(s1.CPUTime AS BIGINT)), 0) AS CPUTime,
ISNULL(MAX(lb.LastBatchSec), 0) AS LastBatchSec,
ISNULL(SUM(CAST(s1.DiskIO AS BIGINT)), 0) AS DiskIO,
ISNULL(MAX(b1.EventInfo), '') AS EventInfo
FROM #sp_who2 s1
INNER JOIN @BlockList bl ON s1.SPID = bl.SPID
CROSS APPLY (SELECT DATEDIFF(SECOND,
CAST(LEFT(s1.LastBatch, 5) + '/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)
+ RIGHT(s1.LastBatch, 9) AS DATETIME), GETDATE()) AS LastBatchSec) lb
OUTER APPLY (SELECT TOP 1 EventInfo FROM #buffer b1 WHERE b1.SPID = s1.SPID) b1
WHERE s1.blkby LIKE '%[0-9]%'
GROUP BY s1.SPID) s1
INNER JOIN @BlockList bk ON bk.SPID = s1.blkby
INNER JOIN @BlockList bl ON s1.SPID = bl.SPID
OUTER APPLY (SELECT ISNULL(LTRIM(RTRIM(MAX(s2.LOGIN))), '') AS LOGIN,
COUNT(*) AS COUNTER,
ISNULL(LTRIM(RTRIM(MAX(s2.hostname))), '') AS hostname,
ISNULL(LTRIM(RTRIM(MAX(s2.ProgramName))), '') AS ProgramName,
ISNULL(LTRIM(RTRIM(MAX(s2.DBName))), '') AS DBName,
ISNULL(Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(s2.Command)), ', '), '') AS Command,
ISNULL(SUM(CAST(s2.CPUTime AS BIGINT)), 0) AS CPUTime,
MAX(DATEDIFF(SECOND,
CAST(LEFT(s2.LastBatch, 5) + '/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + RIGHT(s2.LastBatch, 9) AS DATETIME),
GETDATE())) AS LastBatchSec,
ISNULL(SUM(CAST(s2.DiskIO AS BIGINT)), 0) AS DiskIO,
ISNULL(MAX(b2.EventInfo), '') AS EventInfo
FROM #sp_who2 s2
LEFT OUTER JOIN #buffer b2 ON b2.SPID = s1.blkby
WHERE s1.blkby = s2.SPID) b2
OUTER APPLY (SELECT COUNT(DISTINCT s3.SPID) AS BlockedCount,
Util.dbo.StringConcat(DISTINCT s3.SPID, ', ') AS BlockedProcesses
FROM #sp_who2 s3
WHERE s3.blkby LIKE '%[0-9]%'
AND s3.blkby = s1.blkby) b3),
step2
AS (SELECT ' -- /* BLOCKED PROCESS REPORT BY BLOCKED PROCESS */ --
' + Util.dbo.StringConcat(SQL, '
') AS SQL
FROM (SELECT '-- /* BLOCKED PROCESS */
' + Util.dbo.StringConcat(CASE WHEN SPID = BLKBY THEN ' /* !!! BLOCKING ITSELF !!!*/' + BlockedInfo + '
' + CASE WHEN BlockedSQLStmt <> BlockedEventInfo THEN '/* ******************** Blocked Statement Start ******************** */
' + BlockedSQLStmt + '
/* ******************** Blocked Statement End ******************** */
' ELSE ''
END + BlockedEventInfo ELSE +'
' + BlockedInfo + '
' + BlockedEventInfo + '
-- /* BLOCKING PROCESS */
' + BlockingInfo + '
' + CASE WHEN BlockingSQLStmt <> BlockingEventInfo THEN '/* ******************** Blocking Statement Start ******************** */
' + BlockingSQLStmt + '
/* ******************** Blocking Statement End ******************** */
' ELSE ''
END + BlockingEventInfo
END + '
------------------------------------------------------------------------------------------------------------------------------------------------', '
') AS SQL
FROM comp) kk
UNION ALL
SELECT '-- /* BLOCKED PROCESS REPORT BY BLOCKING PROCESS */ --
' + Util.dbo.StringConcat(SQL, '
')
FROM (SELECT '-- /* BLOCKING PROCESS */
' + BlockingInfo + '
' + CASE WHEN BlockingSQLStmt <> BlockingEventInfo THEN '/* ******************** Blocking Statement Start ******************** */
' + BlockingSQLStmt + '
/* ******************** Blocking Statement End ******************** */
' ELSE ''
END + BlockingEventInfo + '
' + Util.dbo.StringConcat('-- /* BLOCKED PROCESS */
' + BlockedInfo + '
' + CASE WHEN BlockedSQLStmt <> BlockedEventInfo THEN '/* ******************** Blocked Statement Start ******************** */
' + BlockedSQLStmt + '
/* ******************** Blocked Statement End ******************** */
' ELSE ''
END + BlockedEventInfo, '
') AS SQL
FROM comp
GROUP BY BlockingInfo,
BlockingEventInfo,
BlockingSQLStmt) kk)
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM step2
EXEC Util.dbo.PrintLargeText
@SQL
END
IF @Delay > 0
BEGIN
IF OBJECT_ID('tempdb..#sp_wholock_temp_delay') IS NOT NULL
DROP TABLE #sp_wholock_temp_delay
SET @SQL = 'WAITFOR DELAY ''00:00:' + RIGHT('0' + CAST(@Delay AS VARCHAR), 2) + ''''
EXEC (@SQL)
IF OBJECT_ID('tempdb..#sp_who2_Delay') IS NULL
CREATE TABLE #sp_who2_Delay (SPID SMALLINT,
status VARCHAR(30),
LOGIN VARCHAR(128),
hostname VARCHAR(128),
BlkBy VARCHAR(5),
DBName VARCHAR(128),
Command VARCHAR(128),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(30),
ProgramName VARCHAR(128),
SPID2 SMALLINT,
request_id INT,
CommandText VARCHAR(MAX))
ELSE
TRUNCATE TABLE #sp_who2_Delay
INSERT #sp_who2_Delay
(SPID,
status,
LOGIN,
hostname,
BlkBy,
DBName,
Command,
CPUTime,
DiskIO,
LastBatch,
ProgramName,
SPID2,
request_id)
EXEC sp_who2
UPDATE #sp_who2_Delay
SET blkby = NULL
WHERE BLKBY NOT LIKE '%[0-9]%'
IF OBJECT_ID('Util.SE.SQLExecution') IS NOT NULL
UPDATE s2
SET LOGIN = e.UserName,
ProgramName = 'SERVICE_BROKER' + ISNULL(', EGId: ' + CAST(ExecutionGroupId AS VARCHAR), '') + ', EId: ' + CAST(ExecutionId AS VARCHAR),
DBName = e.DatabaseName,
CommandText = e.CommandText,
LastBatch = CONVERT(VARCHAR(5), e.DateAdded, 101) + ' ' + CONVERT(VARCHAR(8), e.DateAdded, 114)
FROM #sp_who2_Delay s2 (NOLOCK)
INNER JOIN master.sys.sysprocesses (NOLOCK) p ON s2.SPID = p.SPID
INNER JOIN Util.SE.SQLExecution (NOLOCK) e ON e.ExecutionId = CAST(CAST(p.CONTEXT_INFO AS BINARY(4)) AS INT)
WHERE ISNULL(p.[hostname], '') = ''
AND ISNULL(p.[program_name], '') = ''
AND p.[loginame] = 'sa'
AND p.sql_handle = 0x0
AND p.CONTEXT_INFO <> 0x0 ;
WITH step1
AS (SELECT s1.*,
CASE WHEN ISNULL(s1.CommandText, b.EventInfo) <> ''
THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + ISNULL (s1.CommandText, b.EventInfo) + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE)
END AS InputBuffer
FROM (SELECT s1.SPID,
COUNT(*) AS ProcCnt,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(STATUS)), ', ') AS STATUS,
MAX(LTRIM(RTRIM(LOGIN))) AS LOGIN,
MAX(LTRIM(RTRIM(hostname))) AS hostname,
MAX(blkby) AS BlkBy,
(SELECT Util.dbo.StringConcat (DISTINCT s2.SPID, ',') FROM #sp_who2 s2 WHERE s2.blkby = s1.SPID) AS Blocking,
MAX(LTRIM(RTRIM(DBName))) AS DBName,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(Command)), ', ') AS Command,
SUM(CAST(CPUTime AS BIGINT)) AS CPUTime,
SUM(CAST(DiskIO AS BIGINT)) AS DiskIO,
MAX(DATEDIFF(SECOND,
CAST(LEFT(s1.LastBatch, 5) + '/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + RIGHT(s1.LastBatch, 9) AS DATETIME),
GETDATE())) AS LastBatchSec,
MAX(LTRIM(RTRIM(ProgramName))) AS ProgramName,
MAX(s1.CommandText) AS CommandText
FROM #sp_who2 s1
WHERE EXISTS ( SELECT *
FROM @BlockList b
WHERE b.SPID = s1.SPID )
OR ((@IgnoreSleeping = 0
OR (@IgnoreSleeping = 1
AND NOT LTRIM(RTRIM(STATUS)) = 'sleeping'))
AND s1.CommandText IS NOT NULL
OR ((@IgnoreSystem = 0
OR (@IgnoreSystem = 1
AND NOT EXISTS ( SELECT *
FROM master.dbo.sysprocesses m (NOLOCK)
WHERE m.SPID = s1.SPID
AND [context_info] > 0x0 )
AND NOT (LTRIM(hostname) = '.'
AND LTRIM(programname) = ''
AND LOGIN = 'sa')))))
GROUP BY s1.SPID) s1
LEFT OUTER JOIN #buffer b ON b.SPID = s1.SPID),
step2
AS (SELECT s1.*
FROM (SELECT s1.SPID,
COUNT(*) AS ProcCnt,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(STATUS)), ', ') AS STATUS,
MAX(LTRIM(RTRIM(LOGIN))) AS LOGIN,
MAX(LTRIM(RTRIM(hostname))) AS hostname,
MAX(blkby) AS BlkBy,
(SELECT Util.dbo.StringConcat (DISTINCT s2.SPID, ',') FROM #sp_who2_Delay s2 WHERE s2.blkby = s1.SPID) AS Blocking,
MAX(LTRIM(RTRIM(DBName))) AS DBName,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(Command)), ', ') AS Command,
SUM(CAST(CPUTime AS BIGINT)) AS CPUTime,
SUM(CAST(DiskIO AS BIGINT)) AS DiskIO,
MAX(DATEDIFF(SECOND,
CAST(LEFT(LastBatch, 5) + '/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + RIGHT(LastBatch, 9) AS DATETIME),
GETDATE())) AS LastBatchSec,
MAX(LTRIM(RTRIM(ProgramName))) AS ProgramName
FROM #sp_who2_Delay s1
WHERE EXISTS ( SELECT *
FROM @BlockList b
WHERE b.SPID = s1.SPID )
OR ((@IgnoreSleeping = 0
OR (@IgnoreSleeping = 1
AND NOT LTRIM(RTRIM(STATUS)) = 'sleeping'))
AND s1.CommandText <> ''
OR ((@IgnoreSystem = 0
OR (@IgnoreSystem = 1
AND NOT EXISTS ( SELECT *
FROM master.dbo.sysprocesses m (NOLOCK)
WHERE m.SPID = s1.SPID
AND [context_info] > 0X0 )
AND NOT (LTRIM(hostname) = '.'
AND LTRIM(programname) = ''
AND LOGIN = 'sa')))))
GROUP BY s1.SPID) s1)
SELECT ISNULL(l.SPID, r.SPID) AS SPID,
l.blkby AS OldBlkBy,
r.BlkBy AS NewBlkBy,
l.Blocking AS OldBlocking,
r.Blocking AS NewBlocking,
--CASE WHEN l.spid IS NULL THEN 'new'
-- WHEN r.spid IS NULL THEN 'old'
-- ELSE ''
--END AS Change,
l.ProcCnt,
--ISNULL(r.ProcCnt, 0) - ISNULL(l.ProcCnt, 0) AS ProcDiff,
l.[Status] AS OldStatus,
r.[Status] AS NEWStatus,
ISNULL(l.LOGIN, r.LOGIN) AS [Login],
ISNULL(l.hostname, r.hostname) AS HOST,
ISNULL(l.programname, r.ProgramName) AS Program,
ISNULL(l.dbname, r.dbname) AS DBName,
l.Command AS OldCommand,
r.Command AS NewCommand,
CASE WHEN mg.granted_memory_kb = 0 THEN NULL
ELSE CAST(mg.granted_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS grant_mem_mb,
CASE WHEN mg.requested_memory_kb = 0 THEN NULL
ELSE CAST(mg.requested_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS req_mem_mb,
CASE WHEN mg.used_memory_kb = 0 THEN NULL
ELSE CAST(mg.used_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS used_mem_mb,
ISNULL(l.cputime, r.cputime) AS CPUTime,
ISNULL(r.cputime, 0) - ISNULL(l.cputime, 0) AS CPUDiff,
ISNULL(l.DiskIo, r.DiskIo) AS DiskIO,
ISNULL(r.DiskIo, 0) - ISNULL(l.diskio, 0) AS DiskIODiff,
l.LastBatchSec AS OldLastBatch,
r.LastBatchSec AS NewLastBatch,
l.INPUTBUFFER
INTO #sp_wholock_temp_delay
FROM step1 l
FULL OUTER JOIN step2 r ON l.SPID = r.SPID
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg (NOLOCK) ON @IncludeMemoryGrants = 1
AND mg.session_id = ISNULL(l.SPID, r.SPID)
OPTION (RECOMPILE)
IF EXISTS ( SELECT *
FROM @BlockList )
SELECT *
FROM #sp_wholock_temp_delay a
WHERE EXISTS ( SELECT *
FROM @BlockList b
WHERE b.SPID = a.SPID )
SELECT *
FROM #sp_wholock_temp_delay a
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#sp_wholock_temp') IS NOT NULL
DROP TABLE #sp_wholock_temp
SELECT s1.SPID,
s1.BlkBy,
s1.Blocking,
s1.ProcCnt,
s1.LOGIN,
s1.HOST,
s1.Program,
s1.DBName,
s1.Command,
CASE WHEN mg.granted_memory_kb = 0 THEN NULL
ELSE CAST(mg.granted_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS grant_mem_mb,
CASE WHEN mg.requested_memory_kb = 0 THEN NULL
ELSE CAST(mg.requested_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS req_mem_mb,
CASE WHEN mg.used_memory_kb = 0 THEN NULL
ELSE CAST(mg.used_memory_kb / 1024.0 AS NUMERIC(12, 2))
END AS used_mem_mb,
s1.CPUTime,
s1.DiskIO,
s1.LastBatch,
CASE WHEN ISNULL(s1.CommandText, b.EventInfo) <> ''
THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + ISNULL (s1.CommandText, b.EventInfo) + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE)
END AS INPUTBUFFER
INTO #sp_wholock_temp
FROM (SELECT s1.SPID,
COUNT(*) AS ProcCnt,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(STATUS)), ', ') AS [Status],
MAX(LTRIM(RTRIM(LOGIN))) AS [Login],
MAX(LTRIM(RTRIM(hostname))) AS HOST,
MAX(LTRIM(RTRIM(ProgramName))) AS Program,
MAX(blkby) AS BlkBy,
(SELECT Util.dbo.StringConcat (DISTINCT s2.SPID, ',') FROM #sp_who2 s2 WHERE s2.blkby = s1.SPID) AS Blocking,
MAX(LTRIM(RTRIM(DBName))) AS DBName,
Util.dbo.StringConcat(DISTINCT LTRIM(RTRIM(Command)), ', ') AS Command,
SUM(CAST(CPUTime AS BIGINT)) AS CPUTime,
SUM(CAST(DiskIO AS BIGINT)) AS DiskIO,
MAX(DATEDIFF(SECOND, CAST(LEFT(LastBatch, 5) + '/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + RIGHT(LastBatch, 9) AS DATETIME),
GETDATE())) AS LastBatch,
MAX(s1.CommandText) AS CommandText
FROM #sp_who2 s1
WHERE EXISTS ( SELECT *
FROM @BlockList b
WHERE b.SPID = s1.SPID )
OR ((@IgnoreSleeping = 0
OR (@IgnoreSleeping = 1
AND NOT LTRIM(RTRIM(STATUS)) = 'sleeping'))
AND s1.CommandText IS NOT NULL
OR ((@IgnoreSystem = 0
OR (@IgnoreSystem = 1
AND NOT EXISTS ( SELECT *
FROM master.dbo.sysprocesses m (NOLOCK)
WHERE m.SPID = s1.SPID
AND [context_info] > 0X0 )
AND NOT (LTRIM(hostname) = '.'
AND LTRIM(programname) = ''
AND LOGIN = 'sa')))))
GROUP BY s1.SPID) s1
LEFT OUTER JOIN #buffer b ON b.SPID = s1.SPID
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg (NOLOCK) ON @IncludeMemoryGrants = 1
AND mg.session_id = s1.SPID
OPTION (RECOMPILE)
IF EXISTS ( SELECT *
FROM @BlockList )
SELECT *
FROM #sp_wholock_temp a
WHERE EXISTS ( SELECT *
FROM @BlockList b
WHERE b.SPID = a.SPID )
SELECT *
FROM #sp_wholock_temp
END
GO
GRANT EXEC ON dbo.sp_wholock TO PUBLIC
GO
EXEC sys.sp_ms_marksystemobject
sp_wholock
GO