{"id":970,"date":"2010-08-18T00:00:00","date_gmt":"2010-08-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/rebuilding-indexes-using-the-ssms-database-maintenance-wizard\/"},"modified":"2021-08-24T13:40:26","modified_gmt":"2021-08-24T13:40:26","slug":"rebuilding-indexes-using-the-ssms-database-maintenance-wizard","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/rebuilding-indexes-using-the-ssms-database-maintenance-wizard\/","title":{"rendered":"Rebuilding Indexes using the SSMS Database Maintenance Wizard"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">This article will describe how to use the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task in the Database Maintenance Wizard to maintain the health of your database indexes, which in turn, can boost the performance of your queries. It will cover:<\/p>\n<ul>\n<li>What <span class=\"STCodeinTextChar\">Rebuild Index<\/span> does and the problems that can arise if it is not used  <\/li>\n<li>Considerations when using the task, and possible alternatives  <\/li>\n<li>How to configure and schedule the task using the Wizard<\/li>\n<\/ul>\n<p>This article assumes basic familiarity with the interface of the SSMS Maintenance Plans Wizard and focuses on the details of the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task. If you require a thorough tutorial on use of the Wizard, please refer to my free eBook, <a href=\"http:\/\/www.simple-talk.com\/books\/sql-books\/brads-sure-guide-to-sql-server-maintenance-plans\/\">Brad&#8217;s Sure Guide to SQL Server Maintenance Plans<\/a>.<\/p>\n<h1>An Overview of the Rebuild Index Task<\/h1>\n<p>Over time, as indexes are subjected to data modifications, index fragmentation can occur in the form of:<\/p>\n<ul>\n<li><b>Gaps in data pages<\/b> &#8211; this creates wasted empty space.  <\/li>\n<li><b>Logical fragmentation<\/b> &#8211; this is a logical ordering of the data that no longer matches the physical ordering of the data<\/li>\n<\/ul>\n<p>Gaps in data pages can reduce the number of rows that can be stored in SQL Server&#8217;s data cache, leading to increased disk I\/O. Logical fragmentation can cause extra disk activity as the disk subsystem has to work harder to find the data on disk and move it to the data cache. The only way to remove wasted space and logical fragmentation is to rebuild or reorganize the indexes on a regular basis. This is one of the most useful and powerful maintenance tasks that you can perform on a database, because the steps it performs can greatly boost database performance.<\/p>\n<p>If you configure the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task using all the default settings, then when the task runs, it physically drops and rebuilds every index in your selected databases, removing both wasted empty space and logical fragmentation. As a byproduct of rebuilding all the indexes, index and column statistics are also recreated anew and fully updated.<\/p>\n<p>The T-SQL command that is generated from these default settings is as follows:<\/p>\n<pre>ALTER INDEX index_name ON table_name REBUILD PARTITION = ALL WITH ( PAD_INDEX&#160; = OFF, STATISTICS_NORECOMPUTE&#160; = OFF, ALLOW_ROW_LOCKS&#160; = ON, ALLOW_PAGE_LOCKS&#160; = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )<\/pre>\n<p>While this command looks complicated, the bulk of the code is simply turning off various options. The <span class=\"STCodeinTextChar\">ALTER INDEX<\/span> command has a lot of options, some of which you can configure using the Maintenance Plan Wizard, but many more that you cannot. We will discuss all of the available configuration options as we work through this article.<\/p>\n<p>If you review the text file report from this task, it looks something similar to the following:<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Retrieving Text file reports<\/b><br \/>Unless you specified otherwise in the &#8220;Select Report Options&#8221; screen of the Wizard, text reports created by the Wizard are, by default, located in this folder: C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\JOBS<\/p>\n<\/div>\n<pre>Microsoft(R) Server Maintenance Utility (Unicode) Version\nReport was generated on \"HAWAII\".\nMaintenance Plan: User Databases Maintenance Plan\nDuration: 00:00:23\nStatus: Succeeded.\nDetails:\nRebuild Index (HAWAII)\nRebuild index on Local server connection\nDatabases: AdventureWorks\nObject: Tables and views\nOriginal amount of free space\nTask start: 2009-07-29T16:01:48.\nTask end: 2009-07-29T16:02:09.\nSuccess\nCommand:USE [AdventureWorks]\nGO\nALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REBUILD PARTITION = ALL WITH ( PAD_INDEX&#160; = OFF, STATISTICS_NORECOMPUTE&#160; = OFF, ALLOW_ROW_LOCKS&#160; = ON, ALLOW_PAGE_LOCKS&#160; = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )\nGO\nUSE [AdventureWorks]\nGO\nALTER INDEX [PK_DatabaseLog_DatabaseLogID] ON [dbo].[DatabaseLog] REBUILD PARTITION = ALL WITH ( PAD_INDEX&#160; = OFF, STATISTICS_NORECOMPUTE&#160; = OFF, ALLOW_ROW_LOCKS&#160; = ON, ALLOW_PAGE_LOCKS&#160; = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )\nGO\n<\/pre>\n<p>Once the header information is displayed, note that the command executes for each index in the database. I have abbreviated the report to only show two indexes being rebuilt, as there were a lot more that were actually rebuilt in the full report.<\/p>\n<h1>When and how often should you Rebuild Indexes?<\/h1>\n<p>The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.<\/p>\n<p>On the other hand, the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task is a resource intensive task. In addition, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked, until the rebuild is complete. As such, the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task is considered an <b>offline activity<\/b>, to be run when as few people as possible are accessing a database. In general, this means during a scheduled maintenance window.<\/p>\n<p>It is quite difficult to offer general advice with regard to when and how often to rebuild indexes, using the Maintenance Plan Wizard, as it is so dependent on the nature of the data, the indexes and the queries that use them. However, take a look at my general advice with regard to index rebuilding, and then we&#8217;ll consider the advice in a little more detail over the coming sections:<\/p>\n<ul>\n<li><b>Nightly, if required<\/b>. If your indexes fragment rapidly, and you have a nightly maintenance window that allows you to run the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, along with all the other maintenance tasks; then do so. Index fragmentation will degrade the performance of your indexes. Assuming that you have a maintenance window, rebuilding every night can&#8217;t do any harm, and can very well boost the performance of your server.  <\/li>\n<li><b>Weekly, at minimum<\/b>. If you can&#8217;t perform this task nightly, then, at a minimum, it should be run once a week, during a maintenance window. If you wait much longer than a week, you risk hurting your SQL Server&#8217;s performance due to the negative impact of wasted empty space and logical fragmentation.  <\/li>\n<li><b>Consider alternatives, otherwise<\/b>. If you don&#8217;t have a maintenance window long enough to run this task at least once a week, then you need to consider the following alternatives:  <\/li>\n<li><b>Use the online version of the<\/b> <span class=\"STCodeinTextBold\">Rebuild Index<\/span><b> task<\/b> &#8211; available only with the Enterprise Edition of SQL Server.  <\/li>\n<li><b>Use the <\/b><span class=\"STCodeinTextBold\">Reorganize Index<\/span><b> task followed by the <\/b><span class=\"STCodeinTextBold\">Update Statistics<\/span><b> task<\/b> &#8211; if you&#8217;re using the Standard Edition of SQL Server. This is your only real alternative when using the Maintenance Plan Wizard if want to avoid the <span class=\"STCodeinTextChar\">Rebuide Index<\/span> task.  <\/li>\n<li><b>Avoid the Maintenance Plan Wizard<\/b> &#8211; T-SQL or PowerShell scripts offer greater control and flexibility over the exact nature and duration of this task.<\/li>\n<\/ul>\n<h1>Tracking Index Fragmentation<\/h1>\n<p>The question of exactly <i>how often<\/i> to rebuild indexes is a difficult one to answer, and the Maintenance Plan Wizard doesn&#8217;t offer any guidance. The speed and degree to which an index fragments depends on how it is used and will vary wildly from database-to-database.<\/p>\n<p>It is beyond the scope of this article to enter a full discussion of measuring index fragmentation, and therefore deciding how often you should rebuild your database&#8217;s indexes. However, it is worth noting that the <span class=\"STCodeinTextChar\">sys.dm_db_index_physical_stats<\/span> Dynamic Management Function contains two columns that store valuable information regarding index fragmentation:<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">avg_page_space_used_in_percent<\/span> &#8211; this column stores the average amount of space that is used on a page. For example, a particular index might have 50% space used, which means that only half of the space on a data page, on average, is used to store rows of data.  <\/li>\n<li><span class=\"STCodeinTextBold\">avg_fragmentation_in_percent<\/span> &#8211; this column stores the degree of logical fragmentation of an index, as a percentage. For example, a particular index might be 80% fragmented, which means that, on average, 80% of the data pages physical ordering does not match their logical ordering.<\/li>\n<\/ul>\n<p>If you were to track this data over a period of time, you will be able to better gauge how quickly your indexes fragment, and so how often you should consider rebuilding them. However, if you are at this level, then the chances are high that you&#8217;ll be using scripting techniques to rebuild your indexes rather than the Maintenance Plan Wizard.<\/p>\n<h1>Offline Index Maintenance<\/h1>\n<p>While it is not a requirement to perform the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task offline, while the database is not being accessed, it is certainly a strong recommendation, especially for large databases with many users. If your tables are relatively small, rebuilding will be fast and most users who happen to be accessing the database at the same time probably won&#8217;t notice any performance degradation as a result of the locking required by <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task. On the other hand, if your tables are big, or if you have lots of concurrent users, the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task can negatively affects your user&#8217;s experience, greatly slowing down their access to the database, and potentially causing some queries to time out from their client application.<\/p>\n<p>Generally speaking, if you have a large enough maintenance window to accommodate running your <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task offline, then I recommend you use this task, and run it during that window. <\/p>\n<h1>Online Index Maintenance<\/h1>\n<p>If you don&#8217;t have a maintenance window, or it is not long enough to accommodate an offline <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, then you have one or two possible alternatives available to you, when using the Maintenance Plan Wizard:<\/p>\n<ul>\n<li>Use the online version of the <span class=\"STCodeinTextChar\">Rebuild Index task <\/span> <\/li>\n<li>Use the <span class=\"STCodeinTextChar\">Reorganize Index task<\/span> followed by the <span class=\"STCodeinTextChar\">Update Statistics<\/span> task<\/li>\n<\/ul>\n<p>If you have the Enterprise Edition of SQL Server, the Maintenance Plan Wizard offers a <b>Keep index online while reindexing<\/b> option, which means that the index will continue to be available to users even while it is being rebuilt. Even though this is an online activity, you will still want to schedule this task during a time of the day when the server is less busy as it is still a resource intensive activity. Performing this online task during busy times of the day can affect your users&#8217; ability to access the database in a timely manner, especially if your SQL Server already has performance bottlenecks.<\/p>\n<p>If you don&#8217;t have Enterprise Edition, and your maintenance widow is too short to accommodate an offline <span class=\"STCodeinTextChar\">Rebuild Index <\/span>task, then you should consider using the <span class=\"STCodeinTextChar\">Reorganize Index<\/span> task instead, and then running the <span class=\"STCodeinTextChar\">Update Statistics<\/span> task immediately thereafter. The <span class=\"STCodeinTextChar\">Reorganize Index<\/span> task is an online operation, which means that it can run while users are accessing the database. While this is an online process, it is still resource intensive, and you should schedule the task during a time of the day when the server is less busy.<\/p>\n<p>The downside to using the <span class=\"STCodeinTextChar\">Reorganize Index<\/span> task is that its index defragmentation capability is not as thorough and complete as the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task. In addition, it can take longer to run than the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, and you have to run the <span class=\"STCodeinTextChar\">Update Statistics<\/span> task as a separate step.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Reorganizing Indexes<\/b><br \/>This topic, as well as subsequent updating of statistics, is also covered in detail in my book, Brad&#8217;s Sure Guide to SQL Server maintenance Plans, available as a <a href=\"http:\/\/www.simple-talk.com\/books\/sql-books\/brads-sure-guide-to-sql-server-maintenance-plans\/\">free download from here<\/a>.<\/p>\n<\/div>\n<h1>Scripting Index Rebuilds<\/h1>\n<p>If you have the Enterprise Edition of SQL Server, chances are that your databases may be very large, and using the Maintenance Plan Wizard to maintain your databases may not be a great choice in the first place. You can obtain more flexibility and control by creating your own custom maintenance plans using T-SQL or PowerShell scripts.<\/p>\n<p>For example, you can measure and track fragmentation using <span class=\"STCodeinTextChar\">sys.dm_db_index_physical_stats<\/span> and then build a script to defragment only those indexes that really need it.<\/p>\n<h1>Configuring the Rebuild Index Task<\/h1>\n<p>Now that we know a little about the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, and when it should be run, let&#8217;s take a look at its configuration screen from the Maintenance Plan Wizard, shown in Figure  1.1.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM1.jpg\" alt=\"1119-BM1.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.1: We are ready to configure the &#8220;Rebuild Index&#8221; task.<\/p>\n<p>Our first choice is to select which databases we want to run this task against..<\/p>\n<h1>Database Selection<\/h1>\n<p>First, notice the <b>Databases<\/b> drop-down box appears on the screen. Second, notice that directly below the <b>Databases<\/b> drop-down box are two more drop-down boxes: <b>Object<\/b> and <b>Selection<\/b>. These two drop-down boxes appear for some tasks, and not others. We will talk about what they do in a moment.<\/p>\n<h2>Selecting Several Databases<\/h2>\n<p>As a general rule, you want to keep the number of separate Maintenance Plans to a minimum, so ideally you&#8217;d create a single Plan and apply the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task to all indexes in a given set of databases, for example in all <b>user<\/b> databases. Also, in order to ease maintenance and avoid confusion, each task in the Plan should be applied to the same set of databases.<\/p>\n<p>However, there may be special cases where you&#8217;d need to create separate Plans to deal with the specific index maintenance requirements of different databases. For example, let&#8217;s assume that on a single SQL Server instance you have 25 small databases, each less than 1 GB in size, and one large database, say 50 GB. Let&#8217;s also assume that few if any users will need access to the small databases during your maintenance windows, but that many users may need to access the 50 GB database during this time. In this case, you might consider creating a special Maintenance Plan for the 50 GB database that uses the <span class=\"STCodeinTextChar\">Reorganize Index<\/span> and <span class=\"STCodeinTextChar\">Update Statistics<\/span> tasks, and another Maintenance Plan that applies the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task to the smaller databases.<\/p>\n<p>For this example, we are going to keep things simple, so let&#8217;s assume we want to perform the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task for all user databases. In this case, we can choose the option shown in Figure  1.2, and then click <b>OK<\/b>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM2.jpg\" alt=\"1119-BM2.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.2: To keep things simple, select the &#8220;All user databases&#8221; option.<\/p>\n<p>The <b>Define Rebuild Index Task<\/b> screen reappears, and the two drop-down boxes I referred to earlier are displayed below the <b>Databases<\/b> drop-down box, but they are grayed out, as shown in Figure  1.3.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM3.jpg\" alt=\"1119-BM3.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.3: The &#8220;Object&#8221; and &#8220;Selection&#8221; drop down boxes are not available.<\/p>\n<p>So what&#8217;s going on? Why are these two options grayed out? The reason is that these two options are only available if you select <b>one<\/b> database on which to run the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task. Since we selected <b>All user databases<\/b>, these two options are not available.<\/p>\n<h2>Selecting a Specific Database<\/h2>\n<p>Although it does not apply to our example, let&#8217;s take a look at what happens if we select only a single database, such as <span class=\"STCodeinTextChar\">AdventureWorks<\/span>, for the task. To do this, select <b>These databases<\/b> from the screen shown in Figure  1.2 and then check the checkbox for <span class=\"STCodeinTextChar\">AdventureWorks<\/span>. When you click <b>OK<\/b>, this section of the <b>Define Rebuild Index Task<\/b> screen will look as shown in Figure  1.4.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM4.jpg\" alt=\"1119-BM4.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.4: When a single database is selected, then the &#8220;Object&#8221; drop-down box becomes available.<\/p>\n<p>Notice that <b>Specific databases<\/b> now appears in the <b>Databases<\/b> drop-down box, the <b>Object<\/b> box is now available, and the <b>Selection<\/b> box is still, for the time being, grayed out.<\/p>\n<p>What the <b>Object<\/b> and <b>Selection<\/b> options allow us to do is to selectively rebuild some of the indexes in your database, and not others. If we click on the <b>Object<\/b> drop-down box, we&#8217;ll see the choices shown in Figure  1.5.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM5.jpg\" alt=\"1119-BM5.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.5: You must select either &#8220;Table&#8221; or &#8220;View&#8221;.<\/p>\n<p>Notice that there are three choices for <b>Object<\/b>. If you leave the default option selected, <b>Tables and views<\/b>, then the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task will be applied to the indexes associated with all tables, and all indexed views, in the selected database. In other words, you haven&#8217;t changed anything. In order to narrow the scope of the task to specific objects, you need to choose either <b>Table<\/b> or <b>View<\/b>. Having done this, the <b>Selection<\/b> drop-down box becomes available. For example, let&#8217;s choose <b>Table<\/b>, and then click on &#8220;Select one or more&#8221; in the now available <b>Selection<\/b> drop-down box, as shown in Figure  1.6.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM6.jpg\" alt=\"1119-BM6.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.6: You can select which tables you want to rebuild with the Rebuild Index task.<\/p>\n<p>Now, we get the option of selecting specific tables within the <span class=\"STCodeinTextChar\">AdventureWorks <\/span>database, to which this task should apply. For example, we could choose to rebuild only the indexes associated with the <span class=\"STCodeinTextChar\">dbo.ErrorLog<\/span> table, or we could select some combination of tables, by checking each of the relevant checkboxes.<\/p>\n<p>Why would we want to rebuild the indexes for some tables and not others? Actually, there is a very good reason for this. In most databases, there are some tables that are virtually static; they rarely if ever change, and so there is no benefit in rebuilding their associated indexes as they don&#8217;t, over time, develop wasted empty space or become logically fragmented. By selecting only those indexes that really need defragmenting, you can reduce the time it takes to perform the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, and at the same time, reduce the resource overhead associated with this task.<\/p>\n<p>The problem I see is that most people who are using the Maintenance Wizard won&#8217;t have the knowledge to determine which indexes are relatively static and which are subject to a lot of wasted space and logical fragmentation. If you are at the level where you know how to evaluate each index using the <span class=\"STCodeinTextChar\">sys.dm_db_index_physical_stats<\/span> DMF, in order to apply a selective rebuild process, then the chances are you are probably better off implementing this process using T-SQL or PowerShell scripts, and avoiding use of the Maintenance Plan Wizard in the first place.<\/p>\n<p>Before we move on, let&#8217;s briefly consider the <b>View<\/b> option that is available in the <b>Object<\/b> drop-down box, as shown in Figure  1.7. <\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM7.jpg\" alt=\"1119-BM7.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.7: You can select which indexed views you want to rebuild with the Rebuild Index task.<\/p>\n<p>In this case, &#8220;view&#8221; doesn&#8217;t refer to conventional views, but to indexed views. Indexed views are physical views, unlike regular views, which are only materialized when they are called by a query. Because indexed views are physical, they need rebuilding just like regular indexes. As per my advice with regard to the <b>Table<\/b> option, if you need this kind of granularity for the maintenance of your indexes, you shouldn&#8217;t be using the Maintenance Plan Wizard for this task.<\/p>\n<p>While I have taken a little time to explain what the <b>Object<\/b> and <b>Selection<\/b> drop-down boxes do, I am recommending that you don&#8217;t use then, as they just make Maintenance Plans overly complicated, defeating the benefit of using them in the first place.<\/p>\n<h1>Free space options<\/h1>\n<p>We still have several more choices to make before we are done configuring this task. Note that the discussion of these options assumes that each of your tables has a clustered index, and is not a heap. A heap is a table without a clustered index. As a best practice, all tables should have a clustered index.<\/p>\n<p>The first two choices are listed under <b>Free space options<\/b> and include &#8220;Reorganize pages with the default amount of free space&#8221; and &#8220;Change free space per page percentage to&#8221;, as shown in Figure  1.8. You can choose one option or the other, but not both.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM8.jpg\" alt=\"1119-BM8.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.8: These options can have a significant impact on the Rebuild Index task.<\/p>\n<p>The default option of &#8220;Reorganize pages with the default amount of free space&#8221; is a little confusing. First, it says <i>reorganize<\/i>, not <i>rebuild<\/i>. Remember, we are working on the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, not the <span class=\"STCodeinTextChar\">Reorganize Index<\/span> task. Don&#8217;t let this confuse you into thinking that selecting this option reorganizes indexes, rather than rebuild them. It does the latter, and this is actually a mistake in the user interface. It really should say &#8220;rebuild,&#8221; not &#8220;reorganize&#8221;.<\/p>\n<p>The second part of this first option says &#8220;default amount of free space&#8221;. What does that mean? When creating a SQL Server index, there is an option to create the index with a certain amount of free space on each data page. This setting is known as the <b>fill factor<\/b>. If an index is created without specifying a fill factor, then the default fill factor is used, which is 100 (actually 0, but 0 means the same thing as a 100% fill factor). This means that no free space is created for the data pages of an index.<\/p>\n<p>The potential problem with a fill factor of 100 arises when data is added to a table as a result of an <span class=\"STCodeinTextChar\">INSERT<\/span> or <span class=\"STCodeinTextChar\">UPDATE<\/span>, and a new row needs to be added to a data page. If there is no room for it, then SQL Server will reorganize the rows, moving some of the rows onto a new data page, and leaving some on the old data page. This is known as <b>page splitting<\/b>. While page splitting is a normal SQL Server activity, too much page splitting can cause performance issues because it results in index fragmentation, the very thing we are trying to eliminate with the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task. In order to mitigate this problem, DBAs often decrease the fill factor to, say, 90, meaning that data pages be 90% full, leaving 10% free space.<\/p>\n<p>For more information regarding fill factors and page splitting refer to Books Online. A full discussion of these topics is beyond the scope of this article, but I needed to include a little background so you better understand what is happening when you make particular selections within the Wizard. Also, don&#8217;t assume that the example of 90 I give in this example for a fill factor is appropriate for your indexes. It may be, or it may not be.<\/p>\n<p>What is really confusing is that the phrase &#8220;default amount of free space&#8221; in the Wizard does not mean the same thing as the &#8220;default fill factor&#8221; that can be set for the entire server. Some people confuse the two.<\/p>\n<p>In the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task, &#8220;default amount of free space&#8221; refers to the fill factor that was used when a specific index was first built, or last rebuilt. In other words, if you choose the option &#8220;Reorganize pages with the default amount of free space&#8221;, what happens is that each index is rebuilt using whatever fill factor value was used the last time it was rebuilt. This may be the same as the server-wide default, or it may be a specific value that was specified for that index, or it may be a value set using the second &#8220;Change free space per page percentage to&#8221; option (discussed next).<\/p>\n<p>In almost all cases the &#8220;default amount of free space&#8221; option is the one you want to use, as it means the index will be rebuilt using the fill factor that was originally specified when the index was created.<\/p>\n<p>With the second option, &#8220;Change free space per page percentage to&#8221;, you specify a single fill factor value to be used for every index when it is rebuilt. For example, if you choose &#8220;Change free space per page percentage to&#8221; and set it to 10%, this is the same thing as setting all of the indexes in your database to a fill factor of 90, regardless of what the value was when the index was created. It is rarely a good idea for every index in your database to have the same fill factor. The appropriate fill factor is specific to an index, and you can&#8217;t generalize a fill factor that will work well for every index in your database. While this setting might be beneficial for some indexes, it could cause performance problems with other indexes. As a result, I advise against using this option.<\/p>\n<p>Of course, the choice of the default &#8220;Reorganize pages with the default amount of free space&#8221; options assumes that the fill factors of all of your indexes have been ideally set when they were originally created, or last rebuilt. If they aren&#8217;t, then it&#8217;s a tossup as to which option is really the best. But assuming that you don&#8217;t know if the fill factors are ideal or not, which you probably don&#8217;t, I would still recommend using this default option.<\/p>\n<h1>Advanced Options<\/h1>\n<p>The two options under <b>Advanced options<\/b> are shown in Figure 1.9.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM9.jpg\" alt=\"1119-BM9.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.9: The Advanced options section of the Define Rebuild Index Task screen<\/p>\n<p>By default, both options are turned off. The first one is &#8220;Sort results in <span class=\"STCodeinTextChar\">tempdb<\/span>&#8220;. If you <b>don&#8217;t<\/b> choose this option, then when an index is rebuilt, all of the rebuilding activity is performed in the database file itself. If you select the &#8220;Sort results in <span class=\"STCodeinTextChar\">tempdb<\/span>&#8221; option, then some of the activity is still performed in the database, but some of it is also performed in <span class=\"STCodeinTextChar\">tempdb<\/span>. The benefit is that this can often speed up the index rebuild process. The drawback is that it also takes up a little more overall disk space, as space in <span class=\"STCodeinTextChar\">tempdb<\/span> is required, in addition to some space in the database where the indexes are being rebuilt.<\/p>\n<p>The benefit you get out of this option depends on where <span class=\"STCodeinTextChar\">tempdb<\/span> is located on your server. If <span class=\"STCodeinTextChar\">tempdb<\/span> is located on the same drive or array as the database file that is having its indexes rebuilt, then the benefit may be minimal, if any. However, if <span class=\"STCodeinTextChar\">tempdb<\/span> is located on its own isolated drive spindles, then the benefit will be greater because there is less disk I\/O contention.<\/p>\n<p>So, should you use this option? If your databases are small, you probably won&#8217;t be able to discern much performance benefit, but if you have large databases, with large tables and indexes, and if <span class=\"STCodeinTextChar\">tempdb<\/span> is located on its own spindles, then turning this feature on will probably boost index rebuild performance.<\/p>\n<p>The second advanced option is one we&#8217;ve discussed previously: &#8220;Keep index online while reindexing&#8221;. This option is only available if you have the Enterprise Edition of SQL Server. By selecting this option, index rebuilding becomes an online, rather than offline task. If you are using Enterprise Edition, you will probably want to select this option. I say &#8220;probably&#8221; because there are pros and cons of performing an online index rebuild; a topic that is beyond the scope of this article.<\/p>\n<h1>Creating the Job Schedule<\/h1>\n<p>Our final step is to define an appropriate schedule on which to run our <span class=\"STCodeinTextChar\">Rebuild Index<\/span> job. With the previous advice in mind, the best option would be to run the job within a nightly maintenance window. However, in many organizations, only a single weekly maintenance window is available, which is often entire day of Sunday.<\/p>\n<p>Therefore, let&#8217;s schedule the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task to occur on Sunday, right after the <span class=\"STCodeinTextChar\">Database Integrity<\/span> task completes. As such the screen will look as shown in Figure  1.10.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1119-BM10.jpg\" alt=\"1119-BM10.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.10: The schedule for the Rebuild Index job<\/p>\n<p>The only question we need to consider is how soon after running the <span class=\"STCodeinTextChar\">Check Database Integrity<\/span> task should we schedule a <span class=\"STCodeinTextChar\">Database Rebuild<\/span> task? That depends on how long the <span class=\"STCodeinTextChar\">Check Database Integrity<\/span> task takes to complete, and we won&#8217;t know until we try it.<\/p>\n<p>Since this is a new Maintenance Plan, we don&#8217;t have any experience with regard to how long each task runs yet, and so we have to guess. In this example, I&#8217;ll guess that the first <span class=\"STCodeinTextChar\">Check Database Integrity<\/span> task will take an hour, starting at 1:00 AM, so I will schedule the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task to start at 2:00 AM. If I&#8217;m wrong, the two jobs will overlap, which could cause some performance problems.<\/p>\n<p>As a DBA, the first time you run any Maintenance Plan, you need to check how long each job takes to run in order to avoid overlapping jobs. If your guess is wrong, and jobs overlap, you can use the Maintenance Plan Designer (covered in my book) to alter the schedule for the next time it runs.<\/p>\n<p>I recommend that you run the <span class=\"STCodeinTextChar\">Rebuild Index <\/span>task before any of the backup tasks are performed. This way, if you have to restore a backup, your backup will be of the latest, index rebuilt version.<\/p>\n<h1>Summary<\/h1>\n<p>Index fragmentation is an issue all databases experience, and if it is not removed on a regular basis, it can lead to query performance problems. One way to remove index fragmentation is to run the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task on a regular basis, which drops and rebuilds every index in a database. While the <span class=\"STCodeinTextChar\">Rebuild Index<\/span> task is very effective at what it does, it is considered an off-line activity, and it is very resource intensive. As such, using this task may not always be appropriate for all of your databases.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Index fragmentation can cause problems with query performance. Indexes therefore need to be occasionally rebuilt. the Rebuild Index task of the SSMS Database Maintenance Wizard drops and rebuilds every index in a database. It is effective but an off-line activity that is resource-intensive, so it not always the best way of avoiding index fragmentation in a production database. Brad explains&#8230;&hellip;<\/p>\n","protected":false},"author":221835,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[5045,4168,4170,4179,4150,5270],"coauthors":[],"class_list":["post-970","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-brad-mcgehee","tag-database","tag-database-administration","tag-source-control","tag-sql","tag-sql-server-maintenance-plans"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221835"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=970"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/970\/revisions"}],"predecessor-version":[{"id":92264,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/970\/revisions\/92264"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=970"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}