Dlaczego ALTER COLUMN na NOT NULL powoduje ogromny wzrost pliku dziennika?

Mam tabelę z 64-metrowymi wierszami zajmującymi 4,3 GB na dysku dla danych.

Każdy wiersz ma około 30 bajtów kolumn liczb całkowitych oraz zmienną NVARCHAR(255)kolumnę dla tekstu.

Dodałem kolumnę NULLABLE o typie danych Datetimeoffset(0).

Następnie zaktualizowałem tę kolumnę dla każdego wiersza i upewniłem się, że wszystkie nowe wstawki umieszczają wartość w tej kolumnie.

Kiedy nie było żadnych NULL wpisów, uruchomiłem to polecenie, aby moje nowe pole było obowiązkowe:

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

Rezultatem był OGROMNY wzrost wielkości dziennika transakcji - z 6 GB do ponad 36 GB, aż zabrakło miejsca!

Czy ktoś ma pojęcie, co u licha robi SQL Server 2008 R2, aby to proste polecenie spowodowało tak ogromny wzrost?

Po zmianie kolumny na NOT NULL SQL Server musi dotykać każdej strony, nawet jeśli nie ma wartości NULL. W zależności od współczynnika wypełnienia może to prowadzić do wielu podziałów stron. Oczywiście każda dotknięta strona musi zostać zarejestrowana i podejrzewam, że ze względu na podziały w przypadku wielu stron konieczne może być zarejestrowanie dwóch zmian. Ponieważ wszystko dzieje się w jednym przejściu, dziennik musi uwzględniać wszystkie zmiany, aby po anulowaniu wiedział dokładnie, co należy cofnąć.

Przykład. Prosty stół:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Teraz spójrzmy na szczegóły strony. Najpierw musimy dowiedzieć się, z jaką stroną i identyfikatorem DB_ID mamy do czynienia. W moim przypadku utworzyłem bazę danych o nazwie foo, a zdarzeniem DB_ID było 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

Dane wyjściowe wskazują, że byłem zainteresowany stroną 159 (jedynym wierszem w DBCC INDdanych wyjściowych z PageType = 1).

Teraz przyjrzyjmy się wybranym szczegółom strony, gdy przechodzimy przez scenariusz PO.

DBCC PAGE(5, 1, 159, 3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5, 1, 159, 3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

Teraz nie mam na to wszystkich odpowiedzi, ponieważ nie jestem facetem od głębokich chorób wewnętrznych. Ale jasne jest, że - chociaż zarówno operacja aktualizacji, jak i dodanie ograniczenia NOT NULL niezaprzeczalnie zapisują się na stronie - ta ostatnia robi to w zupełnie inny sposób. Wygląda na to, że faktycznie zmienia strukturę rekordu, zamiast po prostu bawić się bitami, zamieniając kolumnę null na kolumnę, która nie ma wartości null. Dlaczego tak się dzieje, nie jestem do końca pewien - wydaje mi się, że to dobre pytanie dla zespołu silnika pamięci masowej . Wierzę, że SQL Server 2012 radzi sobie z niektórymi z tych scenariuszy o wiele lepiej, FWIW - ale muszę jeszcze przeprowadzić wyczerpujące testy.

Podczas wykonywania polecenia

ALTER COLUMN ... NOT NULL

Wydaje się, że jest to zaimplementowane jako operacja dodawania kolumny, aktualizacji, upuszczania kolumny.

• Wstawiany jest nowy wiersz sys.sysrscolsreprezentujący nową kolumnę. statusBit 128jest ustawiony wskazuje kolumnę nie pozwala NULLs
• Aktualizacja jest przeprowadzana w każdym wierszu tabeli, ustawiając nową wartość kolumny do wartości starej wartości kolumny. Jeśli wersje wiersza „przed” i „po” są dokładnie takie same, nie powoduje to zapisania żadnych rzeczy w dzienniku transakcji, w przeciwnym razie aktualizacja zostanie zarejestrowana.
• Oryginalna kolumna jest oznaczona jako upuszczona (jest to tylko zmiana metadanych sys.sysrscols. rscolidZaktualizowana do dużej liczby całkowitej i statusustawiony bit 2 na wskazany upuszczony)
• Wpis sys.sysrscolsnowej kolumny jest zmieniany, aby nadać jej rscolidpoprzednią kolumnę.

Operacja, która może powodować wiele rejestrowania, dotyczy UPDATEwszystkich wierszy w tabeli, ale nie oznacza to, że zawsze tak się stanie. Jeśli obrazy „przed” i „po” wiersza są identyczne, będzie to traktowane jako aktualizacja nie aktualizująca i do tej pory nie będzie logowane z moich testów.

Wyjaśnienie, dlaczego otrzymujesz dużo rejestrowania, będzie zależeć od tego, dlaczego dokładnie wersje wiersza „przed” i „po” nie są takie same.

W przypadku kolumn o zmiennej długości przechowywanych w FixedVarformacie stwierdziłem, że ustawienie NOT NULLzawsze powoduje zmianę w wierszu, który należy zarejestrować. Liczba kolumn i liczba kolumn o zmiennej długości są zwiększane, a nowa kolumna jest dodawana na końcu sekcji o zmiennej długości, powielając dane.

datetimeoffset(0)ma jednak ustaloną długość, a dla kolumn o stałej długości przechowywanych w FixedVarformacie zarówno stare, jak i nowe kolumny wydają się mieć tę samą szczelinę w części danych o stałej długości wiersza i ponieważ oba mają tę samą długość i wartość „przed” oraz wersje „po” wiersza są takie same . Można to zobaczyć w odpowiedzi @ Aarona. Obie wersje wiersza przed i po ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;są

0x10000c00 01000000 00000000 020000

To nie jest zalogowane.

Logicznie z mojego opisu zdarzeń wiersz powinien być tutaj inny, ponieważ 02należy zwiększyć liczbę kolumn, 03ale w praktyce taka zmiana nie ma miejsca.

Oto niektóre możliwe powody, dla których może to wystąpić w kolumnie o stałej długości

• Jeśli kolumna została pierwotnie zadeklarowana jako, SPARSEwówczas nowa kolumna byłaby przechowywana w innej części wiersza niż oryginał, powodując, że obrazy przed i po wierszu będą inne.
• Jeśli korzystasz z którejkolwiek z opcji kompresji, wersje przed i po wierszu będą się różnić, ponieważ sekcja licznika kolumn w macierzy CD jest zwiększana.
• W bazach danych z włączoną jedną z opcji izolacji migawki aktualizowana jest informacja o wersji w każdym wierszu (@SQL Kiwi wskazuje, że może to również wystąpić w bazach danych bez włączonego SI, jak opisano tutaj ).
• Być może poprzednia ALTER TABLEoperacja została zaimplementowana jako zmiana tylko metadanych i nie została jeszcze zastosowana do wiersza. Na przykład, jeśli dodano nową zerowalną kolumnę o zmiennej długości, wówczas jest ona pierwotnie stosowana jako zmiana tylko metadanych i jest faktycznie zapisywana do wierszy, gdy są one następnie aktualizowane (zapis, który faktycznie występuje w tej ostatniej instancji, to tylko aktualizacje sekcja liczby kolumn i NULL_BITMAPjako NULL varcharkolumna na końcu wiersza nie zajmuje miejsca)

Ten sam problem napotkałem w przypadku tabeli mającej 200 000 000 wierszy. Początkowo dodałem kolumnę nullable, następnie zaktualizowałem wszystkie wiersze, a na koniec zmieniłem kolumnę NOT NULLza pomocą ALTER TABLE ALTER COLUMNinstrukcji. Spowodowało to dwie ogromne transakcje niewiarygodnie wysadzające plik dziennika (wzrost o 170 GB).

Najszybszy sposób, jaki znalazłem, to:

1. Dodaj kolumnę, używając wartości domyślnej

   ALTER TABLE table1 ADD column1 INT NOT NULL DEFAULT (1)

2. Usuń domyślne ograniczenie, używając dynamicznego SQL, ponieważ ograniczenie nie było wcześniej nazywane:

   DECLARE 
       @constraint_name SYSNAME,
       @stmt NVARCHAR(510);
   
   SELECT @CONSTRAINT_NAME = DC.NAME
   FROM SYS.DEFAULT_CONSTRAINTS DC
   INNER JOIN SYS.COLUMNS C
       ON DC.PARENT_OBJECT_ID = C.OBJECT_ID
       AND DC.PARENT_COLUMN_ID = C.COLUMN_ID
   WHERE
       PARENT_OBJECT_ID = OBJECT_ID('table1')
       AND C.NAME = 'column1';
   

Czas wykonania skrócił się z> 30 minut do 10 minut, w tym replikacja zmian za pomocą replikacji transakcyjnej. Korzystam z instalacji programu SQL Server 2008 (SP2).

Przeprowadziłem następujący test:

create table tblCheckResult(
        ColID   int identity
    ,   dtoDateTime Datetimeoffset(0) null
    )

 go

insert into tblCheckResult (dtoDateTime)
select getdate()
go 10000

checkpoint 

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

select * from fn_dblog(null,null)

Uważam, że ma to związek z zarezerwowanym miejscem w dzienniku na wypadek wycofania transakcji. Zajrzyj do funkcji fn_dblog w kolumnie „Log Reserve” dla wiersza LOP_BEGIN_XACT i zobacz, ile miejsca próbuje zarezerwować.

Zachowanie to jest inne w SQL Server 2012. Zobacz http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

Liczba rekordów dziennika wygenerowanych dla SQL Server 2008 R2 i niższych wersji będzie znacznie wyższa niż liczba rekordów dziennika dla SQL Server 2012.