Index Rebuilds Make Even Less Sense with ADR & RCSI

Автор: Brent Ozar Index Rebuilds Make Even Less Sense with ADR & RCSI

Ускоренное восстановление базы данных (Accelerated Database Recovery - ADR) — это новая функция базы данных в SQL Server 2022, которая делает откат транзакций практически мгновенным. Без ADR при изменении записи SQL Server копирует старые значения в журнал транзакций и обновляет строку по месту. Если вы откатываете эту транзакцию, SQL Server должен извлечь старые значения из журнала транзакций, а затем применить их к записи на том же месте. Чем больше строк изменения затронули, тем больше времени займет эта транзакция. Когда у базы данных SQL Server включена ADR, в таблицу записывается новая версию записи, а старая версия остаётся на прежнем месте. Как вы догадываетесь, хранение нескольких версий строки внутри одной таблицы наверняка приведёт к проблемам с их хранением: размер нашей таблицы будет быстро увеличиваться. Однако проблема еще масштабнее, и она начинается с самого начала, когда мы ещё загружаем данные.

Таблицы с ADR больше с самого начала

Мы продемонстрируем это, создав две базы данных, Test и Test_ADR. Мне придется использовать разные базы данных, поскольку ADR — это настройка на уровне базы данных. Затем я создам две тестовые таблицы, Products и Products_ADR, и загружу в них по миллиону строк.

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

Конечный результат выглядит так:

Давайте сравним размеры двух таблиц с помощью sp_BlitzIndex. Первый набор результатов — Products (обычная таблица), а второй набор результатов — Products_ADR.

Все кластерные и не-кластерные индексы в таблице Products_ADR в сравнении заметно больше, потому что, как и с Read Committed Snapshot Isolation (RCSI), ADR необходимо добавлять к каждой строке timestamp, чтобы отслеживать ее версию. Этот timestamp будет занимать дополнительное место, и в этом причина отличий, верно?

Ну, не совсем так — перестройте индексы в обеих таблицах и посмотрите, что произойдет:

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

GO

Результаты:

База данных с ADR резко уменьшилась в размере и стала занимать место также, как и база без ADR. Это... странно для меня. Если бы у меня было больше времени, можно было бы удовлетворить любопытство, и узнать, почему так случилось, но сейчас нам лучше перейти к более существенной проблеме.

Я понимаю, почему всегда говорят: «После включения RCSI нужно перестроить индексы, чтобы уменьшить занятое ими место из-за разбиения страниц». Но здесь этого не произошло — ADR уже был включен до загрузки данных, поэтому не должно было быть разбиений страниц из-за увеличения занимаемого места данными, вызванного добавлением версий. Штампы времени версий должны были добавится в момент вставок. Действительно странно.

Для полноты картины, я также выполнил запросы этих демонстраций на других базах данных, где были включены ADR и RCSI, и только RCSI. После загрузки я полностью перестроил индексы, чтобы все базы были в одном и том же начальном состоянии. Чтобы эта статья за счёт этого не разрослась, я  опустил в ней код этой демонстрации, но вы можете загрузить полный код демонстрации здесь.

Все результаты имеют схожие размеры:

1. Функционал не включен.
2. ADR on
3. ADR and RCSI on
4. RCSI on

Однако ситуация начинает меняться, когда появляются данные.

Таблицы с включенными ADR и RCSI растут очень быстро

Давайте изменим 10% строк во всех наших таблицах:

UPDATE Test.dbo.Products SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 0;

UPDATE Test_ADR.dbo.Products_ADR SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 0;

UPDATE Test_ADR_RCSI.dbo.Products_ADR_RCSI SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 0;

UPDATE Test_RCSI.dbo.Products_RCSI SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 0;

А затем проверим их размеры:

В базе данных «без выкрутасов» размеры объектов будут достаточно компактными, поскольку SQL Server смог изменить строки на месте. Некластерный индекс на QtyInStock распухнет, поскольку около 10% строк поменяют значение с 1 на 2, поэтому серверу придется переместить их в соответствии с новым порядком в сортировке, что потребует выделения новых страниц.

С другой стороны, мы видим, что в базах данных с включенными ADR и/или RCSI размеры объектов резко возросли, почти вдвое.

Давайте обновим еще 10% строк:

UPDATE Test.dbo.Products SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 1;

UPDATE Test_ADR.dbo.Products_ADR SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 1;

UPDATE Test_ADR_RCSI.dbo.Products_ADR_RCSI SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 1;

UPDATE Test_RCSI.dbo.Products_RCSI SET QtyInStock = QtyInStock + 1

WHERE Id % 10 = 1;

И еще раз проверим размеры:

Для всех баз данных наблюдается рост индексов QtyInStock, поскольку значения изменяются с 1 на 2, и должны переместится в разные места в b-дереве, что приводит к распределению новых страниц. Но кластерные индексы остаются прежними. У нас до этого уже было взрывное увеличение числа страниц за счёт их расщеплений, после которого осталось много пустого пространства, и оно повторно используется для нового раунда изменений.

Давайте выполним ещё пару раундов изменений — это займет некоторое время:

DECLARE @Remainder INT = 2;

WHILE @Remainder <= 9

    BEGIN

    UPDATE Test.dbo.Products SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = @Remainder;

    UPDATE Test_ADR.dbo.Products_ADR SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = @Remainder;

    UPDATE Test_ADR_RCSI.dbo.Products_ADR_RCSI SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = @Remainder;

    UPDATE Test_RCSI.dbo.Products_RCSI SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = @Remainder;

    SET @Remainder = @Remainder + 1;

    END

GO 2

 

А затем проверим наши размеры:

В базах данных с ADR и RCSI кластерный индекс может занять примерно в два раза больше места, чем в «нормальной» базе данных. Это проблема? Ну… в глазах большинства людей, да. Они видят, что таблица разрослась, в ней много бесполезного пустого места, и хотят это исправить.

Можно «исправить» это с помощью пересоздания индекса – но не делайте этого!

Чтобы привести к компактному состоянию разросшиеся за счёт хранения версий строк таблицы, давайте поступим прямолинейно, просто перестроим их:

ALTER INDEX ALL ON Test.dbo.Products REBUILD;

ALTER INDEX ALL ON Test_ADR.dbo.Products_ADR REBUILD;

ALTER INDEX ALL ON Test_ADR_RCSI.dbo.Products_ADR_RCSI REBUILD;

ALTER INDEX ALL ON Test_RCSI.dbo.Products_RCSI REBUILD;

 

После этого объекты вернутся к своим первоначальным компактным размерам, какими они были сразу после загрузки данных:

Теперь мы видим приятную глазу картину, мы «сэкономили» дисковое пространство, но угадайте, что произойдет, когда вернётся рабочая нагрузка, сымитируем её изменив всего 10% записей:

UPDATE Test.dbo.Products SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = 0;

UPDATE Test_ADR.dbo.Products_ADR SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = 0;

UPDATE Test_ADR_RCSI.dbo.Products_ADR_RCSI SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = 0;

UPDATE Test_RCSI.dbo.Products_RCSI SET QtyInStock = QtyInStock + 1 WHERE Id % 10 = 0;

ADR и/или RCSI снова удвоит размер кластерного индекса:

 

Резюме: Какую проблему стоит попытаться решить?

Если вы все еще перестраиваете индексы, как в 2005 году, думая, что вы чего-то этим добиваетесь – остановитесь! Время идет, и лучшие практики меняются.

Вам нужно взглянуть на проблему шире и переосмыслить, в чем же заключается настоящая проблема. На самом деле REBUILD не экономит дисковое пространство, потому что оно снова взорвется в мгновение ока. Это дисковое пространство не пропадёт зря, потому что будет использоваться для размещения новых данных и версий.

Я не утверждаю, что вам никогда не следует перестраивать индексы – есть случаи, когда это абсолютно необходимо. Например – когда записи вставляются с большим количеством значений по умолчанию - Null, но затем сразу же эти Null-значения заполняются реальными значениями, которые больше не меняются. В этом случае высвободившееся на страницах место после их расщеплений можно убрать перестроением индексов, что поможет плотно убрать не рационально используемое место.

Однако в большинстве случаев, если вы рассчитываете сэкономить место, и при этом включили ADR или RCSI, выигрыш от последующих перестроений индексов будет иллюзорен и временен.