10 SQL Server TempDB Best Practices

SQL Server tempDB is a system database that is used to store temporary data. The data stored in tempDB can be either user data or system data. TempDB is used by SQL Server to store the following:

– Intermediate results from complex queries – Row versioning data – Work tables for spools – Cursors – User-defined functions – Service Broker objects

Since tempDB is used to store such important data, it is crucial to follow best practices when configuring and managing tempDB. In this article, we will discuss 10 SQL Server tempDB best practices that you should follow.

1. Use multiple data files

When SQL Server creates a TempDB database, it creates a single data file –– by default. This can cause performance problems because of contention as multiple users try to write to the same file.

Creating additional data files helps to spread out the load and reduces contention. It’s a good idea to create as many data files as you have CPU cores. So, if you have a quad-core processor, you should have four data files.

You can create additional data files using the following T-SQL:

USE master;
GO
ALTER DATABASE tempdb
ADD FILE (NAME = ‘tempdev2’,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf’)
TO FILEGROUP tempdev;
GO
This example adds a second data file named tempdb2.ndf to the tempdb database.

2. Set the initial size of each tempdb data file to a reasonable value

If the initial size of a tempdb data file is too small and the server experiences heavy tempdb usage, SQL Server will have to constantly grow the size of the file, which can lead to performance issues. On the other hand, if the initial size is too large, there could be wasted disk space.

The best way to determine the initial size of each tempdb data file is to monitor the server’s tempdb usage over time and set the size accordingly.

3. Monitor and adjust the growth rate for each tempdb data file

If the growth rate is too low, tempdb will run out of space and cause performance issues. If the growth rate is too high, tempdb will waste disk space.

The best way to find the optimal growth rate is to monitor tempdb usage over time and make adjustments as needed.

4. Place tempdb on a fast disk subsystem

When SQL Server needs to perform a sort operation, it writes the data to be sorted into tempdb. If tempdb is on a slow disk subsystem, this can cause performance problems.

To avoid this, make sure tempdb is on a fast disk subsystem. This will help ensure that sort operations are performed quickly and efficiently.

5. Keep tempdb small

When SQL Server starts up, it creates tempDB files equal to the number of logical processors on the server. So, if you have a quad-core processor, tempDB will initially create four files. The problem is that these files are often much too large for most workloads.

A good rule of thumb is to size each tempDB file at 8 MB per gigabyte of RAM. So, if you have 32 GB of RAM, each tempDB file should be 256 MB.

You can change the initial size of tempDB files by setting the “initial size” option when creating the database. For example, the following T-SQL code would create four tempDB files, each with an initial size of 128 MB:

CREATE DATABASE tempdb
(
name = N’tempdev’,
filename = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf’,
size = 128MB,
filegrowth = 64MB
)

Once tempDB has been created, you can use the ALTER DATABASE command to modify the size of the existing files.

6. Avoid autogrowth events

Autogrowth events are when SQL Server automatically increases the size of the TempDB database files. These events can cause performance issues because they can introduce latency while the disk is being resized.

To avoid autogrowth events, it’s important to properly size your TempDB database files. Start by estimating the maximum size that the TempDB database will ever need to be, and then create database files that are large enough to accommodate that size.

It’s also a good idea to configure the database files to use a fixed size. This way, you can be sure that the database will never need to autogrow.

Finally, make sure to monitor the size of the TempDB database over time. This will help you identify any potential problems before they cause performance issues.

7. Pre-allocate space in tempdb

When SQL Server starts up, it creates tempdb from a set of files. The size and number of these files are defined by the system administrator during setup. If the initial size of tempdb is too small, and the auto-growth feature is not enabled, then SQL Server will run out of space in tempdb, and the system will come to a grinding halt.

To avoid this, it’s important to pre-allocate enough space in tempdb so that it can accommodate the workload. This can be done by increasing the initial size of the tempdb files, or by adding more files.

It’s also important to configure the auto-growth feature properly. The goal is to have tempdb grow in a controlled manner so that it doesn’t cause performance problems.

One way to do this is to set the auto-growth increment to a fixed size. For example, if the initial size of tempdb is 100 MB, you can configure the auto-growth increment to be 10 MB. This way, tempdb will only grow in 10 MB increments, and the growth will be evenly spaced out.

Another way to control the auto-growth of tempdb is to use the MAXSIZE option. This option allows you to specify the maximum size to which tempdb can grow. Once tempdb reaches the MAXSIZE, the auto-growth feature will be disabled, and tempdb will no longer be able to grow.

Configuring the MAXSIZE option can be helpful in preventing tempdb from growing uncontrollably and consuming all available disk space.

8. Do not use trace flags 1118 or 1117 with SQL Server 2016 (13.x)

Trace flag 1118 is used to prevent allocation updates from being logged in the transaction log, which can help reduce log space usage and improve performance. However, it can also cause data loss if a database crash occurs.

Trace flag 1117 is used to enable instant file initialization, which can improve performance when creating or growing databases. However, it can also allow unauthorized users to access data that has been deleted but not yet overwritten.

Both of these trace flags should be avoided when using SQL Server 2016 (13.x) because they are no longer needed. SQL Server 2016 (13.x) includes native features that provide the same benefits as trace flags 1118 and 1117 without the risks.

9. Enable Instant File Initialization

When SQL Server creates or expands a database file, the pages in the file must be zeroed out (filled with zeros) before they can be used. This process is known as zero initialization, and it can cause significant performance issues because it’s a disk-intensive operation.

Enabling Instant File Initialization bypasses the zero initialization process by allowing SQL Server to use newly allocated or extended pages directly, without initializing them first. This can provide a significant performance boost, especially on systems with fast storage subsystems.

10. Don’t mix user objects and internal objects in tempdb

When SQL Server creates a user object, such as a table or index, it stores the object in tempdb. However, when SQL Server needs to perform an internal operation, such as sorting data or creating a worktable for a hash join, it uses internal objects. These internal objects are not stored in tempdb; instead, they’re stored in memory.

If you mix user objects and internal objects in tempdb, it can cause contention for resources and lead to performance problems. Therefore, it’s best to keep user objects and internal objects separate.