Tempdb Configuration Best Practices in SQL Server

Problem

In SQL Server 2005, TempDB has taken on some additional responsibilities.  As such, some of the best practice have changed and so has the necessity to follow these best practices on a more wide scale basis.  In many cases TempDB has been left to default configurations in many of our SQL Server 2000 installations.  Unfortunately, these configurations are not necessarily ideal in many environments.  With some of the shifts in responsibilities in SQL Server 2005 from the user defined databases to TempDB, what steps should be taken to ensure the SQL Server TempDB database is properly configured?

Solution

In an earlier tip, we discussed sizing (Properly Sizing the TempDB Database) the TempDB database properly.  The intention of that tip was to determine the general growth and usage of the database in order to determine the overall storage needs.  In this tip we want to take a broader look at how TempDB can be optimized to improve the overall SQL Server performance.

What is TempDB responsible for in SQL Server 2005?

  • Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
  • Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
  • Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
  • DBCC CHECKDB work tables.
  • Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

  • Do not change collation from the SQL Server instance collation.
  • Do not change the database owner from sa.
  • Do not drop the TempDB database.
  • Do not drop the guest user from the database.
  • Do not change the recovery model from SIMPLE.
  • Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
  • If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
  • Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Where can I find additional information related to TempDB best practices?

Next Steps

  • Based on this information, take a look at your TempDB configurations across your SQL Server environment and determine what changes are needed.
  • As you spec out new machines, be sure to keep TempDB in mind.  If you suspect TempDB should have its own disks, be sure to account for those as you purchase and/or configure your disk drives.
  • If you have a standard SQL Server deployment checklist, be sure that it includes the items from this tip that make sense in your environment.
  • Check out these related tips:
  • If you have some additional tips and tricks related to TempDB, please share your knowledge with the community in the forum below.

Related Posts

One comment

  1. –Script to add the temp data files to temp databases.
    USE [tempdb]
    go

    DECLARE
    @CPU tinyint,
    @loopCount tinyint,
    @fileLoc Varchar(500),
    @DataFileCount tinyint,
    @Name Varchar(100);
    SELECT @CPU = cpu_count FROM sys.dm_os_sys_info;

    SELECT @DataFileCount = COUNT(*) FROM sys.database_files WHERE [type] =0;
    SELECT TOP 1 @Name = [name], @fileLoc = physical_name FROM sys.database_files WHERE [type] =0;

    IF @DataFileCount = @CPU
    BEGIN
    PRINT ‘Tempdb database is having all required datafiles…!! No Action Needed..!!’
    RETURN;
    END;

    /**Set the number of data file based on CPU count.
    by default one data file will be available in the tempdb, so we need to add extra.
    ideal number files is 8 and it depends on the number of CPU cores.
    So if there are more than 8cores then we should have only 8 data files, but since already one data file available then we need add 7.
    if the number of cores are less then are equal to 8 then cpucore minus one number of data files we need to add. **/

    IF @cpu > 8
    BEGIN
    SET @loopCount = 8-@DataFileCount;
    END
    ELSE
    BEGIN
    SET @loopCount = @CPU-@DataFileCount;
    END

    PRINT ‘Available CPU cores: ‘+ convert(varchar(10), @cpu);
    PRINT ‘Number of Data files being added as per CPU core availabe: ‘+ convert(varchar(10), @loopCount);
    PRINT ‘Current tempdb file available in: ‘ +@fileLoc;

    –get the file location

    SET @fileLoc = REPLACE(@fileloc,’.mdf’,”);

    declare
    @i int=1,
    @Filenumber int = @DataFileCount+1,
    @sql NVarchar(1000);
    WHILE (@i <= @loopCount)
    BEGIN
    SET @sql = ‘ALTER DATABASE [tempdb] ADD FILE(NAME = N”’+@Name+CONVERT(VARCHAR(2),@Filenumber)+”’, FILENAME=N”’+@fileLoc+CONVERT(VARCHAR(2),@Filenumber)+’.ndf” , SIZE = 512MB , FILEGROWTH = 512MB,, Maxsize = UNLIMITED);’
    SET @i = @i+1;
    SET @Filenumber =@Filenumber+1
    –PRINT @SQL;
    PRINT ‘Executing ‘+ @sql;
    EXEC sp_executesql @SQL;
    END

    –This script will setup the Equal Size of the fies
    Go

    DECLARE @FileName AS NVARCHAR(256);
    DECLARE @SQLCommand AS NVARCHAR(MAX);

    — Cursor to iterate through the file names of TempDB
    DECLARE TempDBFiles CURSOR FOR
    SELECT name
    FROM tempdb.sys.database_files
    WHERE type_desc = ‘ROWS’; — or use ‘LOG’ for log files

    — Open the cursor
    OPEN TempDBFiles;

    — Fetch the next file name
    FETCH NEXT FROM TempDBFiles INTO @FileName;

    — Iterate through all file names
    WHILE @@FETCH_STATUS = 0
    BEGIN
    — Construct the SQL command
    SET @SQLCommand = ‘ALTER DATABASE tempdb MODIFY FILE (NAME = ”’ + @FileName + ”’, SIZE = 512 , FILEGROWTH = 512, Maxsize = UNLIMITED);’;

    — Execute the SQL command
    PRINT @SQLCOMMAND
    EXEC sp_executesql @SQLCommand;

    — Fetch the next file name
    FETCH NEXT FROM TempDBFiles INTO @FileName;
    END

    — Close and deallocate the cursor
    CLOSE TempDBFiles;
    DEALLOCATE TempDBFiles;

    —–3rd Part of Script will Enable TempDB memory Optimised If its supported

    DECLARE @majorVersion INT;

    SELECT @majorVersion = LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR), CHARINDEX(‘.’, CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR)) – 1);

    IF @majorVersion >= 15 — SQL Server 2019 or Higher version
    BEGIN
    EXEC(‘ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;’);
    PRINT ‘Memory-Optimized TempDB metadata has been enabled.’;
    END
    ELSE
    BEGIN
    PRINT ‘Memory-Optimized TempDB metadata cannot be enabled on this version of SQL Server.’;
    END

Leave a Reply

Your email address will not be published. Required fields are marked *