Clustered vs Non-Clustered

• A clustered index reorders the data rows in the table based on key value. This means there can only be one clustered index per table.

• A non-clustered index holds pointers to each data row ordered by the key value (i.e. the index is ordered by key value, the table is not)

Is Index Fragmentation Important?

TL;DR Possibly not.

• Because a fragmented index usually has more pages then an index scan will likely need to do more I/O. But, you could change the fill factor.. and have a less defragmented index that still has more pages (i.e. fragmentation isn't the only thing that can cause extra I/O).

• More pages can also cause the optimiser to choose a different plan (it might choose to parallelize, which might make things go faster.. but that might impact CPU usage.. or it might choose something else).

• The benefits of index defragmentation are more relevant on dedicated storage. On shared storage the pages are probably scattered across disks anyway (but, still, the less you have to read, the better).

• Best practice is to defragment. It may not have much positive effect on performance but it's sometimes easier to do it than defend not doing it... Although defragmenting can have its own detrimental effects, especially if the system is 24x7 without a maintenance window and without the option of online rebuilds.

• If an index fits entirely in cache and will remain there for the duration of the service operation "the difference in performance between the index being 99% fragmented vs. 0% fragmented (assuming the same or very similar page densities) would be minimal. The only times you may see a performance impact would be during the initial cache warm-up (i.e. the first and only time the index data was pulled from disk into cache) and potentially during some checkpoint operations."  (1) . Note that increased memory levels may need a different edition of Windows.

• Singleton seeks into an index are not impacted by fragmentation (even seeks that require IO operations). Scans (full and range) are impacted significantly. (2)

• Advanced storage systems available today (high-end SANs, large scale DAS systems) can minimize the effects of fragmentation on performance - many have large cache's where many reads are fulfilled from, generally read requests to a single LUN are spread across 10's to 100's of spindles, and the fabric/hardware that carry the data are very, very performant. (3)

Is My Index cached?

Rebuild vs Reorganize

• For Standard Edition, Index Rebuilds are always offline and single-threaded. If your database usage causes a lot of index fragmentation, and addressing that fragmentation is important to you, then you should seriously consider Enterprise Edition.

• For Enterprise Edition you can specify an online rebuild (with only a few exceptions) and you can use parallelism to make the rebuild faster. A schema modification lock (SCH-M) is taken out during the final swap to the new index. This is an exclusive lock which can cause blocking in highly concurrent environments.

• If you kill an index rebuild, a rollback will occur and running the same rebuild later will start from scratch.

• Reorganizing an index compacts and reorders at a leaf level. It is always online and is resumable (if you kill it, when you run it again it will pick up where it left off).

• A defrag/reorg will not address extent fragmentation at all, only logical fragmentation - however this is traditionally the most intrusive type of fragmentation, so many times a defrag/reorg is the best option. (4)

Online vs Offline Index Builds/Rebuilds

OFFLINE

• No read or write operation is permitted on the table while the index is being built/rebuilt. The table is locked at the start of the build and is not unlocked until the index operation is complete.

• OFFLINE builds/rebuilds are faster than ONLINE rebuilds.

ONLINE

• The new index is built while the old index (if it exists) is accessible to reads and writes. Any update on the old index will also get applied to the new index. 

• When the index build is complete, the table is locked for a brief period and the new index replaces the old index. 

• Indexes containing LOB columns are not supported for ONLINE rebuilds in SQL Server 2005/2008/2008R2, but are supported in SQL2012 and later.

Index Creation Progress

Bibliography & References