databáze tempdb

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed InstanceSQL Database v Microsoft Fabric

Tento článek popisuje systémovou databázi tempdb, globální prostředek dostupný všem uživatelům připojeným k instanci databázového stroje v SQL Serveru, Azure SQL Database nebo spravované instanci Azure SQL.

Přehled

Databáze systému tempdb je celosvětový zdroj, který obsahuje:

• objekty uživatele, které jsou explicitně vytvořeny. Patří mezi ně:

  • Globální nebo místní dočasné tabulky a indexy v těchto tabulkách
  • Dočasné uložené procedury
  • Tabulkové proměnné
  • Tabulky vrácené ve funkcích s hodnotami tabulky
  • Kurzory

  Objekty uživatele, které lze vytvořit v uživatelské databázi, lze také vytvořit v tempdb, ale jsou vytvořeny bez záruky stálosti a jsou vyřazeny při restartování instance databázového stroje.

• Interní objekty, které databázový stroj vytvoří. Patří mezi ně:

  • Pracovní tabulky pro ukládání průběžných výsledků pro spouly, kurzory, řazení a dočasné úložiště velkých objektů (LOB).
  • Pracovní soubory pro operace spojení hash nebo hashového agregování.
  • Průběžné výsledky řazení pro operace, jako je vytváření nebo opětovné sestavení indexů (pokud je zadán SORT_IN_TEMPDB), nebo určité GROUP BY, ORDER BYnebo UNION dotazy.

  Každý vnitřní objekt používá minimálně devět stránek: stránku IAM a osmistránkový rozsah. Další informace o stránkách a rozsahech najdete v části Stránky a rozsahy.

• Verze ukládají, což jsou kolekce datových stránek, které obsahují datové řádky podporující verzování řádků. Existují dva typy: úložiště běžných verzí a úložiště verzí sestavení online indexu. Úložiště verzí obsahují:

  • Verze řádků generované transakcemi úprav dat v databázi, která používá izolační transakce na bázi verzí řádků READ COMMITTED nebo SNAPSHOT.
  • Verze řádků vygenerované transakcemi úprav dat pro funkce, jako jsou online indexovací operace, více aktivních sad výsledků (MARS) a triggery AFTER.

Operace v rámci tempdb se protokolují minimálně. tempdb se znovu vytvoří při každém spuštění databázového enginu, aby vždy systém začínal prázdnou databází tempdb. Dočasné uložené procedury a místní dočasné tabulky se automaticky zahodí, když se relace, která je vytvořila, odpojí.

tempdb nikdy nemá co ukládat z jednoho období provozu databázového stroje do jiného. Operace zálohování a obnovení nejsou na tempdbpovoleny .

Fyzické vlastnosti databáze tempdb na SQL Serveru

Následující tabulka uvádí počáteční konfigurační hodnoty tempdb dat a souborů protokolů na SQL Serveru. Hodnoty jsou založeny na výchozích hodnotách databáze model. Velikosti těchto souborů se můžou mírně lišit u různých edicí SQL Serveru.

Soubor	Logický název	Fyzický název	Počáteční velikost	Růst souboru
Primární data	tempdev	tempdb.mdf	8 megabajtů	Automatické zvětšování o 64 MB, dokud nebude disk plný
Sekundární datové soubory	temp#	tempdb_mssql_#.ndf	8 megabajtů	Automatické zvětšování o 64 MB, dokud nebude disk plný
Záznam	templog	templog.ldf	8 megabajtů	Automatické zvětšování o 64 megabajtů na maximálně 2 terabajty

Všechny tempdb datové soubory by měly mít vždy stejnou počáteční velikost a parametry růstu.

Počet datových souborů tempdb

V závislosti na verzi databázového stroje, jeho konfiguraci a úloze může tempdb vyžadovat více datových souborů ke zmírnění kolizí přidělení.

Doporučený celkový počet datových souborů závisí na počtu logických procesorů na počítači. Obecné pokyny:

• Pokud je počet logických procesorů menší nebo roven osmi, použijte stejný počet datových souborů.
• Pokud je počet logických procesorů větší než osm, použijte osm datových souborů.
• Pokud jsou stále pozorovány kolize při přidělení tempdb, zvyšte počet datových souborů o násobky čtyř, dokud se kolize nezmenší na přijatelné úrovně, nebo proveďte změny v úloze.

Další informace naleznete v tématu Doporučení k omezení konfliktu přidělení v databázi tempdb systému SQL Server.

Pokud chcete zkontrolovat aktuální velikost a parametry růstu pro tempdb, použijte zobrazení katalogu sys.database_files v tempdb.

Přesun dat databáze tempdb a souborů protokolů na SQL Serveru

Pokud chcete přesunout data a soubory protokolu tempdb, podívejte se na Přesunutí systémových databází.

Možnosti databáze pro databázi tempdb na SQL Serveru

Následující tabulka uvádí výchozí hodnotu pro každou možnost databáze v databázi tempdb a zda lze tuto možnost upravit. Pokud chcete zobrazit aktuální nastavení těchto možností, použijte katalogové zobrazení sys.databases.

Možnost databáze	Výchozí hodnota	Lze upravit.
ACCELERATED_DATABASE_RECOVERY	OFF	Ne
ALLOW_SNAPSHOT_ISOLATION	OFF	Ano
ANSI_NULL_DEFAULT	OFF	Ano
ANSI_NULLS	OFF	Ano
ANSI_PADDING	OFF	Ano
ANSI_WARNINGS	OFF	Ano
ARITHABORT	OFF	Ano
AUTO_CLOSE	OFF	Ne
AUTO_CREATE_STATISTICS	ON	Ano
AUTO_SHRINK	OFF	Ne
AUTO_UPDATE_STATISTICS	ON	Ano
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Ano
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)	OFF	Ne
CHANGE_TRACKING	OFF	Ne
COMPATIBILITY_LEVEL	Závisí na verzi databázového stroje. Další informace naleznete v tématu ALTER DATABASE (Transact-SQL) úroveň kompatibility.	Ano
CONCAT_NULL_YIELDS_NULL	OFF	Ano
CONTAINMENT	NONE	Ne
CURSOR_CLOSE_ON_COMMIT	OFF	Ano
CURSOR_DEFAULT	GLOBAL	Ano
Stav databáze	ONLINE	Ne
Aktualizace databáze	READ_WRITE	Ne
Přístup uživatele databáze	MULTI_USER	Ne
DATE_CORRELATION_OPTIMIZATION	OFF	Ano
DB_CHAINING	ON	Ne
DELAYED_DURABILITY	DISABLED Bez ohledu na tuto možnost je opožděná trvanlivost vždy povolena na tempdb.	Ano
ENCRYPTION	OFF	Ne
MIXED_PAGE_ALLOCATION	OFF	Ne
NUMERIC_ROUNDABORT	OFF	Ano
PAGE_VERIFY	CHECKSUM pro nové instalace SQL Serveru Existující PAGE_VERIFY hodnota může být zachována, když je instance SQL Serveru upgradována.	Ano
PARAMETERIZATION	SIMPLE	Ano
QUOTED_IDENTIFIER	OFF	Ano
READ_COMMITTED_SNAPSHOT	OFF	Ne
RECOVERY	SIMPLE	Ne
RECURSIVE_TRIGGERS	OFF	Ano
Zprostředkovatel služeb	ENABLE_BROKER	Ano
TARGET_RECOVERY_TIME	60	Ano
TEMPORAL_HISTORY_RETENTION	ON	Ano
TRUSTWORTHY	OFF	Ne

Popis těchto možností databáze naleznete v tématu ALTER DATABASE SET Options (Transact-SQL).

tempdb ve službě Azure SQL Database

V Azure SQL Database se některé aspekty chování a konfigurace tempdb liší od SQL Serveru.

Pro jednotlivé databáze má každá databáze na logickém serveru svůj vlastní tempdb. V elastickém fondu je tempdb sdíleným prostředkem pro všechny databáze ve stejném fondu, ale dočasné objekty vytvořené jednou databází nejsou viditelné pro jiné databáze ve stejném elastickém fondu.

Objekty v tempdb, včetně zobrazení katalogu a zobrazení dynamické správy (DMV), jsou přístupné prostřednictvím odkazu mezi databázemi na databázi tempdb. Můžete se například dotazovat na zobrazení sys.database_files:

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

Globální dočasné tabulky v Azure SQL Database mají rozsah na úrovni databáze. Další informace najdete v tématu Globální dočasné tabulky s rozsahem databáze ve službě Azure SQL Database.

Další informace o velikostech tempdb ve službě Azure SQL Database najdete v tématu:

• Nákupní model virtuálních jader: samostatné databáze, databáze ve fondu
• Nákupní model DTU: jednotlivé databáze, společné databáze

tempdb ve službě SQL Managed Instance

Ve službě Azure SQL Managed Instance se některé aspekty chování tempdb a výchozí konfigurace liší od SQL Serveru.

Můžete nakonfigurovat počet souborů tempdb, jejich nárůst a maximální velikost. Další informace o konfiguraci nastavení tempdb ve službě Azure SQL Managed Instance najdete v tématu Konfigurace nastavení databáze tempdb pro službu Azure SQL Managed Instance.

Spravovaná instance Azure SQL podporuje dočasné objekty stejným způsobem jako SQL Server, kde všechny globální dočasné tabulky a globální dočasné uložené procedury jsou přístupné pro všechny uživatelské relace ve stejné spravované instanci SQL.

Další informace o velikostech tempdb ve službě Azure SQL Managed Instance najdete v dokumentaci o omezení prostředků .

tempdb v databázi SQL ve Fabric

Další informace o velikostech tempdb v databázi SQL v Microsoft Fabric najdete v části Omezení prostředků v porovnání funkcí : Azure SQL Database a databáze SQL v Microsoft Fabric.

Podobně jako Azure SQL Databasemají globální dočasné tabulky v databázi SQL v Microsoft Fabric rozsah na úrovni databáze. Další informace najdete v tématu Databáze s oborem globální dočasné tabulky ve službě Azure SQL Database.

Omezení

V databázi tempdb nelze provést následující operace:

• Přidání skupin souborů
• Zálohování nebo obnovení databáze.
• Změna kolace. Výchozí kolace je kolace serveru.
• Změna vlastníka databáze tempdb patří sa.
• Vytvoření snímku databáze
• Smazání databáze
• Vyřazení hosta uživatele z databáze
• Povolení změnového zachytávání dat
• Účast na zrcadlení databáze.
• Odebrání primární skupiny souborů, primárního datového souboru nebo souboru protokolu
• Přejmenování databáze nebo primární skupiny souborů
• Spuštění DBCC CHECKALLOC.
• Běžící DBCC CHECKCATALOG.
• Nastavení databáze na OFFLINE.
• Nastavení databáze nebo primární skupiny souborů na READ_ONLY.

Dovolení

Libovolný uživatel může v tempdbvytvářet dočasné objekty .

Uživatelé mají přístup pouze k vlastním ne dočasným objektům v tempdb, pokud neobdrží další oprávnění.

Je možné odvolat oprávnění CONNECTtempdb zabránit uživateli nebo roli databáze v používání tempdb. To se nedoporučuje, protože mnoho operací vyžaduje použití tempdb.

Optimalizace výkonu databáze tempdb na SQL Serveru

Velikost a fyzické umístění tempdb souborů může ovlivnit výkon. Pokud je například počáteční velikost tempdb příliš malá, může být čas a prostředky potřeba k automatickému zvětšování tempdb na velikost potřebnou k podpoře úlohy při každém restartování instance databázového stroje.

• Pokud je to možné, použijte okamžitou inicializaci souborů ke zlepšení výkonu operací rozšíření datových souborů.
  • Od verze SQL Server 2022 (16.x) mohou události růstu souboru transakčního protokolu až do velikosti 64 MB také těžit z okamžité inicializace souborů. Další informace naleznete v tématu Okamžité inicializace souboru a transakční protokol.
• Předem přidělte místo pro všechny soubory tempdb nastavením velikosti souboru na hodnotu dostatečně velkou, aby vyhovovala typické úloze v prostředí. Předalokace brání tomu, aby se tempdb příliš často automaticky zvětšovalo, což může negativně ovlivnit výkon.
• Soubory v databázi tempdb by měly být nastaveny na automatické zvětšování, aby poskytovaly prostor během neplánovaného růstu.
• Rozdělení tempdb do více datových souborů stejné velikosti může zlepšit efektivitu operací, které používají tempdb.
  • Aby nedocházelo k nevyváženostem přidělení dat, měly by mít datové soubory stejnou počáteční velikost a parametry růstu, protože databázový stroj používá algoritmus proporcionální vyplnění, který upřednostňuje přidělení v souborech s větším volným místem.
  • Nastavte růst souboru na přiměřenou velikost, například 64 MB, a zvyšte stejný růst u všech datových souborů, aby se zabránilo nevyváženosti růstu.

Pokud chcete zkontrolovat aktuální velikost a parametry růstu pro tempdb, použijte následující dotaz:

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;

Umístěte databázi tempdb na rychlý vstupně-výstupní subsystém. Jednotlivé datové soubory nebo skupiny tempdb datových souborů nemusí být nutně na různých discích, pokud nenarazíte na kritické body vstupně-výstupních operací na úrovni disku.

Pokud existují kolize vstupně-výstupních operací mezi tempdb a uživatelskými databázemi, umístěte tempdb soubory na disky, které se liší od disků, které používají uživatelské databáze.

Poznámka

Ke zlepšení výkonu je zpožděná odolnost vždy zapnutá na tempdb, a to i když je možnost databáze DELAYED_DURABILITY nastavená na DISABLED. Vzhledem k tomu, že se při spuštění znovu vytvoří tempdb, neprochází procesem obnovení a neposkytuje záruku stálosti.

Vylepšení výkonu v databázi tempdb pro SQL Server

Představeno v SQL Serveru 2016 (13.x)

• Dočasné tabulky a proměnné tabulek se ukládají do mezipaměti. Ukládání do mezipaměti umožňuje rychlé vykonávání operací, které odstraní a vytvoří dočasné objekty. Ukládání do mezipaměti také snižuje alokaci stránek a konkurenci metadat.
• Protokol pro západky alokační stránky je vylepšen, aby se snížil počet použitých západek UP (aktualizace).
• Režie protokolování u tempdb se snižuje, aby se snížila spotřeba vstupně-výstupní šířky pásma disku v souboru protokolu tempdb.
• Instalační program SQL přidá během instalace nové instance několik tempdb datových souborů. Projděte si doporučení a nakonfigurujte tempdb na stránce konfigurace databázového stroje v instalačním programu SQL nebo použijte parametr příkazového řádku /SQLTEMPDBFILECOUNT. Ve výchozím nastavení instalační program SQL přidá tolik tempdb datových souborů jako počet logických procesorů nebo osm, podle toho, co je nižší.
• Pokud existuje více tempdb datových souborů, všechny soubory se automaticky zvětšují a mají stejnou velikost v závislosti na nastavení růstu. příznak trasování 1117 už není potřeba. Další informace najdete v tématu změny -T1117 a -T1118 pro TEMPDB a změny uživatelských databází.
• Všechna přidělení v tempdb používají jednotné rozsahy. příznak trasování 1118 už není potřeba. Další informace o vylepšení výkonu v tempdbnaleznete v blogovém článku TEMPDB - Soubory a trasovací příznaky a aktualizace, Oh My!.
• Vlastnost AUTOGROW_ALL_FILES je vždy zapnutá pro skupinu souborů PRIMARY.

Představeno v SQL Serveru 2017 (14.x)

• Prostředí instalace SQL nabízí lepší pokyny pro počáteční přidělení souboru tempdb. Instalační program SQL upozorní zákazníky, pokud je počáteční velikost souboru nastavená na hodnotu větší než 1 GB a pokud okamžitá inicializace souboru není povolená, zabraňuje zpoždění při spouštění instance.
• Zobrazení dynamické správy sys.dm_tran_version_store_space_usage sleduje využití úložného prostoru verzí pro každou databázi. Toto zobrazení dynamické správy je užitečné pro správce databáze, kteří chtějí proaktivně plánovat velikost tempdb na základě požadavku na využití úložiště verzí pro každou databázi.
• Inteligentní zpracování dotazů funkce, jako jsou adaptivní spojení a zpětná vazba ohledně udělení paměti, snižují přetečení paměti při po sobě jdoucích spuštěních dotazu, což snižuje její využití tempdb.

Představeno v SQL Serveru 2019 (15.x)

• Databázový stroj při otevírání souborů tempdb nepoužívá možnost FILE_FLAG_WRITE_THROUGH, aby se povolila maximální propustnost disku. Vzhledem k tomu, že se při spuštění znovu vytvoří tempdb, tato možnost není nutná k zajištění odolnosti dat. Další informace o FILE_FLAG_WRITE_THROUGHnaleznete v tématu Protokolování a algoritmy úložiště dat, které rozšiřují spolehlivost dat v SQL Serveru.
• Metadata databáze TempDB optimalizovaná pro paměť odstraňuje potíže s kolizí metadat dočasných objektů v tempdb.
• Souběžné aktualizace Stránky volného místa (PFS) snižují ladění stránky ve všech databázích, což je problém nejčastěji vídaný v tempdb. Toto vylepšení změní správu souběžnosti aktualizací stránek PFS tak, aby je bylo možné aktualizovat pod sdílenou západkou, a ne exkluzivní západkou. Toto chování je ve výchozím nastavení zapnuté ve všech databázích (včetně tempdb) počínaje SQL Serverem 2019 (15.x). Další informace o stránkách PFS naleznete v článku Pod pokličkou: stránky GAM, SGAM a PFS.
• Ve výchozím nastavení nová instalace SQL Serveru v Linuxu vytvoří několik tempdb datových souborů na základě počtu logických jader (s až osmi datovými soubory). To neplatí pro místní upgrade podverze nebo hlavní verze. Každý tempdb datový soubor má velikost 8 MB, s automatickým přírůstkem o 64 MB. Toto chování se podobá výchozí instalaci SQL Serveru ve Windows.

Představeno v SQL Serveru 2022 (16.x)

• Zavedli jsme vylepšenou škálovatelnost prostřednictvím zvýšení souběžnosti zámků systémových stránek. Souběžné aktualizace stránek globálního mapování přidělení (GAM) a sdílených stránek mapy globálního přidělování (SGAM) snižují západkové kolize stránek při přidělování a dealokaci datových stránek a rozsahů. Tato vylepšení platí pro všechny uživatelské databáze a zejména prospívají náročným úlohám v tempdb. Pro více informací o stránkách GAM a SGAM si přečtěte Pod pokličku: stránky GAM, SGAM a PFS. Další informace najdete v tématu Vylepšení souběžnosti systémových stránek (Ep. 6) | Data vystavená.

Metadata databáze TempDB optimalizovaná pro paměť

Kolize metadat dočasných objektů byla historicky kritickým bodem pro škálovatelnost mnoha úloh SQL Serveru. Sql Server 2019 (15.x) zavedl funkci, která je součástí databáze v paměti řady funkcí: metadata databáze TempDB optimalizovaná pro paměť.

Povolení funkce metadat TempDB optimalizované pro paměť odebraje toto kritické místo pro úlohy, které byly dříve omezeny promítáním metadat dočasných objektů uvnitř tempdb. Od SQL Serveru 2019 (15.x) se systémové tabulky, které se podílí na správě metadat dočasných objektů, mohou stát tabulkami bez zámků, neodolnými a optimalizovanými pro paměť.

Spropitné

Vzhledem k aktuálním omezením doporučujeme povolit metadata databáze TempDB optimalizovaná pro paměť pouze v případě, že dojde k kolizí metadat objektů a výrazně ovlivňuje vaše úlohy.

Následující diagnostický dotaz vrátí jeden nebo více řádků, pokud dochází ke kolizí dočasných metadat objektů. Každý řádek představuje systémovou tabulkua vrátí počet relací, které bojují o přístup k této tabulce v době, kdy se tento diagnostický dotaz spustí.

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;

V tomto sedmiminutových videu najdete přehled o tom, jak a kdy používat funkci metadat TempDB optimalizovanou pro paměť:

Poznámka

V současné době není funkce metadat databáze TempDB optimalizovaná pro paměť dostupná ve službě Azure SQL Database, databázi SQL v Microsoft Fabric a spravované instanci Azure SQL.

Konfigurace a použití metadat databáze TempDB optimalizovaných pro paměť

Následující části obsahují kroky pro povolení, konfiguraci, ověření a zakázání funkce metadat databáze TempDB optimalizovaná pro paměť.

Zapnout

Pokud chcete tuto funkci povolit, použijte následující skript:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Další informace naleznete v sekci ALTER SERVER. Tato změna konfigurace vyžaduje restartování služby, aby se projevila.

Pomocí následujícího příkazu T-SQL můžete ověřit, jestli je tempdb optimalizováno pro paměť:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Pokud vrácená hodnota je 1 a po povolení funkce došlo k restartování, je tato funkce povolená.

Pokud se serveru nepodaří spustit z nějakého důvodu po povolení metadat databáze TempDB optimalizovaných pro paměť, můžete tuto funkci obejít spuštěním instance databázového stroje s minimální konfigurace pomocí možnosti spuštění -f. Potom můžete zakázat funkci a odebrat -f možnost restartování databázového stroje v normálním režimu.

Vytvoření vazby k fondu zdrojů za účelem omezení využití paměti

Pokud chcete chránit server před možnými stavy nedostatku paměti, doporučujeme svázat tempdb se správcem prostředků a fondem prostředků, který omezuje paměť spotřebovanou metadaty TempDB optimalizované pro paměť. Následující ukázkový skript vytvoří fond zdrojů, nastaví jeho maximální paměť na 20%, povolí správce prostředkůa připojí tempdb k fondu zdrojů.

Tento příklad používá jako limit paměti pro demonstrační účely 20%. Optimální hodnota ve vašem prostředí může být větší nebo menší v závislosti na vaší úloze a v průběhu času se může změnit, pokud se úloha změní.

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');

Tato změna také vyžaduje restartování služby, aby se projevila, i když jsou metadata TempDB optimalizovaná pro paměť již povolena.

Ověřte vazbu fondu prostředků a monitorujte využití paměti

Pokud chcete ověřit, že tempdb je svázaný s fondem zdrojů a ke sledování statistik využití paměti pro fond, použijte následující dotaz:

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 JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Odstranit vazbu fondu zdrojů

Pokud chcete odebrat vazbu na fond prostředků přitom zachovat pro paměť optimalizovaná metadata databáze TempDB, spusťte následující příkaz a restartujte službu.

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Vypnout

Pokud chcete zakázat metadata databáze TempDB optimalizovaná pro paměť, spusťte následující příkaz a restartujte službu:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Omezení metadat databáze TempDB optimalizovaných pro paměť

• Povolení nebo zakázání funkce metadat databáze TempDB optimalizované pro paměť vyžaduje restartování.

• V některých případech můžete pozorovat vysoké využití paměti MEMORYCLERK_XTP správcem paměti, což způsobuje chyby z nedostatku paměti ve vaší úloze.

  Pokud chcete zobrazit využití paměti MEMORYCLERK_XTP úředníkem vzhledem ke všem ostatním úředníkům paměti a vzhledem k paměti cílového serveru, spusťte následující dotaz:

  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;
  

  Pokud je MEMORYCLERK_XTP paměť vysoká, můžete problém zmírnit následujícím způsobem:

  • Vytvořte vazbu tempdb databáze s fondem prostředků, který omezuje spotřebu paměti pomocí metadat databáze TempDB optimalizovaných pro paměť. Další informace najdete v tématu Konfigurace a použití metadat databáze tempdb optimalizovaných pro paměť.
  • Systémovou uloženou proceduru je možné pravidelně spouštět, aby se uvolnila MEMORYCLERK_XTP paměť, která už není nutná. Další informace naleznete v tématu sys.sp_xtp_force_gc (Transact-SQL).

  Další informace najdete v tématu metadata tempdb optimalizovaná pro paměť (HkTempDB), při chybách paměti.

• Pokud používáte In-Memory OLTP, není jedné transakci povolen přístup k tabulkám optimalizovaným pro paměť ve více než jedné databázi. Z tohoto důvodu nemůže žádná transakce čtení nebo zápisu, která zahrnuje tabulku optimalizovanou pro paměť v uživatelské databázi, také přistupovat k systémovým zobrazením tempdb ve stejné transakci. Pokud k tomu dojde, zobrazí se chyba 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.
  

  Toto omezení platí také pro jiné scénáře, kdy se jedna transakce pokusí o přístup k tabulkám optimalizovaným pro paměť ve více než jedné databázi.

  Může se například zobrazit chyba 41317, pokud dotazujete zobrazení sys.stats katalogu v uživatelské databázi, která obsahuje tabulky optimalizované pro paměť. K tomu dochází, protože dotaz se pokusí získat přístup ke statistikám data v tabulce optimalizované pro paměť v uživatelské databázi a metadat optimalizovaných pro paměť v tempdb.

  Následující ukázkový skript vytvoří tuto chybu, pokud je povolena metadata databáze TempDB optimalizovaná pro paměť:

  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;
  

  Poznámka

  Toto omezení neplatí pro dočasné tabulky. Ve stejné transakci můžete vytvořit dočasnou tabulku, která přistupuje k tabulce optimalizované pro paměť v uživatelské databázi.

• Dotazy na zobrazení systémového katalogu vždy používají úroveň izolace READ COMMITTED. Pokud jsou povolena metadata databáze TempDB optimalizovaná pro paměť, dotazy na zobrazení systémového katalogu v tempdb používají úroveň izolace SNAPSHOT. V obou případech nejsou dodrženy pokyny pro uzamykání.

• Columnstore indexy nelze vytvořit v dočasných tabulkách, pokud jsou aktivována metadata databáze TempDB optimalizovaná pro paměť.

  • V důsledku toho se při povolení metadat databáze TempDB optimalizovaných pro paměť nepodporuje použití sp_estimate_data_compression_savings uložené procedury s parametrem komprese dat COLUMNSTORE nebo COLUMNSTORE_ARCHIVE.

Plánování kapacity pro databázi tempdb na SQL Serveru

Určení vhodné velikosti pro tempdb závisí na mnoha faktorech. Mezi tyto faktory patří úlohy a používané funkce databázového stroje.

Doporučujeme analyzovat tempdb spotřebu místa provedením následujících úloh v testovacím prostředí, kde můžete reprodukovat typickou úlohu:

• Povolte automatické rozšiřování pro soubory tempdb. Všechny tempdb datové soubory by měly mít stejnou počáteční velikost a konfiguraci automatického zvětšování.
• Reprodukujte pracovní zátěž a monitorujte využití místa tempdb.
• Pokud provádíte pravidelnou údržbu indexů , spusťte úlohy údržby a monitorujte prostor tempdb.
• Pomocí maximálního využitého místa z předchozích kroků můžete předpovědět celkové využití úloh. Upravte tuto hodnotu pro promítanou souběžnou aktivitu a pak nastavte velikost tempdb odpovídajícím způsobem.

Monitorování použití databáze tempdb

Nedostatek místa na disku v tempdb může způsobit významné výpadky a výpadky aplikací. Pomocí zobrazení dynamické správy sys.dm_db_file_space_usage můžete monitorovat využité místo v tempdb souborech.

Například následující ukázkový skript najde:

• Volné místo v tempdb (bez zohlednění volného místa na disku, které by mohlo být k dispozici pro růst tempdb)
• Prostor používaný úložištěm verzí
• Prostor používaný interními objekty
• Prostor používaný uživatelskými objekty

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;

Pokud chcete monitorovat přidělení nebo aktivitu uvolnění stránky v tempdb na úrovni relace nebo úkolu, můžete použít sys.dm_db_session_space_usage a sys.dm_db_task_space_usage zobrazení dynamické správy. Tato zobrazení vám můžou pomoct identifikovat dotazy, dočasné tabulky nebo proměnné tabulky, které používají velké objemy tempdb prostoru.

Použijte například následující ukázkový skript ke zjištění tempdb přiděleného a uvolněného prostoru interními objekty ve všech aktuálně běžících úlohách v každé relaci.

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;

Pomocí následujícího ukázkového skriptu zjistěte přidělené a aktuálně využité místo tempdb interními a uživatelskými objekty pro každou relaci a požadavek, jak pro probíhající, tak dokončené úkoly.

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;

Související obsah

• možnost SORT_IN_TEMPDB pro indexy
• systémové databáze
• sys.databases
• sys.master_files
• Přesunutí souborů databáze