Jak zapobiegać zakleszczeniom w partycjonowaniu magazynu kolumn w systemie SELECT

Mam trzy tabele Clustered Columnstore Index (CCI) w SQL Server 2016. Wszystkie te CCI są w tym samym schemacie partycjonowania, w oparciu o identyfikator dzierżawcy. Ostatnio i niekonsekwentnie dostaję się do impasu prostych instrukcji select od złączeń do tych tabel. Przykładowe zapytanie zakleszczone:

SELECT  TOP 33 r.tenantid
FROM    Table_r r
        INNER JOIN Table_cm cm ON r.MyKey=cm.MyKey 
        INNER JOIN Table_pe pe ON r.MyKey=pe.MyKey 
WHERE   r.TenantId = 69
        AND pe.TenantId = 69
        AND cm.TenantId = 69

Komunikat o błędzie:

Transakcja (ID procesu 56) została zakleszczona na ogólnych zasobach obiektów oczekujących na inny proces i została wybrana jako ofiara zakleszczenia. Uruchom ponownie transakcję.

Wskazówki:

• Jeśli zapytanie używa innego indeksu oprócz CCI, nie jest zakleszczone.
• Jeśli usunę dwa z trzech filtrów dzierżawcy, nie spowoduje to impasu.
• Jeśli wybiorę top 32 lub niższą, nie zostanie zakleszczony.
• Jeśli dodam OPCJĘ (MAXDOP 1), nie spowoduje to zakleszczenia.
• Mogę repro to w mojej zaszyfrowanej replice PROD, PROD READ-TYLKO wtórne i PROD samej.
• Nie mogę zarzucić tego zachowania w DEV lub INT.
• Nadal blokuje się, jeśli dodam Z (NOLOCK) do wszystkich 3 połączeń stolików
• Zapytanie samo się blokuje. Zakleszczy się, gdy nie będzie żadnych innych aktywnych procesów.
• Plany zapytań bez równoległości nie blokują się

Zakleszczenie xml tutaj

Nasza wersja PROD:

Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64) 10 stycznia 2019 18:51:38 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows Server 2012 R2 Standard 6.3 (kompilacja 9600 :) (Hypervisor)

Jak zapobiec zakleszczeniom tego zapytania?

Ponieważ korzystasz z programu SQL Server 2016, warto wspomnieć, że istnieje co najmniej jedna publiczna poprawka błędu dla równoległych zakleszczeń obejmujących indeksy magazynu kolumn:

POPRAWKA: Zakleszczenie występuje podczas uruchamiania równoległego zapytania w klastrowanym indeksie magazynu kolumn w SQL Server 2016 i 2017

(dzięki Denisowi Rubashkinowi za początkowe udostępnienie linku)

Zostało to wydane jako część SP1 CU7. Jeśli nie jesteś do tego CU, powinieneś spróbować. Ta poprawka byłaby również zawarta w dodatku SP2 (dowolnej jednostce sterującej).

Zasadniczo dwa podejścia do ustalania zakleszczeń równoległości w obrębie zapytania:

• równoległość unikać (o tuningu kwerendę tak, że nie idzie równolegle, przy użyciu MAXDOPwskazówkę, itp) - jest to uwzględnione w innej odpowiedzi przez Thomas Costers
• zastosuj najnowszy dodatek service pack / aktualizacje zbiorcze do SQL Server

Czy sprawdziłeś następujący blog na temat zakleszczeń wątków równoległych w obrębie zapytania

SyncPointZasobów wskazuje na wykorzystanie zdarzenia wymiany jeśli nie jestem pomylić.
Patrząc na uczestników impasu, widać, że wszyscy pochodzą z tego samego spid (55) i partii (0), ale używają różnych wątków. Oznacza to, że wszystkie są częścią tego samego zapytania równoległego i potwierdza to fakt, że nie pojawia się żaden zakleszczenie przy każdym uruchomieniu zapytania MAXDOP 1. W przypadku zakleszczeń równoległych wątków wewnątrz zapytania, wątki pojedynczego zapytania zakończą się wzajemnym zakleszczeniem oczekując na obiekty synchronizacji, w twoim przypadku SyncPoints.

Ostatnim razem, gdy byłem świadkiem tego rodzaju zachowania, byłem w stanie dalej optymalizować zapytanie, uniemożliwiając w ten sposób korzystanie z równoległego planu wykonania. Podejrzewam, że zrobiłeś to samo, ograniczając swój zestaw wyników do 32 rekordów lub używając innego indeksu.
Inną opcją byłoby dodanie MAXDOP 1do zapytania, choć nie jest wielkim fanem tej opcji.

Ale zanim zaczniesz majstrować przy tych dwóch opcjach, najpierw sprawdź, czy korzystasz z najnowszej wersji SP / CU.