Whats causing my tempdb to grow? - SQL 2000

The topic of this post was raised by Anon commentor on the last post. This post will answer that question.

To provide a quick answer to the same question, SQL Server 2000 doesnt provide a perfect way to find the object that was using tempdb. Let us look at the options we are left with.

Tempdb is heavily used when

* When a order by clause is used.
* When a group by clause is used.
* When indexes are created or rebuilt
* When temp table / @table variables are used.
* When Key set and Static Cursors are used.
* Queries that require 'Work table' operator in query plan. Or in other
words queries that require results to be stored/spooled temporarily.
* DBCC Checkdb command
* Hash join operators in query plan.
* Long running transactions

Having these points at the back of our minds, let us look at the options available for monitoring tempdb growth or looking at the queries that use tempdb to the most. Lemme divide it into 3 steps.

Step 1: Perfmon

There are two perfmon counters namely Workfiles Created/sec,Worktables Created/sec under the object SQLServer:Access Methods . Worktables are temporary tables created in tempdb by SQL Server to perform a sort,union or group by operation.Worktables Created/sec indicates the number of worktables that were created per sceond and Workfiles Created/sec indicates the number of workfiles created per second. The good thing about these two counters is that, as they are SQL counters, their values can be directly obtained using the following query.


SELECT *
FROM   MASTER.dbo.sysperfinfo
WHERE  object_name LIKE '%SQLServer:Access Methods%'
       AND counter_name IN ('Workfiles Created/sec','Worktables Created/sec')


One can dump the results of this query to a table on your production server using a Scheduled job running periodically say every 2 minutes. The scheduled job shouldnt be a huge stress on the database as it just inserts 2 rows at every run.

Step 2: fn_virtualfilestats

Along with monitoring worktable creation rate, one should also keep track of the Reads and writes happening on tempdb. This can be done by

SELECT *
FROM   :: Fn_virtualfilestats(2,1)


fn_virtualfilestats returns the number of Reads and writes happened on tempdb. 2 is the dbid of tempdb. 1 is the filed id. Dump the results of fn_virtualfilestats periodically to a table as done earlier.

Step 3: SQL Profiler

Enable SQL Profiler to capture the queries running on the server. A word of caution while using profiler. Profiler uses around 10 to 15% of CPU usage on the server. Lesser the number of events one captures, lesser the usage.

Use the data collected at step 1 and step 2 and identify the times at which there is a sharp increase in tempdb usage. Correlate the same with data captured from the profiler and narrow down the queries that are most likely using the tempdb the most.

SQL 2005 has made lot of improvements in tempdb monitoring, which will be covered in my upcoming posts.