Usuń wydajność dla danych LOB w SQL Server

To pytanie jest powiązane z tym wątkiem na forum .

Uruchamianie programu SQL Server 2008 Developer Edition na mojej stacji roboczej i dwuwęzłowego klastra maszyn wirtualnych w wersji Enterprise Edition, gdzie nazywam „klaster alfa”.

Czas potrzebny na usunięcie wierszy z kolumną varbinary (max) jest bezpośrednio związany z długością danych w tej kolumnie. Na początku może to zabrzmieć intuicyjnie, ale po przeprowadzeniu dochodzenia jest to sprzeczne z moim rozumieniem, w jaki sposób SQL Server faktycznie usuwa wiersze i zajmuje się tego rodzaju danymi.

Problem wynika z problemu przekroczenia limitu czasu (> 30 sekund), który widzimy w naszej aplikacji .NET, ale uprościłem go ze względu na tę dyskusję.

Po usunięciu rekordu SQL Server oznacza go jako ducha, który ma zostać wyczyszczony przez zadanie oczyszczania duchów w późniejszym czasie po zatwierdzeniu transakcji (patrz blog Paula Randala ). W teście usuwającym trzy wiersze z danymi odpowiednio 16 KB, 4 MB i 50 MB w kolumnie varbinary (max) widzę, że dzieje się to na stronie z częścią danych w wierszu, a także w transakcji log.

Dziwne wydaje mi się to, że X blokady są umieszczane na wszystkich stronach danych LOB podczas usuwania, a strony są zwalniane w PFS. Widzę to w dzienniku transakcji, a także za pomocą sp_locki wyników dm_db_index_operational_statsDMV ( page_lock_count).

Tworzy to wąskie gardło We / Wy na mojej stacji roboczej i naszym klastrze alfa, jeśli strony te nie są jeszcze w buforze bufora. W rzeczywistości page_io_latch_wait_in_msz tego samego DMV jest praktycznie cały czas trwania usuwania i page_io_latch_wait_countodpowiada liczbie zablokowanych stron. W przypadku pliku 50 MB na mojej stacji roboczej przekłada się to na ponad 3 sekundy, gdy zaczynam z pustą pamięcią podręczną bufora ( checkpoint/ dbcc dropcleanbuffers), i nie mam wątpliwości, że byłby dłuższy przy dużym fragmentacji i przy dużym obciążeniu.

Starałem się upewnić, że nie zajmuje to tylko miejsca w pamięci podręcznej. Przeczytałem 2 GB danych z innych wierszy przed wykonaniem operacji usuwania zamiast checkpointmetody, co jest więcej niż przydzielono procesowi SQL Server. Nie jestem pewien, czy jest to prawidłowy test, czy nie, ponieważ nie wiem, jak SQL Server przetasowuje dane. Zakładałem, że zawsze wyprze to stare na rzecz nowego.

Co więcej, nawet nie modyfikuje stron. Mogę to zobaczyć dm_os_buffer_descriptors. Strony są czyste po usunięciu, a liczba zmodyfikowanych stron jest mniejsza niż 20 dla wszystkich trzech małych, średnich i dużych operacji usuwania. Porównałem również wyniki DBCC PAGEpróbkowania przeglądanych stron i nie było żadnych zmian (tylko ALLOCATEDbit został usunięty z PFS). To po prostu zwalnia ich.

Aby dodatkowo udowodnić, że przyczyną problemu są odnośniki / zwolnienia strony, wypróbowałem ten sam test, używając kolumny strumienia danych zamiast waniliowego varbinary (maks.). Usunięcia miały stały czas, niezależnie od wielkości LOB.

Najpierw moje akademickie pytania:

1. Dlaczego SQL Server musi wyszukać wszystkie strony danych LOB, aby je zablokować? Czy to tylko szczegół, w jaki sposób zamki są reprezentowane w pamięci (przechowywanej w jakiś sposób ze stroną)? To powoduje, że wpływ wejścia / wyjścia zależy silnie od wielkości danych, jeśli nie jest całkowicie buforowany.
2. Dlaczego X w ogóle się blokuje, żeby je cofnąć? Czy nie wystarczy zablokować tylko liść indeksu za pomocą części w wierszu, ponieważ dealokacja nie musi modyfikować samych stron? Czy istnieje inny sposób uzyskania danych LOB, przed którymi blokuje zamek?
3. Po co w ogóle cofać przydział stron, skoro istnieje już zadanie w tle poświęcone tego rodzaju pracy?

A może ważniejsze jest moje praktyczne pytanie:

• Czy jest jakiś sposób, aby usunięcia działały inaczej? Moim celem jest ciągłe usuwanie czasu bez względu na rozmiar, podobnie jak strumień plików, w którym każde czyszczenie odbywa się w tle po fakcie. Czy to kwestia konfiguracji? Czy dziwnie przechowuję rzeczy?

Oto jak odtworzyć opisany test (wykonywany przez okno zapytania SSMS):

CREATE TABLE [T] (
    [ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [Data] [varbinary](max) NULL
)

DECLARE @SmallID uniqueidentifier
DECLARE @MediumID uniqueidentifier
DECLARE @LargeID uniqueidentifier

SELECT @SmallID = NEWID(), @MediumID = NEWID(), @LargeID = NEWID()
-- May want to keep these IDs somewhere so you can use them in the deletes without var declaration

INSERT INTO [T] VALUES (@SmallID, CAST(REPLICATE(CAST('a' AS varchar(max)), 16 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@MediumID, CAST(REPLICATE(CAST('a' AS varchar(max)), 4 * 1024 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@LargeID, CAST(REPLICATE(CAST('a' AS varchar(max)), 50 * 1024 * 1024) AS varbinary(max)))

-- Do this before test
CHECKPOINT
DBCC DROPCLEANBUFFERS
BEGIN TRAN

-- Do one of these deletes to measure results or profile
DELETE FROM [T] WHERE ID = @SmallID
DELETE FROM [T] WHERE ID = @MediumID
DELETE FROM [T] WHERE ID = @LargeID

-- Do this after test
ROLLBACK

Oto niektóre wyniki profilowania usunięć na mojej stacji roboczej:

| Typ kolumny | Usuń rozmiar | Czas trwania (ms) | Czyta | Pisze | CPU |
-------------------------------------------------- ------------------
| VarBinary | 16 KB | 40 | 13 | 2 | 0 |
| VarBinary | 4 MB | 952 | 2318 | 2 | 0 |
| VarBinary | 50 MB | 2976 | 28594 | 1 | 62 |
-------------------------------------------------- ------------------
| FileStream | 16 KB | 1 | 12 | 1 | 0 |
| FileStream | 4 MB | 0 | 9 | 0 | 0 |
| FileStream | 50 MB | 1 | 9 | 0 | 0 |

Zamiast tego nie możemy po prostu użyć strumienia plików, ponieważ:

1. Nasz rozkład wielkości danych tego nie gwarantuje.
2. W praktyce dodajemy dane w wielu porcjach, a strumień plików nie obsługuje częściowych aktualizacji. Musielibyśmy projektować w oparciu o to.

Aktualizacja 1

Przetestowano teorię, że dane są zapisywane w dzienniku transakcji w ramach operacji usuwania, i wydaje się, że tak nie jest. Czy testuję to niepoprawnie? Patrz poniżej.

SELECT MAX([Current LSN]) FROM fn_dblog(NULL, NULL)
--0000002f:000001d9:0001

BEGIN TRAN
DELETE FROM [T] WHERE ID = @ID

SELECT
    SUM(
        DATALENGTH([RowLog Contents 0]) +
        DATALENGTH([RowLog Contents 1]) +
        DATALENGTH([RowLog Contents 3]) +
        DATALENGTH([RowLog Contents 4])
    ) [RowLog Contents Total],
    SUM(
        DATALENGTH([Log Record])
    ) [Log Record Total]
FROM fn_dblog(NULL, NULL)
WHERE [Current LSN] > '0000002f:000001d9:0001'

W przypadku pliku o rozmiarze większym niż 5 MB plik został zwrócony 1651 | 171860.

Ponadto spodziewałbym się, że same strony będą brudne, jeśli dane zostaną zapisane w dzienniku. Wydaje się, że rejestrowane są tylko dezalokacje, które pasują do tego, co jest brudne po usunięciu.

Aktualizacja 2

Otrzymałem odpowiedź od Paula Randala. Potwierdził fakt, że musi przeczytać wszystkie strony, aby przejść przez drzewo i znaleźć strony do zwolnienia, i stwierdził, że nie ma innego sposobu na sprawdzenie, które strony. To połowa odpowiedzi na pytanie 1 i 2 (choć nie wyjaśnia potrzeby blokowania danych poza wierszem, ale to małe ziemniaki).

Pytanie 3 jest nadal otwarte: Po co cofać przydział stron z góry, jeśli istnieje już zadanie w tle, aby wykonać czyszczenie w celu usunięcia?

I oczywiście najważniejsze pytanie: Czy istnieje sposób na bezpośrednie złagodzenie (tj. Nie obejście) tego zależnego od rozmiaru zachowania usuwania? Myślę, że byłby to częstszy problem, chyba że tak naprawdę jesteśmy jedynymi, którzy przechowują i usuwają 50 MB wierszy w SQL Server? Czy wszyscy inni tam pracują nad jakąś formą wywozu śmieci?

Nie mogę powiedzieć, dlaczego dokładnie byłoby tak nieefektywne usuwanie VARBINARY (MAX) w porównaniu do strumienia plików, ale jeden pomysł, który możesz rozważyć, jeśli próbujesz uniknąć przerw w aplikacji sieciowej podczas usuwania tych LOBS. Można przechowywać wartości VARBINARY (MAX) w osobnej tabeli (nazwijmy to tblLOB), do której odwołuje się oryginalna tabela (nazwijmy to tblParent).

Odtąd po usunięciu rekordu możesz go po prostu usunąć z rekordu nadrzędnego, a następnie od czasu do czasu przeprowadzić proces czyszczenia pamięci, aby przejść i wyczyścić rekordy w tabeli LOB. Podczas tego procesu usuwania śmieci może występować dodatkowa aktywność dysku twardego, ale będzie ona przynajmniej oddzielna od sieci frontonu i może być wykonywana w godzinach poza szczytem.