Whitepaper : Demystify Tempdb Performance & Management

Microsoft SQL Server stores a significant amount of data in the tempdb system database. Consequently, tempdb can fill up quickly when disk space is low, or the maximum size for database growth is small. Also, specific workloads may cause excessive space usage or create contention in tempdb, which can affect performance on the entire server. A whole SQL Server instance can become inoperable due to problems with tempdb. Tempdb is a very critical component of SQL Server. It can be a significant performance pain point if not managed properly. Having a performance tempdb starts with proper configuration and includes consistent baselining and monitoring of tempdb.

There exist many misconceptions and myths about tempdb. Purported best practices are inconsistent at best. It is hard to know which advice to follow when one resource says always to do it one way and another always says to do it in the opposite direction. Many times, both resources are correct in certain situations or to a certain degree. Part of the problem is that rarely is there a single right solution to any scenario in Microsoft SQL Server. Unfortunately, many database administrators will assume that because a practice worked for them in one situation that they should do the same method in every case. Clear guidance on best practices for managing tempdb to provide the optimum balance between performance, reliability, and manageability.

This whitepaper provides guidance on how to make the right decisions for managing tempdb to help you determine the proper solution for any given scenario. To be able to make these decisions, several concepts must be understood. The whitepaper describes what tempdb is, how to use it, some common significant problems, and how to prevent them. The whitepaper also provides some best practices on configuring and monitoring tempdb. The presented best practices will help to prevent, detect, and mitigate common performance problems. Couple these best practices with an understanding of how tempdb works and the role it plays to guide troubleshooting issues with tempdb.