Opisywanie statystyk oczekiwania

Ukończone

Kompleksowe podejście do monitorowania wydajności serwera obejmuje ocenę czynników, na które serwer czeka. Statystyki oczekiwania są skomplikowane, a SQL Server jest wyposażony w setki typów oczekiwania, które monitorują każdy uruchomiony wątek i rejestrują, na co wątek czeka.

Aby skutecznie wykrywać i rozwiązywać problemy z wydajnością programu SQL Server, ważne jest, aby zrozumieć, jak działają statystyki oczekiwania i jak aparat bazy danych wykorzystuje je podczas przetwarzania żądań. Ta wiedza pozwala wskazać wąskie gardła i dokładniej zoptymalizować wydajność.

Statystyki oczekiwania są podzielone na trzy typy oczekiwań: oczekiwania zasobów, oczekiwania w kolejce i oczekiwania zewnętrzne.

• Czas oczekiwania na zasoby występuje , gdy wątek procesu roboczego w programie SQL Server żąda dostępu do zasobu, który jest obecnie używany przez wątek. Przykłady oczekiwania na zasoby to blokady, zatrzaski i operacje wejścia/wyjścia dysku.
• Oczekiwania w kolejce występują, gdy wątek procesu roboczego jest bezczynny i oczekuje na przypisanie pracy. Przykładowe oczekiwania kolejek to monitorowanie zakleszczenia i czyszczenie usuniętych rekordów.
• Oczekiwania zewnętrzne występują, gdy program SQL Server oczekuje na proces zewnętrzny, taki jak zapytanie połączonego serwera do ukończenia. Przykładem oczekiwania zewnętrznego jest oczekiwanie sieciowe związane z zwróceniem dużego zestawu wyników do aplikacji klienckiej.

Możesz sprawdzić sys.dm_os_wait_stats widok systemu, aby eksplorować wszystkie oczekiwania napotkane przez wątki podczas ich wykonywania oraz sys.dm_db_wait_stats w przypadku usługi Azure SQL Database. Widok systemowy sys.dm_exec_session_wait_stats wyświetla listę aktywnych sesji oczekujących.

Te widoki systemowe umożliwiają zapoznanie się z wydajnością serwera oraz czytelne identyfikowanie problemów z konfiguracją lub sprzętem. Te dane są utrwalane od momentu uruchomienia wystąpienia, ale dane można wyczyścić zgodnie z potrzebami w celu zidentyfikowania zmian.

Statystyki oczekiwania są oceniane jako procent całkowitego oczekiwania na serwerze.

Wynik tego zapytania z sys.dm_os_wait_stats pokazuje typ oczekiwania oraz agregację procentu czasu oczekiwania (kolumna Procent oczekiwania) i średni czas oczekiwania w sekundach dla każdego typu oczekiwania.

W takim przypadku serwer ma Grupy dostępności Always On, co wskazują typy oczekiwania REDO_THREAD_PENDING_WORK i PARALLEL_REDO_TRAN_TURN. Stosunkowo wysoki odsetek oczekiwań CXPACKET i SOS_SCHEDULER_YIELD wskazuje, że ten serwer jest pod obciążeniem procesora.

Ponieważ DMVs dostarczają listę typów oczekiwania z największym czasem zgromadzonym od ostatniego uruchomienia SQL Server, okresowe zbieranie i przechowywanie danych dotyczących statystyk oczekiwania może pomóc w lepszym zrozumieniu i powiązaniu problemów z wydajnością z innymi zdarzeniami w bazie danych.

Biorąc pod uwagę, że dynamiczne widoki zarządzania zapewniają listę typów oczekiwania z najwyższym czasem skumulowanym od ostatniego uruchomienia programu SQL Server, zbieranie i przechowywanie statystyk oczekiwania okresowo może pomóc zrozumieć i skorelować problemy z wydajnością z innymi zdarzeniami bazy danych.

Istnieje kilka typów oczekiwania dostępnych w programie SQL Server, ale niektóre z nich są typowe.

• RESOURCE_SEMAPHORE — wskazuje, że zapytania oczekują na udostępnienie pamięci, często ze względu na nadmierne przydziały pamięci do niektórych zapytań. Ten problem zwykle objawia się jako długie czasy wykonywania zapytań, a nawet przekroczenia limitu czasu. Przyczyny tych typów oczekiwania mogą obejmować nieaktualne statystyki, brakujące indeksy i wysoką współbieżność zapytań.

• LCK_M_X — często wskazuje problem z blokowaniem. Ten problem można rozwiązać, zmieniając READ COMMITTED SNAPSHOT poziom izolacji, optymalizując indeksowanie w celu skrócenia czasu transakcji lub poprawiając zarządzanie transakcjami w kodzie T-SQL.

• PAGEIOLATCH_SH — ten typ oczekiwania może wskazywać na problemy z indeksami lub brakiem przydatnych indeksów, co powoduje przeskanowanie nadmiernej ilości danych przez program SQL Server. Alternatywnie, jeśli liczba oczekiwania jest niska, ale czas oczekiwania jest wysoki, może to sugerować problemy z wydajnością magazynu. Tę reakcję można zaobserwować, analizując dane w kolumnach waiting_tasks_count i wait_time_ms w widoku systemowym sys.dm_os_wait_stats, aby obliczyć średni czas oczekiwania dla danego rodzaju oczekiwania.

• SOS_SCHEDULER_YIELD — ten typ oczekiwania może wskazywać na wysokie wykorzystanie procesora, które jest skorelowane z wieloma dużymi skanowaniami lub brakującymi indeksami, a często z dużą liczbą oczekiwań CXPACKET.

• CXPACKET — częste występowanie tego typu oczekiwania może wskazywać na nieprawidłową konfigurację. Przed programem SQL Server 2019 ustawieniem domyślnym maksymalnego stopnia równoległości (MAXDOP) było użycie wszystkich dostępnych procesorów CPU dla zapytań. Ponadto próg kosztów równoległości został ustawiony na 5, co może spowodować równoległe wykonywanie małych zapytań, co ogranicza przepływność. Aby zmniejszyć ten typ oczekiwania, możesz obniżyć ustawienie MAXDOP i zwiększyć próg kosztów dla równoległości. Jednak typ oczekiwania CXPACKET może również wskazywać wysokie wykorzystanie procesora, co jest zwykle rozwiązywane poprzez optymalizację indeksów.

• PAGEIOLATCH_UP — ten typ oczekiwania na stronach danych 2:1:1 może wskazywać na rywalizację Bazy danych TempDB na stronach danych o wolne miejsce (PFS). Każdy plik danych ma jedną stronę PFS na 64 MB danych. To oczekiwanie jest zwykle spowodowane tylko jednym plikiem TempDB, tak jak przed programem SQL Server 2016, domyślnym zachowaniem było użycie jednego pliku danych dla bazy danych TempDB. Najlepszym rozwiązaniem dla bazy danych TempDB jest użycie jednego pliku na rdzeń procesora CPU, do ośmiu plików. Ważne jest również, aby upewnić się, że pliki danych bazy danych TempDB mają ten sam rozmiar i mają te same ustawienia automatycznego zwiększania, aby upewnić się, że są używane równomiernie. SQL Server 2016 oraz nowsze sterują wzrostem plików danych TempDB, zapewniając ich jednorodny i równoczesny rozwój.

Oprócz wcześniej wspomnianych widoków DMV, Query Store śledzi również czasy oczekiwania skojarzone z określonymi zapytaniami. Chociaż dane dotyczące czasu oczekiwania śledzone przez Query Store nie są tak szczegółowe jak dane w widokach DMV, nadal dostarczają przydatnego przeglądu na temat tego, na co czeka zapytanie.