{"id":1732,"date":"2013-03-25T20:55:12","date_gmt":"2013-03-26T00:55:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1732"},"modified":"2017-04-13T12:55:36","modified_gmt":"2017-04-13T16:55:36","slug":"dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/","title":{"rendered":"DBCC CHECKDB Execution Memory Grants &#8211; Not Quite What You Expect"},"content":{"rendered":"<p>I was recently working on a client system that has problems running DBCC CHECKDB normally due to the concurrent volume of data changes occurring against their 2TB OLTP database.\u00a0 The challenge with running DBCC CHECKDB on this system is that the database snapshot (which DBCC CHECKDB creates) quickly exceeds the sparse file limits in Windows and their <a href=\"https:\/\/support.microsoft.com\/kb\/2002606\" target=\"_blank\">check fails with error 665<\/a>.<\/p>\n<p>To work around this I wrote a custom procedure for them that <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-consistency-checking-options-for-a-vldb\/\" target=\"_blank\">breaks down the DBCC CHECKDB checks<\/a> into individual checks and spreads the process over a period of roughly two weeks, within a 2-3 hour window each morning when the system has the least amount of other activity.<\/p>\n<p>While I was working on changes to this custom script I noticed that DBCC CHECKTABLE was taking a ~96GB execution memory grant during its execution.\u00a0 The server is a Dell R720 with 2 x Intel E5-2690 processors (2.9GHz 8 cores and HT is enabled so 16 logical cores per socket) and 512GB of RAM.\u00a0 I mentioned this to Paul and it became something that we wanted to investigate further.<\/p>\n<p>We have a similar R720 with a multiple Fusion-IO PCI-X SSDs installed in it as a test server, but it only has 64GB RAM.\u00a0 I had already setup a 500GB test database for <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-performance-and-computed-column-indexes\/\" target=\"_blank\">Paul&#8217;s DBCC CHECKDB performance tests<\/a>, using AdventureWorks and <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/enlarging-the-adventureworks-sample-databases\/\" target=\"_blank\">my scripts to enlarge it<\/a> by adding new objects and creating a number of enlarged versions of the SalesOrder* tables in SQL Server 2012 SP1 with CU3, so when Paul finished his tests, I decided to take a deeper look at how DBCC CHECKDB uses execution memory in SQL Server.<\/p>\n<p>The SQL instance was configured with <em>\u2018max server memory\u2019<\/em> at 54000 which leaves roughly 4GB of available memory on the server at all times.\u00a0 I then wrote a test harness to perform DBCC CHECKDB with <em>\u2018max degree of parallelism\u2019<\/em> set a 32, 16, 8, and 4 that logged the start and end time of each test, the tempdb usage for each test, and the deltas for wait, latch, and spinlock stats for the each test.\u00a0 Under the default configuration, DBCC CHECKDB acquired a 10GB memory grant from the instance.\u00a0 I ran the test harness through four iterations of tests and had the following averages:<br \/>\n<figure id=\"attachment_1737\" aria-describedby=\"caption-attachment-1737\" style=\"width: 542px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png\"><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png\" alt=\"Default Configuration Results\" width=\"542\" height=\"225\" class=\"size-full wp-image-1737\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png 542w, https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results-300x124.png 300w\" sizes=\"(max-width: 542px) 100vw, 542px\" \/><\/a><figcaption id=\"caption-attachment-1737\" class=\"wp-caption-text\">Default Configuration Results<\/figcaption><\/figure><\/p>\n<p>I then configured Resource Governor on the instance and created a Resource Pool that had MAX_MEMORY set at 10% and a Workload Group in the pool with REQUEST_MAX_MEMORY_GRANT_PERCENT set at 25% which yields a ~1GB maximum execution grant size for sessions assigned to the group.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE RESOURCE POOL &#x5B;Maint]\r\nWITH (min_cpu_percent=0,\r\n      max_cpu_percent=100,\r\n      min_memory_percent=0,\r\n      max_memory_percent=10,\r\n      cap_cpu_percent=100,\r\n      AFFINITY SCHEDULER = AUTO);\r\nGO\r\nCREATE WORKLOAD GROUP &#x5B;wg_Maint]\r\nWITH (group_max_requests=0,\r\n      importance=Medium,\r\n      request_max_cpu_time_sec=0,\r\n      request_max_memory_grant_percent=25,\r\n      request_memory_grant_timeout_sec=0,\r\n      max_dop=0)\r\nUSING &#x5B;Maint];\r\nGO\r\n<\/pre>\n<p>Next I created a dbcc_user login on the server, specifically for performing DBCC CHECKDB, added it to the sysadmin server role, and created a classifier function to place connections from this login into the wg_Maint group for testing the effects of reducing the memory grant for DBCC CHECKDB.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\nCREATE LOGIN &#x5B;dbcc_user] WITH PASSWORD=N'R3@lly$tr0ngP@$$w0rd!', DEFAULT_DATABASE=&#x5B;master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;\r\nGO\r\nALTER SERVER ROLE &#x5B;sysadmin] ADD MEMBER &#x5B;dbcc_user];\r\nGO\r\n\r\n--- Create the classifier function\r\nCREATE FUNCTION fnRGClassifier()\r\nRETURNS SYSNAME\r\nWITH SCHEMABINDING\r\nAS\r\nBEGIN\r\n     DECLARE @group SYSNAME;\r\n     IF(SUSER_NAME() = 'dbcc_user')\r\n     BEGIN\r\n          SET @group =  N'wg_Maint';\r\n     END\r\n--- Use the default workload group if there is no match on the lookup.\r\n     ELSE\r\n        BEGIN\r\n             SET @group =  N'default';\r\n       END\r\n       RETURN @group;\r\nEND\r\nGO\r\n\r\n--- Reconfigure the Resource Governor to use the new function\r\nALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnRGClassifier);\r\nALTER RESOURCE GOVERNOR RECONFIGURE;\r\nGO\r\n<\/pre>\n<p>Next I changed the test harness to login as the dbcc_user and reran the series of tests an additional four times with the following averages for the results of each test:<br \/>\n<figure id=\"attachment_1735\" aria-describedby=\"caption-attachment-1735\" style=\"width: 541px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/1gb_grant_results.png\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/1gb_grant_results.png\" alt=\"1GB Memory Grant Results\" width=\"541\" height=\"224\" class=\"size-full wp-image-1735\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/1gb_grant_results.png 541w, https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/1gb_grant_results-300x124.png 300w\" sizes=\"(max-width: 541px) 100vw, 541px\" \/><\/a><figcaption id=\"caption-attachment-1735\" class=\"wp-caption-text\">1GB Memory Grant Results<\/figcaption><\/figure><\/p>\n<p>Reviewing the data, you will see that the elapsed time decreased for all of the tests, while the tempdb usage did not increase significantly as a result of running with only 1GB of execution memory granted.\u00a0 This reduction in execution memory allowed the remaining 9GB to be used by the data cache for database pages during the tests, which was confirmed by capturing the Memory Manager and Buffer Manager performance counters for the instance during the testing.<\/p>\n<p>I then decided to see what different memory effects had and ran the tests at 2GB, 520MB and 240MB by changing the Workload Group REQUEST_MAX_MEMORY_GRANT_PERCENT to values of 50%, 13%, and 6% respectively.\u00a0 Using the same series of tests for additional times per configuration.\u00a0 The following averages were observed after all of the tests were completed:<br \/>\n<figure id=\"attachment_1736\" aria-describedby=\"caption-attachment-1736\" style=\"width: 542px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/additional_lower_grant_results.png\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/additional_lower_grant_results.png\" alt=\"Additional Lower Memory Grant Results\" width=\"542\" height=\"722\" class=\"size-full wp-image-1736\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/additional_lower_grant_results.png 542w, https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/additional_lower_grant_results-225x300.png 225w\" sizes=\"(max-width: 542px) 100vw, 542px\" \/><\/a><figcaption id=\"caption-attachment-1736\" class=\"wp-caption-text\">Additional Lower Memory Grant Results<\/figcaption><\/figure><\/p>\n<p>From the results, the only time that tempdb usage increased, was for the 240MB execution grant size and then only for the DOP 32 and DOP 16 tests.\u00a0 The total increase in tempdb usage was roughly 300MB in the worst case, which is considerably smaller than the gains in data cache space savings by reducing the execution memory grant size for DBCC CHECKDB.\u00a0 The fastest average execution time for this specific database was obtained by using 520MB for all of the levels of DOP that were tested.<\/p>\n<p>After completing this testing, Paul and I spent an hour on a call reviewing the spinlock stats, the wait stats, and the latch stats for each of the tests to try to identify why performance improved by reducing the execution memory grant with Resource Governor like this.\u00a0 The only thing that pointed to an explanation for the improvement in performance is the higher amount of memory available for database pages as a result of reducing the execution memory grant.<\/p>\n<p>Additionally, using Extended Events, I attempted to collect the query_post_execution_showplan events for the executions under the default configuration and under the constrained Resource Governor configuration.\u00a0 It only received a few parts of the actual execution plan information, but the plans returned were identical with the exception of the memory grant information for RequestedMemory, which matched the limitations that were in place for the Resource Governor tests.<\/p>\n<p>The most interesting item from the execution plan however, was the value for the desired memory, which was 45,182,976,776 KB, or roughly 43TB.\u00a0 I don&#8217;t have an answer, yet, to why this occurs, but I hope to be able to figure it out at some point. It appears that the costing model for DBCC CHECKDB has limitations that weren&#8217;t apparent years ago when servers didn&#8217;t have the levels of memory available in today&#8217;s servers. Even doing a DBCC CHECKDB against the master database, which is 5MB in size on this instance, requests a ~2GB memory grant for execution, so it really seems to point to an excessive costing algorithm for DBCC CHECKDB inside of SQL Server.<\/p>\n<p>(From Paul: the way that costing is performed for DBCC CHECKDB is based on the expected number of bits of information DBCC CHECKDB will create in the rowset it gives to the query processor to sort, hash, and give back to DBCC again. This cardinality estimation method has been the same since SQL Server 2000.)<\/p>\n<p>In summary, I&#8217;ll be reviewing our clients&#8217; systems and implementing the Resource Governor method I described above on systems where it will make a difference to performance by not taking so much of the buffer pool. I suggest you consider doing this too.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was recently working on a client system that has problems running DBCC CHECKDB normally due to the concurrent volume of data changes occurring against their 2TB OLTP database.\u00a0 The challenge with running DBCC CHECKDB on this system is that the database snapshot (which DBCC CHECKDB creates) quickly exceeds the sparse file limits in Windows [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1732","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias<\/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\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"I was recently working on a client system that has problems running DBCC CHECKDB normally due to the concurrent volume of data changes occurring against their 2TB OLTP database.\u00a0 The challenge with running DBCC CHECKDB on this system is that the database snapshot (which DBCC CHECKDB creates) quickly exceeds the sparse file limits in Windows [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-26T00:55:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"DBCC CHECKDB Execution Memory Grants &#8211; Not Quite What You Expect\",\"datePublished\":\"2013-03-26T00:55:12+00:00\",\"dateModified\":\"2017-04-13T16:55:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/\"},\"wordCount\":1300,\"commentCount\":24,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/03\\\/default_config_results.png\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/\",\"name\":\"DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/03\\\/default_config_results.png\",\"datePublished\":\"2013-03-26T00:55:12+00:00\",\"dateModified\":\"2017-04-13T16:55:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/03\\\/default_config_results.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/03\\\/default_config_results.png\",\"width\":542,\"height\":225,\"caption\":\"Default Configuration Results\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Uncategorized\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/uncategorized\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"DBCC CHECKDB Execution Memory Grants &#8211; Not Quite What You Expect\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?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\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias","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\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/","og_locale":"en_US","og_type":"article","og_title":"DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias","og_description":"I was recently working on a client system that has problems running DBCC CHECKDB normally due to the concurrent volume of data changes occurring against their 2TB OLTP database.\u00a0 The challenge with running DBCC CHECKDB on this system is that the database snapshot (which DBCC CHECKDB creates) quickly exceeds the sparse file limits in Windows [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-03-26T00:55:12+00:00","article_modified_time":"2017-04-13T16:55:36+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"DBCC CHECKDB Execution Memory Grants &#8211; Not Quite What You Expect","datePublished":"2013-03-26T00:55:12+00:00","dateModified":"2017-04-13T16:55:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/"},"wordCount":1300,"commentCount":24,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/","name":"DBCC CHECKDB Execution Memory Grants - Not Quite What You Expect - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png","datePublished":"2013-03-26T00:55:12+00:00","dateModified":"2017-04-13T16:55:36+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/03\/default_config_results.png","width":542,"height":225,"caption":"Default Configuration Results"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Uncategorized","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/uncategorized\/"},{"@type":"ListItem","position":3,"name":"DBCC CHECKDB Execution Memory Grants &#8211; Not Quite What You Expect"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?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\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1732"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1732\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}