Czy ALTER TABLE… DROP COLUMN naprawdę jest operacją opartą wyłącznie na metadanych?

11

Znalazłem kilka źródeł, które informują, że ALTER TABLE ... DROP COLUMN jest operacją tylko dla metadanych.

Źródło

Jak to może być? Czy dane podczas DROP COLUMN nie muszą być usuwane z leżących u ich podstaw indeksów nieklastrowych i indeksów klastrowych / sterty?

Ponadto, dlaczego Dokumenty Microsoft sugerują, że jest to w pełni zalogowana operacja?

Zmiany wprowadzone w tabeli są rejestrowane i można je w pełni odzyskać. Zmiany, które wpływają na wszystkie wiersze w dużych tabelach, takie jak upuszczanie kolumny lub, w niektórych wersjach SQL Server, dodawanie kolumny NOT NULL z wartością domyślną, mogą zająć dużo czasu, aby zakończyć i wygenerować wiele rekordów dziennika . Uruchom te instrukcje ALTER TABLE z taką samą ostrożnością jak każda instrukcja INSERT, UPDATE lub DELETE, która wpływa na wiele wierszy.

Drugie pytanie: w jaki sposób silnik śledzi upuszczone kolumny, jeśli dane nie są usuwane z podstawowych stron?

sql-server  transaction-log  alter-table 
— George.Palacios
źródło
2
Cóż, myślę, że język przetrwał w wielu wersjach produktu i wielu kolejnych iteracjach dokumentacji. Z biegiem czasu coraz więcej operacji na kolumnach zmieniało się tylko w trybie online / metadanych. Być może jest to obecnie zły, konkretny przykład, ale celem tego zdania jest po prostu ostrzeżenie, że, ogólnie rzecz biorąc, niektóre operacje zmiany mogą być operacjami polegającymi na zmianie rozmiaru danych w niektórych scenariuszach, zamiast wymieniać każdy konkretny scenariusz.
— Aaron Bertrand

Odpowiedzi:

14

Istnieją pewne okoliczności, w których usunięcie kolumny może być operacją tylko dla metadanych. Definicje kolumn dla dowolnej tabeli nie są zawarte na każdej stronie, na której przechowywane są wiersze, definicje kolumn są przechowywane tylko w metadanych bazy danych, w tym sys.sysrowsets, sys.sysrscols itp.

Podczas upuszczania kolumny, do której nie odwołuje się żaden inny obiekt, silnik pamięci po prostu zaznacza definicję kolumny jako już nieobecną, usuwając odpowiednie szczegóły z różnych tabel systemowych. Operacja usunięcia metadanych unieważnia pamięć podręczną procedury, wymagając ponownej kompilacji za każdym razem, gdy zapytanie odwołuje się następnie do tej tabeli. Ponieważ rekompilacja zwraca tylko te kolumny, które obecnie istnieją w tabeli, nigdy nawet nie jest wymagane podanie szczegółów kolumny dla upuszczonej kolumny; silnik pamięci pomija bajty zapisane na każdej stronie dla tej kolumny, tak jakby kolumna już nie istniała.

Gdy wystąpi kolejna operacja DML względem tabeli, strony, których to dotyczy, są ponownie zapisywane bez danych dla upuszczonej kolumny. Jeśli odbudujesz indeks klastrowy lub stertę, wszystkie bajty upuszczonej kolumny nie zostaną oczywiście zapisane z powrotem na stronie na dysku. To skutecznie rozkłada obciążenie związane z upuszczaniem kolumny w czasie, co czyni ją mniej zauważalną.

Istnieją okoliczności, w których nie można upuścić kolumny, na przykład gdy kolumna jest zawarta w indeksie lub gdy ręcznie utworzono obiekt statystyki dla kolumny. Napisałem post na blogu pokazujący błąd, który pojawia się podczas próby zmiany kolumny ręcznie utworzonym obiektem statystycznym. Ta sama semantyka obowiązuje przy upuszczaniu kolumny - jeśli do kolumny odwołuje się jakikolwiek inny obiekt, nie można jej po prostu usunąć. Obiekt odniesienia należy najpierw zmienić, a następnie upuścić kolumnę.

Jest to dość łatwe do pokazania, patrząc na zawartość dziennika transakcji po upuszczeniu kolumny. Poniższy kod tworzy tabelę z pojedynczą kolumną znaków o długości 8 000 znaków. Dodaje wiersz, upuszcza go i wyświetla zawartość dziennika transakcji dotyczącą operacji upuszczania. Rekordy dziennika pokazują modyfikacje różnych tabel systemowych, w których przechowywane są definicje tabel i kolumn. Jeśli dane kolumny faktycznie zostały usunięte ze stron przypisanych do tabeli, zobaczysz rekordy dziennika rejestrujące rzeczywiste dane strony; nie ma takich zapisów.

DROP TABLE IF EXISTS dbo.DropColumnTest;
GO
CREATE TABLE dbo.DropColumnTest
(
    rid int NOT NULL
        CONSTRAINT DropColumnTest_pkc
        PRIMARY KEY CLUSTERED
    , someCol varchar(8000) NOT NULL
);

INSERT INTO dbo.DropColumnTest (rid, someCol)
SELECT 1, REPLICATE('Z', 8000);
GO

DECLARE @startLSN nvarchar(25);

SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1)
      , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1)
      , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1) 
    + ':' + CONVERT(varchar(8), @b, 1) 
    + ':' + CONVERT(varchar(8), @c, 1)

ALTER TABLE dbo.DropColumnTest DROP COLUMN someCol;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)


--modify an existing data row 
SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

SET @a = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1);
SET @b = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1);
SET @c = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1) 
    + ':' + CONVERT(varchar(8), @b, 1) 
    + ':' + CONVERT(varchar(8), @c, 1)

UPDATE dbo.DropColumnTest SET rid = 2;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)

(Dane wyjściowe są zbyt duże, aby je wyświetlić, a plik dbfiddle.uk nie pozwoli mi uzyskać dostępu do fn_dblog)

Pierwszy zestaw danych wyjściowych pokazuje dziennik jako wynik instrukcji DDL upuszczającej kolumnę. Drugi zestaw danych wyjściowych pokazuje dziennik po uruchomieniu instrukcji DML, w której aktualizujemy ridkolumnę. W drugim zestawie wyników widzimy rekordy dziennika wskazujące usunięcie z dbo.DropColumnTest, a następnie wstawienie do dbo.DropColumnTest. Długość każdego dziennika wynosi 8116, co oznacza, że ​​rzeczywista strona została zaktualizowana.

Jak widać z danych wyjściowych fn_dblogpolecenia w powyższym teście, cała operacja jest w pełni rejestrowana. Dotyczy to zarówno prostego odzyskiwania, jak i pełnego odzyskiwania. Terminologia „w pełni zalogowana” może być źle interpretowana, ponieważ modyfikacja danych nie jest rejestrowana. Tak się nie dzieje - modyfikacja jest rejestrowana i można ją w pełni wycofać. Dziennik po prostu rejestruje tylko te strony, które zostały dotknięte, a ponieważ żadna ze stron danych tabeli nie została zarejestrowana przez operację DDL, zarówno, jak DROP COLUMNi wszelkie wycofywanie, które może nastąpić, nastąpi niezwykle szybko, niezależnie od wielkości tabeli.

W przypadku nauki poniższy kod zrzuci strony danych dla tabeli zawartej w powyższym kodzie, używając DBCC PAGEstylu „3”. Styl „3” oznacza, że ​​chcemy nagłówka strony oraz szczegółowej interpretacji dla poszczególnych wierszy . Kod używa kursora, aby wyświetlić szczegóły każdej strony w tabeli, więc możesz chcieć się upewnić, że nie uruchomisz tego na dużym stole.

DBCC TRACEON(3604); --directs out from DBCC commands to the console, instead of the error log
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
FROM sys.schemas s  
    INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N'DropColumnTest'
    AND s.name = N'dbo'
    AND dpa.page_type_desc = N'DATA_PAGE';
OPEN cur;
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (@dbid, @fileid, @pageid, 3);
    FETCH NEXT FROM cur INTO @fileid, @pageid;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF(3604);

Patrząc na wynik pierwszej strony z mojej wersji demonstracyjnej (po upuszczeniu kolumny, ale przed aktualizacją kolumny), widzę to:

STRONA: (1: 100104)


BUFOR:


BUF @ 0x0000021793E42040

bpage = 0x000002175A7A0000 bhash = 0x0000000000000000 bpageno = (1: 100104)
bdbid = 10 braków = 1 bcputicks = 0
bsampleCount = 0 bUse1 = 13760 bstat = 0x10b
blog = 0x212121cc bnext = 0x0000000000000000 bDirtyContext = 0x000002175004B640
bstat2 = 0x0                        

NAGŁÓWEK:


Strona @ 0x000002175A7A0000

m_pageId = (1: 100104) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 300 m_indexId (AllocUnitId.idInd) = 256 
Metadane: AllocUnitId = 72057594057588736                                
Metadane: PartitionId = 72057594051756032 Metadane: IndexId = 1
Metadane: ObjectId = 174623665 m_prevPage = (0: 0) m_nextPage = (0: 0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 79
m_freeData = 8111 m_reservedCnt = 0 m_lsn = (616: 14191: 25)
m_xactReserved = 0 m_xdesId = (0: 0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1                      

Status przydziału

GAM (1: 2) = PRZYDZIELONY SGAM (1: 3) = NIE PRZYZNANY          
PFS (1: 97056) = 0x40 PRZYDZIELONY 0_PCT_FULL DIFF (1: 6) = ZMIENIONY
ML (1: 7) = NIE MIN_LOGGED           

Slot 0 Offset 0x60 Długość 8015

Typ rekordu = PRIMARY_RECORD Atrybuty rekordu = NULL_BITMAP VARIABLE_COLUMNS
Rozmiar rekordu = 8015                  
Zrzut pamięci @ 0x000000B75227A060

0000000000000000: 30000800 01000000 02000001 004f1f5a 5a5a5a5a 0 ............ O.ZZZZZ
0000000000000014: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
.
.
.
0000000000001F2C: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
0000000000001F40: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a ZZZZZZZZZZZZZZZ

Szczelina 0 Kolumna 1 Przesunięcie 0x4 Długość 4 Długość (fizyczna) 4

rid = 1                             

Szczelina 0 Kolumna 67108865 Przesunięcie 0xf Długość 0 Długość (fizyczna) 8000

DROPPED = NULL                      

Slot 0 Przesunięcie 0x0 Długość 0 Długość (fizyczna) 0

KeyHashValue = (8194443284a0)

Dla zwięzłości usunąłem większość surowego zrzutu strony z danych wyjściowych pokazanych powyżej. Na końcu danych wyjściowych zobaczysz to dla ridkolumny:

Szczelina 0 Kolumna 1 Przesunięcie 0x4 Długość 4 Długość (fizyczna) 4

rid = 1

Ostatni wiersz powyżej rid = 1zwraca nazwę kolumny i bieżącą wartość przechowywaną w kolumnie na stronie.

Następnie zobaczysz to:

Szczelina 0 Kolumna 67108865 Przesunięcie 0xf Długość 0 Długość (fizyczna) 8000

DROPPED = NULL

Wynik pokazuje, że Slot 0 zawiera usuniętą kolumnę na podstawie DELETEDtekstu, w którym normalnie byłaby nazwa kolumny. Wartość kolumny jest zwracana, NULLponieważ kolumna została usunięta. Jednak, jak widać w surowych danych, wartość 8 000 znaków REPLICATE('Z', 8000)dla tej kolumny nadal istnieje na stronie. To jest przykład tej części wyniku DBCC PAGE:

0000000000001EDC: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
0000000000001EF0: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
0000000000001F04: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
0000000000001F18: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZZ
— Max Vernon
źródło
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.