SQLShack

I denne artikkelen vil vi diskutere de beste praksisene som bør følges for å holde TempDB-databasen i en sunn tilstand og forhindre uventet vekst i databasen, i tillegg til prosedyren som kan følges for å oppdage denne uventede veksten en gang oppstod.

TempDB-oversikt

TempDB-databasen er en av DE viktigste SQL Server-systemdatabasene, som brukes til å lagre midlertidige brukerobjekter, for eksempel midlertidige tabeller som er definert av brukeren eller returnert fra utføring av tabellverdier, midlertidige lagrede prosedyrer, tabellvariabler eller indekser.

I Tillegg Til brukerobjektene brukes TempDB til å lagre interne objekter som opprettes av SQL Server-Databasemotoren under de ulike interne operasjonene, for eksempel mellomliggende sortering, spoling, aggregering eller markøroperasjoner.

TempDB-systemdatabasen brukes også til å lagre radversjonene for å støtte funksjonene som krever sporing av endringene som utføres på tabellrader, for eksempel stillbilde-isolasjonsnivået for transaksjonene, nettindeksgjenoppbygginger eller Funksjonen Flere Aktive Resultatsett.

TempDB-databasen slettes og gjenopprettes på nytt hver GANG SQL Server-tjenesten startes på nytt, og starter med en ny ren kopi av databasen. Basert på dette faktum, vil alle brukeren og intern database protestert som er lagret på denne databasen slippes automatisk NÅR SQL Server-tjenesten startes på nytt, eller når økten der disse objektene opprettes kobles fra. Derfor er sikkerhetskopiering og gjenoppretting operasjoner ikke tilgjengelig For TempDB.

Hvorfor TempDB

TempDB systemdatabasen spiller en viktig rolle I SQL Server ytelse tuning prosessen. Fordi den brukes som caching lagring for å lagre ulike typer brukerdatabaseobjekter og lagre interne systemobjekter for å øke HASTIGHETEN PÅ SQL Server Database Engine relaterte prosesser.

På grunn Av Denne viktige rollen Som TempDB-systemdatabasen spiller i å forbedre SQL Server-forekomsten generelle ytelsen, er det svært viktig å bruke den opprinnelige størrelsen, automatisk vekst og plassering beste praksis På TempDB-databasedata og loggfiler. Bruk av disse beste fremgangsmåtene på en måte som passer DIN SQL Server-forekomst arbeidsmengden vil hindre utvide TempDB database data og loggfiler svært ofte, tatt i betraktning at filen utvider prosessen er en kostbar prosess DER SQL Server Engine vil be om ekstra plass fra operativsystemet, som vil utføre nullstilling på den plassen før tildeling TIL SQL Server Engine. Du kan forestille deg tid og ressurser som kreves for denne tildelingsprosessen.

hvis du allerede bruker alle anbefalte fremgangsmåter og det oppstod en uventet vekstoperasjon, kan du øke hastigheten på tildelingsprosessen ved hjelp av alternativet database instant file initialization, der operativsystemet vil klarere SQL Server-tjenesten og tildele nødvendig plass uten å utføre tid og ressurser sløse operasjonen på den tildelte plassen.

Anbefalte fremgangsmåter

Det finnes En rekke Anbefalte fremgangsmåter For TempDB-systemdatabase-konfigurasjon som anbefales for å få best mulig ytelse fra den delte ressursen og beskytte SQL Server-forekomsten fra uventet vekst I TempDB-databasedataene eller loggfilene som kan fylle den underliggende diskplassen.

disse konfigurasjonene inkluderer:

• Antallet TempDB-datafiler skal samsvare med antall logiske prosessorer, opptil åtte filer, på maskinen DER SQL Server-forekomsten er installert. Hvis antall logiske prosessorer på den maskinen er større enn åtte, angi antall datafiler til åtte, med muligheten til å utvide den med flere av 4 i tilfelle Du har En TempDB påstand problemet
• Kontroller At TempDB filene har lik størrelse i samme filgruppe for å gi høyest mulig grad av parallelle operasjoner effektivitet, På Grunn Av Proporsjonal Fyll skriftlig mekanisme som brukes til å skrive dataene i datafilene. Hvis Du vil ha mer informasjon, kan Du se Forstå Proporsjonal fylling FOR SQL Server-algoritmen
• Konfigurere TempDB-databasedataene og loggfilene med en rimelig opprinnelig størrelse og automatisk vekstbeløp som passer til forventet datamengde som skal skrives på disse filene, og de ulike typer arbeidsbelastninger som utføres i SQL Server-forekomsten, inkludert brukeraktiviteter og indeksvedlikehold, for å unngå hyppige små økningsbeløp som påvirker den generelle ytelsen for forekomsten
• Finn TempDB-filene på et raskt i / O-delsystem. I / O-flaskehalser
• Finn TempDB på en egen harddisk, borte fra brukerdatabaser og andre systemdatabaser. På denne måten vil de tunge lese-og skriveoperasjonene som utføres På TempDB, ikke påvirke eller påvirkes av arbeidsbelastningen til brukerdatabasene, noe som forbedrer YTELSEN for SQL Server-forekomsten

hvis du vil ha mer informasjon om de nye forbedringene I tempdb-ytelsen, kan Du sjekke TempDB-Databasedokumentet.

hvor du skal bruke

før SQL Server-2016-versjonen, kan tempdb størrelse tildeling utføres etter installasjon AV SQL Server-forekomsten, Fra Siden Databaseegenskaper. Der du kan kontrollere antall databasedatafiler som passer til GJELDENDE SQL Server-forekomst, den opprinnelige størrelsen for hver databasedata og loggfil, riktig verdi For Alternativet Autogrowth og plasseringen av databasedata og loggfiler.

for å utføre disse endringene, utvid Databasenoden I SQL Server Management Studio, utvid systemdatabasenoden, høyreklikk på tempdb-databasen og velg Alternativet Egenskaper som nedenfor:

bla Gjennom Filer-siden I Vinduet Åpne Databaseegenskaper, og kontroller eller endre innstillingene for ulike databasedata og loggfiler som vises nedenfor:

alle disse endringene, med beste praksis som passer til gjeldende serverressurser som allerede er foreslått av installasjonsveiviseren, kan kontrolleres og utføres under INSTALLASJONSPROSESSEN FOR SQL Server-forekomst, ved HJELP AV Installasjonsveiviseren FOR SQL Server, Under Databasemotorkonfigurasjoner | TempDB, som vist nedenfor:

Spor TempDB vekst

Spore veksten Av TemDB databasefiler og spørringer som bruker tempdb ressurser kan utføres ved Hjelp Av Ytelsesmåler tellere knyttet Til TempDB-filer OG SQL Profiler-verktøyet som lytter til en bestemt database arbeidsbelastningstype.

en mer databaseadministrator vennlig måte å spore veksten Av TempDB databasefiler er spørring Følgende Dynamiske Visninger:

• sys.dm_db_file_space_usage som returnerer informasjon om plassbruk for hver fil i databasen, uten å vise hvilken økt eller oppgave som brukte plassen
• sys.dm_db_session_space_usage som returnerer antall tildelte og deallokerte sider per hver økt
• sys.dm_db_task_space_usage som returnerer antall tildelte og deallokerte sider per hver oppgave

merk at resultatet returnert av sys.dm_db_session_space_usage dmv oppsummerte resultatet returnert fra sys.dm_db_task_space_usage DMV NÅR alle oppgaver er fullført, på grunn av det faktum at flere oppgaver knyttet til samme spørring vil bli utført under samme økt. En annen fordel med Disse Dmv-Ene er at den vil vise typen objekt som bruker TempDB-ressursene, og hvis dette objektet er et brukerobjekt, for eksempel en midlertidig eller variabel tabell eller et internt objekt, for eksempel indeksvedlikehold eller sorteringsoperasjon. Så, basert på antall sider som forbrukes av hvert objekt, kan du angi Om TempDB forbrukes av et internt eller brukerobjekt og optimalisere forbrukeren.

sys.dm_db_file_space_usage

sys. dm_db_file_space_usage DMV kan brukes til å overvåke diskplass som brukes I TempDB databasefiler. Spørringene nedenfor viser ledig plass, plassen som brukes til å lagre rader versjoner, plassen som forbrukes av de interne objektene og til slutt plassen som forbrukes av brukerobjektene, som følger:

fra resultatet, som returneres fra min lokale SQL Server-forekomst, kan du se at det er 60 MB ubrukt I TempDB-filene, med bare 0.5 MB brukt til interne objekter og 1.5 MB brukt til brukerobjektene, som vist nedenfor:

sys.dm_db_task_space_usage Og sys.dm_db_session_space_usage og sys.dm_db_session_space DMVs brukes til å kontrollere antall sider tildelt Og deallokert av hver oppgave eller økt i tempdb-databasen. På denne måten vil du kunne se hvilken bruker eller et internt objekt som bruker TempDB-rommet.

for eksempel returneres resultatet nedenfor fra spørring av sys.dm_db_task_space_usage DMV under TempDB database, hvor det vil returnere antall sider tildelt og deallokert for hver bruker eller intern oppgave, som følger:

MEN DBA krever ikke at mange detaljer for å begynne å spore spørringen som bruker tempdb-ressursene. Så, vi må oppsummere det forrige resultatet for hver økt, ved hjelp av spørringen nedenfor:

og de oppsummerte sidene forbruk resultat per hver økt vil være som nedenfor:

det samme resultatet kan returneres ved å spørre sys.dm_db_session_space_usage DMW, som oppsummerer sidetildeling og deallokering for alle oppgaver som kjører under samme økt, som vist nedenfor:

la oss utføre noen handlinger På TempDB database ved hjelp av både interne og brukerobjekter som nedenfor:

utfør deretter den konsoliderte spørringen nedenfor, som returnerer antall tildelte og deallokerte sider av interne og brukerobjekter, og vis begge de fullførte operasjonene (ved hjelp av sys.dm_db_session_space_usage DMV) og uferdige operasjoner (ved hjelp av sys. dm_db_task_space_usage DMV):

og resultatet av tidligere operasjoner vil gjenspeile spørringsutgangen, som viser antall sider som er tildelt og deallokert ved å opprette brukerens midlertidige tabell og sortere dataene under indeksoppbyggingsprosessen, som vist nedenfor:

for å få teksten i spørringen som bruker tempdb ressurser, kan du bruke SQL utvikling ferdigheter ved å bli resultatet av ys.dm_db_task_space_usage DMV med Andre DMVs som sys.dm_exec_requests og sys.dm_exec_sessions med sys.dm_exec_sql_text dynamic management funksjon, der det vil vise spørringene som bruker tempdb plass nå, som i denne spørringen.

resultatet vil være lik den nedenfor:

• Forfatter
• Siste Innlegg

Ahmad Yaseen Er En Microsoft Big Data ingeniør med dyp kunnskap og erfaring I SQL BI, SQL Server Database Administrasjon og Utvikling felt.
Han er Microsoft Certified Solution Expert innen Dataadministrasjon og Analyse, Microsoft Certified Solution Associate innen ADMINISTRASJON OG UTVIKLING AV SQL Database, Azure Developer Associate og Microsoft Certified Trainer.
han bidrar Også med SINE SQL-tips i mange blogger.
Vis Alle innlegg Av Ahmad Yaseen

Siste innlegg Av Ahmad Yaseen (se alle)

• Azure Data Factory Intervjuspørsmål og Svar-11. februar 2021
• slik overvåker Du Azure Data Factory – 15. januar 2021
• Ved Hjelp Av Kildekontroll I Azure Data Factory – januar 12, 2021