Sql-server – index rebuild/reorganize frequency

There are two different types of fragmentation to worry about: physical and logical. Physical fragmentation means the file that stores your indexes is fragmented at a file system level. Logical fragmentation means that page splits have occurred while inserted or updating data in an index. Incidentally, shrinking your database files will eventually cause physical fragmentation if (when) your database files are forced to grow to accommodate more data and the space needed for the grow is not contiguous with the existing data file. Also, reorganizing and rebuilding your indexes, unless sorting is performed in tempdb, will, more than likely, cause grows on the file containing your indexes. This could further compound the physical fragmentation issue. This is why shrinking your datafiles is almost never a good idea; they grew for a reason, they will simply grow again, and when they do they will cause physical fragmentation.

Unfortunately, I cannot speak to your first question. I assume that logical index fragmentation will not cause problems at an IO level. However, physical fragmentation will manifest itself as an increased number of random reads, due to the file system fragmentation.

As for your second question, index maintenance should not cause any issues. As a matter of fact, you should already be performing index maintenance as a part of your routine maintenance. An important thing to note is that index rebuilds implicitly update your index statistics with a full scan. However, index reorganizations do not. As a part of my index maintenance, I explicitly perform a full scan statistics update against indexes I reorganize, instead of rebuild.

Finally, I doubt your issue is caused by indexes. It sounds like you are loading a lot of data into either a staging table or a temporary table, prior to running this report. If you are loading into a staging table, perhaps you could move the staging table to its own filegroup, on another storage volume. If you are loading into a temp table, you are probably running into tempdb running out of space. Either way, it appears you either need additional storage or a new filegroup on a different storage medium for your staging table.

Hope this (admittedly too long) answer helps!

Matt

As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.

Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.

External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.

If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!

When you reorganise an index, then SQL Server uses the existing index pages and just shuffles data around on those ages. This will alleviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.

When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.

Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.