At what point should I worry about page splits, and what can I do to help reduce them?

For those not familiar with page splits, let's first take a look at what they are. Whenever you INSERT new data into a SQL Server table, or in some cases, UPDATE data in a SQL Server table, there may not enough room for the newly INSERTed or UPDATEd row in the applicable data page. In this case, SQL Server will have to move some of the data from the current data page and move it to a new data page. So not only does SQL Server experience the normal I/O of INSERTing or UPDATing a row, it must also move data and update any applicable indexes. This can result in a lot of excess I/O.

A certain amount of page splits is normal and expected, but when there are too many page splits, then your SQL Server's performance is compromised and you should take steps to reduce them.

So this comes back to our original question, how many page splits is too many? Like many questions in SQL Server performance tuning, it depends. The number of page splits will very depending on the following factors:

• Number of users
• Level of user activity
• Is the database mostly read or mostly write, or some other combination?
• The fillfactor used in table indexes
• How often are the indexes rebuilt?
• Is the primary key a clustered index?
• The performance of your I/O subsystem

One of the easiest ways to measure page split activity is to use Performance Monitor and watch the SQLServer:Access Methods:Page Splits/sec counter.

On one SQL Server system I am familiar with that has about 75-100 active users, the page splits per second runs less than 1, with peaks up to 10 page splits per second.

On another SQL Server system I am familiar with, that has up to 500 active users, the page splits per second runs about 100, with peaks of up to 800 page splits per second.

Obviously, the first example above is not having a page splitting problem. Page splits are minimal. In the second system, page splitting is probably excessive, and is most likely contributing to less than optimal performance. If there are 100 page splits per second, the disk subsystem has to do a lot of extra work to move the records to new pages and update indexes.

Ideally, you want the page splits per second to be as small as possible. Your first step is to monitor the SQLServer:Access Methods:Page Splits/sec counter over a typical busy day and see what your results are. At the same time, monitor total disk I/O activity to see if you are experiencing a bottleneck. If you find that there is a very strong correlation between page splits and excessive disk I/O activity, then most likely you have a page splitting problem. The disk I/O activity that I like to watch are these two counters: PhysicalDisk: % Disk Time and PhysicalDisk: Average Disk Queue Length.

If you find out that your system is experiencing too many page splits, here are some things you should consider to remedy the problem:

• Increase the fillfactor on your indexes. By decreasing the fillfactor in your indexes, what you are doing is increasing the amount of empty space on each data page. The more empty space there is, the fewer page splits you will experience. On the other hand, having to much unnecessary empty space can also hurt performance because it means that less data is stored per page, which means it takes more disk I/O to read tables, and less data can be stored in the buffer cache. You may have to experiment to find the ideal fillfactor.
  
• Rebuild your indexes more often. Over time, any empty space created with the fillfactor will be used up, and more and more page splits will occur. Each time you rebuild an index, the fillfactor's empty space is renewed, meaning that there is more room for data and fewer page splits will occur.
  
• Add clustered indexes to your monotonically increasing primary keys. By doing this, you eliminate INSERT-caused page splitting entirely because all new records will be added to the end of the table. But only do this if you know that a clustered index on the primary key is the best option for you when it comes to query performance.
  
• Get a faster I/O subsystem. Obviously, the faster your hardware, the less of an issue are page splits.