Diagnozowanie i rozwiązywanie problemów z zatrzaśnianiem w programie SQL Server

W tym przewodniku opisano, jak identyfikować i rozwiązywać problemy z konkurencją o blokady zaobserwowane podczas uruchamiania aplikacji programu SQL Server w systemach o wysokiej współbieżności z określonymi obciążeniami.

Wraz ze wzrostem liczby rdzeni procesora CPU na serwerach, towarzyszący wzrost współbieżności może wprowadzać punkty konfliktu o struktury danych, które muszą być dostępne w sposób seryjny w silniku bazy danych. Jest to szczególnie istotne w przypadku obciążeń przetwarzania transakcji o wysokiej przepływności/wysokiej współbieżności (OLTP). Istnieje kilka narzędzi, technik i sposobów podejścia do tych wyzwań, a także praktyk, które można stosować w projektowaniu aplikacji, które mogą pomóc w całkowitej ich unikaniu. W tym artykule omówiono konkretny typ rywalizacji o struktury danych, które używają spinlocków do serializacji dostępu do tych struktur danych.

Co to jest rywalizacja o zatrzasanie programu SQL Server?

Zatrzaśnięcia są lekkimi elementami podstawowymi synchronizacji, które są używane przez aparat programu SQL Server w celu zagwarantowania spójności struktur w pamięci, w tym indeksów, stron danych i struktur wewnętrznych, takich jak strony inne niż liścia w drzewie B. Program SQL Server używa zatrzaśnięć buforów do ochrony stron w puli buforów i zatrzaśnięć operacji we/wy w celu ochrony stron, które nie zostały jeszcze załadowane do puli buforów. Za każdym razem, gdy dane są zapisywane lub odczytywane ze strony w buforowej puli SQL Server, wątek roboczy musi najpierw uzyskać zatrzask buforu dla strony. Istnieją dostępne różne typy zatrzasków bufora do uzyskiwania dostępu do stron w puli buforów, w tym zatrzask wyłączny (PAGELATCH_EX) i zatrzask udostępniony (PAGELATCH_SH). Gdy program SQL Server próbuje uzyskać dostęp do strony, która nie jest jeszcze obecna w puli buforów, asynchroniczna operacja we/wy jest zlecana w celu załadowania strony do puli buforów. Jeśli SQL Server musi poczekać na odpowiedź podsystemu I/O, czeka na wyłączną (PAGEIOLATCH_EX) lub dzieloną (PAGEIOLATCH_SH) zatrzask I/O w zależności od typu żądania; jest to wykonywane w celu uniemożliwienia innemu wątkowi roboczemu załadowania tej samej strony do puli z niezgodnym zatrzaskiem. Zatrzaski są również używane do ochrony dostępu do wewnętrznych struktur pamięci, innych niż strony puli buforowej; są one nazywane zatrzaskami Non-Buffer.

Rywalizacja o zatrzaski stron jest najbardziej typowym scenariuszem napotkanym w systemach z wieloma procesorami CPU, dlatego większość tego artykułu koncentruje się na tych problemach.

Spór o zatrzaski występuje, gdy wiele wątków równocześnie podejmuje próbę uzyskania niekompatybilnych zatrzasków do tej samej struktury w pamięci. Zatrzask jest to mechanizm kontroli wewnętrznej, dlatego silnik SQL automatycznie określa, kiedy je stosować. Ponieważ zachowanie zatrzasków jest deterministyczne, decyzje aplikacji, w tym projekt schematu, mogą mieć wpływ na to zachowanie. Ten artykuł ma na celu dostarczenie następujących informacji:

• Podstawowe informacje na temat sposobu użycia zatrzaśnięć przez program SQL Server.
• Narzędzia używane do badania rywalizacji o zatrzasanie.
• Jak stwierdzić, czy obserwowany poziom konkurencji jest problematyczny.

Omawiamy niektóre typowe scenariusze i jak najlepiej je obsłużyć, aby złagodzić rywalizację.

Jak program SQL Server używa zatrzasków?

Strona w programie SQL Server to 8 KB i może przechowywać wiele wierszy. Aby zwiększyć współbieżność i wydajność, zatrzaski buforu są utrzymywane tylko przez czas trwania operacji fizycznej na stronie, w przeciwieństwie do blokad, które są utrzymywane przez czas trwania operacji logicznej.

Zatrzaski są wewnętrzne dla silnika SQL i są używane do zapewnienia spójności pamięci, podczas gdy blokady są używane przez SQL Server w celu zapewnienia logicznej spójności transakcyjnej. W poniższej tabeli porównaliśmy zatrzaski do blokad:

Tryby blokad programu SQL Server i zgodność

Oczekuje się pewnego stopnia blokad jako normalnej części działania silnika SQL Server. Nieuniknione jest, że wiele współbieżnych żądań zatrzaśnięcia o różnej zgodności występuje w systemie o wysokiej współbieżności. Program SQL Server wymusza zgodność zatrzasków, wymagając, aby niezgodne żądania zatrzasków czekały w kolejce, aż zostaną ukończone bieżące żądania zatrzasków.

Zatrzaski są uzyskiwane w jednym z pięciu różnych trybów, które odnoszą się do poziomu dostępu. Tryby blokad programu SQL Server można podsumować w następujący sposób:

• KP: Zachowaj zatrzask. Gwarantuje, że nie można zniszczyć przywoływanej struktury. Używane, gdy wątek chce przyjrzeć się strukturze buforu. Ponieważ zatrzask KP jest zgodny ze wszystkimi zatrzaskami, z wyjątkiem zatrzasku niszczącego (DT), uważa się, że zatrzask KP jest lekki, co oznacza, że jego używanie ma minimalny wpływ na wydajność. Ponieważ zatrzask KP jest niezgodny z zatrzaskiem DT, zapobiega niszczeniu struktury przez jakikolwiek inny wątek, do którego się odnosi. Na przykład zatrzask KP zapobiega zniszczeniu struktury, do której się odnosi, przez proces leniwego zapisywania. Aby uzyskać więcej informacji na temat sposobu, w jaki proces zapisu leniwego jest używany przy zarządzaniu stronami bufora SQL Server, patrz Zapisywanie stron w aparacie bazy danych.

• SH: współdzielony zatrzask. Wymagane do odczytania odwołanej struktury (na przykład odczytu strony danych). Wiele wątków może jednocześnie uzyskać dostęp do zasobu do odczytu przy użyciu współdzielonej blokady.

• UP: Zatrzask aktualizacji. Zgodny z SH (zatrzaskiem udostępnionym) i KP, ale nie z żadnym innym, i dlatego nie pozwala zatrzaskowi EX na zapis w odniesionej strukturze.

• EX: Zatrzasek wyłączny. Blokuje inne wątki przed zapisywaniem do lub odczytywaniem ze struktury, do której się odwołuje. Jednym z przykładów użycia jest zmodyfikowanie zawartości strony w celu ochrony przed rozdarty stroną.

• DT: Zniszcz zatrzasek. Należy uzyskać przed zniszczeniem zawartości powiązanej struktury. Na przykład zatrzask DT musi zostać uzyskany przez proces leniwego zapisywania, aby zwolnić czystą stronę przed dodaniem jej do listy dostępnych wolnych buforów, które mogą być używane przez inne wątki.

Tryby zatrzaśnięć mają różne poziomy zgodności, na przykład współdzielony zatrzask (SH) jest zgodny z zatrzaskiem aktualizacji (UP) lub zatrzaskiem (KP), ale niezgodny z zatrzaskiem niszczenia (DT). Wiele zatrzasków można uzyskać jednocześnie w tej samej strukturze, o ile zatrzaski są zgodne. Gdy wątek próbuje uzyskać blokadę utrzymywaną w trybie, który nie jest zgodny, jest umieszczany w kolejce, aby czekać na sygnał wskazujący, że zasób jest dostępny. Spinlock typu SOS_Task służy do ochrony kolejki oczekiwania poprzez wymuszanie serializowanego dostępu do kolejki. Ten spinlock należy uzyskać, aby dodać elementy do kolejki. SOS_Task spinlock sygnalizuje również wątki w kolejce, gdy niezgodne zatrzaski są zwalniane, pozwalając wątkom oczekującym na uzyskanie zgodnego zatrzasku i kontynuują pracę. Kolejka oczekiwania jest przetwarzana na zasadzie "pierwsze weszło, pierwsze wyszło" (FIFO) w miarę zwalniania żądań blokady. Zatrzaski są zgodne z tym systemem FIFO, aby zapewnić uczciwość i zapobiec zagłodzeniu wątków.

Zgodność trybu zatrzaśnięć jest wymieniona w poniższej tabeli (Tak wskazuje zgodność i Nie wskazuje niezgodność):

Superlatches i podlatches programu SQL Server

Wraz z rosnącą obecnością systemów wielogniazdowych i wielordzeniowych opartych na architekturze NUMA, w programie SQL Server 2005 wprowadzono superzatrzaski, znane również jako zatrzaski podrzędne, które są skuteczne tylko w systemach z 32 lub więcej procesorów logicznych. Superlatches poprawiają wydajność silnika SQL dla niektórych wzorców użycia w wysoce współbieżnych obciążeniach OLTP; na przykład, gdy niektóre strony mają wzorzec dużego dostępu tylko do odczytu (SH), ale są rzadko zapisywane. Przykładem strony z takim wzorcem dostępu jest strona główna drzewa B (czyli indeksu); Aparat SQL wymaga, aby współdzielony zatrzask był trzymany na stronie głównej drzewa B, gdy podział strony występuje na dowolnym poziomie drzewa B. W obciążeniu OLTP z dużą liczbą operacji wstawiania i dużą współbieżnością liczba podziałów stron znacznie wzrasta wraz z przepustowością, co może obniżyć wydajność. Superlatches może umożliwić zwiększenie wydajności dostępu do udostępnionych stron, w których wiele równoczesnych wątków roboczych wymaga SH zatrzasków. Aby to osiągnąć, silnik SQL Server dynamicznie promuje zatrzask na takiej stronie do superzatrzasku. Superlatch partycjonuje pojedynczy zatrzask w tablicę struktur podzatrzasków, po jednym na partycję na rdzeń procesora, gdzie główny zatrzask staje się serwerem proxy do przekierowania i nie jest wymagana globalna synchronizacja stanu dla zatrzasków tylko do odczytu. W ten sposób proces roboczy, który jest zawsze przypisany do określonego procesora CPU, musi uzyskać tylko udostępniony (SH) podlatek przypisany do lokalnego harmonogramu.

Pozyskiwanie zgodnych zatrzasków, takich jak współdzielona Superlatch, zużywa mniej zasobów i lepiej skalują dostęp do gorących stron niż zatrzask współdzielony bez partycjonowania, ponieważ usunięcie globalnego wymogu synchronizacji stanu znacznie poprawia wydajność, umożliwiając dostęp wyłącznie do lokalnej pamięci NUMA. Z drugiej strony uzyskanie wyłącznego (EX) Superlatch jest droższe niż uzyskanie zwykłego zatrzasku EX, ponieważ SQL musi sygnalizować we wszystkich podzatrzaskach. Gdy Superlatch wykazuje wzorzec intensywnego EX dostępu, aparat SQL może zmniejszyć jego priorytet po usunięciu strony z puli buforowej. Na poniższym diagramie przedstawiono normalny zatrzask i partycjonowany Superlatch.

Użyj obiektu SQL Server:Latches i powiązanych liczników w Monitorze wydajności, aby zebrać informacje o Superlatch, w tym ich liczbie, liczbie promocji Superlatch na sekundę oraz liczbie degradacji Superlatch na sekundę. Aby uzyskać więcej informacji na temat obiektu SQL Server:Latches i skojarzonych liczników, zobacz SQL Server, Latches object.

Rodzaje oczekiwania mechanizmów blokady

Zgromadzone informacje o czasie oczekiwania są śledzone przez program SQL Server i można je uzyskać, korzystając z widoku dynamicznego zarządzania (DMW). sys.dm_os_wait_stats SQL Server wykorzystuje trzy typy oczekiwania z synchronizacją określone przez odpowiadające im wait_type w widoku DMV sys.dm_os_wait_stats.

• Zatrzask buforu (BUF): służy do zagwarantowania spójności indeksów i stron danych dla obiektów użytkownika. Są one również używane do ochrony dostępu do stron danych używanych przez program SQL Server do obsługi obiektów systemowych. Na przykład strony, które zarządzają alokacjami, są chronione zatrzaśnięciami buforu. Obejmują one strony Strony Wolnego Miejsca (PFS), Globalnej Mapy Alokacji (GAM), Udostępnionej Globalnej Mapy Alokacji (SGAM) oraz Strony Mapy Alokacji Indeksu (IAM). Zatrzaski buforu są zgłaszane w sys.dm_os_wait_stats z wait_typePAGELATCH_*.

• Zatrzask bez buforu (NON-BUF): służy do zagwarantowania spójności wszelkich struktur w pamięci innych niż strony puli buforowej. Wszystkie oczekiwania na zatrzaski niebuforowe są zgłaszane jako wait_typeLATCH_*.

• Zatrzask we/wy: podzbiór zatrzasków buforu, który gwarantuje spójność tych samych struktur chronionych zatrzaskami buforu, gdy te struktury wymagają ładowania do puli za pomocą operacji we/wy. Zatrzaski I/O uniemożliwiają załadowanie przez inny wątek tej samej strony do puli bufora przy użyciu niezgodnego zatrzasku. Skojarzone z elementem z wartością wait_typePAGEIOLATCH_*.

  Uwaga / Notatka

  Jeśli widzisz istotne PAGEIOLATCH opóźnienia, oznacza to, że program SQL Server czeka na podsystem WE/WY. Chociaż pewna ilość PAGEIOLATCH oczekiwania jest oczekiwana i jest normalnym zachowaniem, jeśli średni PAGEIOLATCH czas oczekiwania stale przekracza 10 milisekund (ms), należy zbadać, dlaczego podsystem we/wy jest pod presją.

Jeśli podczas badania dynamicznego widoku zarządzania sys.dm_os_wait_stats napotkasz zatrzaski niebuforowe, konieczne jest zbadanie sys.dm_os_latch_stats, aby uzyskać szczegółowy podział skumulowanych informacji o oczekiwaniu dla zatrzasków niebuforowych. Wszystkie oczekiwania zatrzaśnięć buforu są klasyfikowane w BUFFER klasie zatrzasku, a pozostałe są używane do klasyfikowania zatrzasków innych niż bufor.

Objawy i przyczyny rywalizacji o zatrzasanie programu SQL Server

W przypadku obciążonego systemu wysokiej współbieżności normalne jest, aby zaobserwować aktywną konkurencję o dostęp do struktur, które są często używane i chronione przez zamyki oraz inne mechanizmy kontroli w programie SQL Server. Jest to uważane za problematyczne, gdy rywalizacja i czas oczekiwania związane z uzyskaniem zatrzaśnięcia strony wystarczająco zmniejszają wykorzystanie zasobów (CPU), co ogranicza przepustowość.

Przykład konfliktu o zatrzask

Na poniższym diagramie niebieska linia reprezentuje wydajność w programie SQL Server mierzoną według transakcji na sekundę; czarna linia reprezentuje średni czas oczekiwania na zatrzaśnięcie strony. W takim przypadku każda transakcja wykonuje operację INSERT na indeksie zgrupowanym z sekwencyjnie rosnącą wartością początkową, na przykład podczas wypełniania kolumny typu danych bigint. W miarę wzrostu liczby CPU do 32, widać, że ogólna przepustowość zmniejszyła się, a czas oczekiwania na zatrzaśnięcia strony wzrósł do około 48 milisekund, co pokazuje czarna linia. Ta odwrotna relacja między przepustowością a czasem oczekiwania na blokadę strony jest typowym scenariuszem, który można łatwo zdiagnozować.

Wydajność po rozwiązaniu rywalizacji o zatrzaśnięcie

Jak pokazano na poniższym diagramie, program SQL Server nie jest już dekoltowany na zatrzaśniętych stronach, a przepływność zwiększa się o 300% mierzone przez transakcje na sekundę. Wykonano to za pomocą metody Use Hash Partitioning with a Computed Column (Używanie partycjonowania skrótów z obliczoną kolumną) opisaną w dalszej części tego artykułu. Ta poprawa wydajności jest skierowana do systemów o dużej liczbie rdzeni i wysokiego poziomu współbieżności.

Czynniki wpływające na rywalizację o blokadę

Rywalizacja o opóźnienie, która utrudnia wydajność w środowiskach OLTP, jest zwykle spowodowana wysoką współbieżnością związaną z co najmniej jednym z następujących czynników:

Diagnozowanie kontencji blokad w SQL Server

Ta sekcja zawiera informacje na temat diagnozowania rywalizacji o zatrzasanie programu SQL Server w celu określenia, czy jest to problematyczne dla danego środowiska.

Narzędzia i metody diagnozowania rywalizacji o zasoby latchów

Podstawowe narzędzia używane do diagnozowania zawieszenia zatrzasków to:

• Monitor wydajności do monitorowania wykorzystania procesora CPU i czasów oczekiwania w programie SQL Server i ustalenia, czy istnieje relacja między użyciem procesora CPU i czasami oczekiwania zatrzaśnięć.

• Dynamiczne widoki zarządzania programu SQL Server, których można użyć do określenia określonego typu zatrzaśnięć powodującego problem i zasobu, którego dotyczy problem.

• W niektórych przypadkach zrzuty pamięci procesu programu SQL Server muszą zostać uzyskane i przeanalizowane za pomocą narzędzi do debugowania systemu Windows.

Proces techniczny diagnozowania rywalizacji o zasoby zatrzasków można streścić w następujących krokach:

1. Ustal, czy istnieją konflikty, które mogą być związane z zatrzaskiem.

2. Użyj widoków DMV podanych w dodatku: Skrypty rywalizacji o zatrzask programu SQL Server , aby określić typ zatrzaśnięć i zasobów, których dotyczy problem.

3. Złagodzić rywalizację o blokady przy użyciu jednej z technik opisanych w Obsługa rywalizacji o blokady dla różnych wzorców tabeli.

Wskaźniki rywalizacji o zatrzasanie

Jak wspomniano wcześniej, konflikt o zatrzaski jest problematyczny tylko wtedy, gdy czas oczekiwania na uzyskanie zatrzasków strony powoduje, że przepływność nie może wzrosnąć, mimo że zasoby CPU są dostępne. Określenie akceptowalnej ilości rywalizacji wymaga całościowego podejścia, które uwzględnia wymagania dotyczące wydajności i przepływności wraz z dostępnymi zasobami we/wy i procesora CPU. W tej sekcji opisano, jak określić wpływ rywalizacji o blokady na obciążenie, w następujący sposób:

1. Mierzenie ogólnych czasów oczekiwania podczas reprezentatywnego testu.
2. Klasyfikuj je w kolejności.
3. Określ proporcję czasów oczekiwania, które są związane z zatrzaskami.

Skumulowane informacje dotyczące oczekiwania są dostępne z sys.dm_os_wait_stats oddziału DMV. Najczęstszym typem rywalizacji o zatrzasanie buforu jest rywalizacja o zatrzasanie buforu, obserwowane jako wzrost czasów oczekiwania na zatrzasania z wartością wait_typePAGELATCH_*. Zatrzaski niebuforowe są pogrupowane pod typem LATCH* oczekiwania. Jak pokazano na poniższym diagramie, najpierw należy przyjrzeć się skumulowanym oczekiwaniom systemowym przy użyciu sys.dm_os_wait_stats dynamicznego widoku zarządzania, aby określić procent ogólnego czasu oczekiwania spowodowanego zatrzymaniami buforu lub innymi zatrzymaniami. W przypadku napotkania zatrzasków bezbuforowych, należy również zbadać sys.dm_os_latch_stats, czyli dynamiczny widok zarządzania.

Na poniższym diagramie opisano relację między informacjami zwracanymi przez sys.dm_os_wait_stats i sys.dm_os_latch_stats DMV.

Aby uzyskać więcej informacji na temat widoku dynamicznego zarządzania (DMV), zobacz sys.dm_os_wait_stats w dokumentacji SQL Server.

Aby uzyskać więcej informacji na temat sys.dm_os_latch_stats DMV, zobacz sys.dm_os_latch_stats w pomocy programu SQL Server.

Następujące miary czasu oczekiwania na zatrzask są wskaźnikami, że nadmierna rywalizacja o zatrzask wpływa na wydajność aplikacji:

• Średni czas oczekiwania na zatrzask strony stale wzrasta z przepustowością: Jeśli średni czas oczekiwania na zatrzask strony stale wzrasta wraz z przepustowością, a średni czas oczekiwania na zatrzask buforu również zwiększa się powyżej oczekiwanych czasów odpowiedzi dysku, należy zbadać bieżące zadania oczekujące przy użyciu dynamicznego sys.dm_os_waiting_tasks widoku zarządzania. Średnie mogą być mylące, jeśli są analizowane w oderwaniu, dlatego ważne jest, aby przyjrzeć się systemowi na żywo, gdy jest to możliwe, aby zrozumieć cechy obciążenia. W szczególności sprawdź, czy na jakichkolwiek stronach występują długie oczekiwanie na PAGELATCH_EX i/lub PAGELATCH_SH żądania. Wykonaj następujące kroki, aby zdiagnozować rosnące średnie czasy oczekiwania na blokady strony z przepustowością:

  • Użyj przykładowych skryptów Zapytanie sys.dm_os_waiting_tasks uporządkowane według identyfikatora sesji lub oblicz oczekiwania w danym okresie , aby przyjrzeć się bieżącym zadaniam oczekującym i zmierzyć średni czas oczekiwania na zatrzaśnięcie.

  • Użyj przykładowych skryptów deskryptorów buforu zapytań, aby określić obiekty powodujące rywalizację o zatrzaśnienie , aby określić indeks i tabelę bazową, na której występuje rywalizacja.

  • Mierzenie średniego czasu oczekiwania zatrzaśnięć strony za pomocą licznika monitora wydajności MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time lub przez uruchomienie dynamicznego widoku zarządzania sys.dm_os_wait_stats.

  Uwaga / Notatka

  Aby obliczyć średni czas oczekiwania dla określonego typu oczekiwania (zwracany przez sys.dm_os_wait_stats jako wt_:type), podziel całkowity czas oczekiwania (zwrócony jako wait_time_ms) przez liczbę zadań oczekujących (zwróconych jako waiting_tasks_count).

• Procent całkowitego czasu oczekiwania na typy blokad zatrzaskowych podczas szczytowego obciążenia: Jeśli średni czas oczekiwania na zatrzask jako procent ogólnego czasu oczekiwania wzrasta w miarę wzrostu obciążenia aplikacji, rywalizacja o dostęp do zatrzasków może mieć wpływ na wydajność i powinna zostać zbadana.

  Mierz oczekiwania na zatrzaśnięcie stron i oczekiwania na zatrzaśnięcie nie-stron przy użyciu liczników wydajności obiektu Wait Statistics programu SQL Server. Następnie porównaj wartości tych liczników wydajności z licznikami wydajności skojarzonymi z procesorem CPU, we/wy, pamięcią i przepływnością sieci. Na przykład transakcje na sekundę i żądania wsadowe na sekundę to dwie dobre miary wykorzystania zasobów.

  Uwaga / Notatka

  Względny czas oczekiwania dla każdego typu oczekiwania nie jest uwzględniony w sys.dm_os_wait_stats DMV, ponieważ ten DMV mierzy czas oczekiwania od czasu ostatniego uruchomienia wystąpienia programu SQL Server lub skumulowane statystyki oczekiwania zostały zresetowane przy użyciu polecenia DBCC SQLPERF. Aby obliczyć względny czas oczekiwania dla każdego typu oczekiwania, wykonaj migawkę sys.dm_os_wait_stats przed szczytowym obciążeniem, po obciążeniu szczytowym, a następnie oblicz różnicę. W tym celu można użyć przykładowego skryptu Obliczanie oczekiwań w okresie.

  W przypadku tylko środowiska nieprodukcyjnego wyczyść sys.dm_os_wait_stats widok DMV za pomocą następującego polecenia:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Aby wyczyścić DMV, należy uruchomić podobne polecenie.

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Przepływność nie zwiększa się, a w niektórych przypadkach zmniejsza się wraz ze wzrostem obciążenia aplikacji i wraz ze wzrostem liczby procesorów dostępnych dla SQL Server: pokazano to w Przykładzie konkurencji o zasoby zatrzaskowe.

• Wykorzystanie procesora nie zwiększa się w miarę wzrostu obciążenia aplikacji: jeśli wykorzystanie procesora w systemie nie zwiększa się w miarę wzrostu współbieżności spowodowanej przepustowością aplikacji, jest to wskaźnik, że program SQL Server czeka na coś i jest symptomem przeciążenia opóźnieniami.

Analizowanie głównej przyczyny. Nawet jeśli każdy z powyższych warunków jest spełniony, nadal jest możliwe, że główna przyczyna problemów z wydajnością leży gdzie indziej. W rzeczywistości w większości przypadków nieoptymalne wykorzystanie procesora jest spowodowane przez inne typy oczekiwań, takie jak blokowanie w przypadku zamków, oczekiwania związane z I/O lub problemy sieciowe. Zasadą ogólną jest, że należy najpierw usunąć oczekiwanie na zasób, który stanowi największą część ogólnego czasu oczekiwania, zanim przystąpimy do bardziej szczegółowej analizy.

Analizuj bieżące zatrzaski buforu oczekiwania

Zawiski na zatrzaski buforów objawiają się wzrostem czasów oczekiwania na zatrzaski z wait_type lub PAGELATCH_* albo PAGEIOLATCH_*, jak pokazano w widoku DMV sys.dm_os_wait_stats. Aby przyjrzeć się systemowi w czasie rzeczywistym, uruchom następujące zapytanie w systemie, aby dołączyć do widoków DMV sys.dm_os_wait_stats, sys.dm_exec_sessions i sys.dm_exec_requests. Wyniki mogą służyć do określania bieżącego typu oczekiwania dla sesji wykonywanych na serwerze.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Statystyki uwidocznione przez to zapytanie są opisane w następujący sposób:

Następujące zapytanie zwraca informacje dotyczące wszystkich zatrzasków niebuforowych:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Statystyki uwidocznione przez to zapytanie są opisane w następujący sposób:

Wartości zwracane przez ten dynamiczny widok zarządzania są skumulowane od czasu ostatniego ponownego uruchomienia aparatu bazy danych lub zresetowania dynamicznego widoku zarządzania. Użyj kolumny sqlserver_start_time w sys.dm_os_sys_info , aby znaleźć ostatni czas uruchamiania aparatu bazy danych. W systemie, który działa długo, oznacza to, że niektóre statystyki, takie jak max_wait_time_ms rzadko są przydatne. Następujące polecenie może służyć do zresetowania statystyk oczekiwania dla tego widoku zarządzania dynamicznego:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

Scenariusze rywalizacji o zatrzasanie programu SQL Server

Zaobserwowano, że następujące scenariusze powodują nadmierne zatrzaskiwanie.

Ostatnia strona/końcowa strona wstawiania rywalizacji

Typowym rozwiązaniem OLTP jest utworzenie indeksu klastrowanego w kolumnie tożsamości lub daty. Pomaga to utrzymać dobrą organizację fizyczną indeksu, co może znacznie przynieść korzyści zarówno podczas odczytywania, jak i zapisywania danych w indeksie. Ten schemat może jednak nieumyślnie prowadzić do konfliktu zatrzasków. Ten problem występuje najczęściej w przypadku dużej tabeli z małymi wierszami, gdy dane są wstawiane do indeksu zawierającego sekwencyjnie rosnącą kolumnę klucza wiodącego, taką jak rosnąca liczba całkowita lub klucz oparty na dacie/czasie. W tym scenariuszu aplikacja rzadko, jeśli kiedykolwiek wykonuje aktualizacje lub usuwa, wyjątek dotyczy operacji archiwizacji.

W poniższym przykładzie wątek jeden i wątek drugi chcą wykonać wstawianie rekordu, który będzie przechowywany na stronie 299. Z punktu widzenia blokady logicznej nie ma problemu, ponieważ są używane blokady na poziomie wiersza, a blokady na wyłączność na obu rekordach na tej samej stronie mogą być przechowywane w tym samym czasie. Jednak w celu zapewnienia integralności pamięci fizycznej tylko jeden wątek jednocześnie może uzyskać wyłączny zatrzask, dzięki czemu dostęp do strony jest serializowany, aby zapobiec utracie aktualizacji w pamięci. W tym przypadku wątek 1 uzyskuje wyłączną blokadę, a wątek 2 czeka, co rejestruje oczekiwanie na ten zasób w statystykach oczekiwania. To jest wyświetlane za pośrednictwem wartości wait_type w sys.dm_os_waiting_tasks DMV.

Ta rywalizacja jest często nazywana rywalizacją "Wstaw ostatnią stronę", ponieważ występuje na prawej krawędzi drzewa B, jak pokazano na poniższym diagramie:

Ten typ rywalizacji o zatrzasanie można wyjaśnić w następujący sposób. Po wstawieniu nowego wiersza do indeksu program SQL Server używa następującego algorytmu do wykonania modyfikacji:

1. Przejdź przez drzewo B, aby znaleźć odpowiednią stronę do przechowywania nowego rekordu.

2. Zatrzaśnij stronę używając PAGELATCH_EX, uniemożliwiając innym modyfikowanie jej, i uzyskaj współdzielone zatrzaski (PAGELATCH_SH) na wszystkich stronach niebędących stronami liści.

   Uwaga / Notatka

   W niektórych przypadkach SQL Engine wymaga również uzyskania zatrzasków na nieliściastych stronach drzewa B. Na przykład w przypadku podziału strony w bazie danych wszystkie strony, których to bezpośrednio dotyczy, muszą być zablokowane wyłącznie (PAGELATCH_EX).

3. Zarejestruj wpis w dzienniku, że wiersz został zmodyfikowany.

4. Dodaj wiersz do strony i oznacz stronę jako zanieczyszczoną.

5. Odblokuj wszystkie strony.

Jeśli indeks tabeli jest oparty na sekwencyjnie rosnącym kluczu, każde nowe wstawianie przechodzi do tej samej strony na końcu drzewa B, aż ta strona będzie pełna. W scenariuszach o wysokiej współbieżności może to spowodować rywalizację na najbardziej prawej krawędzi drzewa B i może wystąpić w indeksach klastrowanych i nieklastrowanych. Tabele, których dotyczy ten typ rywalizacji, głównie akceptują zapytania INSERT, a strony problematycznych indeksów są zwykle stosunkowo gęste (na przykład rozmiar wiersza ok. 165 bajtów (w tym narzut wiersza) równoważy ok. 49 wierszy na stronę). W tym przykładzie z dużym obciążeniem wstawiania oczekujemy PAGELATCH_EX/PAGELATCH_SH , że wystąpią oczekiwania i jest to typowa obserwacja. Aby zbadać oczekiwania na zatrzaski stron w porównaniu z oczekiwaniami na zatrzaski stron drzewa, użyj widoku zarządzania sys.dm_db_index_operational_stats.

W poniższej tabeli podsumowano główne przyczyny obserwowane w przypadku tego typu zawodzenia na zatrzasku.

Rywalizacja o zatrzasanie w małych tabelach z indeksem nieklasowanym i losowymi wstawkami (tabela kolejki)

Ten scenariusz jest zwykle spotykany, gdy tabela SQL jest używana jako kolejka tymczasowa (na przykład w asynchronicznym systemie obsługi komunikatów).

W tym scenariuszu rywalizacja o ekskluzywny zatrzask (EX) i udostępniony zatrzask (SH) może wystąpić w następujących warunkach:

• Operacje wstawiania, wybierania, aktualizowania lub usuwania występują przy dużej współbieżności.
• Rozmiar wiersza jest stosunkowo mały (co prowadzi do gęstych stron).
• Liczba wierszy w tabeli jest stosunkowo mała; prowadzące do płytkiego drzewa B, zdefiniowanego przez głębokość indeksu dwóch lub trzech.

Zatłoczenie zatrzasku może wystąpić nawet wtedy, gdy dostęp jest losowy w drzewie B, na przykład gdy kolumna niesekwencyjna jest kluczem wiodącym w indeksie klastrowanym. Poniższy zrzut ekranu pochodzi z systemu, w którym występuje ten typ rywalizacji o zatrzasanie. W tym przykładzie rywalizacja wynika z gęstości stron spowodowanych małym rozmiarem wiersza i stosunkowo płytkim drzewem B. Wraz ze wzrostem współbieżności występuje zabezpieczanie na stronach, mimo że operacje wstawiania są losowe w strukturze B-drzewa, ponieważ GUID był główną kolumną w indeksie.

Na poniższym zrzucie ekranu oczekiwania występują zarówno na stronach danych buforu, jak i na stronach wolnego miejsca (PFS). Nawet gdy liczba plików danych została zwiększona, rywalizacja o blokady była powszechna na stronach danych buforu.

W poniższej tabeli podsumowano główne przyczyny obserwowane w przypadku tego typu zawodzenia na zatrzasku.

Kombinacja płytkiego drzewa B-Tree i losowych wstawień w indeksie jest podatna na spowodowanie podziałów stron w drzewie B-Tree. Aby można było podzielić strony, program SQL Server musi uzyskać udostępnione (SH) zatrzaski na wszystkich poziomach, a następnie uzyskać wyłączne (EX) zatrzaski na stronach w drzewie B, które są zaangażowane w podziały strony. Ponadto, gdy współbieżność jest wysoka, a dane są stale dodawane i usuwane, mogą wystąpić podziały korzenia drzewa B. W takim przypadku inne wstawki mogą czekać na wszelkie zatrzaski niebuforowane pozyskane w drzewie B. Przejawia się to jako duża liczba oczekiwań na typy zatrzasków ACCESS_METHODS_HOBT_VIRTUAL_ROOT obserwowanych w dynamicznym widoku zarządzania sys.dm_os_latch_stats.

Poniższy skrypt można zmodyfikować, aby określić głębokość drzewa B dla indeksów w tabeli, której dotyczy problem.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Konkurencja o blokadę na stronach wolnej przestrzeni (PFS)

PFS to skrót od Page Free Space, program SQL Server przydziela jedną stronę PFS dla każdej 8088 stron (począwszy od PageID = 1) w każdym pliku bazy danych. Każdy bajt na stronie PFS rejestruje informacje, w tym ilość wolnego miejsca na stronie, jeśli jest przydzielona, czy nie i czy strona przechowuje rekordy duchów. Strona PFS zawiera informacje o stronach dostępnych do alokacji, gdy nowa strona jest wymagana przez operację wstawiania lub aktualizacji. Strona PFS musi zostać zaktualizowana w kilku scenariuszach, w tym w przypadku wystąpienia alokacji lub dealokacji. Ponieważ użycie zatrzasku aktualizacji (UP) jest wymagane do ochrony strony PFS, zawody o blokady na stronach PFS mogą wystąpić, jeśli w grupie plików znajduje się stosunkowo mało plików danych i posiada ona dużą liczbę rdzeni procesora CPU. Prostym sposobem rozwiązania tego problemu jest zwiększenie liczby plików na grupę plików.

Jeśli w programie PAGELATCH_UPjest obserwowanych wiele tempdb oczekiwań dotyczących stron PFS lub SGAM, wykonaj następujące kroki, aby wyeliminować to wąskie gardło:

1. Dodaj pliki danych do tempdb, aby liczba plików danych tempdb była równa liczbie rdzeni procesora w twoim serwerze.

2. Włącz flagę śledzenia programu SQL Server 1118.

Aby uzyskać więcej informacji na temat wąskich gardeł alokacji spowodowanych rywalizacją na stronach systemowych, przeczytaj wpis na blogu Co to jest wąskie gardło alokacji?

Funkcje wartości tabeli i rywalizacja o zatrzasanie w bazie danych tempdb

Istnieją inne czynniki, oprócz sporów o alokację, które mogą powodować rywalizację o blokadę na tempdb, takich jak intensywne użycie funkcji TVF w zapytaniach.

Obsługa konkurencji o zablokowania dla różnych schematów tabel.

W poniższych sekcjach opisano techniki, które mogą służyć do rozwiązywania lub obejścia problemów z wydajnością związanych z nadmierną rywalizacją o dostęp do zatrzasków.

Używanie klucza indeksu wiodącego nie sekwencyjnego

Jedną z metod obsługi rywalizacji o zatrzaski jest zastąpienie klucza indeksu sekwencyjnego kluczem niesekwencyjnym, aby równomiernie rozłożyć operacje wstawiania w zakresie indeksu.

Zazwyczaj odbywa się to przez zastosowanie kolumny wiodącej w indeksie, która proporcjonalnie dystrybuuje obciążenie. Dostępnych jest kilka opcji:

Opcja: Użyj kolumny w tabeli, aby dystrybuować wartości między zakresem kluczy indeksu

Oceń obciążenie pod kątem wartości naturalnej, która może służyć do dystrybucji wstawek w całym zakresie kluczy. Rozważmy na przykład scenariusz korzystania z bankomatu, w którym ATM_ID może być dobrym kandydatem do rozmieszczania zapisów w tabeli transakcji dla wypłat, ponieważ jeden klient może używać tylko jednego bankomatu naraz. Podobnie w systemie sprzedaży, być może Checkout_ID lub identyfikator sklepu będzie wartością naturalną, która może służyć do dystrybucji wstawek w całym zakresie kluczy. Ta technika wymaga utworzenia klucza indeksu złożonego, gdzie klucz wiodący jest wartością wskazaną w kolumnie lub pewnym skrótem tej wartości, w połączeniu z co najmniej jedną dodatkową kolumną w celu zapewnienia unikatowości. W większości przypadków skrót wartości działa najlepiej, ponieważ zbyt wiele odrębnych wartości powoduje słabą organizację fizyczną. Na przykład w systemie sprzedaży można utworzyć skrót na podstawie identyfikatora sklepu, który jest pewnym modulo, który jest zgodny z liczbą rdzeni procesora CPU. Ta technika spowoduje stosunkowo małą liczbę zakresów w tabeli, jednak wystarczyłoby rozłożyć wstawki w taki sposób, aby uniknąć rywalizacji o zatrzasanie. Na poniższej ilustracji przedstawiono tę technikę.

Ten wzorzec został zaimplementowany podczas sesji w laboratorium wydajności i rozwiązano problem rywalizacji o zasoby zatrzasku w systemie z 32 rdzeniami procesora fizycznego. Tabela została użyta do przechowywania salda zamknięcia na końcu transakcji; każda transakcja biznesowa wykonała pojedynczą operację wstawiania do tabeli.

Oryginalna definicja tabeli

W przypadku używania oryginalnej definicji tabeli zaobserwowano występowanie nadmiernego zatrzaśniania w indeksie klastrowanym pk_table1:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Zmiana kolejności definicji indeksu

Reorganizacja kluczowych kolumn indeksu, z UserID jako wiodącą kolumną w kluczu głównym, zapewniła niemal losowy rozkład operacji wstawiania na stronach. Wynikowy rozkład nie był w 100% losowy, ponieważ nie wszyscy użytkownicy są online w tym samym czasie, ale rozkład był wystarczająco losowy, aby złagodzić nadmierną konkurencję o zasoby. Jednym z zastrzeżeń dotyczących przestawiania definicji indeksu jest to, że wszystkie zapytania selektywne względem tej tabeli muszą być modyfikowane tak, aby zarówno UserID, jak i TransactionID były używane jako predykaty równości.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Używanie wartości skrótu jako wiodącej kolumny w kluczu podstawowym

Poniższa definicja tabeli może służyć do generowania modulo, który jest zgodny z liczbą procesorów CPU, HashValue jest generowany przy użyciu sekwencyjnie rosnącej wartości TransactionID w celu zapewnienia równomiernego rozkładu w drzewie B:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Opcja: użyj identyfikatora GUID jako wiodącej kolumny klucza indeksu

Jeśli nie ma separatora naturalnego, kolumna GUID może być używana jako kolumna klucza głównego indeksu w celu zapewnienia jednolitego rozkładu operacji wstawiania. Korzystanie z identyfikatora GUID jako wiodącej kolumny w podejściu klucza indeksu umożliwia stosowanie partycjonowania dla innych funkcji; jednak technika ta może także wprowadzać potencjalne wady, takie jak większa liczba podziałów stron, organizacja fizyczna danych oraz niskie gęstości stron.

Używanie partycjonowania haszowego z obliczoną kolumną

Partycjonowanie tabel w programie SQL Server może służyć do łagodzenia nadmiernej konkurencji o zatrzaski. Tworzenie schematu partycjonowania skrótów z obliczoną kolumną w tabeli partycjonowanej jest typowym podejściem, które można wykonać za pomocą następujących kroków:

1. Utwórz nową grupę plików lub użyj istniejącej grupy plików do przechowywania partycji.

2. Jeśli używasz nowej grupy plików, równo zrównoważ poszczególne pliki na jednostkach LUN, dbając o optymalny układ. Jeśli wzorzec dostępu obejmuje dużą liczbę operacji wstawiania, upewnij się, że utworzono taką samą liczbę plików, ile jest fizycznych rdzeni CPU na komputerze z programem SQL Server.

3. CREATE PARTITION FUNCTION Użyj polecenia , aby podzielić tabele na partycje X, gdzie X jest liczbą fizycznych rdzeni procesora CPU na komputerze z programem SQL Server. (co najmniej 32 partycje)

   Uwaga / Notatka

   Wyrównanie liczby partycji do liczby rdzeni procesora w stosunku 1:1 nie zawsze jest konieczne. W wielu przypadkach może to być wartość mniejsza niż liczba rdzeni procesora CPU. Posiadanie większej liczby partycji może spowodować większe obciążenie dla zapytań, które muszą przeszukiwać wszystkie partycje, a w takich przypadkach mniej partycji może pomóc. W testach SQLCAT na 64- i 128-rdzeniowych procesorach logicznych z rzeczywistymi obciążeniami klientów, 32 partycje okazały się wystarczające do rozwiązania nadmiernej rywalizacji o zatrzaski i osiągnięcia docelowej skalowalności. Ostatecznie idealna liczba partycji powinna być określana przez testowanie.

4. Użyj polecenia CREATE PARTITION SCHEME:

   • Powiąż funkcję partycji z grupami plików.
   • Dodaj kolumnę skrótu typu tinyint lub smallint do tabeli.
   • Oblicz dobry rozkład skrótów. Na przykład użyj HASHBYTES z operatorem modulo lub BINARY_CHECKSUM.

Do celów implementacji można dostosować następujący przykładowy skrypt:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Ten skrypt może służyć do partycjonowania hashowego tabeli, która ma problemy z konkurencją przy wstawianiu spowodowaną przez ostatnią stronę. Ta technika przenosi rywalizację z ostatniej strony, partycjonując tabelę i dystrybuując wstawki między partycjami tabeli za pomocą operacji modulu wartości skrótu.

Jakie działanie ma partycjonowanie skrótów z użyciem kolumny obliczeniowej?

Jak pokazano na poniższym diagramie, ta technika przenosi rywalizację z ostatniej strony przez ponowne skompilowanie indeksu w funkcji skrótu i utworzenie tej samej liczby partycji, co fizyczne rdzenie procesora CPU na komputerze z programem SQL Server. Operacje wstawiania nadal idą na koniec zakresu logicznego (sekwencyjnie rosnącej wartości), ale operacja modulowania wartości skrótu gwarantuje, że wstawki są podzielone na różne drzewa B, co eliminuje wąskie gardło. Przedstawiono to na następujących diagramach:

Kompromisy podczas używania partycjonowania skrótów

Podczas gdy partycjonowanie za pomocą skrótów może wyeliminować rywalizację przy operacjach wstawiania, istnieje kilka kompromisów, które należy wziąć pod uwagę podczas podejmowania decyzji, czy należy użyć tej techniki.

• W większości przypadków zapytania należy zmodyfikować tak, aby uwzględniały partycję skrótu w predykacie, co prowadzi do planu zapytania, który nie umożliwia usunięcia partycji po odczytaniu tych zapytań. Poniższy zrzut ekranu przedstawia zły plan bez eliminacji partycji po zaimplementowaniu partycjonowania skrótów.

  

• Eliminuje możliwość usuwania partycji w przypadku niektórych innych zapytań, jak raporty oparte na zakresie.

• Aby osiągnąć eliminację partycji podczas łączenia tabeli partycjonowanej skrótem z inną tabelą, druga tabela powinna być partycjonowana skrótem na tym samym kluczu, a klucz skrótu powinien stanowić część warunku łączenia.

• Partycjonowanie skrótów uniemożliwia zastosowanie partycjonowania do innych funkcji zarządzania, takich jak archiwizowanie z użyciem okna przesuwnego i funkcja przełączania partycji.

Partycjonowanie z haszowaniem jest skuteczną strategią ograniczania nadmiernej rywalizacji o zamki, ponieważ zwiększa ogólną przepływność systemu przez złagodzenie rywalizacji podczas wstawiania. Ponieważ istnieją pewne kompromisy, może to nie być optymalne rozwiązanie dla niektórych wzorców dostępu.

Podsumowanie technik używanych do rozwiązywania problemów z rywalizacją o zatrzasanie

W poniższych dwóch sekcjach przedstawiono podsumowanie technik, które można wykorzystać do rozwiązania problemu nadmiernej rywalizacji o zatrzaski.

Klucz/indeks nie sekwencyjny

Zalety:

• Umożliwia korzystanie z innych funkcji partycjonowania, takich jak archiwizowanie danych przy użyciu schematu okien przesuwnych i funkcji przełącznika partycji.

Wady:

• Możliwe wyzwania podczas wybierania klucza/indeksu w celu zapewnienia rozkładu, który jest 'wystarczająco jednolity' dla wstawek w każdej chwili.
• Identyfikator GUID jako kolumna wiodąca może służyć do zagwarantowania jednolitego rozkładu z zastrzeżeniem, że może to spowodować nadmierne operacje podziału stron.
• Losowe wstawianie w drzewie B może spowodować zbyt wiele operacji podziału stron i prowadzić do zatrzaśnięć rywalizacji na stronach innych niż liści.

Partycjonowanie haszowe z kolumną obliczeniową

Zalety:

• Przezroczyste dla wstawiania.

Wady:

• Partycjonowania nie można używać do przeznaczonych funkcji zarządzania, takich jak archiwizowanie danych przy użyciu opcji przełącznika partycji.
• Może powodować problemy z eliminacją partycji dla zapytań, w tym poszczególnych i opartych na zakresie wybierania/aktualizowania oraz zapytań wykonujących sprzężenia.
• Dodawanie utrwalonej kolumny obliczeniowej jest operacją offline.

Przewodnik: diagnozowanie konfliktu synchronizatora

W poniższym przewodniku przedstawiono narzędzia i techniki opisane w temacie Diagnozowanie rywalizacji o zatrzaśnięcia w SQL Server oraz zarządzanie rywalizacją o zatrzaśnięcia dla różnych wzorców tabel, aby rozwiązać problem w rzeczywistym scenariuszu. W tym scenariuszu opisano przeprowadzanie testów obciążeniowych systemu punktu sprzedaży, który symulował około 8000 sklepów realizujących transakcje na aplikacji SQL Server uruchomionej na systemie z 8 gniazdami, 32 fizycznymi rdzeniami i 256 GB pamięci.

Na poniższym diagramie przedstawiono sprzęt używany do testowania punktu sprzedaży:

Objaw: Zatrzaski na gorąco

W tym przypadku zaobserwowaliśmy długie czasy oczekiwania na PAGELATCH_EX, gdzie zazwyczaj określamy wysoką wartość jako średnio powyżej 1 ms. W tym przypadku stale obserwujemy oczekiwania przekraczające 20 ms.

Po ustaleniu, że rywalizacja o zatrzasanie było problematyczne, ustaliliśmy, co było przyczyną rywalizacji o zatrzasanie.

Izoluj obiekt powodujący rywalizację o blokadę

Poniższy skrypt używa kolumny resource_description do izolowania indeksu powodującego rywalizację PAGELATCH_EX :

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Jak pokazano tutaj, spór znajduje się w tabeli LATCHTEST i nazwie indeksu CIX_LATCHTEST. Nazwy notatek zostały zmienione w celu zanonimizacji obciążenia.

Aby uzyskać bardziej zaawansowany skrypt, który wielokrotnie wykonuje zapytania i używa tabeli tymczasowej do określenia całkowitego czasu oczekiwania w konfigurowalnym okresie, zobacz Deskryptory buforu zapytania, aby określić obiekty powodujące rywalizację o zasoby blokady w dodatku.

Alternatywna technika izolowania obiektu powodującego konflikt blokady

Czasami wykonywanie zapytań sys.dm_os_buffer_descriptorsmoże być niepraktyczne. Wraz ze wzrostem pamięci w systemie i dostępnej dla puli buforowej, zwiększa się również czas wymagany do uruchomienia tego DMV. Na systemie o pojemności 256 GB uruchomienie tego dynamicznego widoku zarządzania (DMV) może zająć do 10 minut lub dłużej. Alternatywna technika jest dostępna, szeroko opisana w następujący sposób i zilustrowana odmiennym scenariuszem obciążeniowym, który przetestowaliśmy w laboratorium.

1. Wykonywanie zapytań dotyczących bieżących zadań oczekujących przy użyciu skryptu dodatku Zapytanie sys.dm_os_waiting_tasks uporządkowane według czasu trwania oczekiwania.

2. Zidentyfikuj stronę główną, na której zaobserwowano konwój, co ma miejsce, gdy wiele wątków rywalizuje na tej samej stronie. W tym przykładzie wątki wykonujące wstawianie konkurują na stronie końcowej w drzewie B i czekają, aż będą mogły zdobyć blokadę EX. Jest to wskazywane przez resource_description w pierwszym zapytaniu, w naszym przypadku 8:1:111305.

3. Włącz flagę śledzenia 3604, która uwidacznia dodatkowe informacje o stronie za pomocą składni DBCC PAGE w następujący sposób: zastąp wartość uzyskaną za pośrednictwem resource_description wartością w nawiasach.

   Aby włączyć dane wyjściowe konsoli, ustaw flagę śledzenia 3604.

   DBCC TRACEON (3604);
   

   Sprawdź szczegóły strony:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Sprawdź dane wyjściowe DBCC. W naszym przypadku powinien istnieć skojarzony identyfikator metadanych ObjectID 78623323.

   

5. Teraz możemy uruchomić następujące polecenie, aby określić nazwę obiektu powodującego zablokowanie, która jest zgodnie z oczekiwaniami LATCHTEST.

   Uwaga / Notatka

   Upewnij się, że jesteś w prawidłowym kontekście bazy danych, w przeciwnym razie zapytanie zwraca NULLwartość.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Podsumowanie i wyniki

Korzystając z powyższej techniki, byliśmy w stanie potwierdzić, że rywalizacja miała miejsce w indeksie klastrowanym z sekwencyjnie zwiększającą wartość klucza w tabeli, która zdecydowanie otrzymała największą liczbę wstawiania. Ten typ rywalizacji nie jest rzadkością dla indeksów z sekwencyjnie rosnącą wartością klucza, tak jak znacznik czasu, automatycznie nadawana tożsamość lub wygenerowana przez aplikację TransactionID.

Aby rozwiązać ten problem, użyliśmy partycjonowania haszującego z obliczoną kolumną i zaobserwowaliśmy 690% poprawę wydajności. W poniższej tabeli przedstawiono podsumowanie wydajności aplikacji przed i po wdrożeniu partycjonowania skrótów z użyciem obliczonej kolumny. Wykorzystanie procesora znacznie wzrasta zgodnie z oczekiwaniami wraz ze wzrostem przepustowości po usunięciu wąskiego gardła związanego z rywalizacją o blokady.

Jak widać w poprzedniej tabeli, prawidłowe identyfikowanie i rozwiązywanie problemów z wydajnością spowodowanych nadmierną rywalizacją o zatrzaski stron może mieć pozytywny wpływ na ogólną wydajność aplikacji.

Dodatek: Technika alternatywna

Jedną z możliwych strategii unikania nadmiernego zatrzasania strony jest wypełnienie wierszy z kolumną char w celu zapewnienia, że każdy wiersz używa pełnej strony. Ta strategia jest opcją, gdy ogólny rozmiar danych jest mały i trzeba rozwiązać EX problem z zatrzaśnieniem strony spowodowanym przez następujące kombinacje czynników:

• Mały rozmiar wiersza
• Płytkie drzewo B
• Wzorzec dostępu z dużą częstotliwością losowych operacji wstawiania, wybierania, aktualizowania i usuwania
• Małe tabele, takie jak tymczasowe tabele kolejek

Dopełniając wiersze w celu zajmowania pełnej strony, musisz uruchomić program SQL w celu przydzielenia większej liczby stron, dzięki czemu więcej stron będzie dostępnych dla wstawień i zmniejszenie rywalizacji o zatrzaśnięcie EX strony.

Wypełnij wiersze, aby upewnić się, że każdy wiersz zajmuje pełną stronę

Skrypt podobny do poniższego może służyć do uzupełniania wierszy w celu zajmowania całej strony:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Ta technika została wyjaśniona dla pełności; w praktyce zespół SQLCAT używał jej tylko w małej tabeli o rozmiarze 10 000 wierszy w jednej sesji związanej z wydajnością. Ta technika ma ograniczone zastosowanie, ponieważ zwiększa wykorzystanie pamięci w SQL Server dla dużych tabel i może powodować rywalizację o zatrzaski na stronach nie-liściowych. Dodatkowe wykorzystanie pamięci może być znaczącym czynnikiem ograniczającym zastosowanie tej techniki. W przypadku ilości pamięci dostępnej na nowoczesnym serwerze duża część zestawu roboczego dla obciążeń OLTP jest zwykle przechowywana w pamięci. Gdy zestaw danych zwiększa się do rozmiaru, który nie mieści się już w pamięci, występuje znaczny spadek wydajności. W związku z tym ta technika jest czymś, co ma zastosowanie tylko do małych tabel. Ta technika nie jest używana przez usługę SQLCAT w scenariuszach, takich jak ostatnia strona/końcowa strona, wstawiania stron dla dużych tabel.

Dodatek: Skrypty rywalizacji o zatrzasanie programu SQL Server

Ta sekcja zawiera skrypty, które mogą służyć do diagnozowania i rozwiązywania problemów z kontencją zatrzasków.

Zapytanie sys.dm_os_waiting_tasks uporządkowane według identyfikatora sesji

Następujący przykładowy skrypt wykonuje zapytanie sys.dm_os_waiting_tasks, a następnie zwraca czasy oczekiwania na zatrzaski, uporządkowane według identyfikatora sesji:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Zapytanie sys.dm_os_waiting_tasks uporządkowane według czasu oczekiwania

Następujący przykładowy skrypt wykonuje zapytanie sys.dm_os_waiting_tasks i zwraca czasy oczekiwania na zatrzaski uporządkowane według długości czasu oczekiwania.

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Obliczanie czasów oczekiwania w przedziale czasowym

Poniższy skrypt oblicza i zwraca czasy oczekiwania zatrzasków w określonym przedziale czasu.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Zapytaj deskryptory bufora, aby określić obiekty powodujące konflikt blokad.

Poniższy skrypt pyta o deskryptory buforów w celu określenia, które obiekty są skojarzone z najdłuższymi czasami oczekiwania na blokadę.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Skrypt partycjonowania hashów

Użycie tego skryptu opisano w Use Hash Partitioning with a Computed Column i należy je dostosować do celów implementacji.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Treści powiązane

• narzędzia do monitorowania wydajności i dostrajania