SQLShack

V tomto článku se budeme diskutovat o osvědčené postupy, které by měly být dodržovány v zájmu zachování databáze TempDB v dobrém stavu a zabránit jakékoliv nečekané růst databáze, kromě řízení, které mohou být dodržovány, aby detekovat tento neočekávaný růst jednou došlo.

TempDB přehled

databáze TempDB je jedním z nejdůležitějších SQL Server systémové databáze, který se používá k ukládání dočasných objektů uživatele, jako je například dočasné tabulky, které jsou definovány uživatelem nebo se vrátil z tabulky-cenil funkci exekuce, dočasně uložené procedury, proměnné tabulka nebo indexy.

kromě uživatelských objektů bude TempDB používán k ukládání Interních objektů vytvořených databázovým strojem SQL Server během různých interních operací, jako je mezilehlé třídění, zařazování, agregace nebo kurzorové operace.

TempDB systému databáze se používá také k ukládání řádky verze za účelem podpory funkce, které vyžadují sledování změn, které jsou prováděny na řádky tabulky, jako je například snímek úroveň izolace transakcí, on-Line Index znovu sestaví nebo Více Aktivní Výsledek Sad funkce.

databáze TempDB bude zrušena a znovu znovu při každém restartování služby SQL Server, počínaje novou čistou kopií databáze. Na základě této skutečnosti budou všechny námitky uživatele a interní databáze uložené v této databázi automaticky zrušeny při restartování služby SQL Server nebo při odpojení relace, kde jsou tyto vytvořené objekty. Operace zálohování a obnovení proto nejsou pro TempDB k dispozici.

proč TempDB

databáze systému TempDB hraje důležitou roli v procesu ladění výkonu serveru SQL. Protože se používá jako ukládání do mezipaměti pro ukládání různých typů objektů databáze uživatelů a pro ukládání vnitřních objektů systému, aby se urychlily procesy související s databázovým strojem SQL Server.

vzhledem k této zásadní roli, kterou hraje systémová databáze TempDB při zvyšování celkového výkonu instance serveru SQL, je velmi důležité použít počáteční velikost, automatický růst a osvědčené postupy umístění v databázových datech a souborech protokolu TempDB. Uplatňování těchto osvědčených postupů způsobem, který odpovídá vaší instance serveru SQL Server pracovní vytížení se zabránilo rozšíření databáze TempDB datové soubory a soubory protokolu velmi často, přičemž v úvahu, že soubor, který rozšiřuje proces je nákladný proces, ve kterém SQL Server Engine bude požadovat větší prostor z operačního systému, který bude provádět nulování na to, že prostor před jejich přidělování k serveru SQL Server Motoru. Můžete si představit čas a zdroje potřebné pro tento proces přidělování.

Pokud jste se již uplatňují všechny osvědčené postupy a nečekaný růst operaci došlo, můžete urychlit proces přidělování pomocí databáze okamžité inicializace souboru možností, které operační systém bude věřit SQL Server služby a přidělit požadované místo bez provedení čas a zdroje plýtvání provoz na přidělené místo.

Osvědčených postupů

Existuje řada databáze TempDB systému databáze konfigurace osvědčené postupy, které jsou doporučené s cílem získat co nejlepší výkon z této sdílené zdroje a chránit vaše instance serveru SQL Server z nějaké neočekávané růst v databáze TempDB dat nebo souborů protokolu, které mohou vyplnění hlubších disku.

tyto konfigurace zahrnují:

• počet datových souborů TempDB by měl odpovídat počtu logických procesorů, až osmi souborů, na počítači, kde je nainstalována instance serveru SQL. Je-li počet logických procesorů na tento stroj je větší než osm, nastavit počet datových souborů na osm, s možností prodloužení o násobek 4 v případě, že máte TempDB tvrzení vydání
• ujistěte Se, že databáze TempDB soubory mají stejné velikosti ve stejné skupina souborů, poskytovat nejvyšší možný stupeň paralelní operace účinnost, vzhledem k Proporcionální Výplň psaní mechanismus, který se používá k zápisu dat uvnitř datových souborů. Pro více informací, podívejte Pochopení SQL Server Proporcionální výplň algoritmus
• Konfigurace databáze TempDB datové soubory a soubory protokolu s přiměřenou počáteční velikost a auto-růst množství, které odpovídá očekávané množství dat, které bude napsáno na tyto soubory, a různé typy zátěží provádí v SQL Server instance, včetně uživatelské aktivity a index operace údržby, aby se zabránilo časté malé zvýšení částek, které mají vliv na celkové stupně výkonu
• Vyhledejte soubory databáze TempDB na rychlé I/O subsystém, aby se předešlo I / O úzká místa
• vyhledejte TempDB na samostatné diskové jednotce, mimo uživatelské databáze a další systémové databáze. Tímto způsobem, je těžké číst a psát operace prováděné na TempDB nebude mít vliv nebo být ovlivněny tím, že pracovní vytížení uživatelských databází, zvýšení celkové instanci serveru SQL Server výkon

další informace o nová vylepšení v databáze TempDB výkon, podívejte se na Databáze TempDB dokumentu.

kde použít

před verzí SQL Server 2016 lze alokaci velikosti TempDB provést po instalaci instance SQL Serveru ze stránky vlastnosti Databáze. Kde můžete ovládat počet databázových datových souborů, které odpovídají aktuální instanci serveru SQL, počáteční velikost pro každé databázové údaje a soubor protokolu, správnou hodnotu pro možnost Autogrowth a umístění databázových dat a souborů protokolu.

provést tyto změny, rozbalte uzel Databáze v SQL Server Management Studio, rozbalte Databází Systému uzlu a pak klepněte pravým tlačítkem myši na databáze tempdb databáze a vyberte možnost Vlastnosti jako níže:

Z Databáze otevřené okno Vlastnosti, procházet Soubory, stránky a zkontrolovat nebo změnit jiný údajů z databáze a soubory protokolu nastavení je uvedeno níže:

Počínaje SQL Server 2016 verze, všechny tyto změny, s osvědčenými postupy, které odpovídají aktuální zdroje serveru již navrhl, průvodce instalací mohou být kontrolovány a prováděny v instanci serveru SQL Server proces instalace, použití SQL Server Průvodce instalací, podle Database Engine Konfigurace | TempDB, jak je uvedeno níže:

Sledovat TempDB růst

Sledování růstu TemDB soubory databáze a dotazy, které jsou náročné TempDB zdrojů lze provést pomocí čítače Sledování Výkonu souvisejících s TempDB soubory a SQL Profiler nástroj, který naslouchá na konkrétní databáze zátěž typ.

příjemnější způsob, jak správce databáze sledovat růst databázových souborů TempDB, je dotazování na následující dynamické pohledy na správu:

• sys.dm_db_file_space_usage, která vrací informace o využití prostoru pro každý soubor v databázi, aniž by ukazovat, jaké relace nebo úkol spotřebované ten prostor,
• sys.dm_db_session_space_usage, která vrací počet alokovaných a platný stránek za každé sezení
• sys.dm_db_task_space_usage, která vrací počet alokovaných a platný stránek za každý úkol

Všimněte si, že výsledek vrácené sys.dm_db_session_space_usage DMV shrnout výsledek se vrátil z sys.dm_db_task_space_usage DMV když jsou všechny úkoly úspěšně dokončeny, vzhledem k tomu, že v rámci stejné relace bude provedeno více úkolů souvisejících se stejným dotazem. Další výhodou těchto DMVs je, že to bude show typ objektu, který spotřebuje TempDB zdrojů a pokud tento objekt je objekt uživatele, jako jsou dočasné nebo proměnné tabulka nebo interní objekt, jako je například index údržby nebo druh provozu. Takže na základě počtu stránek spotřebovaných každým objektem můžete určit, zda je TempDB spotřebován interním nebo uživatelským objektem, a optimalizovat tohoto spotřebitele.

sys.dm_db_file_space_usage

sys.dm_db_file_space_usage DMV může být použit ke sledování místa na disku v databáze TempDB soubory. Dotazy níže ukazují nepřidělené místo, prostor slouží k ukládání řádky verze, prostor pohlcen vnitřní objekty, a konečně prostor spotřebované uživatelem objekty, takto:

Z výsledku, který se vrátil ze své místní instance serveru SQL Server, můžete vidět, že tam je 60 MB nevyužité v databáze TempDB soubory, s pouze 0,5 MB používá pro interní objekty a 1,5 MB používá pro uživatelské objekty, jak je uvedeno níže:

sys.dm_db_task_space_usage a sys.dm_db_session_space_usage

sys.dm_db_task_space_usage a sys.dm_db_session_space DMVs jsou použity ke kontrole, počet stránek přidělené a navrácen tím, že každý úkol nebo relace v databázi TempDB. Tímto způsobem uvidíte, který uživatel nebo interní objekt spotřebovává prostor TempDB.

například níže uvedený výsledek je vrácen z dotazování sys.dm_db_task_space_usage DMV podle databáze TempDB, kde to vrátí počet stránek přidělené a platný pro každého uživatele nebo interní úkol, a to následovně:

Ale DBA nebude vyžadovat, aby mnoho detailů, aby začít sledovat dotaz, který je náročný TempDB zdrojů. Takže musíme shrnout předchozí výsledek pro každou relaci pomocí níže uvedeného dotazu:

A souhrnné stránky spotřebu výsledek na každé zasedání bude jako níže:

stejný výsledek může být vrácena pomocí dotazu sys.dm_db_session_space_usage DMW, která shrnuje stránky alokace a dealokace pro všechny úkoly, které běží v rámci stejné relace, jak je uvedeno níže:

Pojďme provádět některé akce na databáze TempDB pomocí vnitřní a uživatelské objekty jako níže:

Pak spustit konsolidované dotaz níže, která bude vracet číslo přidělené a navrácen stránky, interní a uživatelské objekty, a ukázat, jak dokončených operací (pomocí sys.dm_db_session_space_usage DMV) a nedokončené operace (pomocí sys.dm_db_task_space_usage DMV):

A předchozí operace, výsledek bude odrážet výstupu dotazu, která udává počet stránek přidělené a navrácen vytvořením uživatele dočasné tabulky a třídění dat v indexu obnovit proces, jak je uvedeno níže:

text dotazu, který spotřebuje TempDB zdroje, můžete použít SQL rozvoj schopnosti spojením výsledek ys.dm_db_task_space_usage DMV s jinými DMVs jako sys.dm_exec_requests a sys.dm_exec_sessions s sys.dm_exec_sql_text Dynamické Funkce Řízení, kde to bude zobrazovat dotazy, které jsou náročné TempDB prostor, jako v tomto dotazu.

výsledek bude podobný níže uvedenému:

• Autor
• Poslední Příspěvky

Ahmad Yaseen je Microsoft Velký Datový inženýr se znalostmi a zkušenostmi s SQL, BI, SQL Server, Správa Databáze a Rozvoje oblasti.
On je Microsoft Certified Solution Expert na Správu Dat a Analytics, Microsoft Certified Solution Associate v SQL Databáze Správa a Vývoj, Azure Developer partner a Microsoft Certified Trainer.
také přispívá svými tipy SQL v mnoha blogech.
Zobrazit všechny příspěvky od Ahmad Yaseen

Nejnovější příspěvky od Ahmad Yaseen (viz všechny)

• Azure Data Factory Rozhovor Otázky a Odpovědi – 11. února 2021
• Jak sledovat Azure Data Factory – 15. ledna, 2021
• Pomocí Ovládání Zdroje v Azure Data Factory – leden 12, 2021