Czy chętny operator buforowania jest przydatny do tego usuwania z klastrowanego magazynu kolumn?

Testuję usuwanie danych z klastrowego indeksu magazynu kolumn.

Zauważyłem, że w planie wykonania jest duży chętny operator szpuli:

Uzupełnia to następujące cechy:

• Usunięto 60 milionów wierszy
• Zastosowano 1.9 GiB TempDB
• 14 minutowy czas realizacji
• Plan szeregowy
• 1 ponowne wiązanie na szpuli
• Szacowany koszt skanowania: 364,821

Jeśli skłonię estymatora do niedoceniania, otrzymam szybszy plan, który pozwoli uniknąć użycia TempDB:

Szacowany koszt skanowania: 56,901

(Jest to plan szacunkowy, ale liczby w komentarzach są poprawne).

Co ciekawe, szpula znika ponownie, jeśli opróżnię sklepy delta, wykonując następujące czynności:

ALTER INDEX IX_Clustered ON Fact.RecordedMetricsDetail REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Wydaje się, że bufor jest wprowadzany tylko wtedy, gdy w sklepach delta znajduje się więcej niż pewien próg stron.

Aby sprawdzić rozmiar sklepów delta, uruchamiam następujące zapytanie w celu sprawdzenia stron w wierszu dla tabeli:

SELECT  
  SUM([in_row_used_page_count]) AS in_row_used_pages,
  SUM(in_row_data_page_count) AS in_row_data_pages
FROM sys.[dm_db_partition_stats] as pstats
JOIN sys.partitions AS p
ON pstats.partition_id = p.partition_id
WHERE p.[object_id] = OBJECT_ID('Fact.RecordedMetricsDetail');

Czy jest jakaś prawdopodobna korzyść z iteratora szpuli w pierwszym planie? Muszę założyć, że ma to na celu zwiększenie wydajności, a nie ochronę Halloween, ponieważ jego obecność nie jest spójna.

Testuję to na 2016 CTP 3.1, ale widzę to samo zachowanie na 2014 SP1 CU3.

Mam pisał skrypt generujący schematu i danych i poprowadzi Cię przez wykazanie problem tutaj .

Pytanie jest głównie z ciekawości dotyczące zachowania optymalizatora w tym momencie, ponieważ mam obejście problemu, który spowodował pytanie (duża TempDB wypełniona szpulą). Teraz usuwam za pomocą przełączania partycji.

Czy jest jakaś prawdopodobna korzyść z iteratora szpuli w pierwszym planie?

Zależy to od tego, co uważasz za „prawdopodobne”, ale odpowiedź zgodnie z modelem kosztów brzmi „tak”. Oczywiście jest to prawda, ponieważ optymalizator zawsze wybiera najtańszy znaleziony plan.

Prawdziwe pytanie brzmi: dlaczego model kosztów uważa, że ​​plan ze szpulą jest o wiele tańszy niż plan bez niego? Rozważ szacunkowe plany utworzone dla nowej tabeli (ze skryptu) przed dodaniem wierszy do sklepu delta:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE);

Szacunkowy koszt tego planu to ogromne 771,734 jednostek :

Koszt jest prawie w całości związany z usuwaniem indeksu klastrowanego, ponieważ oczekuje się, że usunięcie spowoduje wiele losowych operacji we / wy. To tylko ogólna logika, która dotyczy wszystkich modyfikacji danych. Na przykład zakłada się, że nieuporządkowany zestaw modyfikacji indeksu b-drzewa powoduje w dużej mierze losowe operacje we / wy, co wiąże się z wysokimi kosztami operacji we / wy.

Plany zmieniające dane mogą zawierać opcję Sortuj, aby wyświetlać wiersze w kolejności, która będzie promować dostęp sekwencyjny, właśnie z tych powodów kosztów. W tym przypadku wpływ jest zaostrzony, ponieważ tabela jest podzielona na partycje. W rzeczywistości bardzo podzielony; twój skrypt tworzy 15 000 z nich. Losowe aktualizacje tabeli bardzo podzielonej na partycje są szczególnie kosztowne, ponieważ cena przełączania partycji (zestawów wierszy) w środkowym strumieniu również jest wysoka.

Ostatnim ważnym czynnikiem, który należy wziąć pod uwagę, jest to, że powyższe proste zapytanie o aktualizację (gdzie „aktualizacja” oznacza dowolną operację zmiany danych, w tym usunięcie) kwalifikuje się do optymalizacji zwanej „udostępnianiem zestawu zestawów wierszy”, gdzie ten sam zestaw zestawów wierszy jest używany zarówno do skanowania, jak i aktualizacja tabeli. Plan wykonania nadal pokazuje dwa oddzielne operatory, ale jednak jest używany tylko jeden zestaw wierszy.

Wspominam o tym, ponieważ możliwość zastosowania tej optymalizacji oznacza, że ​​optymalizator przyjmuje ścieżkę kodu, która po prostu nie bierze pod uwagę potencjalnych korzyści z jawnego sortowania w celu zmniejszenia kosztów losowych operacji we / wy. Gdy tabela jest drzewem b, ma to sens, ponieważ struktura jest z natury uporządkowana, więc udostępnianie zestawu wierszy automatycznie zapewnia wszystkie potencjalne korzyści.

Ważną konsekwencją jest to, że logika kalkulacji kosztów dla operatora aktualizacji nie bierze pod uwagę tej korzyści związanej z porządkowaniem (promowanie sekwencyjnego We / Wy lub innych optymalizacji), w przypadku gdy podstawowym obiektem jest magazyn kolumn. Wynika to z faktu, że modyfikacje magazynu kolumn nie są wykonywane w miejscu; używają sklepu delta. Model kosztów odzwierciedla zatem różnicę między aktualizacjami zestawu wierszy współdzielonych w drzewach b w porównaniu z magazynami kolumn.

Niemniej jednak, w szczególnym przypadku (bardzo!) Partycjonowanego magazynu kolumn, może istnieć korzyść z zachowanego porządku, ponieważ wykonywanie wszystkich aktualizacji jednej partycji przed przejściem do następnej może być korzystne z punktu widzenia I / O .

Standardowa logika kosztów jest tutaj ponownie używana dla magazynów kolumn, więc plan, który zachowuje porządkowanie partycji (choć nie kolejność w obrębie każdej partycji), kosztuje mniej. Możemy to zobaczyć w zapytaniu testowym, używając nieudokumentowanej flagi śledzenia 2332, aby wymagać posortowanego wejścia do operatora aktualizacji. To powoduje, że DMLRequestSortwłaściwość ma wartość true podczas aktualizacji i zmusza optymalizator do stworzenia planu, który udostępnia wszystkie wiersze dla jednej partycji przed przejściem do następnej:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332);

Szacunkowy koszt tego planu jest znacznie niższy i wynosi 52,5174 jednostek:

To zmniejszenie kosztów wynika z niższego szacowanego kosztu we / wy podczas aktualizacji. Wprowadzona szpula nie spełnia żadnej użytecznej funkcji, z wyjątkiem tego, że może gwarantować wyjście w kolejności partycji, zgodnie z wymaganiami aktualizacji za pomocą DMLRequestSort = true(skanowanie szeregowe indeksu magazynu kolumn nie może zapewnić tej gwarancji). Koszt samej szpuli jest uważany za stosunkowo niski, zwłaszcza w porównaniu z (prawdopodobnie nierealistycznym) obniżeniem kosztów w trakcie aktualizacji.

Decyzja o tym, czy wymagać uporządkowanych danych wejściowych do operatora aktualizacji, jest podejmowana bardzo wcześnie podczas optymalizacji zapytań. Heurystyki zastosowane w tej decyzji nigdy nie zostały udokumentowane, ale można je ustalić metodą prób i błędów. Wydaje się, że wielkość dowolnych sklepów delta stanowi wkład w tę decyzję. Po dokonaniu wyboru wybór jest stały dla kompilacji zapytań. Żadna USE PLANpodpowiedź się nie powiedzie: cel planu albo zamówił dane wejściowe do aktualizacji, albo nie.

Istnieje inny sposób uzyskania taniego planu dla tego zapytania bez sztucznego ograniczania oszacowania liczności. Wystarczająco niska ocena, aby uniknąć buforowania, prawdopodobnie spowoduje, że DMLRequestSort będzie fałszywy, co spowoduje bardzo wysoki szacowany koszt planu z powodu oczekiwanego losowego wejścia / wyjścia. Alternatywą jest użycie flagi śledzenia 8649 (plan równoległy) w połączeniu z 2332 (DMLRequestSort = true):

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332, QUERYTRACEON 8649);

Wynikiem tego jest plan, który wykorzystuje skanowanie równoległe w trybie partycji dla partycji i utrzymywanie (łączenie) zbierania strumieni wymiany:

W zależności od efektywności zamawiania partycji w czasie rzeczywistym na twoim sprzęcie, może to być najlepsze z trzech. To powiedziawszy, duże modyfikacje nie są świetnym pomysłem w magazynie kolumn, więc pomysł przełączania partycji jest prawie na pewno lepszy. Jeśli poradzisz sobie z długimi czasami kompilacji i dziwacznymi wyborami planów często spotykanymi w przypadku obiektów podzielonych na partycje - zwłaszcza gdy liczba partycji jest duża.

Łączenie wielu stosunkowo nowych funkcji, szczególnie zbliżonych do ich limitów, to świetny sposób na uzyskanie złych planów wykonania. Głębokość obsługi optymalizatora z czasem się poprawia, ale użycie 15 000 partycji magazynu kolumn prawdopodobnie zawsze oznacza, że ​​żyjesz w interesujących czasach.