I've read that the value for the Page Splits/sec counter in the Windows Performance Monitor should be low, and if it isn't, you might need to increase the fill factor. But what's considered a low value?
The Page Splits/sec counter is one of the "piece of string" counters for which no absolute value can be deemed correct. The counter varies according to table size and density, index depth, workload, and workload type. Page splits occur in clustered indexed tables when you add data to a page that doesn't have enough space to accommodate the new data. Instead of taking a short latch on the page to add the data, SQL Server needs to hold the latch for a longer period to allocate a new page, copy data from the old page to the new page, then write the new data to the new page. In addition, SQL Server might take latches on the intermediate index pages.
Although latches are less expensive than locks (because SQL Server doesn't hold them for the duration of the transaction), latches can negatively affect concurrency. You can use the (undocumented) Database Consistency Checker (DBCC) command DBCC SQLPERF(WAITSTATS) to obtain latch-wait statistics. Note that this command returns cumulative data, so run the command twice and use the difference to obtain point-in-time data.
Let's take a look at the factors that influence this counter.
Workload. As the workload increases, the page-splits-per-second rate is likely to increase proportionately. Therefore, when you compare rates, take the Batch Req/sec counter into consideration as well.
Workload type. If your workload consists mainly of selects (which benefit from having as many records read per page I/O as possible), a high density of records per page is the best type of workload for minimizing I/O and maximizing the value of the buffer pool. When you choose a high record-per-page density, you might see a higher number of page splits per second when you're doing inserts. But overall, you can improve performance by employing a high fill factor on the clustered indexes. Note that the default fill-factor setting (0, which provides maximum data-page density) is well suited to a workload that's mainly read.
You might want to reduce the record-per-page density by using the fill-factor setting when your workload consists of:
- updates that increase record length (e.g., changing NULL values to specific values, increasing the size of varchar and nvarchar strings, adding text to text in page fields)
- inserts of new records.
Index depth. Typically, index depth doesn't affect the rate of page splits per second because clustered index pages constitute less than 1 percent of a table's total number of pages. However, the size ratio for clustered and secondary indexes is sensitive to the size of the clustering key. You can use the PAD_INDEX option to apply the same fill factor as for data pages. Note that a split that SQL Server takes close to the root index page can have a greater influence on concurrency than a split closer to a leaf (data) page because the latch affects a larger number of leaf pages.
Table size. As the table size increases, the page-splits-per-second ratio is likely to increase proportionately, so you should also consider table size when you compare rates between databases. A higher rate against a larger table might have less concurrency impact (assuming that the splits are evenly distributed) than the same or lesser rate against a smaller table.
Table density. The fill-factor setting controls the table density. As the fill factor rises, more page splits likely will occur when you're updating and inserting records—but table and index scans and page read-ahead will also perform better. When the fill-factor default is set to 0, SQL Server packs the intermediate index pages tightly but leaves room for 1 row, and the data pages are 100 percent full.
When you're loading data, different rules for page splitting can apply. Instead of splitting a page when it fills, SQL Server creates a new page rather than moving data from the old page. For more information about the page-split process and fill-character settings, see the following Inside SQL Server columns by Kalen Delaney: "The Fill-Factor Truth," April 2001, InstantDoc ID 19851, "More Fill-Factor Mysteries," May 2001, InstantDoc ID 20242, and "Do the Splits," June 2001, InstantDoc ID 20589.
