Transakcje a tempdb

Transakcje intensywnie korzystające z tempdb mogą spowolnić całą instancję przy niedostatecznej konfiguracji tej bazy.

Rola tempdb w obsłudze transakcji SQL Server

W SQL Server baza systemowa tempdb jest wspólnym obszarem roboczym używanym przez wszystkie bazy danych obsługiwane przez daną instancję. Transakcja może korzystać z tempdb bezpośrednio, gdy aplikacja tworzy tabele tymczasowe lub zmienne tabelaryczne, a także pośrednio, gdy wykonanie zapytania wymaga zapisania danych pośrednich. Do takich operacji należą między innymi sortowanie, grupowanie, operatory hash, materializacja wyników oraz obsługa niektórych planów wykonania. Duże zapytanie uruchomione wewnątrz transakcji może zatem zwiększyć zapotrzebowanie na przestrzeń tempdb i operacje wejścia-wyjścia.

Sama transakcja użytkownika nie oznacza, że każdy jej zapis trafia do tempdb. Zwykłe modyfikacje tabel trwałych są rejestrowane przede wszystkim w dzienniku transakcji właściwej bazy danych. Tempdb uczestniczy jednak w mechanizmach potrzebnych do izolacji i przetwarzania danych. Przykładem jest wersjonowanie wierszy wykorzystywane przez poziom izolacji READ COMMITTED SNAPSHOT, SNAPSHOT oraz inne funkcje korzystające z row versioning. W takim przypadku wersje zmienionych wierszy są przechowywane w tempdb, a aktywna transakcja może przedłużać czas ich utrzymywania.

Jak rośnie obciążenie podczas długiej transakcji

Największe ryzyko pojawia się wtedy, gdy transakcja pozostaje otwarta przez długi czas i jednocześnie wykonuje wiele modyfikacji albo odczytów. Przy włączonym wersjonowaniu SQL Server nie może usunąć wersji wierszy potrzebnych jeszcze aktywnej transakcji. Przestrzeń zajmowana przez version store może więc rosnąć mimo zakończenia części zapytań wykonanych równolegle. Jeżeli transakcja czeka na blokadę, błąd aplikacji nie wywołał COMMIT albo kod otworzył transakcję przed pobraniem danych i zamknął ją dopiero po długiej operacji, odzyskiwanie miejsca może zostać opóźnione.

Drugim źródłem obciążenia są obiekty tymczasowe tworzone w ramach transakcji lub zapytań wykonywanych przez sesję. Tabela tymczasowa utworzona za pomocą nazwy rozpoczynającej się od znaku „#” jest fizycznie obsługiwana w tempdb, chociaż jej zakres widoczności zależy od rodzaju obiektu. Rozbudowane operacje sortowania i hash mogą dodatkowo używać plików roboczych, gdy pamięć przydzielona operatorowi zapytania jest niewystarczająca. W efekcie jedna transakcja może zużywać zarówno przestrzeń danych tempdb, jak i przepustowość magazynu oraz zasoby CPU potrzebne do obsługi jej planu wykonania.

Objawy niedostatecznej konfiguracji tempdb

Problemy z tempdb mogą być widoczne jako spadek wydajności wielu, pozornie niezależnych zapytań. Charakterystyczne są oczekiwania związane z alokacją stron, rywalizacja o wewnętrzne struktury alokacyjne, zwiększona aktywność dyskowa oraz błędy informujące o braku miejsca w bazie tempdb. W przypadku operacji wykorzystujących version store warto sprawdzić, czy nie ma długo trwających transakcji blokujących usuwanie niepotrzebnych wersji. Przydatne jest porównanie czasu rozpoczęcia transakcji z czasem trwania sesji oraz analiza tego, czy sesja czeka na blokadę, czy aktywnie wykonuje zapytanie.

Do oceny sytuacji można wykorzystać dynamiczne widoki zarządzania SQL Server, między innymi sys.dm_db_file_space_usage do sprawdzania wykorzystania plików tempdb oraz sys.dm_tran_active_snapshot_database_transactions do analizy transakcji związanych z izolacją opartą na wersjonowaniu. Informacje o aktywnych transakcjach i ich wieku można zestawić z widokami sys.dm_tran_current_transaction oraz sys.dm_tran_session_transactions. Należy również sprawdzić rozmiar plików danych i dziennika tempdb, ich ustawienia autowzrostu oraz miejsce dostępne na woluminie. Samo powiększenie pliku nie usuwa przyczyny, jeżeli problemem jest niezamykana transakcja albo zapytanie generujące nadmierną ilość danych pośrednich.

Porządkowanie przepływu pracy między transakcją a tempdb

Analizę należy rozpocząć od ustalenia, jaki mechanizm rzeczywiście wykorzystuje tempdb. Inaczej interpretuje się wzrost version store, inaczej zajęcie przez tabele tymczasowe, a jeszcze inaczej pliki robocze operatorów sortowania lub hash. Warto przeanalizować plan wykonania, czas trwania transakcji, liczbę przetwarzanych wierszy oraz moment wykonania COMMIT albo ROLLBACK. Jeżeli aplikacja pobiera dane etapami, transakcja powinna obejmować tylko operacje wymagające atomowości. Przetrzymywanie jej otwartej podczas oczekiwania na użytkownika, zewnętrzną usługę lub operację plikową zwiększa ryzyko blokad i długiego utrzymywania wersji w tempdb.

Po stronie instancji SQL Server sprawdza się poprawność konfiguracji plików tempdb, równomierny rozkład obciążenia między plikami danych, odpowiedni zapas miejsca oraz przewidywalne ustawienia wzrostu. Nie zastępuje to optymalizacji zapytań: indeksy, ograniczenie zbędnego sortowania, właściwe predykaty i przetwarzanie mniejszych partii danych mogą zmniejszyć ilość pracy wykonywanej w tempdb. Diagnostyka powinna obejmować również aplikację, ponieważ niezatwierdzona transakcja, nieobsłużony wyjątek lub nieprawidłowe zarządzanie połączeniem może utrzymywać zasoby znacznie dłużej, niż wynikałoby to z samego czasu wykonania zapytania.