{"id":550,"date":"2013-03-08T06:51:10","date_gmt":"2013-03-08T14:51:10","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=550"},"modified":"2017-04-13T09:19:10","modified_gmt":"2017-04-13T16:19:10","slug":"sql-server-maintenance-plans-and-parallelism-index-rebuild","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/","title":{"rendered":"SQL Server Maintenance Plans and Parallelism – Index Rebuilds"},"content":{"rendered":"

In my previous post, SQL Server Maintenance Plans and Parallelism – CHECKDB<\/a>, we looked at the degree of parallelism used when CHECKDB is run.\u00a0 It ultimately depends on SQL Server Edition and the max degree of parallelism setting for the instance, which is not the case for index rebuilds (today’s topic, as you probably surmised!).<\/p>\n

Index Rebuilds<\/strong><\/p>\n

The max degree of parallelism can be configured for index rebuilds<\/a> using WITH (MAXDOP = n<\/em>):<\/p>\n

\r\nUSE [AdventureWords2012];\r\nGO\r\n\r\nALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]\r\n     REBUILD WITH (MAXDOP = 8);\r\nGO\r\n<\/pre>\n
If this option is included, it overrides the max degree of parallelism value configured for the instance. For example, I can rebuild the IX_SalesOrderDetail_ProductID index on Sales.SalesOrderDetail with MAXDOP set to 8, even though MAXDOP is set to 4 for the instance.\u00a0 If WITH (MAXDOP = n<\/em>) is not specified for an ALTER INDEX … REBUILD statement, then SQL Server will use the MAXDOP value set for the instance.<\/p>\n
Now, unfortunately, parallel index operations are only permitted in Enterprise Edition<\/a>.\u00a0 If you’re running Standard Edition, you’re stuck with single threaded rebuilds, just like you’re stuck with single threaded integrity checks.\u00a0 Despite this sad news, I thought I’d run through a demo that shows the max degree of parallelism used during the index rebuild. I’m going to run ALTER INDEX REBUILD for a selected index in the AdventureWorks2012 database, and I’ll use Extended Events to capture each statement executed (sp_statement_completed event), and the actual query plan for the statement (query_post_execution_showplan event).<\/p>\n
**Important note here again: it is NOT<\/em> <\/strong>recommended to capture the query_post_execution_showplan event against a live, production system.\u00a0 This event generates significant<\/em> <\/strong>performance overhead, and you are warned of this when configuring the session via the GUI.\u00a0 If you repeat any of the demos here, please make sure to execute them against a test environment.\u00a0 It’s very important to me that you do not bring down your production environment.**<\/span><\/p>\n
Here are the statements to create the event session, start it, run the ALTER INDEX … REBUILD statements, then stop the event session.\u00a0 As in my previous post, I am using a file target to capture the output, and the path is C:\\temp.\u00a0 You may need to modify this path for your environment.\u00a0 I still have max degree of parallelism set to 4 for my instance, but we’ll set it before we run anything just for good measure.<\/p>\n
\r\nsp_configure 'show advanced options', 1;\r\nGO\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\nsp_configure 'max degree of parallelism', 4;\r\nGO\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n\r\nCREATE EVENT SESSION [CapturePlans] ON SERVER\r\nADD EVENT sqlserver.query_post_execution_showplan(\r\n     ACTION(sqlserver.plan_handle,sqlserver.sql_text)),\r\nADD EVENT sqlserver.sp_statement_completed(\r\n     ACTION(sqlserver.sql_text))\r\nADD TARGET package0.event_file(SET filename=N'C:\\temp\\CapturePlans.xel'),\r\nADD TARGET package0.ring_buffer(SET max_memory=(102400))\r\nWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,\r\n     MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\n     ON SERVER\r\n     STATE=START;\r\nGO\r\n\r\nUSE [AdventureWords2012];\r\nGO\r\n\r\nALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]\r\n     REBUILD WITH (MAXDOP = 8);\r\nGO\r\n\r\nALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]\r\n     REBUILD;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\n     ON SERVER\r\n     STATE=STOP;\r\nGO\r\n<\/pre>\n
Note that I used a different version of the SalesOrderDetail table named SalesOrderDetailEnlarged.\u00a0 This table has over 4 million rows in it and was populated using Jonathan’s Create Enlarged AdventureWorks Table <\/em>script<\/a> to ensure I’d have a table large enough to warrant a parallel rebuild.\u00a0 After I stopped the event session I opened the .xel file from C:\\temp in Management Studio and added the sql_text column to the display so I could easily find the ALTER INDEX statements.<\/p>\n
The screen shot below is from the ALTER INDEX statement with MAXDOP = 8 included.\u00a0 The query_post_execution_showplan event is highlighted, you can see the sql_text, and I hovered over the showplan_xml to show the first part of the xml version of the plan.\u00a0 Note the red box around QueryPlan DegreeofParallelism…it’s 8, as expected:<\/p>\n
\"ALTER<\/a>
ALTER INDEX … REBUILD WITH (MAXDOP = 8)<\/figcaption><\/figure>\n
If you’re playing along at home in your test environment, you can click on the Query Plan to see the graphical view, or double-click the XML to view that plan that way.\u00a0 Now check out the screen capture below, which is for the ALTER INDEX statement that did not include the MAXDOP option:<\/p>\n
\"ALTER<\/a>
ALTER INDEX … REBUILD (default option)<\/figcaption><\/figure>\n
The max degree of parallelism for the plan is 4 because if the MAXDOP option is not included, SQL Server uses the max degree of parallelism set for the instance.\u00a0 Note that this holds true when parallelism is disabled for an instance (max degree of parallelism = 1):<\/p>\n
\r\nsp_configure 'max degree of parallelism', 1;\r\nGO\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\n ON SERVER\r\n STATE=START;\r\nGO\r\n\r\nUSE [AdventureWords2012];\r\nGO\r\n\r\nALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]\r\n     REBUILD;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\n ON SERVER\r\n STATE=STOP;\r\nGO\r\n<\/pre>\n
\"ALTER<\/a>
ALTER INDEX … REBUILD (default option) – MAXDOP = 1 for instance<\/figcaption><\/figure>\n
The plan shows a DegreeOfParallelism of 0 – this means that the query did not use parallelism – and that the plan includes a NonParallelPlanReason* of “MaxDOPSetToOne”.\u00a0 Therefore, if MAXDOP is set to 1 for an instance, and the default ALTER INDEX … REBUILD statements are used to rebuild indexes – where the MAXDOP option is not<\/em> included – then rebuilds will be single-threaded.\u00a0 For some well-known applications (e.g. SharePoint, SAP, BizTalk)\u00a0 it is<\/em> recommended to set the max degree of parallelism to 1 for the instance.\u00a0 While that option may be appropriate for application-specific queries, it means that your index rebuild operations may run longer than if parallelism was enabled.\u00a0 It may be worth modifying your index maintenance script to include the MAXDOP option for ALTER INDEX REBUILD statements.<\/div>\n
In the event that you have a max degree of parallelism value above 1 specified for the instance, but you’re not sure what the “right” MAXDOP value should be for your index rebuilds, you can let SQL Server decide.\u00a0 If you include the WITH (MAXDOP = 0) option in your rebuild syntax, then the optimizer will determine how many CPUs to use, which could be anywhere from 1 to all of the CPUs available to SQL Server.\u00a0 This is the recommended setting per Books Online, but I would caution you to use this option only if you’re comfortable with SQL Server potentially using all CPUs for a rebuild.\u00a0 If you happen to be running other tasks or processes in the database while the rebuilds run – not ideal, but for a 24×7 solution you often don’t have a choice – then you should specify a MAXDOP value below the total number of CPUs available.<\/p>\n
Finally, in case you’re wondering about parallelism and reorganizing indexes…the WITH (MAXDOP = n<\/em>) option is not available for ALTER INDEX REORGANIZE, as index reorganization is always a single-threaded operation.\u00a0 The final post in this series will cover parallelism and the UPDATE STATISTICS command, and if you’re manually managing statistics and specifying the sample, you don’t want to miss it!<\/p>\n
*If you’re interested, Joe talks about the NonParallelPlanReason attribute\u00a0 in his post, SQL Server 2012 Execution Plan’s NonParallelPlanReason<\/a>, which may be useful when you’re digging into execution plans in SQL Server 2012 and higher. <\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
In my previous post, SQL Server Maintenance Plans and Parallelism – CHECKDB, we looked at the degree of parallelism used when CHECKDB is run.\u00a0 It ultimately depends on SQL Server Edition and the max degree of parallelism setting for the instance, which is not the case for index rebuilds (today’s topic, as you probably surmised!). […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,13,17],"tags":[25,24],"yoast_head":"\nSQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato<\/title>\n<meta name=\"description\" content=\"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds\" \/>\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\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-08T14:51:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2013\/02\/indexrebuild_8.png\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\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\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/\",\"name\":\"SQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2013-03-08T14:51:10+00:00\",\"dateModified\":\"2017-04-13T16:19:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Maintenance Plans and Parallelism – Index Rebuilds\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato","description":"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds","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\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato","og_description":"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/","og_site_name":"Erin Stellato","article_published_time":"2013-03-08T14:51:10+00:00","article_modified_time":"2017-04-13T16:19:10+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2013\/02\/indexrebuild_8.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/","name":"SQL Server Maintenance Plans and Parallelism - Index Rebuilds - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2013-03-08T14:51:10+00:00","dateModified":"2017-04-13T16:19:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"SQL Server ALTER INDEX ... REBUILD commands can use parallelism in Enterprise Edition, and the MAXDOP value can be configured to optimize the index rebuilds","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-maintenance-plans-and-parallelism-index-rebuild\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQL Server Maintenance Plans and Parallelism – Index Rebuilds"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/550"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=550"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/550\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}