Most DBAs have learned the hard
way that they need to monitor tempdb usage. Data warehousing applications
can easily eat up large amounts of tempdb space. Although NT's Performance
Monitor (Perfmon) and the SQL Enterprise Manager (EM) can help give you
a good idea of where tempdb is headed, they don't always tell the whole
truth.
As columnist Andrew Zanevsky has pointed out on several occasions, with
disk storage being so cheap these days, worrying about the size of
tempdb shouldn't even be an issue. However, monitoring tempdb usage,
particularly in data warehousing type systems, can give a good indication
of query performance. Tempdb space usage can be easily monitored in three
different ways: with Perfmon, with SQL EM, or by running sp_spaceused from
the command prompt. When choosing which method to use, it's important to
be aware of the limitations of each.
Perfmon is somewhat limited for monitoring tempdb usage, because all it
can display is "Max Tempdb Space Used (MB)". While this might
seem to be what you want, it's rather misleading. Like all Perfmon parameters
that contain the word "Max", this parameter doesn't qualify the
information. You need to ask yourself, "Okay, this is the maximum,
but since when?" The answer might not be what you expect (or want):
The answer is, since Perfmon began monitoring tempdb's size. If you had
Perfmon running for days -- even if you've cycled the SQL Server down and
back up -- you'll still see the maximum space used since you began the monitoring
session.
An additional way to monitor tempdb is to use SQL EM. From the SQL Server
database properties option, you can display the properties of tempdb, including
thecurrent value for space used. This method provides current information
-- as long as you keep pressing the Recalculate button -- and is actually
just a GUI version of the final method.
Many DBAs prefer the direct command line approach. Running sp_spaceused
against tempdb will provide you with a snapshot of the most current usage
of tempdb. However, it's important to know that sp_spaceused is a system
stored procedure that queries the sysindexes table and is completely
dependent on that table for its accuracy.
Against any database, the values reported by sp_spaceused will be incorrect
if either of two conditions occurs. The first condition is if a checkpoint
has yet to be issued, because it's the checkpoint process that updates the
system tables. If you run sp_spaceused before the sysindexes table
has been updated via checkpoint, you'll probably obtain inaccurate results.
The other condition occurs when an index has been dropped. Sysindexes
records the number of pages allocated to each table, index, and text or
image column in the database. If an index is dropped, sysindexes
isn't updated to reflect that change. This is currently a limitation in
SQL Server that's exasperated in tempdb.
Indexes can be created automatically within tempdb on a worktable by the
query optimizer or manually on a #temporary (local temporary) table by the
DBA. Once the associated query or stored procedure is completed, the index
is dropped without correctly updating the sysindexes table. Therefore,
a tempdb database experiencing healthy use will very quickly begin reporting
incorrect size information.
The results of sp_spaceused can easily go beyond incorrect and into the
impossible (see Figure
1). It's not that the database has grown to an
unnatural size -- it's just that sp_spaceused is reporting information based
on values in sysindexes that haven't been updated. To correct this
problem, run DBCC with SQL Server 6.5's new DBCC UPDATEUSAGE command:
DBCC UPDATEUSAGE (tempdb) WITH COUNT_ROWS
For versions prior to 6.5, you can use this:
USE TEMPDB
DBCC CHECKTABLE (sysindexes)
DBCC NEWALLOC
Either method will correct the counts contained in the sysindexes
table by updating the rows, used, reserved, and dpages columns. Running
the DBCC commands will produce output similar to this (if there were errors
that needed correcting):
The number of data pages in Sysindexes for this table
was 655361. It has been corrected to 1.
An alternative way to correct the potential inaccuracies is to issue the
sp_spaceused with the following parameter:
sp_spaceused @updateusage = 'TRUE'
However, this can take more time and impact performance more significantly
than issuing the combination of DBCC and sp_spaceused without the @updateusage
parameter.
Again, tempdb usage monitoring can provide valuable information for system
tuning. It's just important to take the information with a grain of salt
and understand what the various monitoring methods are telling you.
Joe Luedtke is a managing consultant
with The Revere Group, a Microsoft Solution Provider and IT consulting firm
in Milwaukee, WI. He's a Microsoft Certified Systems Engineer, Novell Certified
Netware Engineer, and Certified Lotus Professional Notes Principal, and
he has more than nine years of experience in application development and
database administration. jluedtke@reveregroup.com.
