Wydajność tempdb istotnie wpływa na całą instancję, dlatego zaleca się kilka plików danych i szybki dysk.
Rola tempdb w silniku SQL Server
TempDB performance oznacza wydajność bazy systemowej tempdb w Microsoft SQL Server. Jest to wspólny obszar roboczy używany przez wiele operacji wykonywanych przez silnik: sortowanie, agregacje, tworzenie tabel tymczasowych, zmienne tabelaryczne, operacje indeksowania, wersjonowanie wierszy oraz niektóre mechanizmy izolacji transakcji. Chociaż tempdb nie przechowuje danych aplikacji po ponownym uruchomieniu usługi, jej przeciążenie może spowolnić zapytania działające w innych bazach tej samej instancji.
Wydajność tempdb zależy przede wszystkim od opóźnień wejścia-wyjścia, dostępnego miejsca, sposobu rozmieszczenia plików oraz konkurencji wielu sesji o te same struktury wewnętrzne. Problem może wystąpić zarówno wtedy, gdy dysk jest zbyt wolny, jak i wtedy, gdy zapytania generują nadmiernie duże zestawy pośrednie. Sama liczba plików nie naprawi źle zaprojektowanych zapytań, braku indeksów ani operacji sortowania, które nie mieszczą się w przydzielonej pamięci.
Pliki danych, rozmiar i konfiguracja dyskowa
tempdb korzysta z jednego pliku dziennika oraz co najmniej jednego pliku danych. Pliki danych powinny mieć początkowo zaplanowany rozmiar odpowiadający przewidywanemu obciążeniu, a ich wartości FILEGROWTH powinny być ustawione w sposób kontrolowany. Wzrost o stały rozmiar, na przykład określoną liczbę megabajtów, jest zwykle bardziej przewidywalny niż wzrost procentowy, ponieważ procentowe powiększanie dużego pliku może powodować bardzo duże, nagłe alokacje.
Przy intensywnej współbieżności często stosuje się kilka równych plików danych tempdb. Równe rozmiary i równe przyrosty pozwalają zachować proporcjonalny algorytm alokacji między plikami. Nie ma uniwersalnej liczby plików odpowiedniej dla każdej instancji; dodawanie kolejnych plików powinno wynikać z obserwacji konfliktów alokacji i charakterystyki obciążenia. Nadmiar niepotrzebnych plików zwiększa złożoność zarządzania, lecz nie daje automatycznej poprawy wydajności.
Pliki danych i dziennika powinny znajdować się na nośniku zapewniającym stabilne opóźnienia oraz wystarczającą przepustowość. Dla tempdb ważne jest również pozostawienie zapasu wolnego miejsca, ponieważ automatyczne powiększanie pliku podczas szczytu obciążenia może chwilowo blokować operacje oczekujące na zakończenie alokacji. Zmiana lokalizacji plików wymaga poprawnej konfiguracji ścieżek w metadanych SQL Server i zwykle ponownego uruchomienia usługi, aby silnik utworzył pliki w nowym miejscu.
Rozpoznawanie presji na tempdb
Diagnoza powinna rozpocząć się od ustalenia, czy problem dotyczy miejsca, opóźnień dyskowych, konfliktów alokacji czy konkretnego zapytania. Widoki dynamicznego zarządzania, takie jak sys.dm_db_file_space_usage oraz sys.dm_db_session_space_usage, pozwalają sprawdzać wykorzystanie przestrzeni przez obiekty tymczasowe i wersjonowane wiersze. Informacje o rozmiarze oraz ścieżkach plików można odczytać z tempdb.sys.database_files.
Wysokie oczekiwania PAGELATCH_UP lub PAGELATCH_EX w bazie tempdb mogą wskazywać na konflikty przy dostępie do stron pamięci, w tym stron struktur alokacji. Są to zatrzymania typu latch, a nie klasyczne blokady transakcyjne. Z kolei oczekiwania PAGEIOLATCH_* częściej wskazują na oczekiwanie na zakończenie operacji wejścia-wyjścia. Interpretacja musi uwzględniać czas trwania, częstotliwość oraz inne oczekiwania występujące w tym samym okresie.
Warto rejestrować zapytania wykonujące duże sortowania, operacje HASH JOIN, tworzenie tabel tymczasowych oraz masowe modyfikacje danych. Plan wykonania może pokazać ostrzeżenia dotyczące wykorzystania tempdb, na przykład przenoszenie części sortowania na dysk. Jednocześnie należy sprawdzić przyrosty plików, zajętość woluminu, obciążenie pozostałych baz i historię restartów. tempdb jest tworzona od nowa przy uruchomieniu instancji, więc chwilowo mała zajętość po restarcie nie dowodzi, że konfiguracja jest wystarczająca dla normalnej pracy.
Dobór działań naprawczych w instancji
Jeżeli obserwowany jest brak miejsca, należy najpierw ustalić, która sesja lub funkcja zużywa przestrzeń, zamiast bezpośrednio usuwać pliki albo zmniejszać ich rozmiar. Zmniejszanie tempdb podczas pracy może pogorszyć sytuację i nie rozwiązuje przyczyny powtarzalnego wzrostu. Trwałym działaniem jest dobranie rozmiaru początkowego, przyrostów oraz woluminu na podstawie zmierzonego obciążenia.
Przy konfliktach alokacji można rozważyć zwiększenie liczby równych plików danych, ale zmianę należy ocenić po wdrożeniu na podstawie oczekiwań i obciążenia. Przy wysokich opóźnieniach wejścia-wyjścia potrzebny jest szybszy lub mniej obciążony magazyn, a także kontrola zapytań generujących duże struktury pośrednie. Pomocne mogą być właściwe indeksy, ograniczenie niepotrzebnych sortowań, poprawa estymacji kardynalności oraz zmniejszenie zakresu danych przetwarzanych jednorazowo.
Każdą zmianę konfiguracji należy poprzedzić pomiarem i zakończyć porównaniem tych samych wskaźników: czasu wykonania zapytań, opóźnień plików, oczekiwań, wykorzystania przestrzeni oraz liczby automatycznych przyrostów. Dzięki temu można odróżnić rzeczywistą poprawę wydajności tempdb od chwilowego spadku obciążenia. W środowisku SQL Server konfiguracja powinna być testowana przy realistycznej liczbie jednoczesnych sesji, ponieważ problem ujawniający się dopiero przy współbieżności nie będzie widoczny podczas pojedynczego testu zapytania.
