Building on what we just did in Chapter 7, we are now going to reorganize our indexes. Is this necessary, since they would have just been rebuilt? Let’s take a look find out.
Reorganizing vs. Rebuilding
Think about a hotel. It’s a nice building, sitting there all built and stuff. It has a structure and multiple floors, with each floor having lots of rooms. You know that you can get right to the room you’re supposed to go to by going into the correct hotel first, and then up the elevator to the floor with the room on it, and then to the actual room. This is exactly how indexing works.
With this hotel scenario in mind, does it make sense to destroy and rebuild the entire hotel, or just maybe reorganize the way you can find your way to the room that you want? Ultimately, that depends on your interpretation and needs. It might not be necessary to rebuild, but it is absolutely necessary to reorganize, at a minimum. Sometimes, all you need to do is update the map to the room that you need to get to, and not destroy the whole hotel.
Tip
Reorganizing indexes keeps the existing indexes as they are. Rebuilding indexes drops and rebuilds the indexes every single time.
Clearly, there are differences in the time and effort it takes to destroy and rebuild a hotel vs. doing the same to an index. The principle is the same, though, and that is what needs to be understood. Just because you can do something doesn’t always mean that you should, in other words.
Again, depending on your scenario, it might be advantageous to do either of the options, or even both options. There really isn’t a need to do both at the same time, since they both accomplish the same goals of a clean index.
Another interesting point about indexes is that indexes are automatically maintained whenever update, insert, or delete queries are made. This causes modifications to the data itself, which is what causes fragmentation of the data. Excessive fragmentation is what leads to long query times, because the data is physically separated on the disk and not in contiguous pages. Reorganizing and rebuilding puts these pages back in order, so that the queries run faster and your database behaves better.
Setting Up the Maintenance Plan
The process of creating a maintenance plan should be very familiar by now. Right-click the Maintenance Plan option under Management in SSMS and choose Maintenance Plan Wizard. Change your interface to match what you see in Figure 8-1.
Figure 8-1. Select Plan Properties
Click the Change… button to set the schedule to Daily, as in previous chapters, and click OK. You really don’t want to reorganize your index during usage hours, if at all possible, although it won’t hurt anything. Click Next when you are ready.
You are now shown the interface where you can select the task, so choose Reorganize Index, as shown in Figure 8-2.
Figure 8-2. Select Maintenance Tasks
Notice the definition for Reorganize Index given by Microsoft: “The Reorganize Index task defragments and compacts clustered and nonclustered indexes on tables and views. This will improve index-scanning performance.”
Recall the definition for Rebuild Index: “The Rebuild task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth.”
They sound sort of alike, right? So why have two different options that do basically the same thing? Because it makes more sense from a DBA point of view to be able to point a task at a small or medium table with an index and say “go rebuild that index,” as opposed to doing the same thing to a massive table with billions of rows. You may want to just reorganize this index, since rebuilding the index will definitely take some time to run. Rebuilding that single massive index is something that will take a bit of time, more than likely a few seconds at least, but remember that this is an eternity in computer time.
Click Next at the Select Maintenance Tasks screen (shown in Figure 8-2) and then click Next again at the Select Maintenance Task Order screen, as shown in Figure 8-3.
Figure 8-3. Select Maintenance Task Order
You then need to define the task on the Define Reorganize Index Task screen. Exactly like in Chapter 6, choose your database and keep Object set to “Tables and views”. Leave the rest as is. You should see what is shown in Figure 8-4.
Figure 8-4. Define Reorganize Index Task
Click Next here and you can start setting up the reporting options. Again, exactly like in Chapter 7, change the folder location to where you are writing your logs, and enable the operator, as shown in Figure 8-5.
Figure 8-5. Select Report Options
Click Next at this screen to go to the summary screen, as shown in Figure 8-6.
Figure 8-6. Complete the Wizard
This shows us the breakdown of what is happening. Everything is pretty much the same as in Chapter 7, so click Finish and wait for those green check boxes, as shown in Figure 8-7.
Figure 8-7. Maintenance Plan Wizard Progress
Great job!
Don’t forget to update the job, like in previous chapters. To do this, double-click the job named Reorganize Index Plan.Subplan_1 and rename it Reorganize Index, and then make the other necessary changes. Your Jobs folder should now look like Figure 8-8.
Figure 8-8. SQL Server Agent Jobs
Your Maintenance Plans should look like Figure 8-9.
Figure 8-9. Maintenance Plans
Summary
If you’ve gotten this far, congratulations on the progress that you have made! Keep reading to learn about the other cool ways that we can manipulate our database in anticipation for the ultimate goal of heightened data integrity.