Chapter 7. Knowing Tempdb

WHAT'S IN THIS CHAPTER

  • How SQL Server uses tempdb for internal and user created temporary objects

  • How to troubleshoot and avoid common tempdb issues

  • How to monitor and tune tempdb performance

  • How configuration best practices can increase the performance and availability of tempdb

This chapter is about the system database called tempdb, which is used for storing temporary objects and has been a key component of SQL Server since its inception. Since SQL Server 2005, however, the role of tempdb has been brought to the forefront with a plethora of new features and optimizations that depend on temporary objects.

All these features have increased the visibility and requirement for good tempdb performance, which is why there is a full chapter dedicated to a thorough grounding in what it's used for, how to troubleshoot issues, and how it should be configured.

In the first section you'll get a look at the facets that make tempdb special, which SQL Server components use it, and specifically how it's used. The next section covers common issues and how to troubleshoot them, which sets the scene nicely to help justify the configuration recommendations. Finally, you'll find an especially useful best practices section at the end of the chapter.

OVERVIEW AND USAGE

You can think of tempdb as the "scratch" database for SQL Server; it's a temporary data store used by both applications and internal operations. It is very similar to other databases in that it has a data file and a log ...

Get Professional SQL Server® 2008 Internals and Troubleshooting now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.