{"id":477,"date":"2012-09-06T11:24:00","date_gmt":"2012-09-06T11:24:00","guid":{"rendered":"\/blogs\/paul\/post\/CHECKDB-internals-what-is-the-BlobEater.aspx"},"modified":"2013-01-01T20:51:56","modified_gmt":"2013-01-02T04:51:56","slug":"checkdb-internals-what-is-the-blobeater","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/","title":{"rendered":"CHECKDB internals: what is the BlobEater?"},"content":{"rendered":"

\nSeveral times over the last month, I've been asked about the query that drives DBCC CHECKDB<\/font> and other consistency checking commands, which has a variable called BlobEater<\/font> in it. In this post I'd like to explain what the query is doing. <\/font>\n<\/p>\n

\nIn my previous post in the <\/font>CHECKDB From Every Angle<\/font><\/a> series, <\/font>How does DBCC CHECKDB WITH ESTIMATEONLY work?<\/font><\/a>, I explained how DBCC CHECKDB<\/font> uses 'facts', little bits of information that describe something that DBCC CHECKDB<\/font> 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<\/font> can tell there's a corruption. <\/font>\n<\/p>\n

\nAs DBCC CHECKDB<\/font> 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<\/font> 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<\/font> to figure out whether corruptions are present. <\/font>\n<\/p>\n

\nA picture to show this mechanism looks as follows: <\/font>\n<\/p>\n

\n \"\"<\/font> <\/font><\/font>\n<\/p>\n

\nIf you're doing any tracing or profiling while DBCC CHECKDB<\/font> is running, you'll see the query below:<\/font>\n<\/p>\n

\n

\n\tDECLARE @BlobEater VARBINARY(8000);
\n\tSELECT @BlobEater = CheckIndex(ROWSET_COLUMN_FACT_BLOB)
\n\tFROM <memory address of fact rowset>
\n\tGROUP BY ROWSET_COLUMN_FACT_KEY
\n\t>> WITH ORDER BY
\n\t     ROWSET_COLUMN_FACT_KEY,
\n\t     ROWSET_COLUMN_SLOT_ID,
\n\t     ROWSET_COLUMN_COMBINED_ID,
\n\t     ROWSET_COLUMN_FACT_BLOB
\n\tOPTION(ORDER GROUP);<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe 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:<\/font>\n<\/p>\n

\n\tThis query brings the query processor and the DBCC CHECKDB<\/em> code together to perform the fact-generation, fact-sorting, fact-storing, and fact-aggregation algorithms. The parts of the query are as follows:<\/font> <\/p>\n

    \n
  • @BlobEater<\/strong> This is a dummy variable with no purpose other than to consume any output from the CheckIndex<\/em> function (there should never be any, but the syntax requires it).<\/font><\/font> <\/li>\n
  • CheckIndex (ROWSET_COLUMN_FACT_BLOB)<\/strong> This is the custom aggregation function inside DBCC CHECKDB<\/em> that the query processor calls with sorted and grouped facts as part of the overall fact aggregation algorithm.<\/font><\/font> <\/li>\n
  • <memory address of fact rowset><\/strong> This is the memory address of the OLEDB rowset that DBCC CHECKDB<\/em> 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.<\/font> <\/li>\n
  • GROUP BY ROWSET_COLUMN_FACT_KEY<\/strong>This triggers the aggregation in the query processor.<\/font><\/font> <\/li>\n
  • >> WITH ORDER BY <column list><\/strong> This is internal-only syntax that provides ordered aggregation to the aggregation step. As I explained earlier, the DBCC CHECKDB<\/em> 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).<\/font> <\/li>\n
  • OPTION(ORDER GROUP)<\/strong> 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.<\/font><\/font> <\/li>\n<\/ul>\n<\/blockquote>\n

    \nAnd 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<\/font>, for instance to force an index rebuild to read the heap\/clustered index rather than reading from the existing index.<\/font>\n<\/p>\n

    \nHope this is interesting!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[],"class_list":["post-477","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle"],"yoast_head":"\nCHECKDB internals: what is the BlobEater? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CHECKDB internals: what is the BlobEater? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-06T11:24:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T04:51:56+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/\",\"name\":\"CHECKDB internals: what is the BlobEater? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-09-06T11:24:00+00:00\",\"dateModified\":\"2013-01-02T04:51:56+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CHECKDB internals: what is the BlobEater?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CHECKDB internals: what is the BlobEater? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/","og_locale":"en_US","og_type":"article","og_title":"CHECKDB internals: what is the BlobEater? - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/","og_site_name":"Paul S. Randal","article_published_time":"2012-09-06T11:24:00+00:00","article_modified_time":"2013-01-02T04:51:56+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/","name":"CHECKDB internals: what is the BlobEater? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-09-06T11:24:00+00:00","dateModified":"2013-01-02T04:51:56+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-internals-what-is-the-blobeater\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CHECKDB internals: what is the BlobEater?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=477"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/477\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}