There are many misconceptions and myths about tempdb and purported best practices are inconsistent at best. It’s hard to know which advice to follow when one resource says to always do it one way and another tells you to always do it the opposite way. 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 correct solution to any scenario in SQL Server. Unfortunately, many administrators will assume that because a practice worked for them in one situation that they should do the same practice in every situation.
The purpose of this whitepaper is to guide you on how to make the right decisions for managing tempdb; to help you determine the proper solution for any given scenario. In order to be able to make these decisions, there are several concepts that must be understood.
We will take a look at:
- what tempdb is and how it is used
- some common major problems and how to prevent them
- best practices on configuring and monitoring tempdb