Dlaczego SQL Server zwalnia?
Pierwsze rzeczy, które sprawdzamy

Aplikacja przez wiele miesięcy działa poprawnie, aż pewnego dnia użytkownicy zaczynają zgłaszać wolne raporty, zawieszające się okna albo problemy z zapisywaniem dokumentów.

Pierwszy odruch często wygląda podobnie: trzeba dołożyć pamięci, storage jest za wolny, baza jest za duża, SQL Server wymaga restartu, trzeba przebudować wszystkie indeksy.

Każda z tych hipotez może być prawdziwa. Żadnej nie należy jednak przyjmować bez wcześniejszej diagnostyki.

SQL Server może zwolnić przez jedno nieoptymalne zapytanie, regresję planu wykonania, blokadę utrzymywaną przez długą transakcję, brak miejsca na pliki danych lub log, problemy ze storage, presję pamięci, nadmierną równoległość, wzrost obciążenia aplikacji albo błędną konfigurację instancji.

Najważniejsze jest ustalenie, na co SQL Server w danym momencie zużywa czas. Dopiero później można bezpiecznie podejmować działania.

Najpierw ustal, co właściwie zwolniło

Stwierdzenie „SQL działa wolno" jest zbyt ogólne. Przed rozpoczęciem analizy warto ustalić:

• -czy problem dotyczy całej aplikacji, czy jednego raportu lub ekranu,
• -czy problem występuje stale, czy tylko okresowo,
• -kiedy pojawił się po raz pierwszy,
• -czy wcześniej wdrożono nową wersję aplikacji,
• -czy zmieniła się liczba użytkowników albo ilość danych,
• -czy w tym czasie wykonywany jest backup, import lub raport,
• -czy problem dotyczy odczytu, zapisu czy obu operacji,
• -czy aplikacja jest wolna, czy całkowicie zablokowana.

To ważne, ponieważ zupełnie inaczej diagnozuje się jedno zapytanie wykonujące się przez minutę, całą instancję wykorzystującą 100% CPU, aplikację oczekującą na blokadę czy timeout spowodowany problemem poza SQL Serverem.

Nie zaczynaj od restartu

Restart SQL Servera może chwilowo poprawić sytuację, ale jednocześnie usuwa część informacji potrzebnych do diagnostyki. Po restarcie można stracić aktywne sesje, bieżące blokady, zawartość cache planów, część statystyk DMV i kontekst pozwalający odtworzyć przebieg zdarzenia.

Jeżeli system działa, choć wolno, najpierw warto zebrać podstawowe dane. Restart może być konieczny podczas poważnej awarii, ale nie powinien być standardową metodą „naprawiania wydajności".

Pierwszy krok: sprawdź aktywne zapytania

Najpierw należy zobaczyć, co SQL Server aktualnie wykonuje. Jednym z najwygodniejszych narzędzi jest sp_WhoIsActive, popularna procedura diagnostyczna rozwijana przez Adama Machanica. Jeżeli nie jest dostępna, można wykorzystać wbudowane widoki dynamicznego zarządzania.

Przykładowe zapytanie tylko do odczytu:

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING(
        t.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(t.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS current_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

Do odczytu części DMV potrzebne jest odpowiednie uprawnienie, zależne od wersji SQL Servera, między innymi VIEW SERVER STATE albo nowsze uprawnienia diagnostyczne.

W wynikach warto zwrócić uwagę na długo działające zapytania, wysoką liczbę odczytów logicznych, sesje oczekujące na blokadę i jedną sesję dominującą nad pozostałymi.

Nie należy automatycznie kończyć sesji tylko dlatego, że działa długo. Może to być poprawny import, backup, aktualizacja albo transakcja, której wycofanie potrwa dłużej niż jej dokończenie.

Drugi krok: sprawdź blokady

Jeżeli wiele sesji posiada wartość w blocking_session_id, problemem może być jedna transakcja blokująca pozostałe. Typowy łańcuch wygląda następująco:

1. 1 aplikacja rozpoczyna transakcję,
2. 2 wykonuje aktualizację,
3. 3 nie kończy jej odpowiednio szybko,
4. 4 kolejne sesje próbują uzyskać dostęp do tych samych danych,
5. 5 liczba oczekujących połączeń rośnie,
6. 6 użytkownicy widzą zawieszoną aplikację.

Do szybkiego sprawdzenia blokowania można użyć:

SELECT
    r.session_id AS waiting_session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS waiting_batch
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Samo znalezienie blokującej sesji nie kończy analizy. Trzeba sprawdzić, kto ją uruchomił, jaką operację wykonuje, czy transakcja nadal pracuje i czy przerwanie operacji nie uszkodzi procesu biznesowego.

Polecenie KILL powinno być decyzją świadomą, a nie automatycznym krokiem z checklisty.

Blokada nie jest tym samym co deadlock

Blokowanie jest normalnym elementem pracy bazy. Jedna transakcja czeka, aż druga zwolni potrzebny zasób. Deadlock, czyli zakleszczenie, powstaje, gdy dwie lub więcej transakcji czekają wzajemnie na zasoby utrzymywane przez siebie - SQL Server wykrywa taki cykl i przerywa jedną z transakcji.

Deadlocki najlepiej analizować na podstawie sesji Extended Events system_health, deadlock graph oraz danych Query Store. Sama liczba blokad nie pozwala stwierdzić, że występują deadlocki.

Trzeci krok: sprawdź, na co SQL Server czeka

Wait statistics są jednym z najważniejszych źródeł informacji o wydajności SQL Servera. Pokazują, na co oczekiwały zadania wykonywane przez silnik.

sys.dm_os_wait_stats zawiera wartości zagregowane od uruchomienia instancji albo ostatniego wyczyszczenia statystyk. Nie przedstawia więc wyłącznie obecnej awarii - należy analizować zmianę wartości w czasie albo oczekiwania konkretnych sesji.

Najczęściej spotykane grupy oczekiwań:

Żaden typ oczekiwania nie daje samodzielnie pełnej diagnozy. Przykładowo PAGEIOLATCH_* może oznaczać wolny storage, ale również zapytanie czytające ogromną ilość danych z powodu złego planu lub braku indeksu. ASYNC_NETWORK_IO może wskazywać problem sieciowy, ale częściej pojawia się, gdy aplikacja wolno przetwarza wyniki.

Również CXPACKET nie oznacza automatycznie, że trzeba zmniejszyć MAXDOP. Równoległość może być skutkiem, a nie przyczyną kosztownego zapytania.

Czwarty krok: sprawdź CPU, pamięć i storage

Dopiero po sprawdzeniu aktywnych zapytań i oczekiwań warto analizować zasoby systemowe.

CPU

Wysokie użycie CPU może wynikać z nieoptymalnych zapytań, dużej liczby kompilacji, skanowania dużych tabel lub nadmiernej równoległości. Sam poziom 80% albo 90% nie jest jeszcze diagnozą - serwer może przez krótki czas poprawnie wykorzystywać cały procesor podczas raportu. Większym problemem jest długotrwałe nasycenie połączone z rosnącym czasem odpowiedzi.

Warto sprawdzić, czy CPU zużywa proces SQL Servera, czy inne oprogramowanie na serwerze (agent backupu, antywirus, monitoring, inna instancja SQL).

Pamięć

SQL Server celowo wykorzystuje dużą ilość dostępnej pamięci. Sam fakt, że proces zajmuje większość RAM, nie oznacza wycieku. Należy sprawdzić konfigurację max server memory, oczekiwania RESOURCE_SEMAPHORE, granty pamięci dla zapytań i nagłe spadki efektywności cache.

Nie należy opierać diagnozy wyłącznie na Page Life Expectancy i sztywnej wartości 300 sekund. Taki próg pochodzi z czasów znacznie mniejszych serwerów i nie jest uniwersalny.

Microsoft zaleca ustawienie max server memory w taki sposób, aby system operacyjny i pozostałe procesy miały zapewnioną odpowiednią ilość pamięci. Nie istnieje jednak jedna wartość typu „zostaw zawsze 2 GB", właściwa dla każdego serwera.

Aktualną konfigurację można sprawdzić bez jej zmieniania:

SELECT
    name,
    value,
    value_in_use
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)',
    'max degree of parallelism',
    'cost threshold for parallelism',
    'optimize for ad hoc workloads'
);

Nie należy kopiować ustawień pamięci i równoległości z przypadkowego poradnika. Zależą one od wersji SQL Servera, topologii NUMA, rodzaju obciążenia i konfiguracji sprzętowej.

Storage

Przy diagnostyce storage interesuje nas nie tylko wykorzystana przestrzeń, ale opóźnienie odczytu i zapisu, kolejki I/O, wydajność logu i obciążenie macierzy przez inne maszyny. Statystyki I/O dla plików można odczytać przez:

SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    CASE
        WHEN vfs.num_of_reads = 0 THEN 0
        ELSE vfs.io_stall_read_ms / vfs.num_of_reads
    END AS avg_read_latency_ms,
    CASE
        WHEN vfs.num_of_writes = 0 THEN 0
        ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON mf.database_id = vfs.database_id
   AND mf.file_id = vfs.file_id
ORDER BY avg_write_latency_ms DESC;

Są to wartości skumulowane. Pojedynczy wynik po wielu miesiącach pracy może ukrywać chwilowe problemy. Największą wartość daje regularny monitoring i porównywanie zmian - piszemy o tym w artykule Jak monitorować serwery Windows i Linux?

Piąty krok: znajdź zapytania zużywające najwięcej zasobów

Nie każde wolne zapytanie jest najważniejszym problemem. Zapytanie wykonujące się przez 30 sekund raz w miesiącu może mieć mniejszy wpływ niż zapytanie trwające 200 ms, ale wykonywane setki razy na sekundę.

Dane z sys.dm_exec_query_stats są związane z cache planów i mogą zniknąć po restarcie lub rekompilacji. Dlatego do analizy historycznej lepiej wykorzystać Query Store.

Query Store jako podstawowe narzędzie diagnostyczne

Query Store zapisuje historię zapytań, planów wykonania, czasów wykonania i regresji wydajności. Pozwala odpowiedzieć na bardzo ważne pytanie:

Co zmieniło się pomiędzy okresem, gdy aplikacja działała poprawnie, a momentem wystąpienia problemu?

Query Store jest dostępny od SQL Server 2016. W SQL Server 2022 jest domyślnie włączany dla nowo tworzonych baz, ale stan konkretnej bazy zawsze należy sprawdzić:

SELECT
    actual_state_desc,
    desired_state_desc,
    current_storage_size_mb,
    max_storage_size_mb,
    readonly_reason
FROM sys.database_query_store_options;

Nie należy jednak włączać go bez sprawdzenia konfiguracji, limitu przestrzeni i polityki retencji. Microsoft zaleca regularne monitorowanie jego stanu i rozmiaru.

Szósty krok: przeanalizuj plan wykonania

Plan wykonania pokazuje, jak SQL Server zamierza uzyskać wynik. Warto szukać dużej różnicy między liczbą wierszy oszacowanych i rzeczywistych, kosztownych skanów, nadmiernych Key Lookup, sortowania rozlewającego się do tempdb i brakujących indeksów.

Nie wolno jednak upraszczać analizy do zasady: Index Seek jest dobry, a Index Scan zły.

Scan może być najlepszym rozwiązaniem, jeżeli zapytanie zwraca znaczną część tabeli. Seek może być nieefektywny, gdy prowadzi do ogromnej liczby odwołań Key Lookup. Podobnie Hash Match nie jest błędem - może być prawidłowym operatorem dla dużych zestawów danych. Plan trzeba oceniać w kontekście liczby wierszy, selektywności i rzeczywistego celu zapytania.

Częsty problem: parameter sniffing

SQL Server może skompilować plan dla pierwszej wartości parametru, a następnie wykorzystywać go dla kolejnych wywołań. Jeżeli rozkład danych jest nierównomierny, plan dobry dla jednego klienta może być bardzo zły dla innego.

Objawy: zapytanie raz działa szybko, a raz wolno; problem znika po rekompilacji albo restarcie; Query Store pokazuje kilka planów o bardzo różnej wydajności.

Rozwiązaniem nie zawsze jest dodanie OPTION (RECOMPILE). W zależności od sytuacji można rozważyć poprawę statystyk, Query Store hints, Parameter Sensitive Plan Optimization w nowszych wersjach albo rozdzielenie różnych przypadków biznesowych. Każda z tych metod ma skutki uboczne i powinna zostać przetestowana.

Siódmy krok: sprawdź statystyki

Optymalizator używa statystyk do szacowania liczby wierszy. Jeżeli estymacja jest błędna, może wybrać niewłaściwy typ połączenia, indeks, poziom równoległości czy grant pamięci.

Nie ma uniwersalnej reguły mówiącej, że statystyki starsze niż tydzień są złe albo że trzeba je aktualizować po zmianie 10% wierszy.

Tabela z milionem równomiernych rekordów może działać poprawnie ze starszą statystyką, podczas gdy niewielka, ale silnie asymetryczna tabela może wymagać częstszej aktualizacji. Aktualizacja statystyk całej bazy z FULLSCAN może mocno obciążyć serwer i spowodować rekompilację wielu planów - powinna być wykonywana świadomie.

Ósmy krok: nie przebudowuj indeksów w ciemno

Fragmentacja indeksów jest jednym z najczęściej przecenianych problemów SQL Servera. Klasyczna reguła (5-30% reorganizacja, powyżej 30% przebudowa) nie powinna być stosowana automatycznie do wszystkich indeksów.

Znaczenie mają również rozmiar indeksu, rodzaj storage, charakter zapytań i koszt operacji. Mały indeks może mieć 90% fragmentacji i nie powodować żadnego mierzalnego problemu.

Z kolei regularne przebudowywanie wszystkich indeksów może generować ogromny log transakcyjny, obciążyć storage, wydłużyć backup i unieważnić cache planów. Najpierw należy potwierdzić, że fragmentacja wpływa na rzeczywistą wydajność.

Dziewiąty krok: sprawdź pliki bazy i log transakcyjny

Problemy z plikami często pojawiają się jako okresowe „zawieszanie" aplikacji. Warto sprawdzić rozmiar plików, wolne miejsce, ustawienia autogrowth, recovery model i obecność AUTO_SHRINK:

SELECT
    d.name,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    d.is_auto_shrink_on
FROM sys.databases AS d
ORDER BY d.name;

Recovery model FULL

Baza w modelu FULL wymaga regularnych backupów logu, jeżeli organizacja chce zachować możliwość odtwarzania do określonego punktu w czasie. Sam backup pełny bazy nie zastępuje backupu logu - piszemy więcej o strategii backupu w artykule Backup ERP.

Nie należy jednak przełączać produkcyjnej bazy z FULL na SIMPLE tylko po to, aby szybko zmniejszyć log. Może to przerwać łańcuch backupów i zmienić możliwości odtwarzania.

Autogrowth i AUTO_SHRINK

Autogrowth jest mechanizmem awaryjnym, a nie metodą codziennego zarządzania pojemnością. Przyrosty powinny być ustawione jako stały rozmiar, a nie mały procent. Nie ma jednej poprawnej wartości 100 MB albo 1 GB dla każdej bazy - dla dużej bazy 100 MB może być zdecydowanie zbyt małym przyrostem.

AUTO_SHRINK najczęściej powinien pozostać wyłączony. Cykliczne zmniejszanie plików, które później ponownie rosną, generuje niepotrzebne I/O i może prowadzić do niestabilnej wydajności. Ręczny shrink ma sens głównie po jednorazowym, znacznym usunięciu danych.

Dziesiąty krok: sprawdź tempdb

tempdb jest używany między innymi przez sortowanie, operacje hash, tabele tymczasowe, wersjonowanie wierszy i przebudowy indeksów. Problemy z tempdb mogą wynikać z braku miejsca, częstego autogrowth, wolnego storage lub konkurencji o strony alokacji.

Microsoft zaleca rozpoczynanie od jednakowo skonfigurowanych plików danych. Przy maksymalnie ośmiu procesorach logicznych punktem wyjścia może być jeden plik na procesor, a powyżej ośmiu zwykle osiem plików. Dalsze zwiększanie liczby powinno następować dopiero po potwierdzeniu problemu z allocation contention.

Nie należy bezrefleksyjnie tworzyć jednego pliku tempdb na każdy rdzeń w dużym serwerze. Wszystkie pliki danych tempdb powinny mieć taki sam rozmiar i przyrost. Samo dodanie plików nie naprawi zapytania, które zapisuje do tempdb setki gigabajtów.

Jedenasty krok: zweryfikuj konfigurację równoległości

Dwa ważne ustawienia to max degree of parallelism i cost threshold for parallelism. Domyślne wartości nie zawsze są optymalne, ale nie ma również uniwersalnej zasady „ustaw MAXDOP na 4" albo „połowę rdzeni".

Microsoft uzależnia rekomendacje od liczby procesorów logicznych, topologii NUMA i charakteru obciążenia. Zbyt szeroka równoległość może powodować, że kilka kosztownych zapytań zużyje większość CPU. Zbyt mocne ograniczenie może natomiast znacząco spowolnić raporty i operacje analityczne. Najpierw należy znaleźć zapytania wykorzystujące parallel plans i sprawdzić ich plany oraz rozkład pracy między wątkami.

Dwunasty krok: sprawdź kod aplikacji

Nie każdy problem można naprawić po stronie konfiguracji SQL Servera. Typowe antywzorce aplikacyjne to SELECT *, pobieranie tysięcy rekordów bez paginacji, wykonywanie zapytania w pętli, długie transakcje i funkcje wykonywane na filtrowanych kolumnach. Przykładowo:

-- Mniej korzystne dla indeksu:
WHERE YEAR(OrderDate) = 2026

-- Zwykle bardziej sargowalne:
WHERE OrderDate >= '20260101'
  AND OrderDate <  '20270101'

Nawet bardzo dobry sprzęt nie rozwiąże problemu aplikacji wykonującej tysiące niepotrzebnych zapytań.

Co z brakującymi indeksami?

SQL Server może sugerować brakujące indeksy w planach wykonania i DMV. Taką sugestię należy traktować jako punkt wyjścia, a nie gotowe polecenie do wykonania. Silnik nie uwzględnia w pełni kosztu utrzymania indeksu podczas zapisów ani istniejących podobnych indeksów.

Automatyczne tworzenie wszystkich rekomendowanych indeksów często prowadzi do wielu duplikatów, wolniejszych operacji zapisu i większego zużycia storage. Indeks należy zaprojektować na podstawie rzeczywistego zapytania, planu i całego obciążenia tabeli.

Monitoring jest ważniejszy niż jednorazowy audyt

Najtrudniejsze są problemy, których nie da się odtworzyć po fakcie. Dlatego warto stale zbierać wykorzystanie CPU, pamięć, opóźnienia dysków, rozmiary baz, użycie tempdb, blokady, czas najważniejszych zapytań i stan Query Store.

Dzięki temu można zauważyć, że raport co tydzień działa dłużej, baza rośnie szybciej niż wcześniej, backup przestaje mieścić się w oknie albo plan konkretnego zapytania uległ regresji - zanim stanie się to poważnym problemem.

Bezpieczna checklista, gdy SQL Server zwalnia

Czego nie robić podczas awarii wydajnościowej?

Bez rozpoznania nie należy:

Każda z tych operacji może chwilowo zmienić objawy, ale również pogorszyć sytuację albo usunąć dane potrzebne do ustalenia przyczyny.

Podsumowanie

Gdy SQL Server zwalnia, zwiększenie zasobów jest tylko jedną z możliwych odpowiedzi. Problem może znajdować się w zapytaniu, planie wykonania, blokadzie, statystykach, storage, pamięci, tempdb, konfiguracji albo kodzie aplikacji.

Najskuteczniejsza diagnostyka zaczyna się od ustalenia, co SQL Server aktualnie wykonuje i na co czeka. Dopiero później analizujemy plany, indeksy i konfigurację.

Najpierw zbierz dane, potem zmieniaj środowisko.

W PRO-Admin analizujemy wydajność SQL Servera razem z całym środowiskiem, w którym działa baza: systemem Windows lub Linux, storage, platformą wirtualizacyjną, siecią, backupem i aplikacją biznesową. Dzięki temu nie ograniczamy się do pojedynczego wykresu CPU ani rekomendacji dodania indeksu. Celem diagnostyki nie jest chwilowe przyspieszenie systemu. Celem jest znalezienie przyczyny, bezpieczne usunięcie problemu i wdrożenie monitoringu, który pozwoli zauważyć jego powrót, zanim zrobią to użytkownicy.