If a SQL Server database is experiencing a large amount of insert activity, you should plan to provide and maintain open space on index and data pages to prevent page splitting. Page splitting occurs when an index page or data page can no longer hold any new rows and a row must be inserted into the page because of the logical ordering of data defined in that page. When this occurs, SQL Server must divide the data on the full page and move about half of the data to a new page so that both pages have some open space. This consumes system resources and time.
When indexes are built initially, SQL Server places the index B-tree structures on contiguous physical pages, which supports optimal I/O performance by scanning the index pages with sequential I/O. When page splitting occurs and new pages must be inserted into the logical B-tree structure of the index, SQL Server must allocate new 8-KB index pages somewhere. This occurs elsewhere on the hard disk drive and breaks up the physically sequential index pages, which switches I/O operations from sequential to nonsequential and cuts performance in half. Excessive page splitting should be resolved by rebuilding the index to restore the physically sequential order of the index pages. This same behavior can be encountered on the leaf level of the clustered index, which affects the data pages of the table.
Use Performance Monitor to watch the SQL Server: Access Methods - Page Splits counter. Nonzero values for this counter indicate page splitting. Further analysis should be done with the DBCC SHOWCONTIG statement. For more information about how to use this statement, see SQL Server Books Online.
The DBCC SHOWCONTIG statement can reveal excessive page splitting on a table. Scan Density, a key indicator that DBCC SHOWCONTIG provides, should be a value as close to 100 percent as possible. If this value is below 100 percent, rebuild the clustered index on that table by using the DROP_EXISTING option to defragment the table. The DROP_EXISTING option of the CREATE INDEX statement permits re-creating existing indexes and provides better index rebuild performance than dropping and re-creating the index. For more information, see SQL Server Books Online.
The FILLFACTOR option on the CREATE INDEX and DBCC DBREINDEX statements provides a way to specify the percentage of open space to leave on index and data pages. The PAD_INDEX option for CREATE INDEX applies what has been specified for FILLFACTOR on the nonleaf-level index pages. Without the PAD_INDEX option, FILLFACTOR mainly affects the leaf-level index pages of the clustered index. You should use the PAD_INDEX option with FILLFACTOR. For more information, see SQL Server Books Online.
The optimal value to specify for FILLFACTOR depends on how much new data is inserted into an 8-KB index and data page within a given time frame. Keep in mind that SQL Server index pages typically contain many more rows than data pages because index pages contain only the data for columns associated with that index, whereas data pages hold the data for the entire row. Also consider how often there will be a maintenance window that permits the rebuilding of indexes to avoid page splitting. Strive to rebuild the indexes only as the majority of the index and data pages have become filled with data by properly selecting clustered index for a table. If the clustered index distributes data evenly so that new row inserts into the table occur across all of the data pages associated with the table, then the data pages will fill evenly. This provides time before page splitting occurs and forces you to rebuild the clustered index. FILLFACTOR should be selected based partly on the estimated number of rows that will be inserted within the key range of an 8-KB page in a certain time frame, and partly by how often scheduled index rebuilds can occur on the system.
You must make a decision based on the performance trade-offs between leaving a lot of open space on pages and page splitting. A small specified percentage for FILLFACTOR leaves large open spaces on the index and data pages, which helps avoid page splitting but also negates some performance gained by compressing data onto a page. SQL Server performs faster if more data is compressed on index and data pages because it can fetch more data with fewer pages and I/Os. Specifying too high a FILLFACTOR may leave too little open space on pages and can allow pages to overflow too quickly, which causes page splitting.
Before using FILLFACTOR and PAD_INDEX, remember that reads tend to outnumber writes, even in an online transaction processing (OLTP) system. Using FILLFACTOR slows down all reads, because it spreads tables over a wider area (reduction of data compression). Before using FILLFACTOR and PAD_INDEX, you should use Performance Monitor to compare SQL Server reads to SQL Server writes and use these options only if writes are more than 30 percent of reads.
If writes are a substantial percentage of reads, the best approach in a busy OLTP system is to specify as high a FILLFACTOR as will leave a minimal amount of free space per 8-KB page and still prevent page splitting and allow SQL Server to reach the next available time window for rebuilding the index. This method balances tuning I/O performance (keeping the pages as full as possible) and avoiding page splits (not letting pages overflow). You can experiment by rebuilding the index with varying FILLFACTOR values and then simulating load activity on the table to validate an optimal value for FILLFACTOR. After the optimal FILLFACTOR value has been determined, automate the scheduled rebuilding of the index as a SQL Server task. For more information, see SQL Server Books Online.