Accelerated Database Recovery (ADR) is a database-level feature that makes transaction rollbacks nearly instantaneous. Here’s how it works.

Without ADR, when you update a row, SQL Server copies the old values into the transaction log and updates the row in-place. If you roll that transaction back, SQL Server has to fetch the old values from the transaction log, then apply them to the row in-place. The more rows you’ve affected, the longer your transaction will take.

With ADR, SQL Server writes a new version of the row inside the table, leaving the old version in place as well.

Because you’re a smart cookie, you immediately recognize that storing multiple versions of a row inside the same table is going to cause a storage problem: we’re going to be boosting the size of our table, quickly. However, the problem’s even bigger than that, and it starts right from the beginning when we load the data.

ADR Tables Are Larger From the Start.

We’ll demo it by creating two databases, Test and Test_ADR. I have to use different databases since ADR is a database-wide setting. Then, I’ll create two test tables, Products and Products_ADR, and load them both with a million rows.

The end result looks like this:

Let’s compare the sizes of the two tables using sp_BlitzIndex. The first result set is Products (the normal table), and the second result set is Products_ADR.

The clustered and nonclustered indexes on the Products_ADR table are all larger because like Read Committed Snapshot Isolation (RCSI), ADR needs to add a timestamp to each row to track its version. That timestamp must take up extra space, and that’s the reason, right?

Well, not exactly – rebuild the indexes on both tables and watch what happens:

The results:

The ADR version of the database plummets in size down to match the non-ADR version. That’s… odd to me. If I had an unlimited amount of time, I’d be curious to find out why that is, but I’m just a man standing in front of a database, asking it to love me, and I’m on the clock, so I gotta move on to the real problem.

I can understand why folks historically said, “After you turn on RCSI, you should rebuild your indexes to reduce space lost to page splits.” That isn’t what happened here, though – ADR was already on at the time we loaded the data, so we shouldn’t have had page splits to add versioning data. The versioning timestamps should have gone in with the initial inserts. Really odd.

I’ve also repeated this demo with additional databases with both ADR & RCSI, and just RCSI, complete with rebuilding the indexes after the load so everyone’s on the same starting point. To keep this blog post short, I’ve omitted the demo code, but you can download the full demo code here.

The results all have similar sizes:

1. No features enabled
2. ADR on
3. ADR and RCSI on
4. RCSI on

However, things start to change when the data does.

ADR & RCSI Tables Grow Really Quickly.

Let’s update 10% of the rows in all of our tables:

And then check their sizes:

In the “normal” database, the object sizes remain pretty similar because SQL Server was able to update the rows in place. The nonclustered index on QtyInStock grows because about 10% of our rows are changing from 1 to 2, so we’re going to have to move them to their new sort order, which will require new pages.

On the other hand, in the databases with ADR & RCSI enabled, the object sizes exploded, nearly doubling.

If we update another 10% of the rows:

And check the sizes again:

All of the databases see growth on the QtyInStock indexes as values move from 1 to 2, different places in the b-tree, which is going to cause some new page allocations. But the clustered indexes remain the same. We already had an explosive amount of page splits there that left a lot of empty space behind, and we’re able to reuse that space for our new round of updates.

Let’s try several rounds of updates – this is gonna take a while:

And then check our sizes:

In the ADR & RCSI databases, the clustered index has stabilized at around twice the size of the “normal” database. Is that a problem? Well… in most folks’ eyes, yes. They see this larger table as wasted space, and they want to fix it.

You Can “Fix” That With Index Rebuilds. Don’t.

To fix table bloat caused by old row versions sticking around, just rebuild the indexes:

And the objects drop back to their initial small sizes, same as right after our data loads:

People get all excited, saying they’ve “saved” disk space – but guess what’s gonna happen as our user workloads start up again, updating just 10% of the rows:

ADR and/or RCSI are both right back to double the clustered index size:

Summary: What’s the Problem That You’re Trying to Solve?

If you’re still rebuilding indexes like it’s 2005, thinking that you’re accomplishing something, stop. Time has moved on, and best practices have moved on.

You have to zoom out and rethink about what the real problem is. You’re not really saving disk space, because it’s going to blow right back up again in no time. You’re still going to need that disk space because users gonna use.

I’m not saying you should never do index rebuilds – there are absolutely cases where you should. A good example is when rows go in with a lot of null defaults, but then over the next several minutes/hours, those nulls are populated in, and then the rows are never touched again. In that case, your older pages don’t need the extra empty space that resulted from all those early-on page splits, and an index rebuild will help cram ’em back in tightly.

In. most cases, though, if you think you’re saving space, and you’ve turned on ADR or RCSI, your index rebuild space gains are illusionary and temporary.

Update 2025-08-19: Fill Factor Helps Here Too

There was a discussion on LinkedIn about this post, and Vedran Kesegic wrote that setting fill factor to 90% would help here. Leaving 10% free on every page was enough to let SQL Server write ADR’s version information to the same page. Only the smallest index jumped in size (by 40%) because SQL Server was packing in so many rows per page that even 10% empty space wasn’t enough to keep the version info on-page.

Vedran noted that as SQL Server adds new pages to existing objects (to handle inserts or rows moving around due to key updates), the fill factor setting is ignored, and data’s still going to get pumped in with 100% fill factor. In that case, Vedran suggested index maintenance jobs with a rebuild threshold of 50% might make sense – again, using a 90% fill factor so that the newly distributed data would leave enough empty space to avoid the problem.

I’d add: remember that a 90% fill factor means you’re leaving 10% empty space on every page, which means your database just grew by 10%, your memory was cut by 10%, your maintenance jobs (backups, checkdb, index rebuilds, stats updates, etc) will take 10% longer, table scans will take 10% longer, etc. I’m not dead-set against lowering fill factor – just make sure you’re solving a problem with it rather than creating new problems.