SQL Server B-Tree Indexes

       Root Level

       Intermediate Level

       Leaf Level

Index Column Selection

DMV for analysing Index

  sys.dm_db_index_physical_stats(null,null,null,null,null)

Clustered Index

  Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

Non-clustered Index

  Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values  (row locator) and each key value entry has a pointer to the data row. 

Index Fill Factor

Index Tuning

Fragmentation

  Internal index page having waist space.

  External   page/extents are not in most efficient order.

Reducing Fragmentation in an Index

Index Re-Build/ Re-Organize

  The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable. All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values.

  Alter Index Index_Name On sce.TableName REBUILD/REORGANIZE;

  Alter Index Index_Name On sce.TableName REBUILD (ONLINE = ON);

Reducing Fragmentation in a Heap

 To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index.