SQL Server TempDB Tutorial

door: Eric Blinn / bijgewerkt: 2020-07-17 | Comments (2) / verwant: meer > systeemdatabases

probleem

SQL Server wordt standaard geïnstalleerd met vier systeemdatabases. Ze zijn master, model, msdb en TempDB. Ik moet weten wat de systeemdatabase “TempDB”is en waarvoor het wordt gebruikt. Deze vorige tutorials covermaster, model andmsdb in groot detail.

oplossing

deze tip dekt de TempDB-database. TempDB is een database die veel functies heeft binnen SQL Server, maar het wordt zelden expliciet genoemd. Het heeft zo veel functies dat het vaak een van de drukste, zo niet de drukste database op de meeste SQL Server instanties. Lees verder om te leren watveel van deze functies zijn!

SQL Server TempDB overzicht

Wat is het doel van SQL Server TempDB?

een van de functies van TempDB is om iets te doen zoals een pagina of swap bestand zou doen op het niveau van het besturingssysteem. Als een SQL Server operatie te groot is om te worden voltooid in het geheugen of als de initiële geheugen subsidie voor een query te klein is, kan de operatie worden verplaatst naar schijf in TempDB.

een andere functie van TempDB is het opslaan van tijdelijke tabellen. Iedereen die een tijdelijke tabel in T-SQL heeft aangemaakt met behulp van een pound of hash prefix (#) of het doublepound/hash prefix (##) heeft een object in TempDB aangemaakt omdat dit is waar deze zijn gestored.

CREATE TABLE #MSSQLTips (RowID int);

wanneer atrigger is het uitvoeren van de ingevoegde en verwijderde virtuele tabellen worden storredin TempDB.

alle databases die USEREAD commit SNAPSHOT ISOLATION (RCSI) hebben hun volledige versiegegevens opgeslagen in TempDB.

elk van de bovenstaande functies van TempDB wordt voltooid zonder dat ooit tempdb bij naam wordt genoemd en kan dus verrassende toepassingen voor TempDB zijn.

TempDB kan ook expliciet worden genoemd op een paar manieren. Tabellen kunnen worden gegenereerd in TempDB door te verwijzen naar de database in een create statement. De code ziet eruit als een normale DDL-operatie, maar wanneer deze in TempDB wordt uitgevoerd, is de tabel per definitie een tijdelijke tabel.

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

TempDB kan zijn bij elke start van de SQL Server instantie. Elk object dat tijdens een vorige sessie in TempDB is aangemaakt, blijft niet aanwezig bij een herstart van de service. TempDB krijgt zijn eerste object lijst van het model database die over het algemeen gaat leeg of bijna leeg zijn.

Index onderhoud kan optioneel worden gevraagd om sorteert in TempDB in plaats van te proberen dit te doen met behulp van vrije ruimte in de gebruikersdatabase.

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

kan ik SQL Server draaien zonder een tempdb database?

SQL Server kan niet draaien (behalve in zeer extreme herstelscenario ‘ s en voor zeer korte perioden) zonder een TempDB.

is de versie van de tempdb-database uniek voor de versie, editie en patchlevel van SQL Server?

TempDB is een grotendeels lege database en er is niets bijzonder uniek aan elke editie of patch niveau van SQL Server.

er zijn de laatste jaren slechts 2 betekenisvolle wijzigingen in TempDB op versieniveau geweest. De eerste is dat vanaf SQL Server 2016 het gedrag van TempDBwas zodanig veranderd dat het inschakelen van trace vlaggen T1117 en T1118 niet langer worden beschouwd als een best practice. De tweede is dat vanaf SQL Server 2019 sommige zwaargebruikte systeemobjecten in TempDB werden opgewaardeerd naar in-memory tabellen om de inhoud te verminderen en de algehele prestaties van de server te verbeteren.

kunt u de tempdb-database laten vallen of hernoemen en MOET u dit om welke reden dan ook doen?

TempDB kan niet worden verwijderd, losgemaakt, offline gehaald of hernoemd. Poging tot een van deze operaties zal een fout terug te keren. Er is geen reden dat dit ooit zou moeten worden geprobeerd, aangezien dit een kritische systeemdatabase is.

SQL Server TempDB Database Location

welke fysieke bestanden en namen ondersteunen de TempDB database?

de standaard logische bestandsnamen zijn tempdev voor data en templog voor log.Ze zijn te vinden op schijf als tempdb.mdf en templog.ldf respectievelijk. TempDBcommonly heeft veel gegevensbestanden.

hoeveel TempDB-gegevensbestanden moeten er zijn?

er moet één TempDB-gegevensbestand zijn voor elke thread/core/vCPU op het moment met een maximum van 8.

Waar vind ik de tempdb-database op schijf en in SSM ‘ s?

de bestanden kunnen worden gevonden door het opvragen van sys.sysfiles dmv of het bestand paneel op het databaseproperties venster.

SELECT * FROM TempDB.sys.sysfiles;

maakt het verschil waar de tempdb databasebestanden zich bevinden (moeten ze op C:\, SAN, etc.)?

Best practices dicteren dat uw TempDB – gegevensbestanden op de snelste opslag beschikbaar moeten zijn vanwege hun activiteitenniveaus. Voor sommige gevallen kan dit betekenen dat ze worden opgeslagen met de gegevensbestanden van de gebruikersdatabase, maar een apart volume op een aparte schijf wordt aanbevolen, indien beschikbaar.

er zijn verschillende meningen over het tempdb logbestand, maar deze auteur geeft de voorkeur dit bestand samen te voegen met de andere transactielogbestanden.

kan de tempdb-database worden verplaatst?

de TempDB-database kan worden verplaatst. De methode voor het verplaatsen van TempDB is eenvoudig te implementeren. Wijzig eenvoudig de eigenschap bestandsnaam van het gewenste bestand (en). geen bestanden hoeven verplaatst te worden zoals wanneer andere systeemdatabases worden verplaatst. SQLServer zal het bestand(en) aanmaken met de nieuwe naam en/of op de nieuwe locatie bij de volgende service start. Vergeet niet om terug te gaan en verwijder de nowunused TempDB bestanden die zullen worden achtergelaten.

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 Database grootte en groei

hoe moeten de database en transactielogboek groei instellingen worden geconfigureerd voor de TempDB database?

Autogrowth instellingen moeten worden ingesteld op specifieke nummers van MB, niet een percentage.Elk gegevensbestand moet hetzelfde aantal MBS instellen voor groei.

de juiste grootte van de bestanden voor een database is over het algemeen een best practice, maar nooit meer dan voor TempDB. Controleer de instantie-vooral als het nieuw is-en bepaal of de bestanden groeien tijdens normaal gebruik.Als dat zo is, verander dan de startgrootte zodanig dat SQL Server niet gedwongen wordt om de bestanden consistent te laten groeien na elke service start.

Wat is de typische grootte van de tempdb-database en hoe groot kan het worden?

er is geen goed antwoord op deze vraag. Wat typerend is voor de ene instantie is misschien niet typerend voor de andere. Deze auteur heeft gevallen gezien waar de TempDB is basicallyunused gemiddeld slechts een paar MB van de gebruikte ruimte op een bepaald moment. Toch worden anderen voortdurend ondervraagd en gebruiken ze honderden GB ruimte-zelfs in ruimte gemeten in TB-vooral tijdens een venster voor indexonderhoud.

bij het bepalen hoeveel ruimte er moet worden besteed aan TempDB op een nieuwe instantie zijn er weinig dingen die gedaan kunnen worden. Vervangt de nieuwe instantie een oudere, bestaande instantie? Als dat zo is, gebruik dan een perfmon trace of geplande DMV capture om de gebruikte ruimte in TempDB in de loop van enkele dagen te bepalen. Zorg ervoor dat u een onderhoudsvenster in die opname opneemt. Probeer voldoende ruimte toe te wijzen op de nieuwe instantie om rekening te houden met de grootste gebruikte ruimte die is vastgelegd tijdens de trace met extra wiggle room. Dan de juiste grootte van de bestanden te passen in die ruimte toewijzing.

de 2 onderstaande vragen kunnen helpen. De eerste zal een momentopname maken van de grootte van de gegevens en logbestanden, samen met de ruimte die in de bestanden wordt gebruikt. Dit kan worden uitgevoerd op een regelmatige basis om het gebruik in de tijd te volgen. De tweede query zal het aantal keren dat het logbestand is gegroeid sinds de laatste keer dat de instancewas herstart draaien.

als er geen bestaande werklast is die als leidraad kan worden gebruikt en er geen andere leidraad is, misschien van een softwareleverancier, dan is het enige dat kan worden gedaan om er zeker van te zijn dat er ruimte is in TempDB om rekening te houden met de grootste geanticipeerde individuele objecten die in TempDB gesorteerd kunnen worden tijdens een indexonderhoudvensters. Vanaf daar, monitor het gebruik en dienovereenkomstig aan te passen.

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 Database Maintenance

kunt u de database en het transactielogboek voor de TempDB database verkleinen?

technisch gezien wel, maar dit is geen operatie die ooit zou moeten worden voltooid.Het doel moet altijd zijn om de juiste maat TempDB. Als een soort van eenmalige operatie zorgt ervoor dat deze bestanden op onverklaarbare wijze groeien, zullen ze terugkeren naar normaal op de nextservice herstart.

moet u indexen opnieuw maken?

TempDB zal geen deel uitmaken van indexgerelateerde onderhoudsplannen. Dit betekent geen heropbouw van indexen, reorganiseren of bijwerken van statistieken.

moet u integriteitscontroles uitvoeren Wat is de code?

er zijn verschillende meningen over de vraag of het PB op integriteit moet worden gecontroleerd.Deze auteur zal de argumentvoor en tegen uiteenzetten en de lezer in staat stellen om te beslissen.

tegen: een integriteitscontrole kan TempDB controleren met dezelfde totaliteit als alle andere databases.De gegevens die bestaan in TempDB is zeer tijdelijk, dus de integriteitscontrole operatie zal niet veel vinden om te controleren op een bepaald moment.Om dezelfde reden zijn alle gegevens die beschadigd raken waarschijnlijk actieve gegevens die een waarschuwing veroorzaken wanneer ze van schijf worden gelezen lang voordat een integriteitscontroleoperatie waarschijnlijk zou worden aangeroepen.

voor: terwijl een integriteitscontroleoperatie niet alles kan controleren in TempDB dat het controleert in andere databases die geen waarde hebben.TempDB is een integrale database enals er corruptie te weten is het noodzakelijk, zodat corrigerende maatregelen kunnen worden genomen.

de code om een integriteitscontrole uit te voeren staat hieronder.

DBCC CHECKDB ('TempDB'); 

SQL Server TempDB Database objecten

Wat zijn de belangrijkste tabellen en procedures in de tempdb database die belangrijk zijn voor SQL Server Professionals om te weten?

die zijn er niet.Deze database wordt gebruikt voor tijdelijke opslag van objects.It komt niet geïnstalleerd met anytables of procedures.

kan ik mijn eigen objecten in de tempdb-database opslaan?

objecten kunnen zeker worden aangemaakt in TempDB, maar ze zullen niet blijven bestaan na de volgende start van de SQL Server service.

SQL Server TempDB Database permissies

Wie heeft toegang tot de TempDB database en zijn er verschillende niveaus van permissies?

iedereen heeft toegang en dezelfde toegang tot de TempDB.

SQL Server TempDB Database Backup

moet ik een backup maken van de tempdb database?

TempDB kan geen back-up maken. Het slaat alleen tijdelijke gegevens op en wordt opnieuw gemaakt elke keer dat de service start, dus er is niets terug te vorderen in het geval van een ramp.

SQL Server TempDB Database Recovery Model

welke recovery TempDB moet de TempDB database hebben en kan het recovery model worden gewijzigd?

TempDB is in eenvoudig herstel en dit kan niet worden gewijzigd.

SQL Server TempDB Database Restore

is het ooit nodig om de TempDB database te herstellen?

TempDB kan niet hersteld worden. Er moeten geen gegevens en geen code in tempdb worden hersteld. Als TempDB corrupt wordt, verwijdert u gewoon de gegevens en logbestanden, zodat Nieuwe kunnen worden gegenereerd bij de volgende service opstarten.

volgende stappen

• deze tip laat zien hoe u waarschuwingen kunt maken voor problemen die zijn gevonden binnen TempDB-groottes en schijfruimte
• deze tip laat zien hoe u instant bestandsinitialisatie kunt inschakelen, een belangrijke functie voor alle DBs, maar vooral TempDB als het niet juist is formaat.

laatst bijgewerkt: 2020-07-17

over de auteur

Eric Blinn is de SR. Data Architect voor schildknaap Patton Boggs. Hij is ook een SQL auteur en PASS lokale groepsleider.
Bekijk al mijn tips

gerelateerde bronnen

• meer SQL Server DBA-Tips…