Read this article in your language IT | EN | DE | ES

Image via Wikipedia

What a SQL re-indexing process should be doing:

1. Re-indexing indexes or tables over 30% fragmentation (BOL). Some estimates are 15-20% or more.

2. Reorganizing indexes or tables less than 30% fragmented (BOL). Some estimates are 2%-10%.

3. It appears the smallest indexes should be re-indexed first. (my performance testing on a 10 GB table only). This may not be a correct assumption and more testing would need to be done to determine if this were a good rule of thumb.

4. Statistics should be updated ONLY when a re-org was done on, not a re-index. Re-indexing will resample the entire table making the index statistics more accurate than doing it again in a separate process. Asking for an update of statistics only uses a 25% sample of the table. On a table that changes frequently this may cause it to be inaccurate until the system automatically re-indexes at the default growth of 20%. Not sure of the accuracy in that last statement, the default trigger for a re-index within SQL may be different. I’d have to sift through BOL to find it.

5. Use a fill factor appropriate to the tables use. This requires knowing what the tables do. If there are no inserts or deletes, then use 100% fill factor. What you should do is figure out the percentage of added records from the last check and determine the fill factor % from that. This requires actively monitoring the database but has potential to be automated.

6. Recompile stored procedures after re-indexing. A newly rebuilt index will cause SQL to recompile the stored procedure when it first gets called so you might as well do this during maintenance to prevent the recompilation from slowing down your application.

7. Defragment the physical disk. Hmm, I’m not sure this can be done in SQL alone. This might require a separate process to stop SQL server, defrag the OS files, restart SQL Server and run the re-index script.

Disk performance and fragmentation on SQL

The OS saves files in open spaces on the disk drive that it can access quickly. It saves this data on the fly so the OS remains responsive but over time the placement of data that makes up a file in the OS will be fragmented. Where it chooses to store those chunks of data may not allow the fastest access within that files application. Defragmenting reviews the patterns of these chunks and attempts to make their placement contiguous on the physical disk. Often times larger file cannot be 100% contiguous but can be "optimized" when defragmented. The process of defragmenting increases disk performance by minimizing the movement of the drive head. The less it has to travel on disk the less time it takes to access files.

You should first defragment the file system through the OS then re-index/re-org SQL to optimize data placement. The first helps to reduce disk access by creating a contiguous file for the application/OS to use. Once that is done, SQL should be optimized to reduce the access time within the file systems file. (MDF and LDF, as an example).

Defragmenting with ANY utility (Microsoft's Defragment or Disk Keeper) will not defragment the SQL files if they are in use. On an active system it is more likely that a disk defragmentation process cannot gain access to the SQL files so the SQL service (along with your application) should be stopped to allow the file chunks to be moved. This can be done incrementally provided the defragmentation process can move chunks faster than the database can grow.