Configuration
Allgemeine Informationen zur TempDB
Die Datenbank TempDB ist der Dreh- und Angelpunkt eines MS SQL-Servers. Hierbei handelt es sich um eine globale Ressource, die jedem Benutzer zur Verfügung steht.
In der TempDB Datenbank sind folgende Elemente enthalten:
- Temporäre vom Benutzer erzeugte Objekte (z. B. globale oder lokale temporäre Tabellen, Tabellenvariablen, temporäre Prozeduren usw.)
- Interne Objekte der Database Engine (Datenbankmodul) (z. B. Arbeitstabellen zum Speichern von Zwischenergebnissen für Spool- und Sortiervorgänge)
Bei einem Neustart des SQL-Servers wird die TempDB neu erstellt. Operationen innerhalb der TempDB werden nur minimal protokolliert, somit kann ein ROLLBACK für Transaktionen durchgeführt werden.
Diese Datenbank kann nicht Gesichert und daher auch nicht durch ein Backup wieder hergestellt werden.
Konfiguration
Die Datenbank besteht wie jede andere Datenbank aus 2 Dateien (*.MDF/*.NDF und *.LDF).
Es wird empfohlen, je CORE eine MDF-Datei zu erstellen. Diese Regelung gilt allerdings nur bis zu 7 CORES.
- 1 bis 7 CORES je CORE eine MDF / NDF-Datei
- 8 bis 32 => Anzahl CORES / 2 = Anzahl MDF / NDF-Dateien
- mehr als 32 CORES => Anzahl CORES / 4 = Anzahl MDF / NDF-Dateien
Die Dateien werden alle mit der gleichen Größe Konfiguriert.
WICHTIG: Betreibt man die TempDB mit mehreren Datenfiles muss der SQL-Server mit dem Trace Flag 1117 gestartet werden. Dieses Trace Flag bewirkt, dass die Datenfiles gleichmäßig befüllt werden.
Bei der automatischen Vergrößerung der Dateien gibt es ebenfalls ein Richtwert.
- Dateigröße von 0 – 100 MB => 10 MB Vergrößerung
- Dateigröße von 100 – 200 MB => 20 MB Vergrößerung
- Dateigröße größer 200 MB => 100 MB Vergrößerung
Es sollte nie eine prozentuale Vergrößerung gewählt werden!
Um noch etwas mehr Leistung aus der TempDB zu ziehen, sollte man die Dateien auf eine eigene schnelle Festplatte legen. Durch diese Maßnahme erhöht sich der I/O Durchsatz. Zusätzlich kann das LOG-File ebenfalls auf eine eigene Festplatte gelegt werden.
Mit diesem Script können Sie die optimale Einstellung der TempDb berechnen:
{code lang:sql lines:false hidden:true}/*--------------------------------------------------------------------------
Teil3:
TempDB in Größe anpassen und bis zu 8 zusätzliche Files.
---------------------------------------------------------------------------*/
-- Groeße der vorhandenen TempDB Files ändern
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 256MB , FILEGROWTH = 256MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 128MB , FILEGROWTH = 128MB )
GO
-- Variables
DECLARE @BITS Bigint -- Affinty Mask
,@NUMPROCS Smallint -- Number of cores addressed by instance
,@tempdb_files_count Int -- Number of exisiting datafiles
,@tempdbdev_location Nvarchar(4000) -- Location of TEMPDB primary datafile
,@X Int -- Counter
,@SQL Nvarchar(max)
,@new_tempdbdev_size_MB Int -- Size of the new files,in Megabytes
,@new_tempdbdev_Growth_MB Int -- New files growth rate,in Megabytes
,@new_files_Location Nvarchar(4000) -- New files path
-- Initialize variables
Select @X = 1, @BITS = 1
SELECT
@new_tempdbdev_size_MB = 256 -- Four Gbytes , it's easy to increase that after file creation but harder to shrink.
,@new_tempdbdev_Growth_MB = 256 -- 512 Mbytes , can be easily shrunk
,@new_files_Location = NULL -- NULL means create in same location as primary file.
IF OBJECT_ID('tempdb..#SVer') IS NOT NULL
BEGIN
DROP TABLE #SVer
END
CREATE TABLE #SVer(ID INT, Name sysname, Internal_Value INT, Value NVARCHAR(512))
INSERT #SVer EXEC master.dbo.xp_msver processorCount
-- Get total number of Cores detected by the Operating system
SELECT @NUMPROCS= Internal_Value FROM #SVer
--Print '-- TOTAL numbers of CPU cores on server :' + cast(@NUMPROCS as varchar(5))
SET @NUMPROCS = 0
-- Get number of Cores addressed by instance.
WHILE @X <= (SELECT Internal_Value FROM #SVer ) AND @X <=32
BEGIN
SELECT @NUMPROCS =
CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
FROM sys.configurations
WHERE NAME = 'AFFINITY MASK'
SET @BITS = (@BITS * 2)
SET @X = @X + 1
END
IF (SELECT Internal_Value FROM #SVer) > 32
Begin
WHILE @X <= (SELECT Internal_Value FROM #SVer )
BEGIN
SELECT @NUMPROCS =
CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
FROM sys.configurations
WHERE NAME = 'AFFINITY64 MASK'
SET @BITS = (@BITS * 2)
SET @X = @X + 1
END
END
If @NUMPROCS = 0 SELECT @NUMPROCS= Internal_Value FROM #SVer
--Print '-- Number of CPU cores Configured for usage by instance :' + cast(@NUMPROCS as varchar(5))
-------------------------------------------------------------------------------------
-- Here you define how many files should exist per core ; Feel free to change
-------------------------------------------------------------------------------------
-- IF cores < 1 then no change , if between 1 & 8 inclusive then number tempdb equal to cores number
IF @NUMPROCS >1 and @NUMPROCS <=8
SELECT @NUMPROCS = @NUMPROCS
-- IF cores > 8 then files should be 8
If @NUMPROCS >8
SELECT @NUMPROCS = 8
-- Get number of exisiting TEMPDB datafiles and the location of the primary datafile.
SELECT @tempdb_files_count=COUNT(*) ,@tempdbdev_location=(SELECT REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX('\',REVERSE(physical_name)) , LEN(physical_name) )) FROM tempdb.sys.database_files WHERE name = 'tempdev')
FROM tempdb.sys.database_files
WHERE type_desc= 'Rows' AND state_desc= 'Online'
--Print '-- Current Number of Tempdb datafiles :' + cast(@tempdb_files_count as varchar(5))
-- Determine if we already have enough datafiles
If @tempdb_files_count >= @NUMPROCS
Begin
--Print '--****Number of Recommedned datafiles is already there****'
Return
End
Set @new_files_Location= Isnull(@new_files_Location,@tempdbdev_location)
-- Determine if the new location exists or not
Declare @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)
insert into @file_results(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @new_files_Location
if (select file_is_a_directory from @file_results ) = 0
Begin
--print '-- New files Directory Does NOT exist , please specify a correct folder!'
Return
end
-- Determine if we have enough free space on the destination drive
Declare @FreeSpace Table (Drive char(1),MB_Free Bigint)
insert into @FreeSpace exec master..xp_fixeddrives
if (select MB_Free from @FreeSpace where drive = LEFT(@new_files_Location,1) ) < @NUMPROCS * @new_tempdbdev_size_MB
Begin
print '-- WARNING: Not enough free space on ' + Upper(LEFT(@new_files_Location,1)) + ':\ to accomodate the new files. Around '+ cast(@NUMPROCS * @new_tempdbdev_size_MB as varchar(10))+ ' Mbytes are needed; Please add more space or choose a new location!'
end
-- Determine if any of the exisiting datafiles have different size than proposed ones.
If exists
(
SELECT (CONVERT (bigint, size) * 8)/1024 FROM tempdb.sys.database_files
WHERE type_desc= 'Rows'
and (CONVERT (bigint, size) * 8)/1024 <> @new_tempdbdev_size_MB
)
PRINT
'
/*
WARNING: Some Existing datafile(s) do NOT have the same size as new ones.
It''s recommended if ALL datafiles have same size for optimal proportional-fill performance.Use ALTER DATABASE and DBCC SHRINKFILE to resize files
Optimizing tempdb Performance : http://msdn.microsoft.com/en-us/library/ms175527.aspx
'
Print '****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG *****/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'
-- Generate the statements
WHILE @tempdb_files_count < @NUMPROCS
BEGIN
--EXEC
SELECT @SQL = 'ALTER DATABASE [tempdb] ADD FILE (NAME = N''tempdev'+CAST (@tempdb_files_count +1 AS VARCHAR (5))+''',FILENAME = N'''+ @new_files_Location + 'tempdb'+CAST (@tempdb_files_count +1 AS VARCHAR(5)) +'.ndf'',SIZE = '+CAST(@new_tempdbdev_size_MB AS VARCHAR(15)) +'MB,FILEGROWTH = '+CAST(@new_tempdbdev_Growth_MB AS VARCHAR(15)) +'MB )
'
--print @sql
EXECUTE (@SQL)
SET @tempdb_files_count = @tempdb_files_count + 1
END
{/code}
Download: {jd_file file==6}
