USE master
GO
IF OBJECT_ID('dbo.sp_whoio_02') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_whoio_02 AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_whoio_02
@PrintSQL BIT = 0,
@ExecSQL BIT = 1,
@SQL VARCHAR(MAX) = '' OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/* EXPECTS TABLES #spid_blocked, #ResourceResult
-- HOW TO TEST
IF OBJECT_ID('tempdb..#spid_blocked') IS NOT NULL DROP TABLE #spid_blocked
IF OBJECT_ID('tempdb..#ResourceResult') IS NOT NULL DROP TABLE #ResourceResult
CREATE TABLE #spid_blocked (SPID SMALLINT, blocked SMALLINT, waitresource VARCHAR(1000))
CREATE TABLE #ResourceResult ( SPID SMALLINT, waitresource VARCHAR(1000), ResourceDescription VARCHAR(8000))
INSERT #spid_blocked VALUES(60, 70, 'TAB: 17:234496660:3'), (60, 70, 'TAB: 17:58496033:1')
EXEC [dbo].[sp_whoio_02]
SELECT * FROM #ResourceResult
*/
SET NOCOUNT ON
IF 1 = 2
BEGIN
CREATE TABLE #spid_blocked (SPID SMALLINT, blocked SMALLINT, waitresource VARCHAR(1000))
CREATE TABLE #ResourceResult ( SPID SMALLINT, waitresource VARCHAR(1000), ResourceDescription VARCHAR(8000))
END
IF OBJECT_ID('TEMPDB..#pageinfo') IS NULL
BEGIN
CREATE TABLE #pageinfo (
ParentObject VARCHAR(128),
OBJECT VARCHAR(128),
Field VARCHAR(128),
VALUE VARCHAR(128))
END
/*;WITH waitresourcelist
AS
(SELECT 55 AS spid, 'TAB: 17:215020593:21' AS VALUE
UNION ALL
SELECT 56 AS spid, 'TAB: 17:215020593:1' AS VALUE
UNION ALL
SELECT 65 AS spid, 'PAGE: 17:7:600000' AS VALUE
UNION ALL
SELECT 75 AS spid, 'KEY: 17:72058151564345344 (960073f684f0)' AS VALUE
UNION ALL
SELECT 95 AS spid, 'KEY: 17:72058116196990976 (960073f684f0)' AS VALUE
UNION ALL
SELECT 97 AS spid, 'KEY: 5:72058151564345346 (960073f684f0)' AS VALUE
UNION ALL
SELECT 85 AS spid, 'RID: 17:10:700000:3' AS VALUE
UNION ALL
SELECT 95 AS spid, 'TAB: 17:1922027270 [[compile]]' AS VALUE)
*/
;
WITH res01
AS (SELECT v.SPID,
v.waitresource,
p.fieldnum AS col,
p2.fieldnum AS ROW,
p2.field AS VALUE
FROM #spid_blocked v
CROSS APPLY Util.dbo.ParseDelimited(CASE WHEN ISNUMERIC(SUBSTRING(v.waitresource, 1, 1)) = 1 THEN 'PAGE: ' + v.waitresource
ELSE v.waitresource
END, ' ') p
CROSS APPLY Util.dbo.ParseDelimited(p.field, ':') p2
WHERE p2.field <> ''),
res02
AS (SELECT SPID,
waitresource,
[1] AS databaseid,
CAST([2] AS BIGINT) AS C1,
CAST([3] AS BIGINT) AS C2,
CAST([4] AS BIGINT) AS C3
FROM (SELECT SPID, waitresource, ROW, VALUE FROM res01 WHERE col = 2
AND ISNUMERIC (VALUE) = 1) p PIVOT ( MAX(VALUE) FOR ROW IN ([1], [2], [3], [4]) ) AS pvt),
res03
AS (SELECT r1.SPID,
r1.waitresource,
CASE WHEN r1.value = 'KEY' THEN 'Key'
WHEN r1.value = 'TAB'
AND r3.value IS NOT NULL THEN 'Compile'
WHEN r1.value = 'TAB'
AND r3.value IS NULL THEN 'Table'
WHEN r1.value = 'PAGE' THEN 'Page'
WHEN r1.value = 'RID' THEN 'Row'
END AS resource,
r1.value AS C1,
r2.databaseid,
r2.C1 AS val1,
r2.C2 AS val2,
r2.C3 AS val3,
r3.value AS C3
FROM res01 r1
INNER JOIN res02 r2 ON r1.SPID = r2.SPID
AND r1.waitresource = r2.waitresource
LEFT OUTER JOIN res01 r3 ON r1.waitresource = r3.waitresource
AND r1.SPID = r3.SPID
AND r3.col = 3
AND r3.ROW = 1
WHERE r1.col = 1
AND r1.ROW = 1),
res04
AS (SELECT d.name,
'DECLARE @DBTABLE_' + d.name + ' TABLE(spid smallint, waitresource varchar(1000), resource varchar(20), Row BIGINT, PartitionId BIGINT)
' + Util.dbo.StringConcat('
TRUNCATE TABLE #pageinfo
INSERT #pageinfo
EXEC(''DBCC PAGE (' + CAST(r.databaseid AS VARCHAR) + ',' + CAST(r.val1 AS VARCHAR) + ', ' + CAST(r.val2 AS VARCHAR) + ') WITH NO_INFOMSGS, TABLERESULTS'')
INSERT @DBTABLE_' + d.name + ' (spid, waitresource, resource, Row, PartitionId)
SELECT ' + CAST(r.SPID AS VARCHAR) + ' as spid, ''' + r.waitresource + ''' as waitresource, ''' + r.resource + ''' as resource, '
+ ISNULL(CAST(r.val3 AS VARCHAR), 'NULL') + ' AS Row, CAST(pi.Value as BIGINT) as PartitionId
FROM #pageinfo pi
WHERE pi.Field = ''Metadata: PartitionId''
', '') + '
INSERT #ResourceResult (spid, waitresource, ResourceDescription)
SELECT
d.spid,
d.waitresource,
d.resource + '' [' + d.name
+ '].['' + s.name + ''].['' + o.name + ''].['' + i.name + ''], indid:'' + CAST(i.index_id AS VARCHAR) + '', part#:'' + CAST(p.partition_number AS VARCHAR) + ISNULL('', row:'' + CAST(row AS VARCHAR), '''') AS ResourceDescription
FROM @DBTABLE_' + d.name + ' d
INNER JOIN [' + d.name + '].sys.schemas s (NOLOCK) ON 1 = 1
INNER JOIN [' + d.name + '].sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN [' + d.name + '].sys.partitions p (NOLOCK) ON p.object_id = o.object_id
INNER JOIN [' + d.name + '].sys.indexes i (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.hobt_id = d.PartitionId
' AS SQL
FROM res03 r
INNER JOIN sys.databases d (NOLOCK) ON d.database_id = r.databaseid
WHERE r.resource IN ('Page', 'Row')
GROUP BY d.name
UNION ALL
SELECT d.name,
'
INSERT #ResourceResult (spid, waitresource, ResourceDescription)
SELECT
rs.spid,
rs.waitresource,
''Key'' + '' [' + d.name
+ '].['' + s.name + ''].['' + o.name + ''].['' + i.name + ''], indid:'' + CAST(i.index_id AS VARCHAR) + '', part#:'' + CAST(p.partition_number AS VARCHAR) AS ResourceDescription
FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(r.SPID AS VARCHAR) + ', ''' + r.waitresource + ''', CAST(' + CAST(r.VAL1 AS VARCHAR) + ' AS BIGINT))',
',') + ') AS rs (spid, waitresource , hobtid)
INNER JOIN [' + d.name + '].sys.schemas s (NOLOCK) ON 1 = 1
INNER JOIN [' + d.name + '].sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN [' + d.name + '].sys.partitions p (NOLOCK) ON p.object_id = o.object_id
INNER JOIN [' + d.name + '].sys.indexes i (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.hobt_id = rs.hobtid
' AS SQL
FROM res03 r
INNER JOIN sys.databases d (NOLOCK) ON d.database_id = r.databaseid
WHERE r.resource = 'Key'
GROUP BY d.name
UNION ALL
SELECT d.name,
'
INSERT #ResourceResult (spid, waitresource, ResourceDescription)
SELECT
rs.spid,
rs.waitresource,
''Table'' + '' [' + d.name
+ '].['' + s.name + ''].['' + o.name + '']'' + ISNULL(''.['' + i.name + '']'', '''') + '', type:'' + o.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + '', indid:'' + CAST(rs.index_id AS VARCHAR) AS ResourceDescription
FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(r.SPID AS VARCHAR) + ', ''' + r.waitresource + ''', CAST(' + CAST(r.VAL1 AS VARCHAR)
+ ' AS BIGINT), CAST(' + CAST(r.VAL2 AS VARCHAR) + ' AS BIGINT))', ',')
+ ') AS rs (spid, waitresource, object_id, index_id)
INNER JOIN [' + d.name + '].sys.schemas s (NOLOCK) ON 1 = 1
INNER JOIN [' + d.name + '].sys.objects o (NOLOCK) ON s.schema_id = o.schema_id AND o.object_id = rs.object_id
LEFT OUTER JOIN [' + d.name + '].sys.indexes i (NOLOCK) ON rs.object_id = i.object_id AND rs.index_id = i.index_id
' AS SQL
FROM res03 r
INNER JOIN sys.databases d (NOLOCK) ON d.database_id = r.databaseid
WHERE r.resource = 'Table'
GROUP BY d.name
UNION ALL
SELECT d.name,
'
INSERT #ResourceResult (spid, waitresource, ResourceDescription)
SELECT
rs.spid,
rs.waitresource,
''Compile'' + '' [' + d.name + '].['' + s.name + ''].['' + o.name + '']'' AS ResourceDescription
FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(r.SPID AS VARCHAR) + ', ''' + r.waitresource + ''', CAST(' + CAST(r.VAL1 AS VARCHAR) + ' AS BIGINT))',
',') + ') AS rs (spid, waitresource, object_id)
INNER JOIN [' + d.name + '].sys.schemas s (NOLOCK) ON 1 = 1
INNER JOIN [' + d.name + '].sys.objects o (NOLOCK) ON s.schema_id = o.schema_id AND o.object_id = rs.object_id
' AS SQL
FROM res03 r
INNER JOIN sys.databases d (NOLOCK) ON d.database_id = r.databaseid
WHERE r.resource = 'Compile'
GROUP BY d.name)
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM res04
IF @PrintSQL = 1
PRINT @SQL
IF @ExecSQL = 1
EXEC(@SQL)
GO
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID('dbo.sp_whoio_02')
AND sysstat & 0xf = 4 )
GRANT EXEC ON dbo.sp_whoio_02 TO PUBLIC
GO
EXEC sys.sp_ms_marksystemobject
sp_whoio_02
GO