Diagnostyka tempdb

Diagnostyka tempdb to proces analizy stanu i wydajności tymczasowej bazy systemowej wykorzystywanej przez SQL Server.

Rola tempdb w silniku SQL Server

tempdb jest systemową bazą danych używaną przez SQL Server do przechowywania danych tymczasowych tworzonych podczas pracy instancji. Silnik korzysta z niej między innymi przy tabelach tymczasowych, zmiennych tabelarycznych, wynikach pośrednich zapytań, operacjach sortowania, haszowania, materializacji części planu wykonania oraz obsłudze wersjonowania wierszy. Zawartość tej bazy nie jest przeznaczona do trwałego przechowywania danych użytkowych. Podczas uruchamiania usługi SQL Server baza tempdb jest tworzona ponownie na podstawie ustawień zapisanych w metadanych instancji.

Diagnostyka tempdb obejmuje analizę zarówno plików danych i pliku dziennika transakcji, jak i sposobu, w jaki poszczególne sesje oraz zapytania wykorzystują przestrzeń tymczasową. Problemy mogą wynikać z braku wolnego miejsca, niewłaściwego rozmieszczenia plików, powolnego podsystemu dyskowego, intensywnego użycia tabel tymczasowych albo dużej liczby operacji wymagających zapisu danych pośrednich. Sama obecność dużego pliku tempdb nie oznacza jeszcze awarii, ponieważ jego rozmiar może być skutkiem wcześniejszego obciążenia i pozostawać stabilny po zakończeniu zapytań.

Pliki, rozmiar i autowzrost tempdb

Podstawowym etapem analizy jest sprawdzenie listy plików tempdb, ich typów, rozmiarów, bieżącego zajęcia oraz lokalizacji. Informacje te można odczytać z widoku katalogowego sys.database_files po przełączeniu kontekstu na bazę tempdb. Dla plików danych istotne są między innymi rozmiar początkowy, rozmiar maksymalny i przyrost autowzrostu. Dla pliku dziennika należy dodatkowo ocenić, czy jego rozmiar wynika z rzeczywistego zapotrzebowania, czy z powtarzających się operacji autowzrostu.

Wartości autowzrostu mogą być określone w megabajtach albo jako procent bieżącego rozmiaru pliku. Przyrost procentowy powoduje, że kolejne rozszerzenia zmieniają się wraz z rozmiarem pliku, co utrudnia przewidywanie zachowania i może prowadzić do bardzo dużych jednorazowych operacji wejścia-wyjścia. Diagnostyka powinna też uwzględniać, czy pliki danych mają porównywalny rozmiar i taki sam przyrost. Nieprawidłowa konfiguracja może powodować nierównomierne użycie plików oraz dodatkowe opóźnienia w czasie ich rozszerzania.

Rozszerzanie pliku tempdb wymaga operacji na systemie plików, a w przypadku dziennika również odpowiedniej inicjalizacji jego zawartości. W przeciwieństwie do plików danych tempdb, plik dziennika nie korzysta z mechanizmu natychmiastowej inicjalizacji plików. Dlatego częste autowzrosty dziennika mogą być zauważalne jako opóźnienia. Podczas kontroli należy sprawdzić, czy rozmiary plików są wystarczające dla przewidywanego obciążenia i czy dostępna przestrzeń na woluminach pozwala na ich dalszy wzrost.

Zużycie przestrzeni przez sesje i zapytania

W SQL Server przestrzeń tempdb jest wykorzystywana przez obiekty użytkownika oraz obiekty wewnętrzne silnika. Obiekty użytkownika obejmują na przykład lokalne i globalne tabele tymczasowe oraz indeksy utworzone dla takich tabel. Obiekty wewnętrzne są tworzone przez silnik na potrzeby realizacji planów zapytań, między innymi podczas sortowania, agregacji, operacji hash join i innych etapów, w których wynik pośredni nie mieści się wyłącznie w pamięci.

Do analizy przydziałów przestrzeni można wykorzystać widoki dynamicznego zarządzania, takie jak sys.dm_db_session_space_usage i sys.dm_db_task_space_usage. Pierwszy z nich pomaga powiązać zużycie z sesjami, a drugi pokazuje przydziały na poziomie zadań. W połączeniu z sys.dm_exec_requests, sys.dm_exec_sessions oraz informacjami o aktualnym zapytaniu można ustalić, która sesja generuje największe zapotrzebowanie. Trzeba rozróżnić przestrzeń przydzieloną od przestrzeni zwolnionej, ponieważ wysokie wartości historyczne nie zawsze oznaczają bieżące zajęcie pliku.

Istotnym sygnałem diagnostycznym są także oczekiwania związane z tempdb. Oczekiwanie PAGELATCH_UP lub PAGELATCH_EX wskazuje na rywalizację o strony w pamięci, natomiast oczekiwania PAGEIOLATCH_* wiążą się z oczekiwaniem na operacje wejścia-wyjścia dla stron danych. Interpretacja wymaga sprawdzenia całego planu wykonania, obciążenia dysków, liczby plików oraz rozkładu przydziałów. Pojedynczy typ oczekiwania nie stanowi samodzielnego dowodu konkretnej przyczyny.

Wersjonowanie wierszy i diagnostyka obciążenia

Jeżeli na instancji działają mechanizmy wykorzystujące wersjonowanie wierszy, tempdb może przechowywać wersje wierszy potrzebne do zachowania wymaganej izolacji odczytu. Dotyczy to między innymi poziomów izolacji opartych na wersjonowaniu oraz funkcji korzystających z magazynu wersji. Długotrwała transakcja może utrzymywać stare wersje przez długi czas, nawet gdy jej aktywne zapytanie chwilowo nie wykonuje intensywnych operacji. W efekcie przestrzeń zajęta przez magazyn wersji może rosnąć i nie zwalniać się natychmiast.

Do sprawdzania stanu magazynu wersji można wykorzystać sys.dm_db_file_space_usage, a do identyfikowania aktywnych transakcji i sesji pomocne są widoki sys.dm_tran_active_snapshot_database_transactions oraz sys.dm_exec_sessions. Analiza powinna obejmować czas trwania transakcji, bazę, z którą jest związana, sesję ją utrzymującą oraz aktualne zużycie przestrzeni. Należy też porównać dane z historią wzrostu plików i licznikami wydajności, ponieważ krótkotrwały skok wykorzystania może mieć inną przyczynę niż stały przyrost magazynu wersji.

Kompletna diagnostyka łączy dane z widoków dynamicznego zarządzania, konfigurację plików, plan wykonania problematycznych zapytań oraz parametry podsystemu dyskowego. Celem nie jest automatyczne zmniejszenie plików po każdym epizodzie, lecz ustalenie, czy źródłem problemu jest zapytanie, długotrwała transakcja, brak miejsca, opóźnienia wejścia-wyjścia, konfiguracja autowzrostu czy konkurencja wielu zadań o zasoby tempdb. Zmiana rozmiaru plików powinna wynikać z rozpoznanej przyczyny i zostać poprzedzona oceną dostępnej przestrzeni oraz wpływu operacji na działającą instancję.