SQL Server nieustannie czeka. Czeka na dyski, na procesory, na zwolnienie blokad. Te oczekiwania to nie błąd systemu, lecz pilna informacja diagnostyczna, która prawidłowo zinterpretowana wskaże rzeczywiste źródło problemów wydajnościowych.
- Wait Statistics pokazują gdzie SQL Server traci czas, nie gdzie zużywa zasoby
- Wysokie CPU lub IO na monitoringu nie zawsze oznacza problem z tym konkretnym zasobem
- Analiza pojedynczego waitu bez kontekstu workloadu prowadzi do błędnych wniosków
- Skuteczna diagnostyka wymaga korelacji waits z DMV, Query Store i Extended Events
Czym naprawdę są Wait Statistics
SQL Server to system wielowątkowy, który w każdej sekundzie wykonuje tysiące operacji. Każda z tych operacji wymaga dostępu do zasobów: pamięci, procesora, dysków, struktur danych. Gdy zasób jest zajęty lub niedostępny, wątek musi czekać. I właśnie te oczekiwania rejestrowane są jako waits.
Mechanizm jest prosty. Każdy worker thread w SQL Server przechodzi przez trzy stany: RUNNING (aktywne wykonywanie na CPU), RUNNABLE (gotowy do wykonania, czeka na dostępny scheduler) oraz SUSPENDED (czeka na zasób zewnętrzny). Czas spędzony w stanie SUSPENDED to właśnie wait time, a typ oczekiwania klasyfikowany jest jako konkretny wait type.
SQL Server rejestruje ponad 900 różnych typów waitów. Na szczęście w praktyce diagnostycznej skupiamy się zazwyczaj na kilkunastu najczęstszych, które odpowiadają za większość problemów wydajnościowych.
Dlaczego monitorowanie zasobów to za mało
Wielu administratorów popełnia podstawowy błąd: widząc wysokie wykorzystanie CPU na poziomie 95%, automatycznie zakładają problem z procesorem. Tymczasem rzeczywistość bywa znacznie bardziej skomplikowana.
Wysokie CPU może być objawem zupełnie innego problemu. Wyobraźmy sobie scenariusz: zapytanie generuje nadmierne IO z powodu braku odpowiedniego indeksu. System buforuje dane w pamięci, ale każde wykonanie wymaga przeszukania milionów wierszy. CPU pracuje intensywnie, ale źródłem problemu jest brak indeksu, nie wydajność procesora.
Po dwudziestu latach pracy z SQL Server nauczyłem się jednej fundamentalnej zasady: nigdy nie ufaj pojedynczej metryce. CPU na 95% może oznaczać problem z procesorem, ale równie dobrze może wskazywać na missing index, parameter sniffing, przestarzałe statystyki lub nadmierną równoległość zapytań. Dopiero Wait Statistics pokazują pełny obraz.
Podobnie z IO. Wysokie latency dysków może wynikać z przeciążonej macierzy, ale też z zapytań skanujących całe tabele zamiast korzystać z indeksów. Wait Statistics pozwalają rozróżnić te scenariusze.
Trzy główne kategorie waitów w praktyce
Waity związane z CPU
Najczęściej spotykane waity CPU to SOS_SCHEDULER_YIELD oraz CXPACKET (i nowszy CXCONSUMER). SOS_SCHEDULER_YIELD pojawia się gdy wątek dobrowolnie oddaje kontrolę schedulerowi po wykorzystaniu swojego kwantu czasu. W umiarkowanych ilościach jest to normalne zachowanie.
Problem zaczyna się gdy SOS_SCHEDULER_YIELD dominuje w statystykach. Zwykle oznacza to intensywne operacje obliczeniowe: skomplikowane agregacje, sortowania dużych zbiorów danych, rekurencyjne CTE lub funkcje skalarne wywoływane miliony razy.
CXPACKET to wait związany z równoległością zapytań. Pojawia się gdy wątki wykonujące równoległe fragmenty zapytania czekają na synchronizację. Sam w sobie nie jest problemem; problematyczna jest jego nadmierna wartość względem innych waitów.
Praktyczna diagnostyka: sprawdź wartość MAXDOP na poziomie instancji i bazy danych. Przeanalizuj Cost Threshold for Parallelism. Zidentyfikuj zapytania z wysokim CPU time przez sys.dm_exec_query_stats.
Waity związane z IO
PAGEIOLATCH_SH i PAGEIOLATCH_EX to klasyczne waity IO. Pierwszy dotyczy operacji odczytu (shared latch), drugi zapisu (exclusive latch). Wysokie wartości wskazują że SQL Server czeka na pobranie stron z dysku do buffer pool.
WRITELOG to wait związany z zapisem do transaction log. Pojawia się przy każdym COMMIT i jest szczególnie istotny w środowiskach OLTP z dużą liczbą małych transakcji. Wysoki WRITELOG często wskazuje na wolny dysk pod transaction log lub nadmierną fragmentację pliku loga.
ASYNC_IO_COMPLETION oraz IO_COMPLETION dotyczą asynchronicznych operacji IO, często związanych z operacjami backupu, DBCC czy bulk insert.
Kluczowe pytanie przy waitach IO: czy problem leży w infrastrukturze czy w zapytaniach? Sprawdź sys.dm_io_virtual_file_stats dla statystyk IO per plik bazy. Porównaj read/write latency z benchmarkami dla Twojego storage. Jednocześnie przeanalizuj zapytania z wysokim logical reads w Query Store.
Waity związane z blokadami
LCK_M_S, LCK_M_X, LCK_M_U i ich warianty to waity blokadowe. Litera określa typ żądanej blokady: S (shared), X (exclusive), U (update). Te waity pokazują że jedna sesja czeka na zwolnienie blokady trzymanej przez inną sesję.
W środowiskach OLTP waity blokadowe są często głównym źródłem problemów. Długie transakcje, brak odpowiednich indeksów (powodujący eskalację blokad) czy nadmiernie restrykcyjny isolation level mogą generować kaskadowe blokowanie.
Praktyczne podejście: monitoruj sys.dm_tran_locks i sys.dm_exec_requests dla bieżących blokad. Zidentyfikuj sesje blokujące przez sys.dm_exec_sessions. Rozważ wdrożenie Read Committed Snapshot Isolation dla redukcji konfliktów reader/writer.
Jak prawidłowo analizować Wait Statistics
Podstawowe zapytanie diagnostyczne wykorzystuje widok sys.dm_os_wait_stats:
SELECT wait_type, wait_time_ms, waiting_tasks_count, wait_time_ms / waiting_tasks_count AS avg_wait_ms FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0 AND wait_type NOT LIKE '%SLEEP%' AND wait_type NOT LIKE '%IDLE%' AND wait_type NOT LIKE '%QUEUE%' ORDER BY wait_time_ms DESC;
Krytyczne jest filtrowanie benign waits, czyli waitów które są normalne i nie wskazują problemów. WAITFOR, LAZYWRITER_SLEEP, BROKER_RECEIVE_WAITFOR to przykłady waitów które zawsze będą obecne i nie wymagają uwagi.
Analiza powinna uwzględniać czas od ostatniego restartu serwera lub ręcznego wyczyszczenia statystyk (DBCC SQLPERF). Kumulatywne statystyki z miesięcy pracy mogą maskować aktualne problemy.
Korelacja z innymi narzędziami
Wait Statistics to punkt startowy, nie końcowy diagnostyki. Po zidentyfikowaniu dominującego waitu kolejnym krokiem jest drążenie głębiej:
- Query Store pokaże które konkretnie zapytania generują problematyczne waity poprzez metrykę wait stats per query
- Extended Events pozwolą na szczegółowe śledzenie waitów w czasie rzeczywistym z pełnym kontekstem sesji i zapytania
- sys.dm_exec_query_stats i sys.dm_exec_requests dadzą obraz bieżącego i historycznego obciążenia
- sys.dm_io_virtual_file_stats pomoże zweryfikować czy waity IO korelują z rzeczywistym latency storage
Szczególnie wartościowa jest funkcjonalność Query Store wprowadzona w SQL Server 2016, która agreguje wait statistics per zapytanie. Pozwala to bezpośrednio powiązać konkretny wait type z problematycznym kodem SQL.
Typowe pułapki interpretacyjne
Pierwsza pułapka to reagowanie na każdy wysoki wait. CXPACKET na szczycie listy nie oznacza automatycznie problemu z równoległością. W środowiskach analitycznych z dużymi zapytaniami równoległość jest pożądana.
Druga pułapka to ignorowanie proporcji. Jeśli PAGEIOLATCH_SH stanowi 60% wszystkich waitów przy średnim wait time 0.5ms, prawdopodobnie nie masz problemu z IO. Ale jeśli stanowi 20% przy średnim czasie 50ms, masz poważny problem ze storage.
Trzecia pułapka to brak baseline. Bez wiedzy jak wyglądają normalne waity w Twoim środowisku, nie rozpoznasz anomalii. Zbieraj statystyki regularnie i porównuj trendy.