Updating rows in Azure SQL Server causing unexpected page splits

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 u

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);).

StackExchange Database Administrators Q#301480, answer score: 5