Czy B-Tree jest ponownie równoważony podczas usuwania danych z tabeli SQL Server z indeksem klastrowym?

Mam tabelę w bazie danych SQL Server z indeksem klastrowym na kluczu podstawowym. Tabela ma 1 milion wierszy. Jeśli usunę 10 000 wierszy z tabeli, czy indeks zostanie zrestrukturyzowany podczas operacji usuwania?

Operacja usuwania jest częścią procedury składowanej. Jednocześnie więcej niż jeden klient może wykonywać procedurę przechowywaną, jednak każde pojedyncze uruchomienie usunie swój własny zestaw wierszy (jednoznacznie identyfikowany przez klucz podstawowy). Dostaję blokowanie blokady klawiatury (typu U), gdy wielu klientów wykonuje tę procedurę. Blokada blokady należy do wiersza z tej samej tabeli i nie jest częścią żadnej z równolegle działających transakcji. Nie powinno być żadnego blokowania, ponieważ każde uruchomienie próbuje usunąć swój własny zestaw wierszy. Eskalacja blokady nie ma miejsca, ponieważ jest wyłączona.

Podejrzewam, że operacja usunięcia musi powodować ponowne wyważenie indeksu, a zatem podczas procesu restrukturyzacji może mieć blokadę klawiszy w dowolnym wierszu tabeli.

Byłbym bardzo wdzięczny za każdą opinię na ten temat.

sql-server index optimization

— jayesh
źródło

Ładne pytanie i niezłe domysły. Tak, po usunięciu rekordu indeks jest odbudowywany. Podczas procesu przebudowywania tabela jest blokowana i inny użytkownik nie będzie mógł uzyskać dostępu do tej tabeli. stackoverflow.com/questions/6309614/…

— KumarHarsh

NIE, usunięcie wierszy z indeksu klastrowanego nie powoduje odbudowania indeksu. Czy możesz również opublikować zapytanie użyte do usunięcia danych. Blokada U pojawia się, gdy zapytanie próbuje znaleźć dane, które zostaną usunięte, i ostatecznie blokuje wyłącznie wiersze, aby je usunąć.

— Shanky,

Po usunięciu tworzy „dziurę” lub można powiedzieć spację, ponieważ dane zostały usunięte z indeksu klastrowego. Może to powodować niską gęstość stron i może być uważane za fragmentację. Gdy wstawianie dzieje się na CI, wypełni on rekordy po prawej stronie, dzięki czemu miejsce może nigdy nie zostać wypełnione. Ale SQL Server nie zamierza automatycznie usunąć tego miejsca. Musisz odbudować indeks lub zreorganizować, aby wypełnić to miejsce. Nie ma takiego przywracania równowagi

— Shanky

@jayesh Nie rozumiem, jak kolejność węzłów w drzewie ma wspólnego z ponownym równoważeniem. B-drzewo może być niezrównoważone (z powodu wstawiania lub usuwania). W tych przypadkach kolejność węzłów nie zmienia się. To tylko niezrównoważone drzewo.

— ypercubeᵀᴹ

@jayesh Myślę, że możesz skorzystać z przeczytania niektórych dokumentów MSSQL, ponieważ myślę, że terminologia, której używasz, wprowadza w błąd zarówno ciebie, jak i niektórych z nas.

— LowlyDBA,

Aby odpowiedzieć na pytanie w tytule, czy B-drzewo ponownie zbalansowało się podczas usuwania, odpowiedź wydaje się nie, przynajmniej w następującym minimalnym przypadku testowym.

Poniższa wersja demonstracyjna uruchamia polecenia, które najlepiej pozostawić w środowisku testowym.

--create table and fill it
DROP TABLE IF EXISTS bunchesofints
CREATE TABLE bunchesofints (
thisisanint INT PRIMARY KEY CLUSTERED,
junkrow CHAR(1000) NOT NULL
)

INSERT dbo.bunchesofints
SELECT TOP 5000
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS thisisanint,
REPLICATE('a',1000) AS junkrow
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2


--with this query we can see all the non-leaf pages of the b-tree, plus the IAM
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1
GO

--Ok, let's delete most of the rows
;WITH CTE AS (
    SELECT TOP (4500) *
    FROM dbo.bunchesofints
    ORDER BY thisisanint DESC
)

DELETE 
FROM CTE
GO

--Hmm, still have 3 non-leaf index pages
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1



--So, where are the rows?
--please note the assumption that your test database has a single file.
DECLARE @firstindexpage INT, @lastindexpage INT, @db INT = DB_ID()
SELECT @firstindexpage = MIN(previous_page_page_id), @lastindexpage = MAX(next_page_page_id)
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type = 2 AND page_level = 1

DBCC PAGE(@db,1,@firstindexpage,3) WITH TABLERESULTS
DBCC PAGE(@db,1,@lastindexpage,3) WITH TABLERESULTS

Ta wersja demonstracyjna pokazuje, że usunięcie może wygenerować bardzo niezrównoważone drzewo b, z praktycznie wszystkimi danymi po jednej stronie.

— Forrest
źródło

dzięki za jasne i zwięzłe wyjaśnienie oraz kod demonstracyjny. Spróbuję tego. Przyjmuję tę odpowiedź. Wciąż próbuję dowiedzieć się, dlaczego usunięcie w rozłącznym zestawie wierszy powoduje blokowanie tabeli z indeksem klastrowym.

— jayesh,