SQL Server Tutorial TempDB

By: Eric Blinn | Updated: 2020-07-17 | Comments (2) | Related: More> systemowe bazy danych

Problem

SQL Server jest domyślnie instalowany z czterema systemowymi bazami danych. Są master, model, msdb i TempDB. Muszę wiedzieć, co to jest systemowa baza danych „TempDB” i do czego jest używana. Te poprzednie tutoriale covermaster, model andmsdb w szczegółach.

rozwiązanie

ta wskazówka obejmie bazę danych TempDB. TempDB to baza danych, która mawiele funkcji w SQL Server, ale rzadko jest wywoływana jawnie. Posiada tak wiele funkcji, że jest często jedną z najbardziej ruchliwych, jeśli nie najbardziej ruchliwych baz danych w większości instancji SQL Server. Czytaj dalej, aby dowiedzieć się, jak wiele z tych funkcji jest!

SQL Server TempDB przegląd

jaki jest cel SQL Server TempDB?

jedną z funkcji TempDB jest działanie czegoś w rodzaju strony lub pliku wymiany na poziomie systemu operacyjnego. Jeśli operacja SQL Server jest zbyt duża, aby została wykonana w pamięci lub jeśli początkowe przydzielenie pamięci dla zapytania jest zbyt małe, operacja może zostać przeniesiona na dysk w programie TempDB.

inną funkcją TempDB jest przechowywanie tabel tymczasowych. Każdy, kto hascreated tymczasową tabelę w T-SQL za pomocą funta lub skrótu prefix ( # ) lub doublepound / hash prefix ( # # ) stworzył obiekt w TempDB, jak to jest, gdzie są one przechowywane.

CREATE TABLE #MSSQLTips (RowID int);

gdy atrigger wykonuje wstawione i usunięte tabele wirtualne są przechowywane w TempDB.

wszystkie bazy danych, w których użyto RCSI (ang. COMMITTED SNAPSHOT ISOLATION), będą miały informacje o ich wersji zapisane w programie TempDB.

każda z powyższych funkcji TempDB jest wypełniona bez faktycznego wspominania o nazwie tempdb i dlatego może być zaskakującym zastosowaniem dla TempDB.

TempDB można również wywołać jawnie na kilka sposobów. Tabele mogą być generowane w programie TempDB, odwołując się do bazy danych w instrukcji create. Kod wygląda jak normalna operacja DDL, ale gdy jest uruchamiany w TempDB, tabela jest z definicji tabelą tymczasową.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB jest regenerowany po każdym uruchomieniu instancji SQL Server. Wszelkie obiekty, które mogły zostać utworzone w programie TempDB podczas poprzedniej sesji, nie będą widoczne po ponownym uruchomieniu usługi. TempDB pobiera swoją początkową listę obiektów z bazy danych themodel, która zazwyczaj będzie pusta lub prawie pusta.

konserwacja indeksu może być opcjonalnie poproszona o wykonanie sortowania w TempDB, a nie o próbę zrobienia tego przy użyciu wolnego miejsca w bazie danych użytkownika.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

Czy mogę uruchomić SQL Server bez bazy danych TempDB?

SQL Server nie może działać (z wyjątkiem bardzo ekstremalnych scenariuszy odzyskiwania i przez bardzo krótkie okresy) bez TempDB.

czy wersja bazy danych TempDB jest unikalna dla wersji, edycji i poziomu Patch serwera SQL?

TempDB jest w większości pustą bazą danych i nie ma nic szczególnego w każdej edycji lub poziomie łatek serwera SQL.

w ostatnich latach na poziomie wersji TempDB wprowadzono tylko 2 znaczące zmiany. Pierwszym jest to, że począwszy od SQL Server 2016 zachowanie tempdb zostało zmienione tak, że włączenie znaczników śledzenia T1117 i T1118 nie jest już uważane za najlepszą praktykę. Po drugie, począwszy od SQL Server 2019 niektóre ciężkie obiekty systemowe w TempDB zostały zaktualizowane do tabel w pamięci, aby zmniejszyć niezgodę i poprawić ogólną wydajność serwera.

czy możesz upuścić lub zmienić nazwę bazy danych TempDB i czy powinieneś to zrobić z jakiegokolwiek powodu?

TempDB nie może być upuszczony, odłączony, zabrany w trybie offline ani zmieniony. Próbowaniekażda z tych operacji zwróci błąd. Nie ma powodu, aby to powinno być podejmowane, ponieważ jest to krytyczna baza danych systemu.

lokalizacja bazy danych SQL Server TempDB

jakie fizyczne pliki i nazwy obsługują bazę danych TempDB?

domyślnymi nazwami plików logicznych są tempdev dla danych i templog dla dziennika.Można je znaleźć na dysku jako tempdb.mdf i templog.odpowiednio ldf. Tempdbwspólnie ma wiele plików danych.

ile powinno być plików danych TempDB?

powinien istnieć jeden plik danych TempDB dla każdego wątku / rdzenia / vCPU w instancji z maksymalnie 8.

Gdzie znajdę bazę danych TempDB na dysku i w SSMS?

pliki można znaleźć przez zapytanie sys.sysfiles dmv lub panel plików w oknie databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

czy ma znaczenie, gdzie znajdują się pliki bazy danych TempDB (czy muszą być na C:\, SAN, itp.)?

najlepsze praktyki nakazują, że pliki danych TempDB powinny znajdować się w najszybszym dostępnym magazynie ze względu na ich poziom aktywności. W niektórych przypadkach może to oznaczać przechowywanie ich z plikami danych bazy danych użytkownika, ale istnieje możliwość użycia oddzielnego woluminu na oddzielnym dysku, jeśli jest dostępny.

istnieją różne opinie na temat pliku dziennika TempDB, ale ten autor preferuje łączenie tego pliku z innymi plikami dziennika transakcji.

czy można przenieść bazę danych TempDB?

baza danych TempDB może zostać przeniesiona. Metoda przenoszenia TempDB jest łatwa do zaimplementowania. Po prostu zmodyfikuj właściwość FILENAME żądanego pliku(ów).żadne pliki nie muszą być przenoszone tak, jak podczas przenoszenia innych systemowych baz danych. SQLServer utworzy plik(y) o nowej nazwie i/lub w nowej lokalizacji przy starcie usługi. Tylko nie zapomnij wrócić i usunąć nowunused pliki TempDB, które zostaną pozostawione.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

rozmiar i wzrost bazy danych TempDB SQL Server

jak należy skonfigurować ustawienia wzrostu bazy danych i dziennika transakcji dla bazy danych TempDB?

Ustawienia Autogrowth powinny być ustawione na określoną liczbę MB, a nie procent.Każdy plik danych powinien mieć taką samą liczbę Mb ustawioną na wzrost.

właściwe dopasowanie plików do bazy danych jest ogólnie najlepszą praktyką, ale nigdy więcej niż w przypadku TempDB. Monitoruj instancję-zwłaszcza jeśli jest nowa – i określ, czy pliki rosną podczas normalnej pracy.Jeśli tak, zmień rozmiar początkowy tak, aby SQL Server nie był zmuszony do konsekwentnego powiększania plików po każdym uruchomieniu usługi.

jaki jest typowy rozmiar bazy danych TempDB i jak duży może być?

nie ma dobrej odpowiedzi na to pytanie. To, co jest typowe dla jednej instancji, nie może być typowe dla innej. Autor ten widział przypadki, w których TempDB jest zasadniczo używany uśredniając tylko kilka MB używanej przestrzeni w danym momencie. Jednak inne są stale wyszukiwane i zużywają setki GB miejsca-nawet w przestrzeni mierzonej w TB-szczególnie podczas okna konserwacji indeksu.

przy podejmowaniu decyzji, ile miejsca poświęcić TempDB na nową instancję, jest kilka rzeczy, które można zrobić. Czy nowa instancja zastępuje starszą, istniejącą instancję? Jeśli Tak, użyj śledzenia perfmon lub zaplanowanego przechwytywania DMV, aby określić używaną przestrzeń w TempDB w ciągu kilku dni. Upewnij się, że w przechwytywaniu znajduje się okno konserwacji. Spróbuj przydzielić wystarczająco dużo miejsca na nowej instancji, aby uwzględnić największą zużytą przestrzeń przechwyconą podczas śledzenia z dodatkowym pokojem poruszania się. Następnie odpowiedni rozmiar plików, aby zmieścić się w tej alokacji przestrzeni.

poniższe 2 zapytania mogą pomóc. Pierwszy zrobi migawkę wielkości danych i plików dziennika wraz z przestrzenią używaną w plikach. Może to być uruchamiane regularnie, aby śledzić wykorzystanie w czasie. Drugie zapytanie zwróci liczbę razy plik dziennika wzrósł od czasu ostatniego uruchomienia instancji.

jeśli nie ma istniejącego obciążenia, które może być użyte jako przewodnik i nie ma innych wskazówek, być może od dostawcy oprogramowania, jedyną rzeczą, którą można zrobić, jest upewnienie się, że w TempDB jest miejsce na uwzględnienie największych pojedynczych obiektów, które mogą być sortowane w TempDB podczas konserwacji indeksu. Stamtąd monitoruj użycie i odpowiednio dostosuj.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

Serwer utrzymanie bazy danych TempDB

czy możesz zmniejszyć bazę danych i dziennik transakcji dla bazy danych TempDB?

technicznie tak, ale to nie jest operacja, która nigdy nie powinna zostać zakończona.Celem powinno być zawsze odpowiedni rozmiar TempDB. Jeśli jakaś Jednorazowa operacja spowoduje, że te pliki będą rosły w niewytłumaczalny sposób, powrócą do normy po ponownym uruchomieniu usługi nextservice.

czy warto odbudować indeksy?

TempDB nie będzie częścią żadnych planów konserwacji związanych z indeksem. Oznacza to brak przebudowy indeksów, reorganizacji lub aktualizacji statystyk.

czy należy uruchomić sprawdzanie integralności co to jest kod?

istnieją różne opinie na temat tego, czy należy sprawdzać poprawność danych.Autor ten przedstawi argumenty za i przeciw i pozwoli czytelnikowi podjąć decyzję.

przeciwko: operacja sprawdzania integralności cannotcheck TempDB z taką samą całością, jak wszystkie inne bazy danych.Dane, które istnieją w TempDB, są bardzo przejściowe, więc operacja sprawdzania integralności nie znajdzie wiele do sprawdzenia w danym momencie.Z tego samego powodu wszelkie dane, które ulegną uszkodzeniu, prawdopodobnie będą aktywnymi danymi, które spowodują alert podczas odczytu z dysku na długo przed uruchomieniem operacji sprawdzania integralności.

For: podczas gdy operacja sprawdzania integralności nie może sprawdzić wszystkiego w TempDB, co sprawdza w innych bazach danych, co nie oznacza, że nie ma wartości.TempDB jest integralną bazą danych ijeśli istnieje korupcja, wiedza o tym jest niezbędna, aby można było podjąć działania naprawcze.

kod do uruchomienia kontroli integralności znajduje się poniżej.

DBCC CHECKDB ('TempDB'); 

obiekty bazy danych TempDB SQL Server

jakie są kluczowe tabele i procedury w bazie danych TEMPDB, które są ważne dla specjalistów SQL Server, o których powinni wiedzieć?

nie ma.Ta baza danych służy do tymczasowego przechowywania objects.It nie jest instalowany z anytables lub procedur.

Czy Mogę przechowywać własne obiekty w bazie danych TEMPDB?

obiekty z pewnością mogą być tworzone w TempDB, ale nie będą się utrzymywać po następnym uruchomieniu usługi SQL Server.

uprawnienia do bazy danych TempDB SQL Server

kto ma dostęp do bazy danych TempDB i czy istnieją różne poziomy uprawnień?

każdy ma dostęp i taki sam dostęp do TempDB.

Kopia zapasowa bazy danych TempDB SQL Server

Czy muszę wykonać kopię zapasową bazy danych TempDB?

nie można utworzyć kopii zapasowej TempDB. Przechowuje tylko dane przejściowe i jest odtwarzany za każdym razem, gdy usługa się uruchamia, więc nie ma nic do odzyskania w przypadku katastrofy.

SQL Server model odzyskiwania bazy danych TempDB

jakie odzyskiwanie TempDB powinna mieć baza danych TempDB i czy model odzyskiwania może ulec zmianie?

TempDB jest w prostym odzyskiwaniu i nie można tego zmienić.

Przywracanie bazy danych TempDB SQL Server

czy istnieje kiedykolwiek potrzeba przywrócenia bazy danych TempDB?

TempDB nie może zostać przywrócony. Nie powinno być żadnych danych ani kodu w tempdb do odzyskania. Jeśli TempDB zostanie uszkodzony, po prostu usuń Dane i pliki dziennika, aby nowe mogły zostać wygenerowane przy następnym uruchomieniu usługi.

kolejne kroki

• ta wskazówka pokazuje, w jaki sposóbzostawiać alerty o problemach znalezionych w rozmiarach TempDB i przestrzeni dyskowej
• ta wskazówka pokazuje, jakzawodna inicjalizacja pliku, ważna funkcja dla wszystkich DBs, ale szczególnie TempDB, jeśli nie jest on prawidłowo rozmiar.

Ostatnia aktualizacja: 2020-07-17

o autorze

Eric Blinn jest Sr. Architekt danych dla dziedzica Pattona Boggsa. Jest również autorem SQL i liderem lokalnej grupy PASS.
Zobacz wszystkie moje porady

powiązane zasoby

• więcej porad DBA SQL Server…