Sql-server – Database Size Increase After Index Rebuild.How to get back down

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are:

1. It can improve read-ahead performance for large range scans; and
2. It may improve the page density (number of rows per page)

Both of these factors are less important for small indexes, by definition.

The counter-argument to rebuilding small indexes is essentially:

"Why bother? Don't you have more important things to worry about?".

That said, rebuilding/reorganizing is not free. It may be worth avoiding the extra effort and log generation in some cases (for example, if the log is shipped/copied across a WAN for any number of possible reasons - mirroring, availability groups, replication...). Also, unless (or even if, in some cases) you are rebuilding online, the rebuild may impact other concurrent processes through locking. Finally, for small indexes, rebuilding may not even reduce the fragmentation anyway, due to allocations from mixed extents (unless you are running with trace flag 1118 enabled).

If you still feel happier rebuilding these small indexes, and don't mind the consequences, then by all means change the value of the @PageCountLevel parameter passed to Ola's procedure.

See the PASS TV recording of Paul Randal's presentation on Index Fragmentation for all the details.

You might also like to watch Brent Ozar talk about Why Index Fragmentation doesn't matter in SQL Server.

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb.

It gives you better advantage when tempdb is on a different set of disks (LUNs) from the user database.

From SORT_IN_TEMPDB Option - BOL :

If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

Make sure you read the disk space requirements when SORT_IN_TEMPDB is ON.

slow/possibly misconfigured SAN

You know the pain point. Why dont you work with your SAN admin to fix it ? Misconfigured and or slow SAN will cause all sort of problems like slowness.

Some important points to note :

• Use MAXDOP=1 when doing an online index rebuild - else it will cause increase fragmentation.
• Intelligently reorg/rebuild your indexs based on the fragmentation level as well as page fragments.
• Read SQLFool's experience with Creating a 60 GB Index
• Make sure you have configured tempdb properly - TF 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY) and have more tempdb datafiles.

What would be the best way to test this?

Yes, you have to test it by analyzing the waitstats when you rebuild the index with and without SORT_IN_TEMPDB. Measure run time as well and when doing in PROD, make sure you do it during a maintenance window or less server activity. Also check your read/write data and log latency.

I am not sure you have Instant file initialization, but it will benefit when restoring, during autogrowth of data files and when creating a new database (just mentioning for completeness).