SQLShack

i den här artikeln kommer vi att diskutera de bästa metoderna som bör följas för att hålla TempDB-databasen i ett hälsosamt tillstånd och förhindra oväntad tillväxt av databasen, förutom proceduren som kan följas för att upptäcka denna oväntade tillväxt en gång inträffade.

TempDB översikt

TempDB-databasen är en av de viktigaste SQL Server-systemdatabaserna, som används för att lagra tillfälliga användarobjekt, till exempel de tillfälliga tabeller som definieras av användaren eller returneras från tabellvärderad funktionsexekvering, tillfälligt lagrade procedurer, tabellvariabler eller index.

förutom användarobjekten kommer TempDB att användas för att lagra interna objekt som skapas av SQL Server-databasmotorn under de olika interna operationerna, till exempel mellanliggande sortering, spolning, aggregering eller marköroperationer.

TempDB-systemdatabasen används också för att lagra raderna versioner för att stödja de funktioner som kräver att spåra de ändringar som utförs på tabellraderna, såsom snapshot isolation nivå transaktioner, online Index ombyggnader eller flera aktiva resultatuppsättningar funktionen.

TempDB-databasen kommer att släppas och återskapas igen varje gång SQL Server-tjänsten startas om, med början med en ny ren kopia av databasen. Baserat på det faktum kommer alla användare och interna databaser som är invända som lagras i den här databasen att släppas automatiskt när SQL Server-tjänsten startas om eller när sessionen där dessa objekt skapas kopplas bort. Därför är säkerhetskopiering och återställning inte tillgängliga för TempDB.

varför TempDB

TempDB systemdatabasen spelar en viktig roll i SQL Server performance tuning process. Eftersom det används som cachelagring för att lagra olika typer av användardatabasobjekt och för att lagra systemets interna objekt för att påskynda SQL Server-Databasmotorrelaterade processer.

på grund av denna viktiga roll som TempDB systemdatabasen spelar för att förbättra SQL Server instans övergripande prestanda, är det mycket viktigt att tillämpa den ursprungliga storleken, automatisk tillväxt och plats bästa praxis på TempDB databasdata och loggfiler. Tillämpa dessa bästa praxis på ett sätt som passar din SQL Server instans arbetsbelastning kommer att förhindra att expandera TempDB databasdata och loggfiler mycket ofta, med hänsyn till att filen expanderar processen är en dyr process där SQL Server Engine kommer att begära extra utrymme från operativsystemet, som kommer att utföra nollställning på det utrymmet innan allokera den till SQL Server Engine. Du kan föreställa dig den tid och resurser som krävs för denna tilldelningsprocess.

om du redan tillämpar alla bästa metoder och en oväntad tillväxtoperation inträffade kan du påskynda allokeringsprocessen genom att använda alternativet database instant file initialization, där operativsystemet litar på SQL Server-tjänsten och allokerar önskat utrymme utan att utföra tid och resurser som slösar bort operationen på det tilldelade utrymmet.

bästa praxis

det finns ett antal TempDB-systemdatabaskonfigurations bästa metoder som rekommenderas för att få bästa prestanda från den delade resursen och skydda din SQL Server-instans från oväntad tillväxt i TempDB-databasdata eller loggfiler som kan fylla det underliggande diskutrymmet.

dessa konfigurationer inkluderar:

• antalet TempDB-datafiler ska matcha antalet logiska processorer, upp till åtta filer, på den maskin där SQL Server-instansen är installerad. Om antalet logiska processorer på den maskinen är större än åtta, Ställ in antalet datafiler till åtta, med möjlighet att förlänga det med flera av 4 Om du har ett TempDB-problem
• se till att TempDB-filerna har samma storlek inom samma filgrupp för att ge högsta möjliga grad av parallella operationer effektivitet, på grund av den proportionella Fyllningsskrivningsmekanismen som används för att skriva data i datafilerna. För mer information, Kontrollera förstå SQL Server proportionell fyllningsalgoritm
• konfigurera TempDB databasdata och loggfiler med en rimlig initial storlek och auto-tillväxt belopp som passar den förväntade mängden data som kommer att skrivas på dessa filer, och de olika typer av arbetsbelastningar som utförs i SQL Server-instans, inklusive användaraktiviteter och index underhållsoperationer, för att undvika de frekventa små steg belopp som påverkar den totala instans prestanda
• leta upp TempDB filer på en snabb I/O-delsystem för att undvika I / O-flaskhalsar
• leta reda på TempDB på en separat hårddisk, bort från användardatabaser och andra systemdatabaser. På detta sätt kommer de tunga läs-och skrivoperationerna som utförs på TempDB inte att påverka eller påverkas av användardatabasernas arbetsbelastning, vilket förbättrar den totala SQL Server-instansens prestanda

för mer information om de nya förbättringarna i TempDB-prestanda, kontrollera TempDB-databasdokumentet.

var du ska tillämpa

före SQL Server 2016-versionen kan TempDB-storlekstilldelningen utföras efter installation av SQL Server-instansen, från sidan Databasegenskaper. Där du kan styra antalet databasdatafiler som passar den aktuella SQL Server-instansen, den ursprungliga storleken för varje databasdata och loggfil, rätt värde för alternativet Autogrowth och platsen för databasdata och loggfiler.

för att utföra dessa ändringar, expandera Databasnoden i SQL Server Management Studio, expandera systemdatabasnoden och högerklicka sedan på tempdb-databasen och välj alternativet Egenskaper enligt nedan:

från fönstret Öppna Databasegenskaper, Bläddra på sidan filer och kontrollera eller ändra de olika databasdata och loggfiler inställningar som visas nedan:

från och med SQL Server 2016-versionen kan alla dessa ändringar, med de bästa metoderna som passar de nuvarande serverresurserna som redan föreslagits av installationsguiden, kontrolleras och utföras under installationsprocessen för SQL Server-instansen, med hjälp av installationsguiden för SQL Server, under Database Engine Configurations / TempDB, som visas nedan:

spåra TempDB tillväxt

spåra tillväxten av TemDB databasfiler och de frågor som konsumerar TempDB resurser kan utföras med hjälp av prestanda Monitor räknare associerade med TempDB filer och SQL Profiler verktyg som lyssnar på en specifik databas arbetsbelastning typ.

ett mer databasadministratörsvänligt sätt att spåra tillväxten av TempDB-databasfilerna frågar följande dynamiska Hanteringsvyer:

• sys.dm_db_file_space_usage som returnerar utrymmesanvändningsinformationen för varje fil i databasen, utan att visa vilken session eller uppgift som förbrukat det utrymmet
• sys.dm_db_session_space_usage som returnerar antalet tilldelade och avallokerade sidor per varje session
• sys.dm_db_task_space_usage som returnerar antalet tilldelade och avallokerade sidor per varje uppgift

Observera att resultatet som returneras av Sys.dm_db_session_space_usage DMV sammanfattade resultatet som returneras från sys.dm_db_task_space_usage DMV när alla uppgifter har slutförts, på grund av att flera uppgifter relaterade till samma fråga kommer att utföras under samma session. En annan fördel med dessa DMV: er är att den visar vilken typ av objekt som förbrukar TempDB-resurserna och om det här objektet är ett användarobjekt, till exempel en tillfällig eller variabel tabell eller ett internt objekt, till exempel indexunderhåll eller sorteringsoperation. Så baserat på antalet sidor som konsumeras av varje objekt kan du ange om TempDB konsumeras av ett internt eller användarobjekt och optimera den konsumenten.

sys.dm_db_file_space_usage

sys.dm_db_file_space_usage DMV kan användas för att övervaka diskutrymme som används i TempDB databasfiler. Frågorna nedan visar det odelade utrymmet, utrymmet som används för att lagra rader versioner, det utrymme som förbrukas av de interna objekten och slutligen det utrymme som förbrukas av användarobjekten, enligt följande:

från resultatet, som returneras från min lokala SQL Server-instans, kan du se att det finns 60 MB oanvänd i TempDB-filerna, med endast 0.5 MB som används för interna objekt och 1.5 MB som används för användarobjekten, som visas nedan:

sys.dm_db_task_space_usage och sys.dm_db_session_space_usage

sys.dm_db_task_space_usage och sys.dm_db_session_space DMV används för att kontrollera antalet sidor som tilldelats och avallokeras av varje uppgift eller session i tempdb-databasen. På detta sätt kommer du att kunna se vilken användare eller ett internt objekt som konsumerar TempDB-utrymmet.

till exempel returneras resultatet nedan från att fråga sys.dm_db_task_space_usage DMV under TempDB databas, där det kommer att returnera antalet sidor som tilldelats och deallokeras för varje användare eller intern uppgift, enligt följande:

men DBA kräver inte så många detaljer för att börja spåra frågan som förbrukar TempDB-resurserna. Så vi måste sammanfatta föregående resultat för varje session med hjälp av frågan nedan:

och det sammanfattade sidans konsumtionsresultat per varje session kommer att vara som nedan:

samma resultat kan returneras genom att fråga sys.dm_db_session_space_usage DMW, som sammanfattar sidans tilldelning och avallokering för alla uppgifter som körs under samma session, som visas nedan:

Låt oss utföra några åtgärder på TempDB-databasen med både interna och användarobjekt enligt nedan:

kör sedan den konsoliderade frågan nedan, som returnerar antalet tilldelade och avallokerade sidor av interna och användarobjekt och visar båda de slutförda operationerna (med sys.dm_db_session_space_usage DMV) och de oavslutade operationerna (med sys. dm_db_task_space_usage DMV):

och det tidigare operationsresultatet kommer att återspegla frågeutmatningen, vilket visar antalet sidor som tilldelats och deallokeras genom att skapa användarens tillfälliga tabell och sortera data under indexåterbyggnadsprocessen, som visas nedan:

för att få texten till frågan som förbrukar TempDB-resurserna kan du använda dina SQL-utvecklingsfärdigheter genom att gå med i resultatet av YS.dm_db_task_space_usage DMV med andra DMV: er som sys.dm_exec_requests och sys.dm_exec_sessions med sys.dm_exec_sql_text dynamisk hanteringsfunktion, där den visar de frågor som konsumerar tempdb-utrymmet nu, som i den här frågan.

resultatet kommer att likna det nedan:

• författare
• Senaste inlägg

Ahmad Yaseen är en Microsoft Big Data engineer med djup kunskap och erfarenhet inom SQL BI, SQL Server databasadministration och utvecklingsfält.
han är Microsoft Certified Solution Expert inom datahantering och analys, Microsoft Certified Solution Associate i SQL Database Administration och utveckling, Azure Developer Associate och Microsoft Certified Trainer.
han bidrar också med sina SQL-tips i många bloggar.
Visa alla inlägg av Ahmad Yaseen

Senaste inlägg av Ahmad Yaseen (se alla)

• frågor och svar från Azure Data Factory-11 februari 2021
• så här övervakar du Azure Data Factory-15 januari 2021
• använda källkontroll i Azure Data Factory-Januari 12, 2021