Adventures in @BlobEater, sp_WhoIsActive and DBCC CHECKDB

The latest version of sp_WhoIsActive (and apparently last for SQL Server 2005/2008, according to Adam Machanic), was released a week ago.

Yesterday, out of curiosity piqued by my recent three-week SQLskills training, I decided to run sp_WhoIsActive while doing a DBCC CHECKDB on a moderately large database (mainly because it takes longer, so it’s easier to poll). The version was SQL Server 2008 R2 with Service Pack 1 (10.50.2500).

Referring to my notes of how CHECKDB works, I was excited to find sp_WhoIsActive capturing one of the queries that runs during the fact-checking phase:

DECLARE @BlobEater VARBINARY(8000)

SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0x30FAC98600000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

I thought this was cool. Knowing how CHECKDB checks that the relationships are consistent in theory is one thing; seeing it in practice is something better. I’ve often found myself preferring an example to demonstrate a theory, to improve my understanding of that theory. Hopefully this will be helpful to you as well.

I also love the name “BlobEater”.

EDIT: I also ran across this query during the Update Statistics phase of a maintenance plan:

SELECT StatMan([SC0], [LC0])
FROM (SELECT TOP 100 PERCENT SUBSTRING ([object], 1, 100)++substring([object],
case when datalength([object])<=200 then 101 else datalength([object])-99 end, datalength([object])) AS [SC0], datalength([object]) AS [LC0] FROM [dbo].[activitylog] WITH (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL