USE master
GO
IF OBJECT_ID('dbo.sp_whoio') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_whoio AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_whoio
@delay TINYINT = 1,
@ignoresleeping BIT = 1,
@showonlyblocked BIT = 0,
@DbccInputbuffer BIT = 1,
@IgnoreSystem BIT = 1,
@GetResourceDescription BIT = 1,
@Get_os_wait_stats BIT = 0,
@status VARCHAR(30) = NULL,
@lastwaittype VARCHAR(32) = NULL,
@hostname VARCHAR(128) = NULL,
@program_name VARCHAR(128) = NULL,
@cmd VARCHAR(16) = NULL,
@loginame VARCHAR(128) = NULL,
@ntuser VARCHAR(128) = NULL,
@database SYSNAME = NULL,
@PrintInfo BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
DECLARE @TempTableExists BIT = 0
PRINT 'RETURN
EXEC dbo.sp_whoio
@delay = 1 /*tinyint*/
,@ignoresleeping = 1 /*bit*/
,@showonlyblocked = 0 /*bit*/
,@DbccInputbuffer = 1 /*bit*/
,@IgnoreSystem = 1 /*bit*/
,@GetResourceDescription = 1 /*bit*/
,@Get_os_wait_stats = 0 /*bit*/
,@status = NULL /*varchar(30)*/
,@lastwaittype = NULL /*varchar(32)*/
,@hostname = NULL /*varchar(128)*/
,@program_name = NULL /*varchar(128)*/
,@cmd = NULL /*varchar(16)*/
,@loginame = NULL /*varchar(128)*/
,@ntuser = NULL /*varchar(128)*/
,@database = NULL /*sysname*/
,@PrintInfo = 1 /*bit*/
GO
'
IF OBJECT_ID('tempdb.dbo.#sysprocesses') IS NOT NULL
DROP TABLE #sysprocesses
SELECT [spid],
[blocked],
[waittime],
[lastwaittype],
[waitresource],
CASE WHEN ecid = 0 THEN [dbid]
END AS [dbid],
dc.name AS DBName,
[cpu],
[physical_io],
[memusage],
CASE WHEN ecid = 0 THEN [last_batch]
END AS [last_batch],
[open_tran],
sp.status,
sh.HostName AS [hostname],
CASE WHEN ecid = 0 THEN RTRIM(LTRIM([program_name]))
END AS [program_name],
CASE WHEN ecid = 0 THEN RTRIM(LTRIM([cmd]))
END AS [cmd],
CASE WHEN ecid = 0 THEN RTRIM(LTRIM([loginame]))
END AS [loginame],
CASE WHEN ecid = 0 THEN NULLIF(CASE WHEN nt_domain <> '' THEN RTRIM(LTRIM(nt_domain)) + '\'
ELSE ''
END + ISNULL(RTRIM(LTRIM(nt_username)), ''), '')
END AS [ntuser],
CASE WHEN SQLHandle <> 0x0 THEN (SELECT CAST( TEXT AS VARCHAR (MAX)) FROM sys.dm_exec_sql_text (SQLHandle))
END AS SQLBatch,
CASE WHEN ecid = 0 THEN STMTStart
END AS stmt_start,
CASE WHEN ecid = 0 THEN STMTEnd
END AS stmt_end,
NULLIF(sp.CONTEXT_INFO, 0x0) AS CONTEXT_INFO
INTO #sysprocesses
FROM master.dbo.sysprocesses sp WITH (NOLOCK)
LEFT OUTER JOIN sys.databases dc (NOLOCK) ON ecid = 0
AND dc.database_id = sp.dbid
LEFT OUTER JOIN sys.dm_exec_requests ex (NOLOCK) ON ex.session_id = sp.SPID
AND sp.sql_handle = 0x0
AND ex.sql_handle <> 0x0
CROSS APPLY (SELECT ISNULL(ex.sql_handle, sp.sql_handle) AS SQLHandle,
ISNULL(ex.statement_start_offset, sp.stmt_start) AS STMTStart,
ISNULL(ex.statement_end_offset, sp.stmt_end) AS STMTEnd,
CASE WHEN ecid = 0
AND sp.[hostname] = ''
AND sp.[program_name] = ''
AND sp.[loginame] = 'sa'
AND ex.sql_handle <> 0x0
AND sp.CONTEXT_INFO <> 0x0 THEN 'SERVICE_BROKER, EId: ' + CAST(CAST(CAST(sp.CONTEXT_INFO AS BINARY(4)) AS INT) AS VARCHAR)
WHEN ecid = 0 THEN RTRIM(LTRIM(sp.[hostname]))
END AS HostName) sh
WHERE sp.SPID <> @@SPID
AND (sp.CONTEXT_INFO > 0x0
OR (@IgnoreSystem = 0
OR (NOT ([loginame] IN ('sa', '')
AND sp.[hostname] IN ('', '.')
AND [program_name] = ''))))
DECLARE @waitfordelay VARCHAR(30) = '00:00:' + RIGHT('00' + CAST(@delay AS VARCHAR), 2),
@SQL VARCHAR(MAX)
-- Getting data over a time window to allow the io_delta metric calculation
DECLARE @io1 TABLE (SPID INT,
physical_io BIGINT,
cpu BIGINT,
[waittime] BIGINT)
DECLARE @io2 TABLE (SPID INT,
physical_io BIGINT,
cpu BIGINT,
[waittime] BIGINT)
DECLARE @wait1 TABLE ([wait_type] VARCHAR(60) NOT NULL,
[waiting_tasks_count] BIGINT NOT NULL,
[wait_time_ms] BIGINT NOT NULL)
DECLARE @wait2 TABLE ([wait_type] VARCHAR(60) NOT NULL,
[waiting_tasks_count] BIGINT NOT NULL,
[wait_time_ms] BIGINT NOT NULL)
IF @delay > 0
BEGIN
INSERT @io1
(SPID,
physical_io,
cpu,
[waittime])
SELECT SPID,
SUM(CAST(physical_io AS BIGINT)) AS physical_io,
SUM(CAST(cpu AS BIGINT)) AS cpu,
SUM(CAST([waittime] AS BIGINT)) AS [waittime]
FROM #sysprocesses WITH (NOLOCK)
GROUP BY SPID
IF @Get_os_wait_stats = 1
BEGIN
INSERT @wait1
([wait_type],
[waiting_tasks_count],
[wait_time_ms])
SELECT wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats (NOLOCK)
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN')
END
END
IF OBJECT_ID('TEMPDB..#ResourceResult') IS NULL
BEGIN
CREATE TABLE #ResourceResult (SPID SMALLINT,
waitresource VARCHAR(1000),
ResourceDescription VARCHAR(8000))
END
ELSE
BEGIN
TRUNCATE TABLE #ResourceResult
END
IF OBJECT_ID('TEMPDB..#spid_blocked') IS NOT NULL
DROP TABLE #spid_blocked
SELECT SPID,
CASE WHEN blocked > 0 THEN blocked
ELSE NULL
END AS blocked,
LTRIM(RTRIM(CAST(waitresource AS VARCHAR(1000)))) AS waitresource
INTO #spid_blocked
FROM #sysprocesses
WHERE (blocked > 0
OR (waitresource <> ''
AND waitresource <> '0:0:0'))
IF @showonlyblocked = 1
AND NOT EXISTS ( SELECT *
FROM #spid_blocked
WHERE blocked > 0 )
RETURN
IF @GetResourceDescription = 1
AND EXISTS ( SELECT *
FROM #spid_blocked
WHERE blocked > 0 )
EXEC dbo.sp_whoio_02
IF OBJECT_ID('tempdb.dbo.#sp_whoio_internal') IS NOT NULL
DROP TABLE #sp_whoio_internal ;
WITH spid_blocked
AS (SELECT sp.SPID,
sp.blocked,
Util.dbo.StringConcat(ISNULL('(' + sp.waitresource + ') ' + rr.ResourceDescription, sp.waitresource), '
') AS waitresource,
Util.dbo.StringConcat(rr.ResourceDescription, ', ') AS ResourceDescription
FROM #spid_blocked sp
LEFT OUTER JOIN #ResourceResult rr ON rr.SPID = sp.SPID
AND rr.waitresource = sp.waitresource
GROUP BY sp.SPID,
sp.blocked),
spid_blocked_list
AS (SELECT SPID,
Util.dbo.StringConcat(CAST(blocked AS VARCHAR), ', ') AS blockedlist,
MIN(CASE WHEN blocked IS NULL THEN 9999
ELSE blocked
END) AS minblocked,
Util.dbo.StringConcat(waitresource, '
') AS waitresource,
Util.dbo.StringConcat(ResourceDescription, ', ') AS ResourceDescription
FROM spid_blocked
GROUP BY SPID),
blocked_list
AS (SELECT blocked,
Util.dbo.StringConcat(CAST(SPID AS VARCHAR), ', ') AS blocking,
Util.dbo.StringConcat(waitresource, '
') AS blockingresource
FROM spid_blocked
WHERE blocked > 0
GROUP BY blocked),
unique_blocked_process_list
AS (SELECT SPID,
blocked,
blocking,
blockingresource,
waitresource,
ROW_NUMBER() OVER (ORDER BY or1, or2) AS RowNumber,
ResourceDescription
FROM (SELECT bl.blocked AS or1,
0 AS or2,
bl.blocked AS SPID,
sl.blockedlist AS blocked,
bl.blocking,
bl.blockingresource,
ISNULL(sl.waitresource, NULL) AS waitresource,
NULL AS ResourceDescription
FROM blocked_list bl
LEFT OUTER JOIN spid_blocked_list sl ON bl.blocked = sl.SPID
UNION ALL
SELECT minblocked AS or1,
SPID AS or2,
SPID,
blockedlist AS blocked,
NULL AS blocking,
NULL AS blockingresource,
waitresource,
ResourceDescription
FROM spid_blocked_list l
WHERE NOT EXISTS ( SELECT *
FROM blocked_list d
WHERE d.blocked = l.SPID )) kk),
MasterProcess
AS (SELECT *
FROM #sysprocesses ap
WHERE EXISTS ( SELECT *
FROM unique_blocked_process_list bl
WHERE bl.SPID = ap.SPID )
OR ((@hostname IS NULL
OR hostname LIKE @hostname)
AND (@loginame IS NULL
OR loginame LIKE @loginame)
AND (@ntuser IS NULL
OR ntuser LIKE @ntuser)
AND (@program_name IS NULL
OR program_name LIKE @program_name)
AND (@database IS NULL
OR dbname LIKE @database))),
spstatus
AS (SELECT SPID,
Util.dbo.StringConcat(status + CASE WHEN Counter > 1 THEN '(' + CAST(Counter AS VARCHAR) + ')'
ELSE ''
END, ', ') AS status
FROM (SELECT SPID,
RTRIM(CAST(status AS VARCHAR(30))) AS status,
COUNT(*) AS Counter
FROM MasterProcess
GROUP BY SPID,
status) kk
GROUP BY SPID),
splastwaittype
AS (SELECT SPID,
Util.dbo.StringConcat(lastwaittype + CASE WHEN Counter > 1 THEN '(' + CAST(Counter AS VARCHAR) + ')'
ELSE ''
END, ', ') AS lastwaittype
FROM (SELECT SPID,
RTRIM(CAST(lastwaittype AS VARCHAR(32))) AS lastwaittype,
COUNT(*) AS Counter
FROM MasterProcess
GROUP BY SPID,
lastwaittype) kk
GROUP BY SPID),
spcommand
AS (SELECT SPID,
Util.dbo.StringConcat(cmd + CASE WHEN Counter > 1 THEN '(' + CAST(Counter AS VARCHAR) + ')'
ELSE ''
END, ', ') AS cmd
FROM (SELECT SPID,
RTRIM(CAST(cmd AS VARCHAR(16))) AS cmd,
COUNT(*) AS Counter
FROM MasterProcess
GROUP BY SPID,
cmd) kk
GROUP BY SPID),
processinfo
AS (SELECT SPID,
SUM([waittime]) AS [waittime],
COUNT(*) AS Threads,
MAX(hostname) AS hostname,
MAX(loginame) AS loginame,
MAX(ntuser) AS ntuser,
MAX(program_name) AS program_name,
SUM(CAST(cpu AS BIGINT)) AS cpu,
SUM(CAST(physical_io AS BIGINT)) AS physical_io,
SUM(CAST(memusage AS BIGINT)) AS memusage,
MAX(dbid) AS dbid,
MAX(last_batch) AS last_batch,
MAX(open_tran) AS open_tran,
MAX(DBName) AS DBName,
MAX(stmt_start) AS stmt_start,
MAX(stmt_end) AS stmt_end,
MAX(CONTEXT_INFO) AS CONTEXT_INFO
FROM MasterProcess
GROUP BY SPID)
SELECT PI.SPID,
PI.Threads,
PI.[waittime],
PI.hostname,
PI.loginame,
PI.ntuser,
PI.program_name,
PI.cpu,
PI.physical_io,
PI.memusage,
PI.dbid,
PI.last_batch,
PI.open_tran,
lw.lastwaittype,
ss.status,
sc.cmd,
PI.DBName,
PI.[context_info],
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,
st.SQLBatch,
CASE WHEN PI.stmt_end IN (0, -1)
AND PI.stmt_start = 0 THEN NULL
WHEN (PI.stmt_end > PI.stmt_start
OR PI.stmt_end = -1)
AND st.SQLBatch <> '' THEN LTRIM(RTRIM(SUBSTRING(SQLBatch, PI.stmt_start / 2, (CASE WHEN PI.stmt_end = -1 THEN 9999999
ELSE PI.stmt_end
END - PI.stmt_start) / 2 + 1)))
ELSE NULL
END AS SQLStmt,
bl.blocked AS blocked,
bl.waitresource AS waitresource,
bl.blocking AS blocking,
bl.blockingresource AS blockingresource,
CASE WHEN PI.stmt_end IN (0, -1)
AND PI.stmt_start = 0 THEN NULL
ELSE PI.stmt_start
END AS stmt_start,
CASE WHEN PI.stmt_end IN (0, -1)
AND PI.stmt_start = 0 THEN NULL
ELSE PI.stmt_end
END AS stmt_end,
ISNULL(bl.RowNumber, 999999) AS RowNumber,
bl.ResourceDescription
INTO #sp_whoio_internal
FROM processinfo PI
INNER JOIN spstatus ss ON ss.SPID = PI.SPID
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg (NOLOCK) ON mg.session_id = PI.SPID
LEFT OUTER JOIN unique_blocked_process_list bl ON bl.SPID = PI.SPID
INNER JOIN splastwaittype lw ON lw.SPID = PI.SPID
INNER JOIN spcommand sc ON sc.SPID = PI.SPID
OUTER APPLY (SELECT TOP 1 SQLBatch FROM #sysprocesses s WHERE SQLBatch <> '' AND s.[spid] = PI.[spid]) st
--LEFT OUTER JOIN #ServiceBrokerSessions sb (NOLOCK) ON sb.spid = PI.spid
WHERE (@cmd IS NULL
OR sc.cmd LIKE @cmd)
AND ((@ignoresleeping = 1
AND (bl.blocked IS NOT NULL
OR bl.blocking IS NOT NULL
OR ss.status NOT IN ('sleeping')))
OR ISNULL(@ignoresleeping, 0) = 0)
IF OBJECT_ID('TEMPDB..#InputBuffer') IS NOT NULL
TRUNCATE TABLE #InputBuffer
ELSE
CREATE TABLE #InputBuffer (SPID SMALLINT PRIMARY KEY CLUSTERED,
EventInfo VARCHAR(4000))
IF @DbccInputbuffer = 1
BEGIN
SELECT @SQL = 'IF OBJECT_ID(''TEMPDB..#temp_buffer'') IS NULL
CREATE TABLE #temp_buffer (EventType VARCHAR(30), Parameters INT, EventInfo VARCHAR(4000))
' + Util.dbo.StringConcat('TRUNCATE TABLE #temp_buffer
BEGIN TRY
INSERT #temp_buffer(EventType, Parameters, EventInfo)
EXEC(''DBCC INPUTBUFFER(' + CAST(SPID AS VARCHAR) + ') WITH NO_INFOMSGS'')
INSERT #InputBuffer(spid, EventInfo)
SELECT TOP 1 ' + CAST(SPID AS VARCHAR) + ' AS spid,
LTRIM(REPLACE(EventInfo, char(0), ''
'')) AS EventInfo
FROM #temp_buffer
END TRY
BEGIN CATCH
END CATCH', '
')
FROM #sp_whoio_internal
EXEC(@SQL)
END
IF @delay > 0
BEGIN
WAITFOR DELAY @waitfordelay
INSERT @io2
(SPID,
physical_io,
cpu,
[waittime])
SELECT SPID,
SUM(CAST(physical_io AS BIGINT)) AS physical_io,
SUM(CAST(cpu AS BIGINT)) AS cpu,
SUM(CAST([waittime] AS BIGINT)) AS [waittime]
FROM master.dbo.sysprocesses WITH (NOLOCK)
GROUP BY SPID
IF @Get_os_wait_stats = 1
BEGIN
INSERT @wait2
([wait_type],
[waiting_tasks_count],
[wait_time_ms])
SELECT wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats (NOLOCK)
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN')
END
IF EXISTS ( SELECT *
FROM #sp_whoio_internal
WHERE waitresource IS NOT NULL
AND ResourceDescription IS NULL )
BEGIN
TRUNCATE TABLE #ResourceResult
TRUNCATE TABLE #spid_blocked
INSERT #spid_blocked
(SPID,
blocked,
waitresource)
SELECT SPID,
blocked,
waitresource
FROM #sp_whoio_internal
WHERE waitresource IS NULL
AND ResourceDescription IS NOT NULL
EXEC dbo.sp_whoio_02
UPDATE s
SET waitresource = '(' + s.waitresource + ') ' + r.ResourceDescription
FROM #sp_whoio_internal s (NOLOCK)
INNER JOIN #ResourceResult r (NOLOCK) ON s.SPID = r.SPID
AND s.waitresource COLLATE SQL_Latin1_General_CP1_CI_AS = r.waitresource COLLATE SQL_Latin1_General_CP1_CI_AS
END
END
--#region CREATE TABLE #sp_whoio
IF OBJECT_ID('tempdb..#sp_whoio') IS NOT NULL
BEGIN
SET @TempTableExists = 1
TRUNCATE TABLE #sp_whoio
END
ELSE
BEGIN
IF @PrintInfo = 1
PRINT 'GO
IF OBJECT_ID(''tempdb..#sp_whoio'') IS NOT NULL DROP TABLE #sp_whoio
GO
CREATE TABLE #sp_whoio (
[spid] smallint NOT NULL,
[blocking] xml NULL,
[blocked] xml NULL,
[Threads] varchar(30) NULL,
[status] varchar(MAX) NULL,
[lastwaittype] varchar(MAX) NULL,
[database] sysname NULL,
[hostname] varchar(128) NULL,
[loginame] varchar(128) NULL,
[ntuser] varchar(128) NULL,
[program_name] varchar(128) NULL,
[cmd] varchar(MAX) NULL,
[cpu] bigint NULL,
[physical_io] bigint NULL,
[io_delta] bigint NULL,
[cpu_delta] bigint NULL,
[memusage] varchar(30) NULL,
[waittime] bigint NULL,
[wait_delta] bigint NULL,
[grant_mem_mb] numeric(12, 2) NULL,
[req_mem_mb] numeric(12, 2) NULL,
[used_mem_mb] numeric(12, 2) NULL,
[open_tran] varchar(30) NULL,
[dbid] smallint NULL,
[waitresource] xml NULL,
[blockingresource] xml NULL,
[last_batch] datetime NULL,
[last_batch_sec] int NULL,
[context_info] varbinary(128) NULL,
[Inputbuffer] xml NULL,
[SQLBatch] xml NULL,
[SQLStmt] xml NULL)
GO
EXEC dbo.sp_whoio
@delay = 1,
@ignoresleeping = 0,
@showonlyblocked = 0,
@DbccInputbuffer = 1,
@IgnoreSystem = 0,
@GetResourceDescription = 1,
@Get_os_wait_stats = 0
GO
SELECT [spid], [blocking], [blocked], [Threads], [status], [lastwaittype], [database], [hostname], [loginame], [ntuser], [program_name], [cmd], [cpu], [physical_io], [io_delta], [cpu_delta], [memusage], [waittime], [wait_delta], [grant_mem_mb], [req_mem_mb], [used_mem_mb], [open_tran], [dbid], [waitresource], [blockingresource], [last_batch], [last_batch_sec], [Inputbuffer], [SQLBatch], [SQLStmt]
FROM #sp_whoio
GO
'
CREATE TABLE #sp_whoio ([spid] SMALLINT NOT NULL,
[blocking] XML NULL,
[blocked] XML NULL,
[Threads] VARCHAR(30) NULL,
[status] VARCHAR(MAX) NULL,
[lastwaittype] VARCHAR(MAX) NULL,
[database] SYSNAME NULL,
[hostname] VARCHAR(128) NULL,
[loginame] VARCHAR(128) NULL,
[ntuser] VARCHAR(128) NULL,
[program_name] VARCHAR(128) NULL,
[cmd] VARCHAR(MAX) NULL,
[cpu] BIGINT NULL,
[physical_io] BIGINT NULL,
[io_delta] BIGINT NULL,
[cpu_delta] BIGINT NULL,
[memusage] VARCHAR(30) NULL,
[waittime] BIGINT NULL,
[wait_delta] BIGINT NULL,
[grant_mem_mb] NUMERIC(12, 2) NULL,
[req_mem_mb] NUMERIC(12, 2) NULL,
[used_mem_mb] NUMERIC(12, 2) NULL,
[open_tran] VARCHAR(30) NULL,
[dbid] SMALLINT NULL,
[waitresource] XML NULL,
[blockingresource] XML NULL,
[last_batch] DATETIME NULL,
[context_info] VARBINARY(128) NULL,
[last_batch_sec] INT NULL,
[Inputbuffer] XML NULL,
[SQLBatch] XML NULL,
[SQLStmt] XML NULL)
END
--#endregion
INSERT #sp_whoio WITH (TABLOCK)
([spid],
[blocking],
[blocked],
[Threads],
[status],
[lastwaittype],
[database],
[hostname],
[loginame],
[ntuser],
[program_name],
[cmd],
[cpu],
[physical_io],
[io_delta],
[cpu_delta],
[memusage],
[waittime],
[wait_delta],
[grant_mem_mb],
[req_mem_mb],
[used_mem_mb],
[open_tran],
[dbid],
[waitresource],
[blockingresource],
[last_batch],
[last_batch_sec],
[context_info],
[Inputbuffer],
[SQLBatch],
[SQLStmt])
SELECT pr.SPID,
CAST(pr.blocking AS XML) AS blocking,
CAST(pr.blocked AS XML) AS blocked,
CASE WHEN pr.Threads = 1 THEN NULL
ELSE CAST(pr.Threads AS VARCHAR)
END AS Threads,
pr.status,
pr.lastwaittype,
pr.DBName AS [database],
pr.hostname,
pr.loginame,
pr.ntuser,
pr.program_name,
pr.cmd,
pr.cpu,
pr.physical_io,
CASE WHEN ISNULL(i2.physical_io, 0) - ISNULL(i1.physical_io, 0) > 0 THEN ISNULL(i2.physical_io, 0) - ISNULL(i1.physical_io, 0)
ELSE NULL
END AS 'io_delta',
CASE WHEN ISNULL(i2.cpu, 0) - ISNULL(i1.cpu, 0) > 0 THEN ISNULL(i2.cpu, 0) - ISNULL(i1.cpu, 0)
ELSE NULL
END AS 'cpu_delta',
CASE WHEN pr.memusage = 0 THEN NULL
ELSE CAST(pr.memusage AS VARCHAR)
END AS memusage,
pr.[waittime],
CASE WHEN ISNULL(i2.[waittime], 0) - ISNULL(i1.[waittime], 0) > 0 THEN ISNULL(i2.[waittime], 0) - ISNULL(i1.[waittime], 0)
ELSE NULL
END AS 'wait_delta',
grant_mem_mb,
req_mem_mb,
used_mem_mb,
CASE WHEN pr.open_tran = 0 THEN NULL
ELSE CAST(pr.open_tran AS VARCHAR)
END AS open_tran,
pr.dbid,
CAST(pr.waitresource AS XML) AS waitresource,
CAST(pr.blockingresource AS XML) AS blockingresource,
pr.last_batch,
CASE WHEN pr.last_batch > DATEADD(DAY, -7, GETDATE()) THEN DATEDIFF(SECOND, pr.last_batch, GETDATE())
ELSE NULL
END AS last_batch_sec,
pr.[context_info],
CASE WHEN ib.EventInfo <> ''
THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + Util.dbo.TrimBothEnds (ib.EventInfo) + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE)
END AS Inputbuffer,
CASE WHEN pr.SQLBatch <> '' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + Util.dbo.TrimBothEnds (pr.SQLBatch) + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS SQLBatch,
CASE WHEN pr.SQLStmt <> '' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + Util.dbo.TrimBothEnds (pr.SQLStmt) + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS SQLStmt
FROM #sp_whoio_internal pr
LEFT OUTER JOIN @io2 i2 ON pr.SPID = i2.SPID
LEFT OUTER JOIN @io1 i1 ON pr.SPID = i1.SPID
LEFT OUTER JOIN #InputBuffer ib ON ib.SPID = pr.SPID
ORDER BY RowNumber,
CASE WHEN ISNULL(i2.physical_io, 0) - ISNULL(i1.physical_io, 0) > 0 THEN ISNULL(i2.physical_io, 0) - ISNULL(i1.physical_io, 0)
ELSE 0
END DESC,
CASE WHEN ISNULL(i2.cpu, 0) - ISNULL(i1.cpu, 0) > 0 THEN ISNULL(i2.cpu, 0) - ISNULL(i1.cpu, 0)
ELSE 0
END DESC,
pr.SPID ASC
IF @TempTableExists = 0
SELECT [spid],
[blocking],
[blocked],
[Threads],
[status],
[lastwaittype],
[database],
[hostname],
[loginame],
[ntuser],
[program_name],
[cmd],
[cpu],
[physical_io],
[io_delta],
[cpu_delta],
[memusage],
[waittime],
[wait_delta],
[grant_mem_mb],
[req_mem_mb],
[used_mem_mb],
[open_tran],
[dbid],
[waitresource],
[blockingresource],
[last_batch],
[last_batch_sec],
[context_info],
[Inputbuffer],
[SQLBatch],
[SQLStmt]
FROM #sp_whoio
IF @Get_os_wait_stats = 1
AND EXISTS ( SELECT *
FROM @wait1 t1
INNER JOIN @wait2 t2 ON t1.wait_type = t2.wait_type
WHERE CAST((t2.wait_time_ms - t1.wait_time_ms) * .001 AS INT) > 0 )
BEGIN
DECLARE @Print VARCHAR(MAX) = '------------------------------ WAIT TYPES ------------------------------
wait_type waiting_tasks_count wait_time_s
'
SELECT @Print = @Print + Util.dbo.StringConcat(List, '')
FROM (SELECT TOP 999999
CAST(t1.wait_type AS CHAR(30)) + CAST((t2.waiting_tasks_count - t1.waiting_tasks_count) AS CHAR(30))
+ CAST(CAST((t2.wait_time_ms - t1.wait_time_ms) * .001 AS INT) AS CHAR(30)) + '
' AS List
FROM @wait1 t1
INNER JOIN @wait2 t2 ON t1.wait_type = t2.wait_type
WHERE CAST((t2.wait_time_ms - t1.wait_time_ms) * .001 AS INT) > 0
ORDER BY (t2.wait_time_ms - t1.wait_time_ms) DESC) kk
PRINT @Print
END
GO
GRANT EXEC ON dbo.sp_whoio TO PUBLIC
GO
EXEC sys.sp_ms_marksystemobject
sp_whoio
GO