Are you noticing a drop in performance? Are you seeing a large percentage of index fragmentation? If your data page fill factor value set to a high number, page splits could be the culprit. We have helped many of our VDBA customers with these problems.
What are Page Splits?
Page splits occur when there is not enough free space on a data page to insert or update data. SQL Server takes the excess data and puts it on another data page. Imagine you have three 8oz jars filled to the rim with layers of alternating M&Ms colors. You need to add Green M&Ms to the layer in the middle of one of the jars. The jars cannot hold any more M&Ms so you have to get another jar. You take out half the M&Ms from the jar with the green layer and place them in the new jar. Then, you add the green M&Ms. Now you have four 8oz jars but only two are full. That costs time and resources and results in index fragmentation. This can decrease performance.
What do I need to look for to identify if page splits are the problem?
The Performance Monitor counter, “SQLServer:Access MethodsPage Splits/sec”, shows the number of page splits per second that occurs. To determine if this amount is a problem, there are several factors that need to be taken into consideration. This includes workload, workload type, table size, and the fill factor value. A large amount of batch requests consisting of mostly insert and update statements will cause a high number of page splits per second. This is due to the amount of changes that are being made to data pages when a batch is executed. Large tables will also cause a boost in the amount of page splits per second. This is because there are more data pages that need to be changed. Indexes with high fill factor values do not leave enough free space for changes so this will also increase the amount of splits.
How can I fix it?
One of the best places to start when fixing page splits per second is to set a fill factor value when the index is rebuilt. By default, SQL Server sets the fill factor to 100. This uses 100% of data page space. In the M&M example, filling up 100% of jar space reduced the amount of jars but left no space for changes. This meant there was no room to add more M&Ms later on. When the INSERT or Update statement is used, there is no room left on the data page to make those changes. When there is not enough room, new data pages are created and half of the data is added to the new data page. The best value for the fill factor varies based on the need of the client and their system. If the fill factor is set too high, page splits can occur. If the fill factor is set too low, the data is spread out too much and creates more data pages than needed. This can also affect the performance. If you have questions about finding the right fill factor value for your indexes or need assistance with SQL Server in general, reach out to us! XTIVIA and I can assist you with adding resiliency for your business.