Updating rows in Azure SQL Server causing unexpected page splits

Question

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.

Answer 1

After a lot of testing, I believe that Accelerated Database Recovery is the cause.

When editing a row, Accelerated Database Recovery will store the difference between the original row and the new row. This is called In Row Versioning and it is there to allow fast rollbacks and recovery.

My table example is a very narrow table and storing the diff of the Int value would basically double the size of the row. Hence why we got almost double the number of pages created (note that those pages were almost full as well).

Alex Orpwood had already found this problem and reached out to Microsoft. They replied saying that this is expected behaviour you can disable this feature with a trace flag, but they don't recommend it and I can't find that flag.

In Azure PAAS SQL Server, you cannot disable ADR so it looks like I'm stuck with this behaviour. I will just need to use a fill factor on tables that I wouldn't normally and take the hit that the databases will be larger than they were on local, as Dan Guzman mentioned:

A fill factor of 80 mitigated the page count to 527 (CREATE UNIQUE CLUSTERED INDEX PK_C_dbo_TestSplit_TestSplitId ON dbo.TestSplit(TestSplitId) WITH(DROP_EXISTING=ON,FILLFACTOR=80);).