Database maintenance and when is it needed (Part 3: “Reorganize Index” and “Rebuild Index”)
This week we will discuss about the “Reorganize Index” and “Rebuild Index” maintenance Tasks.
Why is this kind of maintenance needed? Because the T-SQL queries need the database indexes to be in “good shape” to run optimally. Unfortunately the indexes are getting fragmented every time the data inside the tables are getting modified. Data are getting modified when you run INSERT, UPDATE and DELETE T-SQL statements against the tables of the database.
By reorganizing the indexes, you actually defragment the indexes. This is an online operation – the indexes are used by the database engine while the reorganize operation is running. The operation itself defragments the leaf pages in the clustered or non-clustered index pages.This operation does not use a lot of working space.
By rebuilding the indexes you basically destroy the indexes and rebuild them from scratch. In some SQL Server editions this operation is online, while in other editions it is offline. We already explained what online means. Offline rebuild index mode means that the indexes are not available to the database engine while the index is getting rebuilt and until the rebuild finishes.
How do I check the fragmentation level of my indexes? Well, I use two methods:
1) The Standard Reports from SQL Server Management Studio:
2) A T-SQL query like this one:
Some common mistake people make is to run both operations. They first rebuild the indexes and then reorganize them or vice versa. This is totally useless as you run two similar operations to achieve a similar result. You will want to use one or the other for each index - not both.
When should I choose to rebuild and when to reorganize an index? As a rule of thumb, you will want to rebuild indexes that are heavily fragmented (30% or more) and to reorganize indexes that are lightly fragmented. If your maintenance window is fairly big, you can simply rebuild the indexes and forget about reorganizing them. If you try to save on maintenance time and IO resources, you can follow the rule of thumb I mentioned above. The maintenance script provided by Ola Hallengren (https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html) does this job for you – you just set the FragmentationLevel1 and FragmentationLevel2 values accordingly. Note: Indexes with 5% or less fragmentation are not worth reorganizing or rebuilding. You can alternatively use the scripts developed by Microsoft's SQL Server PFE team, which can be found here: https://blogs.msdn.com/b/blogdoezequiel/archive/2011/07/03/adaptive-index-defrag.aspx#.UkJ9EpXRo5s and here: https://blogs.msdn.com/b/blogdoezequiel/archive/2012/09/18/about-maintenance-plans-grooming-sql-server.aspx#.UkJ9pJXRo5t.. Both the above options are excellent in my view.
How often do I need to do Index maintenance? This depends on how often your data changes as we mentioned in te start of this post. You should first start checking fragmentation of your indexes on a daily basis and see how fast they get fragmented. If you see that it takes the indexes one week to get fragmented (e.g. 30% or more) then you could schedule this index maintenance on a weekly basis. If the indexes manage to get fragmented within one day, then you should try to perform the index maintenance on a daily basis.
Next week I will discuss about the “Update Statistics” maintenance task!