Indexes , Fragmentation and splitting ,Index Statistics

Indexes,Fragmentation,Splitting, Index Statistics

SQL Server indexes are stored in B-Tree Structure as we know there are two types of indexes , for more about indexes refer the post

SQL Server B-Tree Rules

  • B-trees are always sorted.
  • The tree will be maintained during insertion, deletion, and updating so that these rules are met.
  • Root and intermediate nodes point only to other nodes
  • Only leaf nodes point to data
  • The number of nodes between the root and any leaf is the same for all leaves
  • A node always contains between K and K/2 branches, where K is the branching factor–Branching factor is the number of keys in the node
  •   When records are inserted or updated, nodes may split
  • When records are deleted, nodes may be collapsed

Fragmentation: Page split cause fragmentation,Fragmentation happens because these pages must be kept in order.

so basically there are two types of fragmentation.

  • Data pages in a clustered table
  • Index pages in all indexes

Data page fragmentation happens when a new record must be added to a page that is full.

Index page fragmentation occurs when a new key-pointer pair must be added to an index page that is full.

Query to check fragmentation on indexes.

Fragmentation on Indexes
Fragmentation on Indexes

If Avg_fragmentation_in_percentage value >30 then Rebuild  the index with Sql Statement ALTER INDEX REBUILD.

Rebuilding clustered index repairs table fragmentation

DBCC DBREINDEX (tablename [, indexname [, fillfactor]])
–Can reorganize indexes that implement primary key and unique constraints


  • Does not Lock the index
  • Do as thorough job as the other methods
  • Allow specification of a fill factor

Index Statistics:Metric used by the optimizer in determining whether or not an index is useful for a particular query.

  • The statistics allow the optimizer to determine the selectivity of an index
    • A unique, single-column index always has a selectivity of 1
    • One index entry points to exactly one row
  • Another term for this is density
    • Density is the inverse of selectivity
    • Density values range from 0 to 1
  • A selective index has a density of 0.10 or less
  • A unique, single-column index always has a density of 0.0

To see the index statistics:

  • –DBCC SHOW_STATISTICS (‘tablename‘, {‘indexname‘ | ‘statisticsname’})
  • –DBCC SHOW_STATISTICS (‘Employees’, ‘EmpName_Idx’)

Covering Indexes: When a nonclustered index includes all the data requested in a query (both the items in the SELECT list and the WHERE clause), it is called a covering index.

  • With a covering index, there is no need to access the actual data pages
    • Only the leaf nodes of the nonclustered index are accessed.

To know more about types of indexes refer this post. SQL Server indexes