Czy zapisy do TempDB zawsze powodują rzeczywisty fizyczny zapis na dysk, czy też zapisy TempDB są buforowane przez SQL Server dla opóźnionego zapisu, jak w pamięci podręcznej systemu plików Windows?
Czy oni zawsze? Zdecydowanie nie. Czy oni kiedykolwiek Tak, ale nie w wyniku typowego mechanizmu. Odniesieniem tutaj jest Co robi punkt kontrolny dla tempdb? .
W „dobrze wychowanym” systemie zapisy do pliku bazy danych użytkownika występują w punkcie kontrolnym. W źle zachowującym się systemie, zapisy wystąpią również, gdy lazywriter będzie musiał opróżnić strony z puli buforów, aby zrobić miejsce dla innych stron.
Punkt kontrolny jest wykonywany tylko dla tempdb, gdy plik dziennika tempdb osiągnie 70% zapełnienia - ma to na celu zapobieganie wzrostowi dziennika tempdb, jeśli to w ogóle możliwe (zwróć uwagę, że długotrwała transakcja może zasadniczo zatrzymać zakładnika dziennika i zapobiec jego wyczyszczeniu , podobnie jak w bazie danych użytkowników).
Ale nie ma potrzeby opróżniania tempdb na dysk, ponieważ odzyskiwanie po awarii nigdy nie jest uruchamiane na tempdb, zawsze jest odtwarzane ponownie podczas uruchamiania.
Tempdb nie jest odzyskiwany w przypadku awarii, więc nie ma potrzeby wymuszania na dysku brudnych stron tempdb, z wyjątkiem przypadku, gdy proces lazywriter (część puli buforów) musi zwolnić miejsce dla stron z innych baz danych.
To zadziwiające (co było dla mnie zaskoczeniem) jest jedynym mechanizmem, za pomocą którego strony tempdb będą zapisywane na dysk. Jeśli występuje ciśnienie w puli buforów, strony tempdb mogą zostać opróżnione na dysk. Jeśli nie ma, nie powinno się zdarzyć.
Edycja: dyskusyjne, czy „źle zachowane” jest odpowiednim opisem dla bazy danych użytkowników, która pisze strony poza punktem kontrolnym. Niezwykły, nietypowy, a może po prostu nie idealny?
Dodatkowa edycja (następujące komentarze / czat z @PaulWhite):
Rażące pominięcie powyżej polega na tym, że tabele tymczasowe nie są jedynym źródłem ruchu w tempdb. Cytowanie ze zrozumienia skrótów, sortowania i wymiany zdarzeń rozlewania :
Niektóre operacje wykonywania zapytań programu SQL Server są skalibrowane w celu uzyskania najlepszej wydajności przy użyciu (nieco) dużej ilości pamięci jako pamięci pośredniej. Optymalizator zapytań wybierze plan i oszacuje koszt na podstawie tych operatorów korzystających z tego notatnika. Ale to oczywiście tylko szacunek. Podczas wykonywania oszacowania mogą okazać się błędne i plan musi być kontynuowany, pomimo braku wystarczającej ilości pamięci. W takim przypadku operatorzy ci przeniosą się na dysk.
Niepoprawnie założyłem, że mechanizm fizycznego zapisu dla operacji rozlewania był dokładnie taki sam, jak opisano wcześniej, tj. Lazywriter zmuszający strony tempdb do dysku w wyniku nacisku na pulę buforów (spowodowanego rozlewaniem).
@PaulWhite wyjaśnił, gdzie się myliłem (dzięki Paul!):
Myślę, że pytasz, dlaczego fizyczna aktywność tempdb występuje, gdy zapytanie przekracza przyznaną pamięć obszaru roboczego, a nie tylko korzysta z tempdb-in-memory. Gdyby to zrobiło, zapytanie wykorzystywałoby więcej pamięci niż przyznanie, pokonując punkt ograniczenia pamięci dotacje w pierwszej kolejności.
Wycieki są rzeczywiście wyjątkowe w formie pisemnej do przechowywania. Aktywność fizyczna tempdb jest widoczna na wylocie, nawet w obliczu ogromnej ilości wolnej pamięci i zerowego nacisku na tempdb.
Paul wskazał mi także swój post na blogu Zaawansowane strojenie TSQL: Dlaczego wiedza wewnętrzna ma znaczenie, który zawiera przykładowe skrypty do demonstrowania wycieków dla tych, którzy chcą zagłębić się głębiej.