Udostępnij za pomocą

baza danych tempdb

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

W tym artykule opisano tempdb systemową bazę danych, globalny zasób dostępny dla wszystkich użytkowników połączonych z wystąpieniem aparatu bazy danych w programie SQL Server, usłudze Azure SQL Database lub usłudze Azure SQL Managed Instance.

Overview

tempdb Systemowa baza danych jest zasobem globalnym, który zawiera:

  • Obiekty użytkownika , które są jawnie tworzone. To na przykład:

    • Globalne lub lokalne tabele tymczasowe i indeksy w tych tabelach
    • Tymczasowe procedury składowane
    • Duże zmienne, w tym zmienne tabeli
    • Tabele zwracane w funkcjach zwracających tabele
    • Cursors

    Obiekty użytkownika, które można utworzyć w bazie danych użytkownika, można również utworzyć w tempdb, jednak są one tworzone bez gwarancji trwałości i są usuwane po ponownym uruchomieniu wystąpienia silnika bazy danych.

  • Obiekty wewnętrzne tworzone przez aparat bazy danych. To na przykład:

    • Robocze tabele do przechowywania wyników pośrednich dla szpul, kursorów, sortowania oraz tymczasowego przechowywania dużych obiektów (LOB).
    • Pliki robocze dla operacji dopasowywania skrótów lub agregacji skrótów.
    • Wyniki sortowania pośredniego dla operacji takich jak tworzenie lub odbudowywanie indeksów (jeśli określono SORT_IN_TEMPDB), lub niektóre zapytania GROUP BY, ORDER BY lub UNION.

    Każdy obiekt wewnętrzny używa co najmniej dziewięciu stron: strony IAM i ośmiostronicowego zakresu. Aby uzyskać więcej informacji na temat stron i zakresów, zobacz Strony i zakresy.

  • Magazyny wersji, będące kolekcjami stron danych przechowujących wiersze danych obsługujące przechowywanie wersji wierszy. Sklepy z wersjami zawierają:

    • Wersje wierszy, które są generowane przez transakcje modyfikacji danych w bazie danych korzystającej z izolacji transakcji opartych na wersjonowaniu wierszy przy użyciu READ COMMITTED lub SNAPSHOT.
    • Wersje wierszy, które są generowane przez transakcje modyfikacji danych dla takich funkcji jak operacje indeksowania online, wiele aktywnych zestawów wyników (MARS) i wyzwalacze AFTER.

    Począwszy od SQL Server 2025 (17.x), gdy przyspieszone odzyskiwanie bazy danych (ADR) jest włączone w tempdb, tempdb zawiera dwa różne i niezależne magazyny wersji:

    • Tradycyjny magazyn wersji, używany dla wersji wierszy generowanych przez transakcje w bazach danych użytkowników, które nie mają włączonego ADR.
    • Persistent Version Store (PVS) wykorzystywany do wersjonowania wierszy powstających w wyniku transakcji w tempdb.

    Pamiętaj, aby przydzielić wystarczającą ilość miejsca na dysku dla tempdb plików danych, aby mogły zawierać oba magazyny wersji, jeśli ADR w tempdb jest włączone. W zależności od obciążenia może być konieczne zwiększenie rozmiaru tempdb plików danych w celu przechowywania danych PVS.

    Aby uzyskać więcej informacji na temat tempdb użycia miejsca w tradycyjnym magazynie wersji, zobacz Space used in tempdb (Miejsce używane w bazie danych tempdb). Aby uzyskać więcej informacji na temat miejsca używanego przez usługę PVS, zobacz Space used by the persistent version store (PVS)( Miejsce używane przez magazyn wersji trwałej (PVS).

Operacje w ramach programu tempdb są rejestrowane minimalnie. tempdb jest tworzony ponownie za każdym razem, gdy silnik bazy danych jest uruchamiany, aby system zawsze zaczynał się od pustej tempdb bazy danych. Tymczasowe procedury składowane i lokalne tabele tymczasowe są usuwane automatycznie po rozłączeniu sesji, która je utworzyła.

tempdb nigdy nie ma nic do zachowania z jednego czasu działania silnika baz danych do innego. Operacje tworzenia i przywracania kopii zapasowych nie są dozwolone w programie tempdb.

Właściwości fizyczne bazy danych tempdb w programie SQL Server

W poniższej tabeli wymieniono początkowe wartości tempdb konfiguracji plików danych i dzienników w programie SQL Server. Wartości są oparte na wartościach domyślnych bazy model danych. Rozmiary tych plików mogą się nieznacznie różnić w przypadku różnych wersji programu SQL Server.

File Nazwa logiczna Nazwa fizyczna Rozmiar początkowy Wzrost pliku
Dane podstawowe tempdev tempdb.mdf 8 megabajtów Automatyczne zwiększanie o 64 MB do momentu zapełniania dysku
Pomocnicze pliki danych temp# tempdb_mssql_#.ndf 8 megabajtów Automatyczne zwiększanie o 64 MB do momentu zapełniania dysku
Log templog templog.ldf 8 megabajtów Automatyczne zwiększanie o 64 megabajty do maksymalnie 2 terabajtów

Wszystkie tempdb pliki danych powinny zawsze mieć ten sam rozmiar początkowy i parametry wzrostu.

Liczba plików danych bazy danych tempdb

W zależności od wersji aparatu bazy danych, jego konfiguracji i obciążenia, tempdb może wymagać wielu plików danych, aby zredukować konflikt w alokacji.

Zalecana całkowita liczba plików danych zależy od liczby procesorów logicznych na maszynie. Ogólne wskazówki:

  • Jeśli liczba procesorów logicznych jest mniejsza lub równa ośmiu, użyj tej samej liczby plików danych.

  • Jeśli liczba procesorów logicznych jest większa niż osiem, użyj ośmiu plików danych.

  • Jeśli tempdb rywalizacja o alokację jest nadal obserwowana, zwiększ liczbę plików danych o wielokrotność czterech, aż rywalizacja spadnie do akceptowalnych poziomów lub wprowadź zmiany w obciążeniu.

Aby uzyskać więcej informacji, zobacz Zalecenia dotyczące zmniejszenia rywalizacji o alokację w bazie danych tempdb programu SQL Server.

Aby sprawdzić bieżący rozmiar i parametry wzrostu dla tempdb, użyj widoku katalogu sys.database_files w tempdb.

Przenoszenie danych bazy danych tempdb i plików dziennika w programie SQL Server

Aby przenieść tempdb pliki danych i dzienników, zobacz Przenoszenie systemowych baz danych.

Opcje bazy danych dla bazy danych tempdb w programie SQL Server

W poniższej tabeli wymieniono wartość domyślną dla każdej opcji bazy danych w tempdb bazie danych i informację, czy można zmodyfikować tę opcję. Aby wyświetlić bieżące ustawienia tych opcji, użyj widoku katalogu sys.databases .

Opcja bazy danych Wartość domyślna Można modyfikować
ACCELERATED_DATABASE_RECOVERY OFF Yes1
ALLOW_SNAPSHOT_ISOLATION OFF Yes
ANSI_NULL_DEFAULT OFF Yes
ANSI_NULLS OFF Yes
ANSI_PADDING OFF Yes
ANSI_WARNINGS OFF Yes
ARITHABORT OFF Yes
AUTO_CLOSE OFF No
AUTO_CREATE_STATISTICS ON Yes
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS ON Yes
AUTO_UPDATE_STATISTICS_ASYNC OFF Yes
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) OFF No
CHANGE_TRACKING OFF No
COMPATIBILITY_LEVEL Zależy od wersji silnika bazy danych.

Aby uzyskać więcej informacji, zobacz ALTER DATABASE (Transact-SQL) poziom zgodności.		 Yes
CONCAT_NULL_YIELDS_NULL OFF Yes
CONTAINMENT NONE No
CURSOR_CLOSE_ON_COMMIT OFF Yes
CURSOR_DEFAULT GLOBAL Yes
Stan bazy danych ONLINE No
Aktualizacja bazy danych READ_WRITE No
Dostęp użytkowników bazy danych MULTI_USER No
DATE_CORRELATION_OPTIMIZATION OFF Yes
DB_CHAINING ON No
DELAYED_DURABILITY DISABLED

Niezależnie od tej opcji, opóźniona trwałość jest zawsze włączona w systemie tempdb.		 Yes
ENCRYPTION OFF No
MIXED_PAGE_ALLOCATION OFF No
NUMERIC_ROUNDABORT OFF Yes
PAGE_VERIFY CHECKSUM w przypadku nowych instalacji programu SQL Server

Istniejąca PAGE_VERIFY wartość może być zachowywana po uaktualnieniu wystąpienia programu SQL Server.		 Yes
PARAMETERIZATION SIMPLE Yes
QUOTED_IDENTIFIER OFF Yes
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SIMPLE No
RECURSIVE_TRIGGERS OFF Yes
Broker Usług ENABLE_BROKER Yes
TARGET_RECOVERY_TIME 60 Yes
TEMPORAL_HISTORY_RETENTION ON Yes
TRUSTWORTHY OFF No

1 Ustawienie ACCELERATED_DATABASE_RECOVERY na ON in tempdb jest obsługiwane od programu SQL Server 2025 (17.x). W poprzednich wersjach programu SQL Server modyfikowanie ACCELERATED_DATABASE_RECOVERY opcji bazy tempdb danych nie jest dozwolone.

Opis tych opcji bazy danych można znaleźć w ALTER DATABASE SET options.

baza danych tempdb w usłudze Azure SQL Database

W usłudze Azure SQL Database niektóre aspekty tempdb zachowania i konfiguracji różnią się od programu SQL Server.

W przypadku pojedynczych baz danych każda baza danych na serwerze logicznym ma własne tempdb. W elastycznej puli tempdb jest zasobem dzielonym dla wszystkich baz danych w tej samej puli, ale obiekty tymczasowe utworzone przez jedną bazę danych nie są widoczne dla innych baz danych w tej samej elastycznej puli.

Obiekty w tempdb, w tym widoki katalogu i dynamiczne widoki zarządzania (DMV), są dostępne za pośrednictwem odwołania między bazami danych do bazy danych tempdb. Możesz na przykład wykonać zapytanie dotyczące widoku sys.database_files :

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Globalne tabele tymczasowe w usłudze Azure SQL Database są ograniczone do zakresu bazy danych. Aby uzyskać więcej informacji, zobacz Tabele tymczasowe o globalnym zakresie w bazie danych w usłudze Azure SQL Database.

Aby dowiedzieć się więcej o rozmiarach w usłudze Azure SQL Database, zapoznaj się z tempdb.

tempdb w usłudze SQL Managed Instance

W usłudze Azure SQL Managed Instance niektóre aspekty zachowania i konfiguracji domyślnej tempdb różnią się od programu SQL Server.

Można skonfigurować liczbę tempdb plików, ich przyrosty wzrostu i maksymalny rozmiar. Aby uzyskać więcej informacji na temat konfigurowania tempdb ustawień w usłudze Azure SQL Managed Instance, zobacz Konfigurowanie ustawień bazy danych tempdb dla usługi Azure SQL Managed Instance.

Usługa Azure SQL Managed Instance obsługuje obiekty tymczasowe w taki sam sposób jak program SQL Server, gdzie wszystkie globalne tabele tymczasowe i globalne tymczasowe procedury składowane są dostępne dla wszystkich sesji użytkowników w ramach tego samego wystąpienia zarządzanego SQL.

Aby dowiedzieć się więcej o rozmiarach w usłudze Azure SQL Managed Instance, zapoznaj się z tempdblimitami zasobów.

baza danych tempdb w bazie danych SQL w środowisku Fabric

Aby dowiedzieć się więcej o tempdb rozmiarach bazy danych SQL w usłudze Microsoft Fabric, zapoznaj się z sekcją Limity zasobów w artykule Porównanie funkcji: Usługa Azure SQL Database i baza danych SQL w usłudze Microsoft Fabric.

Podobnie jak w przypadku usługi Azure SQL Database globalne tabele tymczasowe w bazie danych SQL w usłudze Microsoft Fabric są ograniczone do zakresu bazy danych. Aby uzyskać więcej informacji, zobacz Tabele tymczasowe o globalnym zakresie w bazie danych w usłudze Azure SQL Database.

Ograniczenia

Nie można wykonać następujących operacji w tempdb bazie danych:

  • Dodawanie grup plików.
  • Tworzenie kopii zapasowej lub przywracanie bazy danych.
  • Zmiana sortowania. Sortowanie domyślne to sortowanie serwera.
  • Zmiana właściciela bazy danych. tempdb jest własnością sa.
  • Tworzenie migawki bazy danych.
  • Usuwanie bazy danych.
  • Usuwanie użytkownika-gościa z bazy danych.
  • Włączanie przechwytywania zmian danych.
  • Udział w dublowaniu bazy danych.
  • Usuwanie podstawowej grupy plików, podstawowego pliku danych lub pliku dziennika.
  • Zmiana nazwy bazy danych lub podstawowej grupy plików.
  • Uruchamianie DBCC CHECKALLOC.
  • Uruchamianie DBCC CHECKCATALOG.
  • Ustawienie bazy danych na OFFLINE.
  • Ustawienie bazy danych lub podstawowej grupy plików na READ_ONLY.

Permissions

Każdy użytkownik może tworzyć obiekty tymczasowe w programie tempdb.

Użytkownicy mogą uzyskiwać dostęp tylko do własnych obiektów nie tymczasowych w tempdb, chyba że otrzymają dodatkowe uprawnienia.

Można cofnąć uprawnienie na CONNECT, aby uniemożliwić użytkownikowi lub roli bazy danych używanie tempdb. Nie jest to zalecane, ponieważ wiele operacji wymaga użycia metody tempdb.

Optymalizowanie wydajności bazy danych tempdb w programie SQL Server

Rozmiar i fizyczne umieszczanie tempdb plików może mieć wpływ na wydajność. Na przykład, jeśli początkowy rozmiar tempdb jest zbyt mały, czas i zasoby mogą być zużywane, aby automatycznie zwiększyć tempdb do rozmiaru wymaganego do obsługi obciążenia przy każdym ponownym uruchomieniu wystąpienia silnika bazy danych.

  • Jeśli to możliwe, użyj natychmiastowej inicjalizacji plików , aby zwiększyć wydajność operacji wzrostu dla plików danych.
  • Wstępnie przydziel miejsce dla wszystkich plików tempdb poprzez ustawienie rozmiaru pliku na wartość wystarczająco dużą, aby obsłużyć typowe obciążenie środowiska. Wstępna alokacja uniemożliwia tempdb zbyt częsty automatyczny wzrost, co może negatywnie wpłynąć na wydajność.
  • Pliki w bazie danych tempdb powinny być ustawione na automatyczne zwiększanie rozmiaru, aby zapewnić miejsce w przypadku niespodziewanego wzrostu.
  • Podzielenie tempdb na wiele plików danych o równym rozmiarze może zwiększyć wydajność operacji korzystających z programu tempdb.
    • Aby uniknąć dysproporcji alokacji danych, pliki danych powinny mieć ten sam początkowy rozmiar i parametry wzrostu, ponieważ aparat bazy danych używa algorytmu wypełniania proporcjonalnego, który faworyzuje alokacje w plikach z większą ilością wolnego miejsca.
    • Ustaw przyrost pliku na odpowiedni rozmiar, na przykład 64 MB, i ustaw taki sam przyrost dla wszystkich plików danych, aby zapobiec nierównowadze wzrostu.
  • Począwszy od SQL Server 2025 (17.x), rozważ włączenie przyspieszonego odzyskiwania bazy danych w tempdb aby skorzystać z natychmiastowego wycofania transakcji i agresywnego obcinania dziennika dla transakcji w tempdb. Aby uzyskać więcej informacji, zobacz ADR w bazie danych tempdb.
    • Włączenie lub wyłączenie ADR w ​​tempdb wymaga ponownego uruchomienia Silnika bazy danych.

Aby sprawdzić obecny rozmiar i parametry dotyczące wzrostu dla tempdb, użyj następującego zapytania:

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST (IIF (max_size = 0, 0, 1) AS BIT) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0 THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1 THEN 'Percent'
       END AS growth_increment_value_unit
FROM tempdb.sys.database_files;

tempdb Umieść bazę danych w szybkim podsystemie we/wy. Pojedyncze pliki danych lub grupy tempdb plików danych nie muszą znajdować się na różnych dyskach, chyba że występują wąskie gardła we/wy na poziomie dysku.

Jeśli między tempdb a bazami danych użytkowników występuje rywalizacja o we/wy, zapewnij, że pliki tempdb znajdują się na dyskach różniących się od dysków, których używają bazy danych użytkowników.

Note

Aby zwiększyć wydajność, opóźniona trwałość jest zawsze włączona na tempdb, nawet jeśli opcja bazy danych DELAYED_DURABILITY jest ustawiona na DISABLED. Ponieważ tempdb jest odtwarzany podczas uruchamiania, nie przechodzi przez proces odzyskiwania i nie zapewnia gwarancji trwałości.

Ulepszenia bazy danych tempdb dla programu SQL Server

Wprowadzono w programie SQL Server 2025 (17.x)

  • Tempdb zarządzanie zasobami przestrzeni wymusza limit całkowitej tempdb ilości miejsca zużywanego przez obciążenie aplikacji lub użytkownika. Zwiększa to niezawodność i pozwala uniknąć awarii, zapobiegając nadmiernemu użyciu miejsca przez niekontrolowane zapytania lub obciążenia w tempdb. Aby uzyskać więcej informacji, zobacz Tempdb space resource governance (Zarządzanie zasobami przestrzeni bazy danych Tempdb).
  • Przyspieszone odzyskiwanie bazy danych jest obsługiwane w programie tempdb, zapewniając natychmiastowe wycofanie transakcji i agresywne skracanie dziennika dla transakcji w programie tempdb.

Wprowadzono w programie SQL Server 2022 (16.x)

Wprowadzono w programie SQL Server 2019 (15.x)

  • Aparat bazy danych nie używa FILE_FLAG_WRITE_THROUGH opcji podczas otwierania tempdb plików, aby zapewnić maksymalną przepływność dysku. Ponieważ tempdb funkcja jest ponownie utworzona podczas uruchamiania, ta opcja nie jest wymagana do zapewnienia trwałości danych. Aby uzyskać więcej informacji na temat FILE_FLAG_WRITE_THROUGH, zobacz Algorytmy rejestrowania i przechowywania danych, które zwiększają niezawodność danych w programie SQL Server (Logging and data storage algorithms that extend data reliability in SQL Server).
  • Metadane TempDB zoptymalizowane pod kątem pamięci eliminują konflikt dotyczący tymczasowych metadanych obiektów w systemie tempdb.
  • Współbieżne aktualizacje strony PFS (strony wolnej przestrzeni) zmniejszają rywalizację o blokady stron we wszystkich bazach danych, co jest najczęściej spotykane w tempdb. To ulepszenie zmienia zarządzanie równoległością aktualizacji strony PFS, aby można je było aktualizować przy użyciu współdzielonego zatrzasku, zamiast wyłącznego zatrzasku. To zachowanie jest domyślnie włączone we wszystkich bazach danych (w tym w tempdb) począwszy od programu SQL Server 2019 (15.x). Aby uzyskać więcej informacji o stronach PFS, przeczytaj GAM, SGAM i strony PFS.
  • Domyślnie nowa instalacja programu SQL Server w systemie Linux tworzy wiele plików danych tempdb na podstawie liczby rdzeni logicznych (z maksymalnie ośmioma plikami danych). Nie dotyczy to uaktualnień wersji pomocniczych ani głównych. Każdy tempdb plik danych wynosi 8 MB, a automatyczny wzrost wynosi 64 MB. To zachowanie jest podobne do domyślnej instalacji programu SQL Server w systemie Windows.

Wprowadzono w programie SQL Server 2017 (14.x)

  • Doświadczenie konfiguracji SQL usprawnia wskazówki dotyczące początkowej alokacji plików tempdb. Instalator SQL ostrzega klientów, jeśli początkowy rozmiar pliku jest ustawiony na wartość większą niż 1 GB i jeśli natychmiastowa inicjalizacja pliku nie jest włączona, co zapobiega opóźnieniom uruchamiania instancji.
  • Dynamiczny widok zarządzania sys.dm_tran_version_store_space_usage śledzi użycie magazynu wersji w bazie danych. Ten dynamiczny widok zarządzania jest przydatny w przypadku baz danych, którzy chcą aktywnie planować tempdb ustalanie rozmiaru na podstawie wymagania dotyczącego użycia magazynu wersji dla bazy danych.
  • Inteligentne funkcje przetwarzania zapytań, takie jak sprzężenia adaptacyjne i sprzężenie zwrotne alokacji pamięci, zmniejszają przeciążenia pamięci podczas kolejnych wykonań zapytania, co zmniejsza wykorzystanie tempdb.

Wprowadzono w programie SQL Server 2016 (13.x)

  • Tabele tymczasowe i zmienne tabeli są buforowane. Buforowanie umożliwia szybkie wykonywanie operacji, które usuwają i tworzą obiekty tymczasowe. Buforowanie zmniejsza również alokację stron i rywalizację o metadane.
  • Ulepszono protokół zatrzasania strony alokacji w celu zmniejszenia liczby używanych zatrzasków (aktualizacji) UP.
  • Obciążenie logowania dla tempdb jest redukowane w celu ograniczenia zużycia przepustowości operacji we/wy dysku w pliku dziennika tempdb.
  • Konfiguracja SQL dodaje wiele tempdb plików danych podczas instalacji nowej instancji. Przejrzyj zalecenia i skonfiguruj swój tempdb na stronie Konfiguracja aparatu bazy danych Instalatora SQL lub użyj parametru wiersza polecenia /SQLTEMPDBFILECOUNT. Domyślnie instalator SQL dodaje tyle tempdb plików danych, ile jest procesorów logicznych lub osiem, w zależności od tego, która wartość jest niższa.
  • Jeśli istnieje wiele tempdb plików danych, wszystkie pliki są automatycznie zwiększane w tym samym czasie i o tę samą wartość, w zależności od ustawień przyrostu. flaga śledzenia 1117 nie jest już wymagana. Aby uzyskać więcej informacji, przeczytaj zmiany -T1117 i -T1118 dotyczące TEMPDB i baz danych użytkownika.
  • Wszystkie alokacje w tempdb używają jednolitych rozmiarów. flaga śledzenia 1118 nie jest już wymagana. Aby uzyskać więcej informacji na temat ulepszeń wydajności w programie tempdb, zobacz artykuł na blogu TEMPDB – Pliki, flagi śledzenia i aktualizacje, o matko!.
  • Właściwość AUTOGROW_ALL_FILES jest zawsze włączona dla grupy plików PRIMARY.

Metadane bazy danych TempDB zoptymalizowane pod kątem pamięci

Rywalizacja o tymczasowe metadane obiektów była historycznie wąskim gardłem dla skalowalności wielu obciążeń programu SQL Server. Aby rozwiązać ten problem, program SQL Server 2019 (15.x) wprowadził funkcję, która jest częścią rodziny funkcji bazy danych w pamięci : metadane bazy danych TempDB zoptymalizowane pod kątem pamięci.

Włączenie funkcji metadanych TempDB zoptymalizowanych pod kątem pamięci usuwa to wąskie gardło dla obciążeń, które wcześniej były ograniczone przez rywalizację o tymczasowe metadane obiektów wewnątrz tempdb. Począwszy od programu SQL Server 2019 (15.x), tabele systemowe zarządzania metadanymi obiektów tymczasowych mogą stać się niezatrzaskowe, niedeterministyczne, zoptymalizowanymi pamięciowo tabelami.

Tip

Ze względu na bieżące ograniczenia zalecamy włączenie metadanych tempDB zoptymalizowanych pod kątem pamięci tylko wtedy, gdy wystąpi rywalizacja o metadane obiektu i znacząco wpływa na obciążenia.

Poniższe zapytanie diagnostyczne zwraca co najmniej jeden wiersz, jeśli występuje rywalizacja o metadane obiektu tymczasowego. Każdy wiersz reprezentuje tabelę systemową i zwraca liczbę sesji, które walczą o dostęp do tej tabeli w czasie wykonywania tego zapytania diagnostycznego.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT (r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Obejrzyj ten siedmiominutowy film wideo, aby dowiedzieć się, jak i kiedy używać funkcji metadanych tempDB zoptymalizowanych pod kątem pamięci:

Note

Obecnie funkcja metadanych TempDB zoptymalizowana pod kątem pamięci nie jest dostępna w usłudze Azure SQL Database, bazie danych SQL w usłudze Microsoft Fabric i usłudze Azure SQL Managed Instance.

Konfigurowanie i używanie metadanych bazy danych TempDB zoptymalizowanych pod kątem pamięci

W poniższych sekcjach opisano kroki włączania, konfigurowania, weryfikowania i wyłączania funkcji metadanych bazy danych TempDB zoptymalizowanej pod kątem pamięci.

Enable

Aby włączyć tę funkcję, użyj następującego skryptu:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Aby uzyskać więcej informacji, zobacz ALTER SERVER. Ta zmiana konfiguracji wymaga ponownego uruchomienia usługi, aby zaczęły obowiązywać.

Możesz sprawdzić, czy tempdb jest zoptymalizowane pod kątem pamięci, używając następującego polecenia T-SQL:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Jeśli zwrócona wartość to 1, a po włączeniu tej funkcji wystąpiło ponowne uruchomienie, funkcja jest włączona.

Jeśli serwer nie uruchomi się z jakiegokolwiek powodu po włączeniu metadanych TempDB zoptymalizowanych pod kątem pamięci, możesz obejść tę funkcję, uruchamiając instancję silnika bazy danych z minimalną konfiguracją, używając opcji startowej -f. Następnie możesz wyłączyć tę funkcję i usunąć opcję -f ponownego uruchomienia silnika bazy danych w trybie normalnym.

Wiązanie z pulą zasobów w celu ograniczenia użycia pamięci

Aby chronić serwer przed potencjalnymi warunkami braku pamięci, zalecamy powiązanie tempdb z pulą zasobów, która ogranicza ilość pamięci zużywanej przez zoptymalizowane pod kątem pamięci metadane TempDB. Poniższy przykładowy skrypt tworzy pulę zasobów i ustawia maksymalną pamięć na 20%, włącza zarządcę zasobów i wiąże się z tempdb pulą zasobów.

W tym przykładzie użyto 20% jako limitu pamięci w celach demonstracyjnych. Optymalna wartość w środowisku może być większa lub mniejsza w zależności od obciążenia i może ulec zmianie w miarę upływu czasu, jeśli obciążenie ulegnie zmianie.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
(RESOURCE_POOL = 'tempdb_resource_pool');

Ta zmiana wymaga również ponownego uruchomienia usługi, nawet jeśli metadane bazy danych TempDB zoptymalizowane pod kątem pamięci są już włączone.

Weryfikowanie powiązania puli zasobów i monitorowanie użycia pamięci

Aby sprawdzić, czy tempdb jest powiązana z pulą zasobów i monitorować statystyki użycia pamięci dla puli, użyj następującego zapytania:

; WITH resource_pool
AS (SELECT p.pool_id,
           p.name,
           p.max_memory_percent,
           dp.max_memory_kb,
           dp.target_memory_kb,
           dp.used_memory_kb,
           dp.out_of_memory_count
    FROM sys.resource_governor_resource_pools AS p
         INNER JOIN sys.dm_resource_governor_resource_pools AS dp
             ON p.pool_id = dp.pool_id)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
     LEFT OUTER JOIN resource_pool AS rp
         ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Usuń powiązanie puli zasobów

Aby usunąć powiązanie puli zasobów, jednocześnie pozostawiając włączone metadane zoptymalizowane pod kątem pamięci dla TempDB, wykonaj następujące polecenie i uruchom ponownie usługę.

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Disable

Aby wyłączyć metadane bazy danych TempDB zoptymalizowane pod kątem pamięci, wykonaj następujące polecenie i uruchom ponownie usługę:

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Ograniczenia metadanych bazy danych TempDB zoptymalizowane pod kątem pamięci

  • Włączenie lub wyłączenie funkcji metadanych bazy danych TempDB zoptymalizowanych pod kątem pamięci wymaga ponownego uruchomienia.

  • W niektórych przypadkach możesz zaobserwować wysokie użycie pamięci przez MEMORYCLERK_XTP zarządcę pamięci, powodujące błędy braku pamięci podczas przetwarzania obciążenia.

    Aby wyświetlić użycie pamięci przez MEMORYCLERK_XTP menedżera względem wszystkich innych menedżerów pamięci oraz pamięci serwera docelowego, wykonaj następujące zapytanie:

    SELECT SUM(IIF (type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF (type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
    CROSS JOIN sys.dm_os_sys_info;

    Jeśli MEMORYCLERK_XTP pamięć jest wysoka, możesz rozwiązać ten problem w następujący sposób:

    Aby uzyskać więcej informacji, zobacz błędy braku pamięci w metadanych zoptymalizowanych pod kątem pamięci w bazie danych tempdb (HkTempDB).

  • Jeśli używasz In-Memory OLTP, jedna transakcja nie może uzyskać dostępu do tabel zoptymalizowanych pod kątem pamięci w więcej niż jednej bazie danych. W związku z tym każda transakcja odczytu lub zapisu, która obejmuje tabelę zoptymalizowaną pod kątem pamięci w bazie danych użytkownika, nie może również uzyskać dostępu do tempdb widoków systemowych w tej samej transakcji. W takim przypadku zostanie wyświetlony błąd 41317:

    A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

    To ograniczenie dotyczy również innych scenariuszy, w których jedna transakcja próbuje uzyskać dostęp do tabel zoptymalizowanych pod kątem pamięci w więcej niż jednej bazie danych.

    Na przykład błąd 41317 może wystąpić, jeśli wykonasz zapytanie dotyczące widoku katalogu sys.stats w bazie danych użytkownika zawierającej tabele zoptymalizowane pod kątem pamięci. Dzieje się tak, ponieważ zapytanie próbuje uzyskać dostęp do danych statystycznych w tabeli zoptymalizowanej pod kątem pamięci w bazie danych użytkownika i metadanych zoptymalizowanych pod kątem pamięci w programie tempdb.

    Poniższy przykładowy skrypt generuje ten błąd, gdy włączono metadane bazy danych TempDB zoptymalizowane pod kątem pamięci:

    BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;

    Note

    To ograniczenie nie dotyczy tabel tymczasowych. Tabelę tymczasową można utworzyć w tej samej transakcji, która uzyskuje dostęp do tabeli zoptymalizowanej pod kątem pamięci w bazie danych użytkownika.

  • Zapytania względem widoków katalogu systemowego zawsze używają READ COMMITTED poziomu izolacji. Gdy włączone są metadane TempDB zoptymalizowane pod kątem pamięci, zapytania względem widoków katalogu systemowego w tempdb używają poziomu izolacji SNAPSHOT. W obu przypadkach wskazówki dotyczące blokowania nie są honorowane.

  • Nie można utworzyć indeksów typu columnstore na tabelach tymczasowych, gdy włączono zoptymalizowane pod kątem pamięci metadane TempDB.

    • W związku z tym użycie systemowej sp_estimate_data_compression_savings procedury składowanej z parametrem COLUMNSTORE lub COLUMNSTORE_ARCHIVE kompresji danych nie jest obsługiwane, gdy włączono metadane TempDB zoptymalizowane pod kątem pamięci.

Planowanie pojemności bazy danych tempdb w programie SQL Server

Określenie odpowiedniego rozmiaru tempdb zależy od wielu czynników. Te czynniki obejmują obciążenie i funkcje aparatu bazy danych, które są używane.

Zalecamy analizowanie tempdb zużycia miejsca przez wykonanie następujących zadań w środowisku testowym, w którym można odtworzyć typowe obciążenie:

  • Włącz autogrow dla tempdb plików. Wszystkie tempdb pliki danych powinny mieć ten sam rozmiar początkowy i konfigurację automatycznego zwiększania.
  • Odtwórz obciążenie i monitoruj tempdb użycie miejsca.
  • Jeśli używasz okresowej konserwacji indeksu, wykonaj zadania konserwacji i monitoruj tempdb miejsce.
  • Użyj wartości maksymalnej ilości używanego miejsca z poprzednich kroków, aby przewidzieć całkowite użycie obciążenia. Dostosuj tę wartość dla przewidywanych współbieżnych działań, a następnie ustaw odpowiednio rozmiar tempdb .

Monitorowanie użycia bazy danych tempdb

Brak miejsca na dysku w programie tempdb może spowodować znaczne zakłócenia i przestoje aplikacji. Aby monitorować miejsce używane w ramach plików , można użyć dynamicznego widoku zarządzania tempdb.

Na przykład następujący przykładowy skrypt znajduje:

  • Wolne miejsce w tempdb (nie uwzględniając wolnej przestrzeni dyskowej, która może być dostępna do rozszerzenia tempdb).
  • Miejsce używane przez tradycyjny magazyn wersji.
    • Aby monitorować rozmiar magazynu wersji trwałej (PVS) po włączeniu przyspieszonego odzyskiwania bazy danych (ADR) w programie, zobacz tempdb.
  • Miejsce używane przez obiekty wewnętrzne.
  • Miejsce używane przez obiekty użytkownika.
SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Aby monitorować alokację lub zwalnianie przydziału stron na poziomie sesji lub zadania w tempdb, można użyć dynamicznych widoków zarządzania sys.dm_db_session_space_usage i sys.dm_db_task_space_usage. Te widoki mogą ułatwić identyfikowanie zapytań, tabel tymczasowych lub zmiennych tabeli, które używają dużych ilości tempdb miejsca.

Na przykład użyj następującego przykładowego skryptu, aby uzyskać tempdb miejsce przydzielone i zwolnione przez obiekty wewnętrzne we wszystkich aktualnie uruchomionych zadaniach w każdej sesji roboczej.

SELECT session_id,
       SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Użyj następującego przykładowego skryptu, aby znaleźć tempdb przydzielone i aktualnie używane miejsce przez obiekty wewnętrzne i użytkownika dla każdej sesji i żądania, zarówno dla uruchomionych, jak i ukończonych zadań:

; WITH tempdb_space_usage
AS (SELECT session_id,
           request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_task_space_usage
    UNION ALL
    SELECT session_id,
           NULL AS request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_session_space_usage)
SELECT session_id,
       COALESCE (request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Treści powiązane

  • Last updated on