Skip to content

Instantly share code, notes, and snippets.

@BrentOzar

BrentOzar/ADR and RCSI Demo of Index Rebuilds.sql

Created January 9, 2025 16:11
Show Gist options
  • Save BrentOzar/6444a8858cc2b5268060d53cee2ea1a1 to your computer and use it in GitHub Desktop.
Save BrentOzar/6444a8858cc2b5268060d53cee2ea1a1 to your computer and use it in GitHub Desktop.
Download ZIP
ADR and RCSI Demo of Index Rebuilds
Raw
ADR and RCSI Demo of Index Rebuilds.sql
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
SELECT TOP 100 * FROM Test.dbo.Products;
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
GO
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
CREATE DATABASE Test_ADR_RCSI;
ALTER DATABASE Test_ADR_RCSI SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE Test_ADR_RCSI SET READ_COMMITTED_SNAPSHOT ON;
GO
CREATE TABLE Test_ADR_RCSI.dbo.Products_ADR_RCSI
(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_ADR_RCSI.dbo.Products_ADR_RCSI (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
CREATE DATABASE Test_RCSI;
ALTER DATABASE Test_RCSI SET READ_COMMITTED_SNAPSHOT ON;
GO
CREATE TABLE Test_RCSI.dbo.Products_RCSI
(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_RCSI.dbo.Products_RCSI (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
ALTER INDEX ALL ON Test_ADR_RCSI.dbo.Products_ADR_RCSI REBUILD;
ALTER INDEX ALL ON Test_RCSI.dbo.Products_RCSI REBUILD;
GO
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
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;
GO
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
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;
GO
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
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
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
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;
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
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;
GO
EXEC sp_BlitzIndex @DatabaseName = 'Test', @TableName = 'Products';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR', @TableName = 'Products_ADR';
EXEC sp_BlitzIndex @DatabaseName = 'Test_ADR_RCSI', @TableName = 'Products_ADR_RCSI';
EXEC sp_BlitzIndex @DatabaseName = 'Test_RCSI', @TableName = 'Products_RCSI';
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment