Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
What is Page Splits in SQL Server?
As it is known, there are 8KB areas called Page in the file base of SQL Server and 8 of them (8 X 8K = 64K) constitute an extent.
Data and Indexes are stored on pages and Read / Write operations take place on extent basis. How many rows will be on each page may vary depending on the length of the row. If a long row is inserted or the current row is updated with too long data, SQL Server can replace existing rows to make changes. If the change does not fit on the page, SQL Server creates a new data page and moves the rows on this new page. This process is called page split.
Page Split operation can sometimes cause performance problems in live systems.
Monitor Page Split on Perfmon
So how do we monitor Page Split on Perfmon? Here, we can capture Page Splits by using Perfmon tool in such performance problems.
Let’s create our sample table.
1 2 3 4 5 6 7 8 9 10 | USE PSplit_Demo GO IF object_id('PageSplit_Table') IS NOT NULL BEGIN DROP TABLE PageSplit_Table END GO CREATE TABLE PageSplit_Table (col_1 INT,col_2 char(3950)) GO CREATE UNIQUE CLUSTERED INDEX CIX_PageSplit_Table_col_1 ON PageSplit_Table(col_1) |
Now let’s open Perfmon. Then we press the (plus) sign and add Page Splits / sec in SQL Server: Access Methods.
Then we run our INSERT block below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | USE PSplit_Demo GO SET NOCOUNT ON GO DECLARE @i INT SET @i=0 WHILE (@i<5000) BEGIN INSERT INTO PageSplit_Table VALUES (@i,'___') INSERT INTO PageSplit_Table VALUES (10000-@i,'___') SET @i=@i+1 END GO SET NOCOUNT OFF GO |
Let’s turn on the monitor while the insert continues.
This is how we capture the jump of Page Split / sec on Perfmon.
Now let’s observe Index Fragmentation on Index.
What is Index Fragmentation in SQL Server?
We created our index. And for a while, we performed update, delete and insert operations on the table continuously.
Suppose we perform a deletion. We have deleted some data from one page of the index. Then we wanted to insert another record. And since there is not enough space on the page where we deleted the record, sql server wrote this record to a new blank page.
In this way, indexes start to be stored scattered on the disk over time and fragmentation increases. As the fragmentation increases, the performance of the index decreases. You can set a fill factor on the index to prevent the indexes from being fragmented quickly on the tables containing intense Update, Insert and Delete.
Index Fragementation Script in SQL Server
1 2 3 4 5 6 7 8 9 10 11 12 13 | USE PSplit_Demo GO SELECT s.[name] +'.'+t.[name] AS table_name ,i.NAME AS index_name ,index_type_desc ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent ,record_count AS table_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.tables t on t.[object_id] = ips.[object_id] INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id] INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) ORDER BY avg_fragmentation_in_percent DESC |
As it seems, the index named CIX_PageSplit_Table_col_1 is fragmented at a rate of 99.98%. This means that our index is now scattered. In other words, Index has reduced its own performance too much.
Let’s observe the difference between statistics used by our queries processed with fragmented index and, healthy indexes.
1 2 3 4 5 6 7 8 9 10 | USE PSplit_Demo GO SET STATISTICS IO ON GO SELECT * FROM PageSplit_Table SET STATISTICS IO OFF GO |
Here we get the statistics results;
1 2 | (10000 rows affected) Table 'PageSplit_Table'. Scan count 1, logical reads 10041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Now let’s prepare a demo from the same table but with the Index not fragmented.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | USE PSplit_Demo GO IF object_id('No_PageSplit_Table') IS NOT NULL BEGIN DROP TABLE No_PageSplit_Table END GO CREATE TABLE No_PageSplit_Table (col_1 INT,col_2 char(3950)) GO CREATE UNIQUE CLUSTERED INDEX CIX_PageSplit_Table_col_1 ON No_PageSplit_Table(col_1) GO USE PSplit_Demo GO SET NOCOUNT ON GO DECLARE @i INT SET @i=0 WHILE (@i<10000) BEGIN INSERT INTO No_PageSplit_Table VALUES (@i,'___') SET @i=@i+1 END GO SET NOCOUNT OFF GO |
We created our second demo table in the same way and inserted the same data. Now let’s check the Fragmentation rates of the Indexes in these two tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 | USE PSplit_Demo GO SELECT s.[name] +'.'+t.[name] AS table_name ,i.NAME AS index_name ,index_type_desc ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent ,record_count AS table_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.tables t on t.[object_id] = ips.[object_id] INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id] INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) ORDER BY avg_fragmentation_in_percent DESC |
As you can see, our first index is fragmented at 99.98%, while our second index is fragmented at 0.44%. Now let’s run the same SELECT operation on both tables and observe the difference in performance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | USE PSplit_Demo GO SET STATISTICS IO ON GO PRINT 'Page Split' SELECT * FROM PageSplit_Table WHERE col_2='a' PRINT 'No Page Split' SELECT * FROM No_PageSplit_Table WHERE col_2='a' SET STATISTICS IO OFF GO |
Statistics:
1 2 3 4 5 6 7 8 | Page Split (0 rows affected) Table 'PageSplit_Table'. Scan count 1, logical reads 10041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. No Page Split (0 rows affected) Table 'No_PageSplit_Table'. Scan count 1, logical reads 5024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
As you can see, Index exposed to Page Split does 10041 logical Read and index not exposed to Page Split does 5024 logical read.
So, unfragmented index seems almost 2 times faster.
Therefore, in your systems, be sure to check the Fragmentation values of your Indexes and monitor the Page Splits.
Resolve SQL Server Index Fragmentation
In such cases, we must perform Reorganize or Rebuild operations according to the Fragmentation values of Indexes. Since the Fragmentation of our index exposed to Page Split is very high, we will apply REBUILD process.
1 | ALTER INDEX CIX_PageSplit_Table_col_1 ON PageSplit_Table REBUILD |
After REBUILD operation, let’s look at the Fragmentation value of our Index again.
1 2 3 4 5 6 7 8 9 10 11 12 13 | USE PSplit_Demo GO SELECT s.[name] +'.'+t.[name] AS table_name ,i.NAME AS index_name ,index_type_desc ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent ,record_count AS table_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.tables t on t.[object_id] = ips.[object_id] INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id] INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) ORDER BY avg_fragmentation_in_percent DESC |
As you can see, our index is fixed. In such cases, we need to apply Index maintenance periodically.
Now, let’s run our SELECT script on both tables again and check the statistics.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | USE PSplit_Demo GO SET STATISTICS IO ON GO PRINT 'Page Split' SELECT * FROM PageSplit_Table WHERE col_2='a' PRINT 'No Page Split' SELECT * FROM No_PageSplit_Table WHERE col_2='a' SET STATISTICS IO O |
Statistics:
1 2 3 4 5 6 7 8 | Page Split (0 rows affected) Table 'PageSplit_Table'. Scan count 1, logical reads 5020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. No Page Split (0 rows affected) Table 'No_PageSplit_Table'. Scan count 1, logical reads 5024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
As you can see, the values are the same. In fact, the performance of our scattered Index even better than the other after rebuild operation.
You may want to read “Index Concept and Performance Effect On SQL Server” and “Statistic Concept and Performance Effect On SQL Server“.
Good luck with.
