Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create.
In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage I/O, tempdb uses the same file structure as a user database one or more data files and a log file. The arrangement of data pages within tempdb data files is also based on the same architecture as user databases.
Unlike all other databases, SQL Server recreates the tempdb database each time the SQL Server service starts. This is because tempdb is a temporary store.
There are three primary ways that the organization of tempdb files can affect system performance:
- Because users and the database engine both use tempdb to hold large temporary objects, it is common for tempdb memory requirements to exceed the capacity of the buffer pool in which case, the data will spool to the I/O subsystem. The performance of the I/O subsystem that holds tempdb data files can therefore significantly impact the performance of the system as a whole. If the performance of tempdb is a bottleneck in your system, you might decide to place tempdb files on very fast storage, such as an array of SSDs.
- Although it uses the same file structure, tempdb has a usage pattern unlike user databases. By their nature, objects in tempdb are likely to be short-lived, and might be created and dropped in large numbers. Under certain workloads especially those that make heavy use of temporary objects this can lead to heavy contention for special system data pages, which can mean a significant drop in
performance. One mitigation for this problem is to create multiple data files for tempdb; this is covered in more detail in the next topic.
- When SQL Server recreates the tempdb database following a restart of the SQL Server service, the size of the tempdb files returns to a preconfigured value. The tempdb data files and log file are configured to autogrow by default, so if subsequent workloads require more space in tempdb than is currently available, SQL Server will request more disk space from the operating system. If the initial
size of tempdb and the autogrowth increment set on the data files is small, SQL Server might need to request additional disk space for tempdb many times before it reaches a stable size.