When I look over the documentation for Accelerated Database Recovery and Read Committed Snapshot Isolation, it appears that every downside of Accelerated Database Recovery is shared by Read Committed Snapshot Isolation. So assuming that I already have Read Committed Snapshot Isolation enabled, what are the downsides of Accelerated Database Recovery? I suspect that there is no coincidence that both Accelerated Database Recovery and Read Committed Snapshot Isolation are enabled by default in Azure. Assume either SQL Server 2019 or SQL Server 2022.

Accelerated Database Recovery (ADR) is on by default in Azure and an option in SQL Server 2019. It has some really interesting potential. I have experimented by turning ADR on in a static test database, and if nothing happens there is no growth. It seems like growth is only going to occur on an active database. So unless you have a test system that allows you to full reproduce your production workload, you are going to be doing your growth experiments in production. Add to this, turning ADR on or off essentially requires a down time as there is an "exclusive lock" requirement to change the state, and you have a recipe for a career changing event. If you have a 10GB, database with 50GB available, probably not a big concern. But if you have a 3.5TB database and only 4TB of space available, this could be a big problem. From the Manage accelerated database recovery documentation: > PVS is considered large if it's significantly larger than baseline or if it is close to 50% of the size of the database. ### Question How can I accurately estimate the growth of using ADR, prior to implementing?

I have ADR enabled on my production database running on SQL Server 2019. In the SQL Server logs , it shows the messages: > [VersionCleaner][DbId:6][sweepOfSelectivePages]: Cleanup of sidelist > aborted due to timeout 0 minutes. 130686 number of pages have been > processed I did not get information about these messages in any blogs. can you help me understand the messages?

I've been doing ALOT of reading up on ADR. However, I cannot find any metrics or even anecdotal evidence to indicate the improvement in recovery time one could expect with ADR. For example, recently we were required to recover a DB. Phase 1 and 2 completed in approx 4 hours. Phase 3 completed in approx 14 hours. Recovery was necessary as the 150GB log file ran out of disk space due to a massive transaction. This article states that ADR provides instantaneous transaction rollback. Does this mean that phase 3 would have been instant rather than 14hrs? Would phase 1 and 2 also be significantly faster? Any guidance people could offer would be greatly appreciated.

On a server with SQL Server 2019 Standard Edition installed, none of our databases have the Accelerated Database Recovery enabled yet when I run the DMV - sys.dm_db_index_physical_stats, the column version_record_count is showing a non-zero value. Question is, if this feature is disabled then why is the DMV telling me that row versions are being retained for tables in each database? The databases in question are using Snapshot Isolation, but I would have thought that the verisons would be stored in tempdb.

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.