Page splits are a normal occurring process in an OLTP system.  However, too many page splits can cause performance issues that will slow down your system.  From my recent adventures in the field, I want to share some of my research and tips that can hopefully help you with your splitting headache. 🙂 Due to the volume of data on this topic and limited blog space, I will split this article into three parts.
 
Before you can take any action, one needs to identify how much page splitting is actually going on with your SQL Server database, and there are several ways to this.  Here I will offer up some available scripts and methods to find information about page splitting. 

Let’s first review what exactly a page split is.  When there is no space left on a data page for more INSERTS or UPDATES, SQL Server moves some data from the current data page and moves it to another data page.  So, if a new row is inserted onto an already full page, this is done in order to make space for the new rows. Since the data is now spanned among multiple data pages, this operation is known as the page split.

The traditional way to monitor and evaluate page splitting activity is using the Performance Monitor counter Page Splits/sec. The page-splits-per-second counter is likely to increase proportionately as workload activity on the server increases.

To locate this counter in PerfMon, navigate and select SQLServer: Access Methods: Page Splits/Sec.  According to BOL, the counter measures the number of times SQL Server had to split a page when updating or inserting data per second. Page splits are expensive, and cause your table to perform more poorly due to fragmentation. Therefore, the fewer page splits you have the better your system will perform. Ideally this counter should be less than 20% of the batch requests per second.

So, the page-split-per-second counter alone will not necessarily tell you if you have too many page splits.  The question arises how high is too many?  As suggested, you may also want to look at the Batch Requests/Sec counter as a comparative measure.

 
Here is a resource on more about Batch Requests/Sec. As you can see, it is often necessary to correlate other metrics to get a clearer picture of how your SQL Server is performing.  Another important thing that I recommend is to set up these counters or capture this info straight away in order to establish a baseline for normal operating workloads.  This way, when the workload increases and transactions are pounding away at the server, you can analyze the trends and understand what is too high, too many, and too much for your particular system.
 
If you want to capture and track Page Splits/Sec, among other metrics, you'd have to insert this into an archive table for historical trending. As with the metadata exposed by SQL Server’s system DMV's, it is not persisted and reset after instance restart (or stats cleared).

You can find the counter in the sys.dm_os_performance_counters DMV with the following query:

select
*
from sys.dm_os_performance_counters
where counter_name = 'page splits/sec'

The number you get is not going to be very telling. The per-second counter for Page Splits/ sec", for example, the cntr_value contains a cumulative number.

Please note that ALL the per-second performance counters have a cntr_type = 272696576. Therefore, in order to calculate the actual per-second rate you need to capture the per-second cntr_value twice and then calculate the per-second amount based on the two cntr_value's and the number of seconds between the two samplings.

Here is an example of how to calculate the per-second counter value for the number of "Page Splits/sec":

-- Collect first sample
DECLARE @old_cntr_value INT;
DECLARE @first_sample_date DATETIME;
SELECT @old_cntr_value = cntr_value,
@first_sample_date = getdate()
FROM sys.dm_os_performance_counters
where counter_name = 'page splits/sec'
 
-- Time frame to wait before collecting second sample
WAITFOR DELAY '00:00:10'
-- Collect second sample and calculate per-second counter
SELECT (cntr_value - @old_cntr_value) /
DATEDIFF(ss,@first_sample_date, GETDATE()) as PageSplitsPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'page splits/sec'

So how useful is this counter?  It is cumulative of all databases and objects in that server.  It does not have any breakdown.  From this counter, there is no way of identifying the database, table or index where the page split has occurred.  The other scripts will have more details.

And that is where I’m going to leave it today!  Stay bookmarked to the PearlKnows Blog for the continuing series on Page Splits.  OK, got to split now 😛

Please follow me on Twitter @Pearlknows, and check out our web-site for all our available services at http://www.pearlknows.com.

We have a special offer going on right now!  Are you SQL Servers healthy?  How do you know for sure? Please contact us about our 15-point Health Check report, which will identify areas for improvement, and allow for best practice recommendations for your SQL Server(s)