Sample Header Ad - 728x90

Updating rows in Azure SQL Server causing unexpected page splits

7 votes
1 answer
297 views
I'm getting a lot of page splits in a live environment using Azure SQL Server PAAS that I don't understand. The update that's occurring should not increase the size of the row and therefore never cause a page split. Additionally, the behaviour only occurs in Azure and does not occur on a local SQL instance. I am using an Azure elastic pool using eDTU pricing and Standard Tier (200 eDTU). I have created the below example to demonstrate:
create table dbo.TestSplit
(
	TestSplitId int not null identity,
	MyInt int not null,
	
	constraint PK_C_dbo_TestSplit_TestSplitId primary key clustered (TestSplitId)
);
Insert 100,000 rows with the MyInt = 5:
insert into dbo.TestSplit
(MyInt)
select top(100000) 5
from sys.columns AS a 
cross join sys.columns AS b 
cross join sys.columns AS c
Running the below SQL shows that 384 pages have been created and there are 2 fragments.
select
    ix.name as index_name,
    st.index_type_desc,
    st.fragment_count,
    st.page_count
from sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) st 
join sys.indexes ix on ix.object_id = st.object_id 
    and ix.index_id = st.index_id
where object_name(ix.object_id) = 'TestSplit'
and object_schema_name(ix.object_id) = 'dbo';
| index_name | index_type_desc | fragment_count | page_count | | ------------------------------ | --------------- | -------------- | ---------- | | PK_C_dbo_TestSplit_TestSplitId | CLUSTERED INDEX | 2 | 384 | Now update every second row, changing the MyInt value from 5 to 6.
update dbo.TestSplit
set MyInt = 6
where TestSplitId %2 = 1
MyInt is a fixed width column so I would expect the edit to simply update the page without causing any page splits. However, the number of pages approximately doubles and the fragments also follow the page count. | index_name | index_type_desc | fragment_count | page_count | | ------------------------------ | --------------- | -------------- | ---------- | | PK_C_dbo_TestSplit_TestSplitId | CLUSTERED INDEX | 767 | 767 | With advice on pages such as this Wayne Sheffield - A Page Split in SQL Server I can see page splits are definitely the cause of the additional pages. I don't understand why this is happening or how to stop it. I have tried all five transaction isolation levels locally and there are still no page splits. My local SQL server is 2017 (v14.0.2037.2) and Accelerated Database Recovery was introduced in 2019 I think. I can try upgrading my local server but I still of course want to stop the splits occurring in the live Azure environment. Dan Guzman reported the same splits as Azure SQL Database with on-prem SQL Server 2019 and ADR enabled.
Asked by Andrew Marshall (121 rep)
Oct 21, 2021, 08:14 PM
Last activity: Oct 26, 2021, 03:33 PM