{"id":4092,"date":"2013-04-03T11:34:48","date_gmt":"2013-04-03T18:34:48","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4092"},"modified":"2017-04-13T12:47:38","modified_gmt":"2017-04-13T19:47:38","slug":"dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/","title":{"rendered":"DBCC CHECKDB scalability and performance benchmarking on SSDs"},"content":{"rendered":"<p>Back in February I ran a bunch of performance tests of <em>DBCC CHECKDB<\/em> on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I&#8217;m finally getting around to presenting the results. Make sure to also read the recent\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-performance-and-computed-column-indexes\/\" target=\"_blank\">post<\/a> where I talk about the detrimental effect of computed-column indexes on <em>DBCC CHECKDB<\/em> performance.<\/p>\n<p>[Edit: I&#8217;ve heard anecdotal evidence that reducing DOP for <em>DBCC CHECKDB<\/em> is even more important on 2012 and later versions. YMMV.]<\/p>\n<p>The two variables I altered during my tests were:<\/p>\n<ul>\n<li><span style=\"line-height: 13px;\">DOP &#8211; 32, 16, 8, 4, 2, 1<\/span><\/li>\n<li>Configuration &#8211; full <em>DBCC CHECKDB<\/em>, using <em>WITH PHYSICAL_ONLY<\/em> (PO), using PO and TF2549, using PO and TF2562, using PO and both trace flags<\/li>\n<\/ul>\n<p>Trace flags 2549 and 2562 are described in <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/2634571\/improvements-for-the-dbcc-checkdb-command-may-result-in-faster-performance-when-you-use-the-physical-only-option\">KB 2634571<\/a>, and can be summarized as:<\/p>\n<ul>\n<li><span style=\"line-height: 13px;\">2549: treat each database file as if it&#8217;s on a separate physical drive for the purposes of driving readahead<\/span><\/li>\n<li>2562: perform the entire set of checks in a single &#8216;batch&#8217; instead of multiple batches, and increase the efficient of per-thread calls to get a new list of pages to read<\/li>\n<\/ul>\n<p>These two trace flags really only apply to <em>DBCC CHECKDB<\/em> when the <em>WITH_PHYSICAL_ONLY<\/em> option is used.<\/p>\n<p>My test system is as follows:<\/p>\n<ul>\n<li><span style=\"line-height: 13px;\">DELL R720 with two 8-core E5-2670 CPUs and hyper-threading enabled<\/span><\/li>\n<li>64GB of memory<\/li>\n<li>The test database is AdventureWorks that Jonathan expanded to 500GB for me using his\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/enlarging-the-adventureworks-sample-databases\/\">cool scripts<\/a>. The database is split over 8 data files stored on two 320GB Fusion-io drives, with tempdb and its log placed on two more 320GB Fusion-io drives. I set things up this way to remove I\/O waits from the test.<\/li>\n<li>There was no additional load running concurrently with <em>DBCC CHECKDB<\/em>.<\/li>\n<\/ul>\n<h2>Complete Results<\/h2>\n<p>The complete results are shown below:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone size-full wp-image-4093\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\" alt=\"CHECKDB-DOP1\" width=\"750\" height=\"653\" \/><\/a><\/p>\n<p>There are some very interesting results from this graph, <span style=\"text-decoration: underline;\">for the test I&#8217;m doing using SSDs<\/span>:<\/p>\n<ul>\n<li>For a full DBCC CHECKDB, there is a 70-80% performance gain from DOP 1 to DOP 2, from DOP 2 to DOP 4, and from DOP 4 to DOP 8.<\/li>\n<li><span style=\"line-height: 13px;\">For a full <em>DBCC CHECKDB<\/em> run, there is really no scalability gain in performance for a DOP greater than 8, and in fact a slight performance degradation for higher DOPs.<\/span><\/li>\n<li>As you can see for the results of a full <em>DBCC CHECKDB<\/em> with DOP 8 and higher, <em>DBCC CHECKDB<\/em> on that server without I\/O waits can process 0.5GB of the database per second. That&#8217;s pretty cool and is down to the fast SSDs (there were no <em>PAGEIOLATCH_SH<\/em> waits) and fast processors. Back in SQL Server 2000, our performance benchmark was about 1GB per minute. See <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-example-dbcc-checkdb-run-times\/\" target=\"_blank\">here<\/a> for some example SQL Server 2000 benchmark times that show around 3-5GB\/minute, but not on SSDs.<\/li>\n<li>The main boost in performance when running on SSDs comes from using the <em>WITH PHYSICAL_ONLY<\/em> option. This is, of course, to be expected as it cuts out all of the logical checks that make up the bulk of the CPU usage of <em>DBCC CHECKDB<\/em>.<\/li>\n<\/ul>\n<p>For the scalability result running a full <em>DBCC CHECKDB<\/em>, some latch contention for structures inside <em>DBCC CHECKDB<\/em> starts to show up at DOP 8, but not enough to give zero scalability at higher DOPs. This comes from a much higher time spent waiting for <em>CXPACKET<\/em> waits, roughly doubling from DOP 8 to DOP 16, and again from DOP 16 to DOP 32. I blame the query processor for that :-) In all seriousness, I didn&#8217;t expect to see a scalability gain with DOP higher than 8, based on what I&#8217;ve seen in the past.<\/p>\n<h2><em>PHYSICAL_ONLY<\/em> Results<\/h2>\n<p>The results without DOP 1 and DOP 2 are shown below, which allows us to focus on the <em>PHYSICAL_ONLY<\/em> results:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop2.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-4094\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop2.jpg\" alt=\"CHECKDB-DOP2\" width=\"749\" height=\"651\" \/><\/a><\/p>\n<p>Again, <span style=\"text-decoration: underline;\">for the test I&#8217;m doing using SSDs<\/span>, there are some interesting results:<\/p>\n<ul>\n<li><span style=\"line-height: 13px;\">DOP 4 seems to give the best performance overall, apart from the use of both trace flags with DOP 8.<\/span><\/li>\n<li>Higher DOPs get progressively worse performance. Analysis of wait and latch statistics show that this is caused by increasing amounts of latch contention at higher DOPs.<\/li>\n<li>The two trace flags don&#8217;t have a pronounced effect on performance in my situation.<\/li>\n<\/ul>\n<h2>Summary<\/h2>\n<p>When running with a database on SSDs and very fast processors, full <em>DBCC CHECKDB<\/em> performance scales to DOP 8, and the main performance gain comes from using the <em>WITH PHYSICAL_ONLY<\/em> option. The two trace flags introduced in late 2011 don&#8217;t have a pronounced effect on <em>PHYSICAL_ONLY<\/em> performance. This is all to be expected, as the SSDs really remove the I\/O component of <em>DBCC CHECKDB<\/em> performance and scalability from consideration.<\/p>\n<p>Although these tests are not conclusive until I&#8217;ve run similar tests on a slower I\/O subsystem, I&#8217;m tempted to recommend limiting <em>DBCC CHECKDB<\/em>&#8216;s DOP to 8 if you can (maybe using Resource Governor) to limit its resource drain on your system.<\/p>\n<p>I obviously also have to recommend using SSDs to vastly reduce or effectively eliminate (in my case) I\/O waits.<\/p>\n<p>During April and May I&#8217;ll perform the same set of tests using 15k SCSI drives and also 7.2k SATA drives to see if and how the results differ.<\/p>\n<p>Hope you found this interesting!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I&#8217;m finally getting around to presenting the results. Make sure to also read the recent\u00a0post where I talk about the detrimental effect of computed-column indexes on [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,22,31,34,43,66,89,96],"tags":[],"class_list":["post-4092","post","type-post","status-publish","format-standard","hentry","category-benchmarking","category-checkdb-from-every-angle","category-database-maintenance","category-dbcc","category-fusion-io","category-performance-tuning","category-ssds","category-trace-flags"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>DBCC CHECKDB scalability and performance benchmarking on SSDs - 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\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DBCC CHECKDB scalability and performance benchmarking on SSDs - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I&#8217;m finally getting around to presenting the results. Make sure to also read the recent\u00a0post where I talk about the detrimental effect of computed-column indexes on [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-03T18:34:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:47:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\" \/>\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\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/\",\"name\":\"DBCC CHECKDB scalability and performance benchmarking on SSDs - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\",\"datePublished\":\"2013-04-03T18:34:48+00:00\",\"dateModified\":\"2017-04-13T19:47:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DBCC CHECKDB scalability and performance benchmarking on SSDs\"}]},{\"@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":"DBCC CHECKDB scalability and performance benchmarking on SSDs - 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\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/","og_locale":"en_US","og_type":"article","og_title":"DBCC CHECKDB scalability and performance benchmarking on SSDs - Paul S. Randal","og_description":"Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I&#8217;m finally getting around to presenting the results. Make sure to also read the recent\u00a0post where I talk about the detrimental effect of computed-column indexes on [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/","og_site_name":"Paul S. Randal","article_published_time":"2013-04-03T18:34:48+00:00","article_modified_time":"2017-04-13T19:47:38+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg","type":"","width":"","height":""}],"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\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/","name":"DBCC CHECKDB scalability and performance benchmarking on SSDs - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg","datePublished":"2013-04-03T18:34:48+00:00","dateModified":"2017-04-13T19:47:38+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/04\/checkdb-dop1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkdb-scalability-and-performance-benchmarking-on-ssds\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"DBCC CHECKDB scalability and performance benchmarking on SSDs"}]},{"@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\/4092","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=4092"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4092\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4092"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4092"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4092"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}