Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 212

The i rst section looks at what makes tempdb special, which SQL Server components use it, and
specii cally how it is used. The next section covers common issues and how to troubleshoot them,
which sets the scene for the coni guration recommendations that follow. Finally, you'll i nd an
especially useful best practices section at the end of the chapter.

NEW FOR SQL SERVER 2012

The only major change for tempdb in SQL Server 2012 is support for i le placement
on local storage within a Failover Cluster Instance. You can read about why and
how you might do that in the “Coni guration Best Practices” section of this 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 i le and a log i le and can be found in SQL Server Management Studio, but it does have some
unique characteristics that affect how you use and manage it.

The i rst fact to note is that everyone using an instance shares the same tempdb; you cannot have
any more than one within an instance of SQL Server but you can get detailed information about
who is doing what in tempdb using DMVs, which are discussed in the section on troubleshooting
space issues later in the chapter.

The following features and attributes should be considered when learning about, using, tuning, and
troubleshooting tempdb:

➤

Nothing stored in tempdb persists after a restart because tempdb is recreated every time
SQL Server starts. This also has implications for the recovery of tempdb — namely, it
doesn't need to be done. See the following sidebar.

TEMPDB HAS FEWER LOGGING OPERATIONS

When you change a value in a normal database, both the old value and the new
value are stored in the transaction log. The old value is used in case you need to roll-
back the transaction that made the change (undo), and the new value is used to
roll-forward the change during recovery (redo) if it hadn't made it to the data i le
before the restart (see dirty pages and checkpoints in Chapter 1).

You still need to be able to undo a change in tempdb but you'll never need to redo
the change as everything is thrown away on restart. Therefore, tempdb doesn't
store the redo information, which can result in signii cant performance gains when
making many changes to big columns compared to a user database.