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