w tym artykule omówimy najlepsze praktyki, których należy przestrzegać, aby utrzymać bazę danych TempDB w dobrym stanie i zapobiec nieoczekiwanemu wzrostowi bazy danych, oprócz procedury, którą można zastosować, aby wykryć ten nieoczekiwany wzrost po wystąpieniu.
przegląd TempDB
baza danych TempDB jest jedną z najważniejszych baz danych systemu SQL Server, która służy do przechowywania tymczasowych obiektów użytkownika, takich jak tymczasowe tabele zdefiniowane przez użytkownika lub zwrócone z wykonania funkcji o wartości tabeli, tymczasowe procedury składowane, zmienne tabel lub indeksy.
oprócz obiektów user, TempDB będzie używany do przechowywania obiektów wewnętrznych, które są tworzone przez silnik bazy danych SQL Server podczas różnych operacji wewnętrznych, takich jak sortowanie pośrednie, buforowanie, agregowanie lub operacje kursora.
systemowa baza danych TempDB służy również do przechowywania wersji wierszy w celu obsługi funkcji wymagających śledzenia zmian wykonywanych w wierszach tabeli, takich jak poziom izolacji migawek transakcji, przebudowa indeksu Online lub funkcja wielu aktywnych zestawów wyników.
baza danych TempDB zostanie usunięta i odtworzona ponownie za każdym razem, gdy usługa SQL Server zostanie ponownie uruchomiona, zaczynając od nowej czystej kopii bazy danych. Na tej podstawie wszystkie zgłoszone przez użytkownika i wewnętrzną bazę danych, które są przechowywane w tej bazie danych, zostaną automatycznie usunięte po ponownym uruchomieniu usługi SQL Server lub po rozłączeniu sesji, w której te obiekty zostały utworzone. Dlatego operacje tworzenia kopii zapasowych i przywracania nie są dostępne dla TempDB.
dlaczego TempDB
systemowa baza danych TempDB odgrywa ważną rolę w procesie dostrajania wydajności serwera SQL. Ponieważ jest używany jako pamięć podręczna do przechowywania różnych typów obiektów bazy danych użytkownika i do przechowywania wewnętrznych obiektów systemu w celu przyspieszenia procesów związanych z silnikiem bazy danych SQL Server.
ze względu na tę istotną rolę, jaką odgrywa baza danych systemu TempDB w zwiększaniu ogólnej wydajności instancji serwera SQL, bardzo ważne jest zastosowanie początkowego rozmiaru, automatycznego wzrostu i najlepszych praktyk lokalizacji w danych bazy danych TempDB i plikach dziennika. Zastosowanie tych najlepszych praktyk w sposób, który pasuje do obciążenia instancji serwera SQL, zapobiegnie bardzo częstemu rozszerzaniu danych bazy danych TempDB i plików dziennika, biorąc pod uwagę, że proces rozszerzenia pliku jest kosztownym procesem, w którym silnik serwera SQL zażąda dodatkowej przestrzeni z systemu operacyjnego, który wykona zerowanie tej przestrzeni przed przydzieleniem jej do silnika serwera SQL. Możesz sobie wyobrazić czas i zasoby potrzebne do tego procesu alokacji.
jeśli zastosujesz już wszystkie najlepsze praktyki i wystąpi nieoczekiwana operacja wzrostu, możesz przyspieszyć proces alokacji, korzystając z opcji inicjalizacji natychmiastowego pliku bazy danych, w której system operacyjny zaufa usłudze SQL Server i przydzieli wymaganą przestrzeń bez wykonywania operacji marnowania czasu i zasobów na przydzieloną przestrzeń.
najlepsze praktyki
istnieje wiele najlepszych praktyk konfiguracji bazy danych systemu TempDB, które są zalecane w celu uzyskania najlepszej wydajności z tego współdzielonego zasobu i ochrony instancji serwera SQL przed nieoczekiwanym wzrostem danych bazy danych TempDB lub plików dziennika, które mogą wypełnić podstawową przestrzeń dyskową.
te konfiguracje obejmują:
- liczba plików danych TempDB powinna odpowiadać liczbie procesorów logicznych, maksymalnie ośmiu plików, na komputerze, na którym zainstalowana jest instancja SQL Server. Jeśli liczba procesorów logicznych na tym komputerze jest większa niż osiem, Ustaw liczbę plików danych na osiem, z możliwością Rozszerzenia o wielokrotność 4 w przypadku wystąpienia problemu z kwestionowaniem TempDB
- upewnij się, że pliki TempDB mają równy rozmiar w tej samej grupie plików, aby zapewnić najwyższy możliwy stopień wydajności operacji równoległych, ze względu na proporcjonalny mechanizm zapisu wypełnienia, który jest używany do zapisu danych w plikach danych. Aby uzyskać więcej informacji, sprawdź zrozumienie algorytmu proporcjonalnego wypełniania SQL Server
- Skonfiguruj dane bazy danych TempDB i pliki dziennika o rozsądnym rozmiarze początkowym i ilościach automatycznego wzrostu, które pasują do oczekiwanej ilości danych, które zostaną zapisane w tych plikach, a także różne typy obciążeń wykonywanych w instancji SQL Server, w tym działania użytkownika i operacje konserwacji indeksów, aby uniknąć częstych małych przyrostów, które wpływają na ogólną wydajność instancji
- Zlokalizuj pliki TempDB w podsystemie szybkich I/O w celu unikaj wszelkich Wąskie gardła we/wy
- Zlokalizuj TempDB na oddzielnym dysku, z dala od baz danych użytkowników i innych systemowych baz danych. W ten sposób intensywne operacje odczytu i zapisu wykonywane na TempDB nie będą miały wpływu na obciążenie baz danych użytkowników, zwiększając ogólną wydajność instancji serwera SQL
aby uzyskać więcej informacji na temat nowych ulepszeń wydajności TempDB, sprawdź dokument bazy danych TempDB.
gdzie zastosować
przed wersją SQL Server 2016 alokację rozmiaru TempDB można wykonać po zainstalowaniu instancji SQL Server na stronie Właściwości bazy danych. Gdzie można kontrolować liczbę plików danych bazy danych, które pasują do bieżącej instancji SQL Server, początkowy rozmiar dla każdej bazy danych i pliku dziennika, odpowiednią wartość dla opcji Autogrowth oraz lokalizację danych bazy danych i plików dziennika.
aby wykonać te zmiany, rozwiń węzeł baz danych w SQL Server Management Studio, rozwiń węzeł baz danych systemowych, a następnie kliknij prawym przyciskiem myszy bazę danych tempdb i wybierz opcję Właściwości, jak poniżej:
w otwartym oknie Właściwości bazy danych przejrzyj stronę pliki i sprawdź lub zmień różne ustawienia danych bazy danych i plików dziennika pokazane poniżej:
począwszy od wersji SQL Server 2016, Wszystkie te zmiany, z najlepszymi praktykami, które pasują do bieżących zasobów serwera już zasugerowanych przez kreatora instalacji, można sprawdzić i wykonać podczas procesu instalacji instancji SQL Server, za pomocą Kreatora instalacji SQL Server, w konfiguracji silnika bazy danych | TempDB, jak pokazano poniżej:
śledź wzrost TempDB
śledzenie wzrostu plików bazy danych TemDB i zapytań, które zużywają zasoby TempDB, można wykonywać za pomocą liczników Performance Monitor powiązanych z plikami TempDB i narzędzia SQL Profiler, które nasłuchuje określonego typu obciążenia bazy danych.
bardziej przyjaznym sposobem administratora bazy danych na śledzenie wzrostu plików bazy danych TempDB jest Zapytanie o następujące Dynamiczne widoki zarządzania:
- sys.dm_db_file_space_usage, który zwraca informacje o wykorzystaniu miejsca dla każdego pliku w bazie danych, bez pokazywania, która sesja lub zadanie pochłonęły tę przestrzeń
- sys.dm_db_session_space_usage, który zwraca liczbę przydzielonych i dealokowanych stron w każdej sesji
- sys.dm_db_task_space_usage, który zwraca liczbę przydzielonych i dealokowanych stron dla każdego zadania
zauważ, że wynik zwracany przez DMV sys.dm_db_session_space_usage podsumował wynik zwracany z sys.dm_db_task_space_usage DMV gdy wszystkie zadania zostaną zakończone pomyślnie, ze względu na fakt, że wiele zadań związanych z tym samym zapytaniem zostanie wykonanych w ramach tej samej sesji. Inną zaletą tych DMV jest to, że pokazuje typ obiektu, który zużywa zasoby TempDB i czy obiekt ten jest obiektem użytkownika, takim jak tymczasowa lub zmienna tabela lub obiekt wewnętrzny, taki jak konserwacja indeksu lub operacja sortowania. Tak więc, na podstawie liczby stron zajętych przez każdy obiekt, możesz określić, czy TempDB jest zużywany przez obiekt wewnętrzny lub użytkownika i zoptymalizować tego konsumenta.
sys.dm_db_file_space_usage
SYS. dm_db_file_space_usage DMV może być używany do monitorowania miejsca na dysku używanego w plikach bazy danych TempDB. Poniższe zapytania pokazują nieprzydzieloną przestrzeń, przestrzeń używaną do przechowywania wersji wierszy, przestrzeń zajmowaną przez obiekty wewnętrzne i wreszcie przestrzeń zajmowaną przez obiekty użytkownika, w następujący sposób:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS TempDB_FreeSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
SELECT (SUM(version_store_reserved_page_count)*1.0/128) AS TEMPDB_VERSIONSTORESPACEAMOUNT_INMB
from sys.Dm_db_file_space_usage;
select (Sum(internal_object_reserved_page_count)*1.0/128) as TempDB_InternalObjSpaceAmount_Inmb
FROM sys.dm_db_file_space_usage;
SELECT (SUM(user_object_reserved_page_count)*1.0/128) AS TempDB_UserObjSpaceAmount_InMB
from SYS.Dm_db_file_space_usage;
|
z wyniku, który jest zwracany z mojej lokalnej instancji SQL Server, widać, że w plikach TempDB jest nieużywane 60 MB, z czego tylko 0,5 MB jest używane dla obiektów wewnętrznych i 1,5 MB jest używane dla obiektów użytkownika, jak pokazano poniżej:
sys.dm_db_task_space_usage and sys.dm_db_session_space_usage
sys.dm_db_task_space_usage and sys.dm_db_session_space DMV są używane do sprawdzania liczby przydzielonych i dealokowanych stron zadanie lub sesja w bazie tempdb. W ten sposób będziesz mógł zobaczyć, który Użytkownik lub wewnętrzny obiekt zużywa przestrzeń TempDB.
na przykład, poniższy wynik jest zwracany z zapytania sys.dm_db_task_space_usage DMV w bazie danych TempDB, gdzie zwróci liczbę stron przydzielonych i dealokowanych dla każdego użytkownika lub zadania wewnętrznego, w następujący sposób:
ale DBA nie będzie wymagało wielu szczegółów, aby rozpocząć śledzenie zapytania, które zużywa zasoby TempDB. Musimy więc podsumować poprzedni wynik dla każdej sesji, korzystając z poniższego zapytania:
|
1
2
3
4
5
6
7
8
|
WYBIERZ session_id,
KWOTA(internal_objects_alloc_page_count) JAK numofpagesallocatedintempdb do wewnętrznej zadania
KWOTA(internal_objects_dealloc_page_count) JAK numofpagesdellocatedintempdb do wewnętrznej zadania
KWOTA(user_objects_alloc_page_count) JAK NumOfPagesAllocatedInTempDBforUsertask,
KWOTA(user_objects_dealloc_page_count) JAKO NumOfPagesDellocatedInTempDBforUsertask
FROM sys. dm_db_task_space_usage
GROUP BY session_id
ORDER BY NumOfPagesAllocatedInTempDBforInternaltask DESC, NumOfPagesAllocatedInTempDBforUsertask DESC
|
a podsumowany wynik zużycia stron na każdą sesję będzie taki jak poniżej:
ten sam wynik może zostać zwrócony przez zapytanie sys.dm_db_session_space_usage DMW, który podsumowuje alokację stron i dealokację dla wszystkich zadań uruchomionych w ramach tej samej sesji, jak pokazano poniżej:
pozwól nam wykonać pewne działania na bazie danych TempDB przy użyciu zarówno wewnętrznych, jak i obiektów użytkownika, jak poniżej:
następnie wykonaj skonsolidowane zapytanie poniżej, które zwróci liczbę przydzielonych i dealokowanych stron przez obiekty wewnętrzne i Użytkownika oraz wyświetli oba zakończone operacje (za pomocą sys.dm_db_session_space_usage DMV) i niezakończonych operacji (za pomocą sys. dm_db_task_space_usage DMV):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
wybierz Spacjepertask.session_id,
internal_objects_alloc_page_count
+ SUM(Spacja.internal_objects_alloc_page_count) jako NumOfPagesAllocatedInTempDBforInternaltask,
SpacePerSession.internal_objects_dealloc_page_count
+ SUM(Spacja.internal_objects_dealloc_page_count) jako NumOfPagesDellocatedInTempDBforInternaltask,
SpacePerSession.User_objects_alloc_page_count
+ SUM(Spacja.User_objects_alloc_page_count) jako NumOfPagesAllocatedInTempDBforUsertask,
SpacePerSession.User_objects_dealloc_page_count
+ SUM(Spacja.User_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUsertask
FROM sys. dm_db_session_space_usage AS SpacePerSession
INNER JOIN sys.dm_db_task_space_usage as SpacePerTask ON SpacePerSession.session_id = SpacePerTask.session_id
GROUP BY SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_objects_alloc_page_count, SpacePerSession.User_objects_dealloc_page_count
ORDER BY NumOfPagesAllocatedInTempDBforInternaltask DESC, NumOfPagesAllocatedInTempDBforUsertask DESC
|
poprzedni wynik operacji będzie odzwierciedlał wynik zapytania, pokazując liczbę stron przydzielonych i dealokowanych przez utworzenie tymczasowej tabeli użytkownika i sortowanie danych podczas procesu odbudowy indeksu, jak pokazano poniżej:
aby uzyskać tekst zapytania, które zużywa zasoby TempDB, możesz użyć swoich umiejętności programistycznych SQL, łącząc wynik DMV ys.dm_db_task_space_usage z innymi DMV, takimi jak sys.dm_exec_requests i sys.dm_exec_sessions z dynamiczną funkcją zarządzania sys.dm_exec_sql_text, gdzie wyświetli zapytania, które pochłaniają teraz przestrzeń tempdb, tak jak w tym zapytaniu.
wynik będzie podobny do poniższego:
- Autor
- Ostatnie posty
jest ekspertem Microsoft Certified Solution w zarządzaniu danymi i analityce, Microsoft Certified Solution Associate w administracji i rozwoju baz danych SQL, Azure Developer Associate i Microsoft Certified Trainer.
również przyczynia się do swoich wskazówek SQL na wielu blogach.
Zobacz wszystkie posty, których autorem jest Ahmad Yaseen
- pytania i odpowiedzi dotyczące wywiadu z Azure Data Factory-11 lutego 2021 r.
- jak monitorować Azure Data Factory-15 stycznia 2021 r.
- Korzystanie z kontroli źródła w Azure Data Factory-styczeń 12, 2021