Index Rebuilds Make Even Less Sense with ADR & RCSI.
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.
DROP DATABASE Test;
DROP DATABASE Test_ADR;
DROP DATABASE Test_ADR_RCSI;
DROP DATABASE Test_RCSI;
CREATE DATABASE Test;
CREATE DATABASE Test_ADR;
ALTER DATABASE Test_ADR SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
CREATE TABLE Test.dbo.Products
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ProductName NVARCHAR(100) INDEX IX_ProductName,
QtyInStock INT INDEX IX_QtyInStock);
GO
CREATE TABLE Test_ADR.dbo.Products_ADR
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ProductName NVARCHAR(100) INDEX IX_ProductName,
QtyInStock INT INDEX IX_QtyInStock);
GO
WITH Cuisines AS
(
SELECT Cuisine
FROM (VALUES
('Italian'), ('Mexican'), ('Chinese'), ('Japanese'),
('Indian'), ('French'), ('Greek'), ('Spanish'),
('Korean'), ('Thai'), ('Cajun'), ('Cuban'), ('Moroccan'),
('Turkish'), ('Lebanese'), ('Vietnamese'), ('Filipino'),
('Ethiopian'), ('Caribbean'), ('Brazilian'), ('Peruvian'),
('Argentinian'), ('German'), ('Russian'), ('Polish'),
('Hungarian'), ('Swiss'), ('Swedish'), ('Norwegian'),
('Danish'), ('Portuguese'), ('Irish'), ('Scottish'),
('English'), ('American'), ('Hawaiian'), ('Middle Eastern'),
('Afghan'), ('Pakistani'), ('Bangladeshi'), ('Nepalese'),
('Sri Lankan'), ('Tibetan'), ('Malay'), ('Indonesian'),
('Singaporean'), ('Malaysian'), ('Burmese'), ('Laotian'),
('Cambodian'), ('Mongolian'), ('Uzbek'), ('Kazakh'),
('Georgian'), ('Azerbaijani'), ('Armenian'), ('Persian'),
('Iraqi'), ('Syrian'), ('Jordanian'), ('Saudi Arabian'),
('Israeli'), ('Palestinian'), ('Yemeni'), ('Sudanese'),
('Somali'), ('Kenyan'), ('Tanzanian'), ('Ugandan'),
('Zimbabwean'), ('South African'), ('Nigerian'), ('Ghanaian'),
('Senegalese'), ('Ivory Coast'), ('Cameroonian'),
('Malagasy'), ('Australian'), ('New Zealand'), ('Canadian'),
('Chilean'), ('Colombian'), ('Venezuelan'), ('Ecuadorian'),
('Paraguayan'), ('Uruguayan'), ('Bolivian'), ('Guatemalan'),
('Honduran'), ('Nicaraguan'), ('Salvadoran'), ('Costa Rican'),
('Panamanian'), ('Belizean'), ('Jamaican'), ('Trinidadian'),
('Barbadian'), ('Bahamian'), ('Antiguan'), ('Grenadian'),
('Grandma''s'), ('Grandpa''s')
) AS t(Cuisine)
),
Adjectives AS
(
SELECT Adjective
FROM (VALUES
('Spicy'), ('Savory'), ('Sweet'), ('Creamy'), ('Crunchy'),
('Zesty'), ('Tangy'), ('Hearty'), ('Fragrant'), ('Juicy'),
('Crispy'), ('Delicious'), ('Mouthwatering'), ('Toasted'),
('Smoky'), ('Rich'), ('Light'), ('Buttery'), ('Tender'),
('Flaky'), ('Succulent'), ('Bitter'), ('Peppery'), ('Charred'),
('Piquant'), ('Nutty'), ('Velvety'), ('Chewy'), ('Silky'),
('Golden'), ('Satisfying'), ('Gooey'), ('Caramelized'), ('Luscious'),
('Hot'), ('Cool'), ('Bold'), ('Earthy'), ('Subtle'),
('Vibrant'), ('Doughy'), ('Garlicky'), ('Herby'), ('Tangy'),
('Mild'), ('Spiced'), ('Infused'), ('Ripe'), ('Fresh'),
('Citrusy'), ('Tart'), ('Pickled'), ('Fermented'), ('Umami'),
('Wholesome'), ('Decadent'), ('Savoured'), ('Fizzy'), ('Effervescent'),
('Melty'), ('Sticky'), ('Toothsome'), ('Crumbly'), ('Roasted'),
('Boiled'), ('Braised'), ('Fried'), ('Baked'), ('Grilled'),
('Steamed'), ('Seared'), ('Broiled'), ('Poached'), ('Simmered'),
('Marinated'), ('Dusted'), ('Drizzled'), ('Glazed'), ('Charred'),
('Seared'), ('Plated'), ('Whipped'), ('Fluffy'), ('Homemade'),
('Comforting'), ('Heartwarming'), ('Filling'), ('Juicy'), ('Piping'),
('Savored'), ('Seasoned'), ('Briny'), ('Doused'), ('Herbed'),
('Basted'), ('Crusted'), ('Topped'), ('Pressed'), ('Folded'),
('Layered'), ('Stuffed')
) AS t(Adjective)
),
Dishes AS
(
SELECT Dish
FROM (VALUES
('Pizza'), ('Taco'), ('Noodles'), ('Sushi'), ('Curry'),
('Soup'), ('Burger'), ('Salad'), ('Sandwich'), ('Stew'),
('Pasta'), ('Fried Rice'), ('Dumplings'), ('Wrap'),
('Pancakes'), ('Stir Fry'), ('Casserole'), ('Quiche'), ('Ramen'),
('Burrito'), ('Chow Mein'), ('Spring Rolls'), ('Lasagna'), ('Paella'),
('Risotto'), ('Pho'), ('Gyoza'), ('Chili'), ('Bisque'),
('Frittata'), ('Toast'), ('Nachos'), ('Bagel'), ('Croissant'),
('Waffles'), ('Crepes'), ('Omelette'), ('Tart'), ('Brownies'),
('Cupcakes'), ('Muffins'), ('Samosa'), ('Enchiladas'), ('Tikka Masala'),
('Shawarma'), ('Kebab'), ('Falafel'), ('Meatballs'), ('Casserole'),
('Pot Pie'), ('Fajitas'), ('Ravioli'), ('Calzone'), ('Empanadas'),
('Bruschetta'), ('Ciabatta'), ('Donuts'), ('Macaroni'), ('Clam Chowder'),
('Gazpacho'), ('Gnocchi'), ('Ratatouille'), ('Poke Bowl'), ('Hotdog'),
('Fried Chicken'), ('Churros'), ('Stuffed Peppers'), ('Fish Tacos'), ('Kabobs'),
('Mashed Potatoes'), ('Pad Thai'), ('Bibimbap'), ('Kimchi Stew'), ('Tteokbokki'),
('Tamales'), ('Meatloaf'), ('Cornbread'), ('Cheesecake'), ('Gelato'),
('Sorbet'), ('Ice Cream'), ('Pavlova'), ('Tiramisu'), ('Custard'),
('Flan'), ('Bread Pudding'), ('Trifle'), ('Cobbler'), ('Shortcake'),
('Soufflé'), ('Eclairs'), ('Cannoli'), ('Baklava'), ('Pecan Pie'),
('Apple Pie'), ('Focaccia'), ('Stromboli'), ('Beignets'), ('Yorkshire Pudding')
) AS t(Dish)
)
INSERT INTO Test.dbo.Products (ProductName, QtyInStock)
SELECT TOP 1000000 a.Adjective + ' ' + c.Cuisine + ' ' + d.Dish, 1
FROM Cuisines c
CROSS JOIN Adjectives a
CROSS JOIN Dishes d
ORDER BY NEWID();
WITH Cuisines AS
(
SELECT Cuisine
FROM (VALUES
('Italian'), ('Mexican'), ('Chinese'), ('Japanese'),
('Indian'), ('French'), ('Greek'), ('Spanish'),
('Korean'), ('Thai'), ('Cajun'), ('Cuban'), ('Moroccan'),
('Turkish'), ('Lebanese'), ('Vietnamese'), ('Filipino'),
('Ethiopian'), ('Caribbean'), ('Brazilian'), ('Peruvian'),
('Argentinian'), ('German'), ('Russian'), ('Polish'),
('Hungarian'), ('Swiss'), ('Swedish'), ('Norwegian'),
('Danish'), ('Portuguese'), ('Irish'), ('Scottish'),
('English'), ('American'), ('Hawaiian'), ('Middle Eastern'),
('Afghan'), ('Pakistani'), ('Bangladeshi'), ('Nepalese'),
('Sri Lankan'), ('Tibetan'), ('Malay'), ('Indonesian'),
('Singaporean'), ('Malaysian'), ('Burmese'), ('Laotian'),
('Cambodian'), ('Mongolian'), ('Uzbek'), ('Kazakh'),
('Georgian'), ('Azerbaijani'), ('Armenian'), ('Persian'),
('Iraqi'), ('Syrian'), ('Jordanian'), ('Saudi Arabian'),
('Israeli'), ('Palestinian'), ('Yemeni'), ('Sudanese'),
('Somali'), ('Kenyan'), ('Tanzanian'), ('Ugandan'),
('Zimbabwean'), ('South African'), ('Nigerian'), ('Ghanaian'),
('Senegalese'), ('Ivory Coast'), ('Cameroonian'),
('Malagasy'), ('Australian'), ('New Zealand'), ('Canadian'),
('Chilean'), ('Colombian'), ('Venezuelan'), ('Ecuadorian'),
('Paraguayan'), ('Uruguayan'), ('Bolivian'), ('Guatemalan'),
('Honduran'), ('Nicaraguan'), ('Salvadoran'), ('Costa Rican'),
('Panamanian'), ('Belizean'), ('Jamaican'), ('Trinidadian'),
('Barbadian'), ('Bahamian'), ('Antiguan'), ('Grenadian'),
('Grandma''s'), ('Grandpa''s')
) AS t(Cuisine)
),
Adjectives AS
(
SELECT Adjective
FROM (VALUES
('Spicy'), ('Savory'), ('Sweet'), ('Creamy'), ('Crunchy'),
('Zesty'), ('Tangy'), ('Hearty'), ('Fragrant'), ('Juicy'),
('Crispy'), ('Delicious'), ('Mouthwatering'), ('Toasted'),
('Smoky'), ('Rich'), ('Light'), ('Buttery'), ('Tender'),
('Flaky'), ('Succulent'), ('Bitter'), ('Peppery'), ('Charred'),
('Piquant'), ('Nutty'), ('Velvety'), ('Chewy'), ('Silky'),
('Golden'), ('Satisfying'), ('Gooey'), ('Caramelized'), ('Luscious'),
('Hot'), ('Cool'), ('Bold'), ('Earthy'), ('Subtle'),
('Vibrant'), ('Doughy'), ('Garlicky'), ('Herby'), ('Tangy'),
('Mild'), ('Spiced'), ('Infused'), ('Ripe'), ('Fresh'),
('Citrusy'), ('Tart'), ('Pickled'), ('Fermented'), ('Umami'),
('Wholesome'), ('Decadent'), ('Savoured'), ('Fizzy'), ('Effervescent'),
('Melty'), ('Sticky'), ('Toothsome'), ('Crumbly'), ('Roasted'),
('Boiled'), ('Braised'), ('Fried'), ('Baked'), ('Grilled'),
('Steamed'), ('Seared'), ('Broiled'), ('Poached'), ('Simmered'),
('Marinated'), ('Dusted'), ('Drizzled'), ('Glazed'), ('Charred'),
('Seared'), ('Plated'), ('Whipped'), ('Fluffy'), ('Homemade'),
('Comforting'), ('Heartwarming'), ('Filling'), ('Juicy'), ('Piping'),
('Savored'), ('Seasoned'), ('Briny'), ('Doused'), ('Herbed'),
('Basted'), ('Crusted'), ('Topped'), ('Pressed'), ('Folded'),
('Layered'), ('Stuffed')
) AS t(Adjective)
),
Dishes AS
(
SELECT Dish
FROM (VALUES
('Pizza'), ('Taco'), ('Noodles'), ('Sushi'), ('Curry'),
('Soup'), ('Burger'), ('Salad'), ('Sandwich'), ('Stew'),
('Pasta'), ('Fried Rice'), ('Dumplings'), ('Wrap'),
('Pancakes'), ('Stir Fry'), ('Casserole'), ('Quiche'), ('Ramen'),
('Burrito'), ('Chow Mein'), ('Spring Rolls'), ('Lasagna'), ('Paella'),
('Risotto'), ('Pho'), ('Gyoza'), ('Chili'), ('Bisque'),
('Frittata'), ('Toast'), ('Nachos'), ('Bagel'), ('Croissant'),
('Waffles'), ('Crepes'), ('Omelette'), ('Tart'), ('Brownies'),
('Cupcakes'), ('Muffins'), ('Samosa'), ('Enchiladas'), ('Tikka Masala'),
('Shawarma'), ('Kebab'), ('Falafel'), ('Meatballs'), ('Casserole'),
('Pot Pie'), ('Fajitas'), ('Ravioli'), ('Calzone'), ('Empanadas'),
('Bruschetta'), ('Ciabatta'), ('Donuts'), ('Macaroni'), ('Clam Chowder'),
('Gazpacho'), ('Gnocchi'), ('Ratatouille'), ('Poke Bowl'), ('Hotdog'),
('Fried Chicken'), ('Churros'), ('Stuffed Peppers'), ('Fish Tacos'), ('Kabobs'),
('Mashed Potatoes'), ('Pad Thai'), ('Bibimbap'), ('Kimchi Stew'), ('Tteokbokki'),
('Tamales'), ('Meatloaf'), ('Cornbread'), ('Cheesecake'), ('Gelato'),
('Sorbet'), ('Ice Cream'), ('Pavlova'), ('Tiramisu'), ('Custard'),
('Flan'), ('Bread Pudding'), ('Trifle'), ('Cobbler'), ('Shortcake'),
('Soufflé'), ('Eclairs'), ('Cannoli'), ('Baklava'), ('Pecan Pie'),
('Apple Pie'), ('Focaccia'), ('Stromboli'), ('Beignets'), ('Yorkshire Pudding')
) AS t(Dish)
)
INSERT INTO Test_ADR.dbo.Products_ADR (ProductName, QtyInStock)
SELECT TOP 1000000 a.Adjective + ' ' + c.Cuisine + ' ' + d.Dish, 1
FROM Cuisines c
CROSS JOIN Adjectives a
CROSS JOIN Dishes d
ORDER BY NEWID();
GO
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:
ALTER INDEX ALL ON Test.dbo.Products REBUILD;
ALTER INDEX ALL ON Test_ADR.dbo.Products_ADR REBUILD;
GO
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
The results:
