Several times over the last month, I've been asked about the query that drives DBCC CHECKDB and other consistency checking commands, which has a variable called BlobEater in it. In this post I'd like to explain what the query is doing.
In my previous post in the CHECKDB From Every Angle series, How does DBCC CHECKDB WITH ESTIMATEONLY work?, I explained how DBCC CHECKDB uses 'facts', little bits of information that describe something that DBCC CHECKDB has noted about a data file page. Once all the pages for an entire table (or set of tables, if batching is enabled – see that same blog post I mentioned above), all the facts are aggregated together and they should all cancel each other out. When there are extra facts (e.g. two pages in an index B-tree point to the same page at a lower level), or missing facts (e.g. a LOB fragment doesn't have any other LOB fragments or data/index record pointing to it), then DBCC CHECKDB can tell there's a corruption.
As DBCC CHECKDB is generating all these factors from essentially random pages in the database (it reads the pages in a table in physical order, not logical order), there has to be some sorting of the facts before aggregation can take place. This is all driven using the query processor. Each thread in DBCC CHECKDB reads pages, generates facts, and gives them to the query processor to sort and aggregate. Once all reading has finished, the facts are then given back to parallel threads inside DBCC CHECKDB to figure out whether corruptions are present.
A picture to show this mechanism looks as follows:
If you're doing any tracing or profiling while DBCC CHECKDB is running, you'll see the query below:
DECLARE @BlobEater VARBINARY(8000);
SELECT @BlobEater = CheckIndex(ROWSET_COLUMN_FACT_BLOB)
FROM <memory address of fact rowset>
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
The explanation of the parts of this query is documented in the Inside SQL Server 2008 and forthcoming Inside SQL Server 2012 books, and I've quoted it below from my DBCC Internals chapter:
This query brings the query processor and the DBCC CHECKDB code together to perform the fact-generation, fact-sorting, fact-storing, and fact-aggregation algorithms. The parts of the query are as follows:
- @BlobEater This is a dummy variable with no purpose other than to consume any output from the CheckIndex function (there should never be any, but the syntax requires it).
- CheckIndex (ROWSET_COLUMN_FACT_BLOB) This is the custom aggregation function inside DBCC CHECKDB that the query processor calls with sorted and grouped facts as part of the overall fact aggregation algorithm.
- <memory address of fact rowset> This is the memory address of the OLEDB rowset that DBCC CHECKDB provides to the query processor. The query processor queries this rowset for rows (containing the generated facts) as part of the overall fact generation algorithm.
- GROUP BY ROWSET_COLUMN_FACT_KEYThis triggers the aggregation in the query processor.
- >> WITH ORDER BY <column list> This is internal-only syntax that provides ordered aggregation to the aggregation step. As I explained earlier, the DBCC CHECKDB aggregation code is based on the assumption that the order of the aggregated stream of facts from the query processor is forced (that is, it requires that the sort order of the keys within each group is the order of the four keys in the query).
- OPTION(ORDER GROUP) This is a Query Optimizer hint that forces stream aggregation. It forces the Query Optimizer to sort on the grouping columns and avoid hash aggregation.
And there you have it. There are quite a few pieces of query processor syntax that are only callable from inside the Engine itself, and several pieces that are only callable from DBCC, for instance to force an index rebuild to read the heap/clustered index rather than reading from the existing index.
Hope this is interesting!