SQL Server TempDB handledning

av: Eric Blinn | uppdaterad: 2020-07-17 | kommentarer (2) | relaterad: mer > Systemdatabaser

Problem

SQL Server kommer installeras med fyra systemdatabaser som standard. De är mästare, modell, msdb och TempDB. Jag behöver veta vad systemdatabasen ”TempDB” är och vad den används för. Dessa tidigare tutorials covermaster, modell ochmsdb i detalj.

lösning

detta tips täcker TempDB-databasen. TempDB är en databas som harmånga funktioner inom SQL Server, men det kallas sällan uttryckligen. Det har så många funktioner att det ofta är en av de mest trafikerade, om inte den mest trafikerade databasen på de flesta SQL Server-instanser. Läs vidare för att lära dig vadmånga av dessa funktioner är!

SQL Server TempDB översikt

vad är syftet med SQL Server TempDB?

en av funktionerna i TempDB är att agera något som en sida eller swap-fil skullepå operativsystemnivå. Om en SQL Server-operation är för stor för att vara slutförd i minnet eller om det ursprungliga minnesanslaget för en fråga är för litet, kan operationen flyttas till disk i TempDB.

en annan funktion av TempDB är att lagratillfälliga tabeller. Den som har skapat en tillfällig tabell i T-SQL med hjälp av ett pund eller hash-prefix (#) eller doublepound/hash-prefixet (##) har skapat ett objekt i TempDB eftersom det är här de lagras.

CREATE TABLE #MSSQLTips (RowID int);

när atrigger kör de infogade och raderade virtuella tabellerna lagras i TempDB.

alla databaser som använder read COMMITTED SNAPSHOT ISOLATION (RCSI) kommer att ha sinradsversionsinformation lagrad i TempDB.

var och en av ovanstående funktioner i TempDB är slutförda utan att någonsin nämna tempdb med namn och kan därmed vara överraskande användningsområden för TempDB.

TempDB kan också kallas uttryckligen på några sätt. Tabeller kan generatedin TempDB genom att referera till databasen i en create uttalande. Koden ser utexakt som en vanlig DDL-operation, men när den körs i TempDB är tabellen per definition en tillfällig tabell.

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

TempDB regenereras vid varje start av SQL Server-instansen. Anyobjects som kan ha skapats i TempDB under en tidigare session kommer inte att kvarstå vid en omstart av tjänsten. TempDB får sin ursprungliga objektlista från themodel databas som i allmänhet kommer att vara tom eller nästan tom.

Index underhåll kan valfritt bli ombedd att göra sorteringar i TempDB snarare än attemptingto göra det med ledigt utrymme i användardatabasen.

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

kan jag köra SQL Server utan en TempDB-databas?

SQL Server kan inte köras (utom i mycket extrema återhämtningsscenarier och för mycketkorta perioder) utan TempDB.

är versionen av TempDB-databasen unik för version, utgåva och patchlevel för SQL Server?

TempDB är en mestadels tom databas och det finns inget särskilt unikt omnågon utgåva eller patchnivå för SQL Server.

det har bara skett 2 meningsfulla förändringar i TempDB de senaste åren på versionsnivå. Den första är att starta i SQL Server 2016 beteendet hos Tempdbändrades så att möjliggöra spårflaggor T1117 och T1118 inte längre beaktasen bästa praxis. Den andra är att från och med SQL Server 2019 uppgraderades några tungt använda systemobjekt i TempDB till minnestabeller för att minska konkurrensen och förbättra den totala serverns prestanda.

kan du släppa eller byta namn på TempDB-databasen och ska du göra det av någon anledning?

TempDB kan inte släppas, lossas, tas offline eller döpas om. Försöknågon av dessa operationer kommer att returnera ett fel. Det finns ingen anledning att detta borde försökas eftersom det här är en kritisk systemdatabas.

SQL Server TempDB Databasplats

vilka fysiska filer och namn stöder TempDB-databasen?

standardlogiska filnamn är tempdev för data och templog för logg.De kan hittas på disk som tempdb.mdf och templog.ldf respektive. Tempdbhar vanligtvis många datafiler.

hur många TempDB-datafiler ska det finnas?

det bör finnas en TempDB-datafil för varje tråd/kärna/vCPU på instancewith högst 8.

var hittar jag TempDB-databasen på disk och i SSMS?

filerna kan hittas genom att fråga sys.sysfiles dmv eller filfönstret i fönstret databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

gör det en skillnad där TempDB-databasfilerna finns (behöver de vara på C:\, SAN, etc.)?

bästa praxis diktera att dina TempDB datafiler ska vara på snabbast storageavailable på grund av deras aktivitetsnivåer. För vissa fall kan det innebära att lagra dem med användardatabasdatafilerna, men en separat volym på separat disk är föredragen, om den är tillgänglig.

det finns olika åsikter om TempDB-loggfilen, men den här författaren föredrar att lägga den här filen med de andra transaktionsloggfilerna.

kan TempDB-databasen flyttas?

TempDB-databasen kan flyttas. Metoden för att flytta TempDB är lättatt genomföra. Ändra bara filnamnegenskapen för önskad fil(er). inga filer behöver flyttas som när andra systemdatabaser flyttas. SQLServer skapar filen / filerna med det nya namnet och / eller på den nya platsen vid next service start. Glöm inte att gå tillbaka och ta bort de nuanvända TempDB-filerna som kommer att vara kvar.

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

SQL Server TempDB databasstorlek och tillväxt

hur ska tillväxtinställningarna för databasen och transaktionsloggen konfigureras för TempDB-databasen?

Autogrowth inställningar bör ställas in på specifika antal MB, inte en procentsats.Varje datafil ska ha samma antal MB som anges för tillväxt.

Right-sizing filerna för en databas är i allmänhet en bästa praxis, men nevermore så än för TempDB. Övervaka instansen – speciellt om den ärny-och avgöra om filerna växer under normal drift.Om så är fallet, ändra startstorleken så att SQL Server inte kommer att tvingas växafilerna konsekvent efter varje servicestart.

Vad är den typiska storleken på TempDB-databasen och hur stor kan den bli?

det finns inget bra svar på denna fråga. Vad som är typiskt för en instans kaninte vara typiskt för en annan. Denna författare har sett instanser där TempDB är i grund och bottenunused genomsnitt endast ett fåtal MB använt utrymme vid varje given tidpunkt. Ändå frågas othersare ständigt och använder hundratals GB utrymme – även i rymden mätt i TB – speciellt under ett indexunderhållsfönster.

när man bestämmer hur mycket utrymme man ska ägna åt TempDB på en ny instans finns detnågra saker som kan göras. Ersätter den nya instansen en äldre befintliginstans? Om så är fallet, använd ett perfmon-spår eller schemalagd DMV-fångst för att bestämmadet använda utrymmet i TempDB under flera dagar. Se till att inkludera ett underhållsfönster i den fångsten. Försök att allokera tillräckligt med utrymme på den nya instansen för att redogöra för det största använda utrymmet som fångats under spåret med ytterligare wiggle room. Sedan rätt storlek filerna för att passa in i det utrymmet tilldelning.

de 2 frågorna nedan kan hjälpa till. Den första kommer att ta en ögonblicksbild av storleken pådata och loggfiler tillsammans med det utrymme som används i filerna. Detta kan köras regelbundet för att spåra användningen över tiden. Den andra frågan kommer att återställa antalet gånger loggfilen har vuxit sedan förra gången instancewas startas.

om det inte finns en befintlig arbetsbelastning som kan användas som en guide och det inte finns någon annan vägledning, kanske från en programvaruleverantör, är det enda som kan göras att se till att det finns utrymme i TempDB för att redogöra för de största anticipated enskilda objekt som kan sorteras i TempDB under ett index maintenancewindows. Därifrån, övervaka användningen och justera därefter.

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

Server TempDB databasunderhåll

kan du krympa databasen och transaktionsloggen för TempDB-databasen?

Tekniskt, Ja, men det här är inte en operation som någonsin ska slutföras.Målet bör alltid vara att rätt storlek TempDB. Om någon form av engångsoperationorsakar att dessa filer växer oförklarligt, kommer de att återgå till det normala vid omstart av nextservice.

ska du bygga om index?

TempDB kommer inte att ingå i några indexrelaterade underhållsplaner. Detta innebär inga ombyggnadsindex, omorganisering eller uppdatering av statistik.

ska du köra integritetskontroller vad är koden?

det finns olika åsikter om huruvida tempdb bör kontrolleras för integritet.Denna författare kommer att lägga fram argumentetför och emot och låta läsaren bestämma.

mot: en integritetskontrolloperation kan intekontrollera TempDB med samma totalitet som alla andra databaser.De data som finns i TempDB ärhögt övergående så att integritetskontrolloperationen inte kommer att hitta mycket för att kontrollera vid varje given tidpunkt.Av samma anledning kommer alla data som blir korrupta sannolikt att vara aktiva data som kommer att orsaka en varning när de ärläs från disken långt innan en integritetskontrolloperation sannolikt skulle kallas.

för: medan en integritetskontrolloperationkan inte kontrollera allt i TempDB som det kontrollerar i andra databaser som inte betyder att det inte har något värde.TempDB är en integrerad databas ochom det finns korruption att veta om det är absolut nödvändigt så att korrigerande åtgärderkan vidtas.

koden för att köra en integritetskontroll finns nedan.

DBCC CHECKDB ('TempDB'); 

SQL Server TempDB-databasobjekt

vilka är de viktigaste tabellerna och procedurerna i TEMPDB-databasen som är viktiga för SQL Server-proffs att veta om?

det finns inga.Denna databas används för temporärlagring av objects.It kommer inte installeras med anytables eller förfaranden.

kan jag lagra mina egna objekt i TEMPDB-databasen?

objekt kan säkert skapas i TempDB, men de kommer inte att bestå förbinästa start av SQL Server-tjänsten.

SQL Server TempDB Databasbehörigheter

Vem har tillgång till TempDB-databasen och finns det olika behörighetsnivåer?

alla har tillgång och samma tillgång till TempDB.

SQL Server TempDB databas Backup

behöver jag säkerhetskopiera TempDB-databasen?

TempDB kan inte säkerhetskopieras. Den lagrar bara övergående data och återskapas varje gång tjänsten startar så det finns inget att återhämta sig i händelse av en katastrof.

SQL Server TempDB Databasåterställningsmodell

vilken återställningstempdb ska TempDB-databasen ha och kan återställningsmodellenändras?

TempDB är i enkel återställning och detta kan inte ändras.

SQL Server TempDB Databasåterställning

finns det någonsin ett behov av att återställa TempDB-databasen?

TempDB kan inte återställas. Det bör inte finnas några data och ingen kod i Tempdbatt återställas. Om TempDB blir skadad, helt enkelt ta bort data och loggfiler så att nya kan genereras vid nästa tjänsten startar.

nästa steg

• detta tips visar hur man gör varningar för problem som finns inom TempDB-storlekaroch diskutrymme
• detta tips visar hur man kan aktivera omedelbar filinitiering, en viktig funktion för alla DBs, menspeciellt TempDB om den inte är korrekt storlek.

Senast uppdaterad: 2020-07-17

om författaren

Eric Blinn är Sr. Data arkitekt för Squire Patton Boggs. Han är också en SQL författare och passera lokal gruppledare.
Visa alla mina tips

relaterade resurser

• fler SQL Server DBA-Tips…