Wydawało mi się, że kiedyś przeczytałem, że zapisywanie do tempdb jest szybsze niż w rzeczywistej tabeli, której nie ma w tempdb. Czy to prawda w jakimkolwiek zakresie? Wydawało mi się, że przypominam sobie, jak mówiłem coś specjalnego o tempdb i przechowywaniu danych w pamięci?
Odpowiedź
zapisywanie do tempdb jest szybsze niż rzeczywista tabela, której nie ma w tempdb
To prawda. TempDb ma dwa ulepszenia we / wy.
Zapisy do tabeli w bazie danych użytkownika muszą mieć swoje rekordy dziennika opróżniane na dysk po zatwierdzeniu, lub jeśli minimalnie zalogowany insert (jak SELECT … INTO), musi mieć strony bazy danych opróżnione na dysk po zatwierdzeniu. Minimalne rejestrowanie w bazie danych użytkownika polega na tym, że strony bazy danych są zapisywane bezpośrednio na dysku. Do czasu zakończenia polecenia SELECT … INTO wszystkie nowe strony zostały zapisane w plikach fizycznych.
Zapisy TempDb nie muszą być opróżniane na dysk po zatwierdzeniu, ponieważ TempDb nigdy nie jest odzyskiwana po awarii. Więc po prostu ich nie ma. Twoje zmiany generują rekordy dziennika, ale bufor dziennika jest opróżniany na dysk tylko wtedy, gdy jest pełny, a nie dla każdego zatwierdzenia.
I od SQL Server 2014 minimalnie zalogowane wpisy w bazie danych TempDb również nie są zawsze zapisywane na dysk. Jeśli załadujesz mały, krótkotrwała tabela tymczasowa może nigdy w ogóle nie zostać zapisana na dysku. Dziennik będzie zawierał kilka rekordów dotyczących alokacji stron i wpisów metadanych dla tabeli, ale to wszystko.
EG uruchom następujące wsad w tempdb, pełną bazę danych odzyskiwania i prostą bazę danych odzyskiwania, aby zobaczyć różnice.
drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs i zobaczysz coś takiego:
Proste odzyskiwanie:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE Do pełnego odzyskiwania:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL i tempdb:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE Czasami w przypadku tempdb „zobaczysz opróżniony bufor dziennika:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE Komentarze
- Jest przypadek, w którym początkowa wstawka jest szybsza, ale wraca, by cię ugryźć później. To demo pokazuje, że zapytanie wprowadzające dane do pamięci podręcznej bufora trwa znacznie dłużej, ponieważ leniwy program zapisujący jest zajęty zapisywaniem na stronach tempdb dysku, które są brudne dla tabeli tymczasowej, która już nie istnieje youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
- Tak. Istnieje ' potencjalne przyszłe ulepszenie mające na celu wyeliminowanie opróżniania stron puli buforów nieprzydzielonych do żadnego obiektu. Ale załadowanie dużej tabeli tymczasowej zawsze będzie napędzać IO, bezpośrednio lub pośrednio, zmniejszając ilość pamięci dostępnej do buforowania.
Odpowiedź
Poza tym, że zapisuje do tempdb, często nie każde uderzające we / wy dysku / sieci, jak zostało rozszerzone w odpowiedzi Davida Brownea , w zależności od Twojego zamówienia reklamowego konfiguracja może się okazać, że nawet jeśli dane są na tyle duże, że trzeba je zbuforować na dysku, jest to nadal szybsze niż wybranie do „normalnej” tabeli:
-
TempDB może być na innym dyski, więc mają własną przepustowość we / wy. Jest to szczególnie istotne w przypadku dysków wirujących, a nie dysków SSD. Czytanie z i zapisywanie do tej samej bazy danych (lub innej bazy danych na tych samych dyskach) będzie wymagało więcej ruchów głowy, dodaj więcej opóźnień we / wy i potencjalnie ogranicz efektywną przepustowość we / wy. Kopiowanie danych między bazami danych na różnych dyskach / macierzach nie będzie miało takiego samego dodatkowego opóźnienia.
-
TempDB może nawet być włączone fa bardziej niż twój główny magazyn. Być może na dyskach lokalnych, w których główna pamięć znajduje się w sieci, lub na dyskach SSD NVMe, gdzie główny magazyn znajduje się na dyskach tradycyjnych.
Obie te różnice można również zobaczyć w tym samym baza danych, jeśli używasz wielu grup plików do rozprowadzania części danych między różnymi dyskami / tablicami.
Odwrotna sytuacja może być również prawdą, jeśli masz wiele baz danych, które są aktywnie używane. Ponieważ baza danych TempDB jest zasobem współdzielonym, to i dyski / sieć, które ją obsługują, mogą być bardziej obciążone niż pliki danych dla dowolnej pojedynczej bazy danych.