“Page Splits” reports the number page splits for data pages, clustered index pages, or nonclustered index pages because there was not enough room for a new row.
When a data row is inserted into an allpages-locked table with a clustered index, the row must be placed in physical order according to the key value. Index rows must also be placed in physical order on the pages. If there is not enough room on a page for a new row, Adaptive Server splits the page, allocates a new page, and moves some rows to the new page. Page splitting incurs overhead because it involves updating the parent index page and the page pointers on the adjoining pages and adds lock contention. For clustered indexes, page splitting also requires updating all nonclustered indexes that point to the rows on the new page.
See “Choosing space management properties for indexes” on page 318 in Performance and Tuning: Basics for more information about how to temporarily reduce page splits using fillfactor.
