Today we are going to unpack the next feature of the SQL Server Database Maintenance wizard which targets indexes. This is a topic I cover a lot, because keeping your database table indexes in tune is a key factor for database performance. This probably one of the most important capabilities in the Database Maintenance Task Wizard tool we are going to review.
First lets do a quick overview of indexes on database tables or materialized (indexed) views. There are two kinds of indexes. The Custered index may be created against any table or view. It physically contains not only the key values of the index, but all of the other columnar data for each row. Since the table data can be located in only one place on the disk, there may only be a single clustered index on any table or view.
Each table or view may have additional indexes, known as non-clustered indexes. These indexes have unique keys just like Clustered Indexes. However, they don’t contain the data from the non-indexed columns Instead they contain a pointer to the record stored in the clustered index.
Over time, as you Add, Update or Delete records in your database, the data for a table begins to be stored all over the disk, instead of contiguously to the other data in the table. Our maintenance wizard has the ability to pull the data back together on the disk with records stored side by side. The Reorganize method will defragment all but the highest level of the index. The Reindex method rebuilds the entire index.
Reorganize is used when you have to gain some performance while your database is under heavy load. Since Rebuild can actually move the entire table contents it is most often reserved for off load traffic times. If you have the enterprise version of SQL Server, you can even rebuild indexes concurrent with heavy load because it does the re-indexing in a temporary work area, and then applies the new index change in a single final transaction.
If you’re not sure which one to use, try the reorganize if you executing it during business hours. You will most likely want to reserve re-index for execution during off peak data times.
Cheers,
Ben