SQL Server TempDB Usage, Performance, and Tuning Tips

What is TempDB responsible for in Microsoft SQL Server?

  • 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.

(Scripts to evaluate TempDB usage: http://www.mssqltips.com/sqlservertip/1388/properly-sizing-the-sql-server-tempdb-database/)

What are some Performance Improvements for TempDB?

  • If your SQL Server’s tempdb database is heavily used by your application(s), consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server’s overall performance.
  • If you need to move the tempdb database after SQL Server is first installed, run this script to move it to a more appropriate location:

USE master

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘E:tempdb.mdf’)

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘E:templog.ldf’)

  • Where NAME refers to the logical name of the tempdb database and log files, and where FILENAME refers to the new location of the tempdb files. Once this command has run, you must restart the mssqlserver service before it takes affect.
  • If your application uses the tempdb database a lot, and causes it to grow larger than its default size, you may want to permanently increase the default size of the tempdb file to a size closer to what is “typically” used by your application on a day-to-day basis.
  • Every time SQL Server is restarted, the old tempdb database is deleted and a new one is created. If the tempdb is set to a size smaller than what is typically used by the tempdb, and it is set to auto grow, then the tempdb has to grow to reach its “typical” size, which incurs some overhead.
  • By having the tempdb file set to the “typical” size when SQL Server is restarted (and when it is recreated from scratch to the size you set), you don’t have to worry about the overhead of the tempdb growing during production.
  • With standard user databases, it’s recommended that you set the number of physical files for each database at .25 to one physical file per CPU core. With the tempdb database, you should have one physical file per CPU core in the server. So, if you have a dual-chip, dual-core server, you should have four physical database files for the tempdb database.
  • By having the larger number of files, you can increase the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. With standard databases, SQL Server can cache a large amount of the data that it needs into memory. Because of the high-write nature of the tempdb, the data needs to be written to the disk before it can be cached back up into memory.
  • When adding more database files, it’s important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across the files as evenly as possible. If the database files end up being different sizes, it will attempt to fill the files with the most free space first.

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.

Check out the tip Using the Microsoft SQL Server 2012 Best Practice Analyzer to further tune your SQL Server.

For more Microsoft Technical Training information visit www.directionstraining.com or call 1-855-575-8900.