Si vous êtes un client Managed Hosting, cette rubrique ne vous concerne pas.

The TEMPDB is one of the most critical areas for optimizing performance of SQL Server. Most complex query operations take place in this region of SQL Server. When configured incorrectly, performance can be affected dramatically.

By default, the space is not meant to shrink (nor do we recommend auto-shrinking the space), but a penalty does exist for this space growing. Size this space carefully based on the needs of the application.

The TEMPDB is often sized to 8MB by default. It continues to grow as long as the SQL Server is online. Restarting the SQL Server process will force the TEMPDB to return to its default size. This is why users often complain about the overall responsiveness of the system after maintenance. Part of the problem could be that the TEMPDB needs to grow, but another factor could be the flushing of the procedure cache during the restart operation. The culprit of slow performing queries on a system with a poorly configured TEMPDB is often latency caused by TEMPDB data file growth.

Sizing the TEMPDB

Blackboard recommends the following when sizing the TEMPDB:

  • To determine the actual TEMPDB size needed, pre-size the TEMPDB to 2GB and let it grow over several days of high usage.
  • To determine the data file size for each CPU, divide the actual TEMPDB size by the number of CPUs available.
  • To enable the last file to grow if additional storage is needed, disable auto-growth for each of the data files except the last one.

For example, suppose that an 8 CPU server needs roughly 4GB of actual TEMPDB space. Configure eight data files, each pre-sized to 512MB. Disable auto-growth for the first seven files, and enable auto-growth for the final file.