Przeciążenie tempdb objawia się spowolnieniem operacji sortowania i tabel tymczasowych całej instancji.
Rola tempdb w Microsoft SQL Server
tempdb to systemowa baza danych silnika Microsoft SQL Server, tworzona ponownie podczas każdego uruchomienia usługi. SQL Server wykorzystuje ją między innymi do przechowywania tabel tymczasowych, tabel typu table variable, wyników pośrednich, obiektów tworzonych przez niektóre procedury oraz danych potrzebnych przy operacjach sortowania, agregacji, łączenia i przetwarzania zapytań. W tempdb znajdują się także struktury używane przez mechanizmy wersjonowania wierszy, w tym przez izolację migawek i odczyty z użyciem wersjonowania, zależnie od konfiguracji instancji i baz użytkownika.
Przeciążenie tempdb nie oznacza wyłącznie zapełnienia jej plików. Problem może dotyczyć także zbyt wolnego magazynu danych, niewystarczającej przepustowości operacji wejścia i wyjścia, konfliktów alokacji stron, nadmiernej liczby jednoczesnych operacji tymczasowych albo długotrwałego przetrzymywania wersji wierszy. Ponieważ z tej samej bazy korzystają wszystkie bazy użytkownika działające w danej instancji, lokalny problem jednego zapytania może powodować opóźnienia widoczne w wielu niezależnych aplikacjach.
Jak powstaje wąskie gardło podczas pracy zapytań
Zapytanie może wymagać obszaru roboczego w tempdb, gdy sortowanie, grupowanie lub operacja hash join nie mieści się w przydzielonej pamięci. W takiej sytuacji SQL Server wykonuje część operacji na dysku, tworząc pliki robocze i zapisując dane pośrednie. W planie wykonania można wtedy zauważyć ostrzeżenia dotyczące niedoboru pamięci dla operatora sortowania lub haszowania, a monitorowanie wykonania może wskazywać na nadmiarowe użycie pamięci roboczej. Częste zrzucanie danych z pamięci do tempdb zwiększa opóźnienia, ale samo wystąpienie operacji dyskowej nie dowodzi jeszcze przeciążenia całej bazy.
Drugą grupę przyczyn stanowią tabele tymczasowe, obiekty tworzone przez procedury oraz zapytania generujące duże zbiory pośrednie. Nieefektywne predykaty, brak odpowiednich indeksów, nieaktualne statystyki lub błędne oszacowanie liczby wierszy mogą prowadzić do planów wymagających znacznie większych obszarów roboczych, niż zakładał optymalizator. Obciążenie może również wzrosnąć przez włączone mechanizmy wersjonowania wierszy. W takim przypadku aktywne transakcje mogą utrzymywać stare wersje dłużej, a sprzątanie tych wersji nie może zakończyć się, dopóki wymagane są one przez odpowiedni odczyt lub transakcję.
Objawy i pomiary potwierdzające problem
Typowym objawem przeciążenia jest wzrost czasu wykonywania zapytań korzystających z sortowania, grupowania, tabel tymczasowych albo wersjonowania wierszy. W dzienniku aplikacji może pojawić się ogólne spowolnienie, lecz taki zapis nie identyfikuje przyczyny. Do oceny sytuacji należy zestawić czas oczekiwania, aktywność sesji, zużycie miejsca oraz opóźnienia wejścia i wyjścia dla poszczególnych plików tempdb. Pomocne są dynamiczne widoki zarządzania SQL Server, między innymi sys.dm_db_file_space_usage, sys.dm_db_task_space_usage i sys.dm_db_session_space_usage. Pierwszy z nich pokazuje wykorzystanie przestrzeni plików, a dwa pozostałe pozwalają wiązać zużycie przestrzeni z zadaniami i sesjami.
Warto także sprawdzić oczekiwania związane z alokacją stron. W SQL Server mogą one pojawić się jako PAGELATCH_UP, PAGELATCH_EX lub pokrewne typy oczekiwania dotyczące stron w pamięci. Ich duża liczba w tempdb może wskazywać na rywalizację podczas alokowania stron lub metadanych, ale interpretacja wymaga sprawdzenia identyfikatorów baz i stron oraz aktualnego obciążenia. Inny obraz daje oczekiwanie PAGEIOLATCH_*, które wiąże się z oczekiwaniem na odczyt strony z dysku. Zapełnienie plików, presja na pamięć, opóźnienia nośnika i rywalizacja alokacji są odrębnymi zjawiskami, dlatego nie powinny być diagnozowane na podstawie jednego licznika.
Postępowanie serwisowe i ograniczanie skutków
Diagnostykę należy rozpocząć od ustalenia, które sesje i zapytania zużywają przestrzeń w tempdb oraz czy zużycie dotyczy obiektów tymczasowych, tabel roboczych, wersji wierszy czy wolnego miejsca niewystarczającego do bieżących operacji. Sprawdza się rozmiar, wzrost i lokalizację plików danych oraz pliku dziennika tempdb, a także ich opóźnienia wejścia i wyjścia. Przyczyną może być zbyt mały początkowy rozmiar plików i częste automatyczne powiększanie. Takie powiększanie powoduje dodatkowe operacje systemu plików i nie zastępuje prawidłowego dobrania pojemności do rzeczywistego obciążenia. Zmiany konfiguracji powinny wynikać z pomiarów oraz uwzględniać dostępne miejsce na woluminie.
Trwałe ograniczenie problemu zwykle wymaga korekty zapytań, indeksów, statystyk albo sposobu prowadzenia transakcji, a nie tylko zwiększenia rozmiaru tempdb. Należy identyfikować zapytania wykonujące duże sortowania i agregacje, sprawdzać ich plany wykonania oraz usuwać niepotrzebnie długie transakcje. Konfigurację wielu plików danych tempdb oraz ich równomiernego wzrostu ocenia się w kontekście konkretnej wersji SQL Server, liczby rdzeni, rodzaju obciążenia i obserwowanych oczekiwań. Restart instancji usuwa zawartość tempdb, lecz nie naprawia przyczyny przeciążenia i może powodować przerwę w pracy usług zależnych od SQL Server.
