:::: MENU ::::

page split for indexes in SQL Server

  • Nov 05 / 2008
  • 0
dbDigger, Indexes, Performance Tunning and Optimization

page split for indexes in SQL Server

Index is sorted based on the index keys. In order to keep the indexes sorted, some rearranging of data constantly occurs. Under normal conditions adding a new entry to the index involves simply adding rows to the leaf pages of the indexes. When there is no more space available in these pages, a new page is created and approximately half of the rows from the existing page are moved into this new index page. This is known as a page split. Page split is quite an expensive operation due to high IOs and page allocations required. If you know that your index will have constant updates or insertions, you can reduce the number of page splits that occur by leaving some extra space in the leaf pages of the index. This is done via the index fill factor.

Consult us to explore the Databases. Contact us