SQL Server Maintenance – Comprehensive Checklists

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

I think you ran the query from 2 different databases (same server, but different databases) and are getting unexpected results due to the INNER JOIN condition.

My suggestion would be to just focus on one database at a time when analyzing indexes.