SQL Temporary Database tempdb setup recommendations
The size, settings, and physical placement of a SQL Server Temporary Database tempdb can affect the performance of a system with Critical System Protection (CSP), especially when querying the database.
For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with ‘auto-growing’ tempdb to the size required to support a specific workload by a query. Or worse, queries will fail if they require more space than what is available on the disk where the tempdb files are located.
It is important to note that tempdb is shared between all databases residing on the same instance of SQL Server.
On an average, CSP consumes ~1 MB of tempdb space per 1000 event records when querying/reporting on events. For example, if you plan on querying ‘Event Details’ for the last 30 days’ events from a CSP system which manages 30 agents, each producing 2000 events a day, the tempdb file size will grow by a minimum of 1.8 GB.
The recommended file size is calculated by the following formula:
Recommended file size (in MB) = Agent events per day * Number of agents * Days / 1000
To achieve optimal tempdb performance with CSP, it is recommended to use the following configuration for tempdb in a production environment:
- Pre-allocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to ‘auto-grow’, but this should be used to increase disk space for unplanned exceptions.
- Set the recovery model of tempdb to SIMPLE (this is the default setting). This model automatically reclaims log space to keep space requirements small.
- Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.
- Set the FILEGROWTH increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand, which will affect performance. It is recommended to use the following general guidelines for setting the FILEGROWTH increment for tempdb files.
|tempdb File size
|< 100 MB
|100 – 200 MB
|> 200 MB
You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the auto-grow operation to approximately 2 minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size.
- Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server and then adjust the number of files up or down as necessary.
- Make each data file the same size; this allows for optimal proportional-fill performance.
- Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
- Put the tempdb database on disks that differ from those that are used by the productions databases.