Readers often come to me with stories of runaway tempdb growth. One DBA recently installed a newer version of an application and saw his tempdb, set to start at 80MB, suddenly grow in huge leaps to 8GB or more. How do you track tempdb growth and correlate that growth to specific commands?

Here are three effective ways to track tempdb’s growth. One, you can use the Performance Monitor counter Data File(s) Size (KB) associated with the tempdb instance for the database’s performance object. This counter is a good way to find out when the tempdb file is growing. You can also access this data directly from SQL Server by querying the master..sysperfinfo table, which materializes all Performance Monitor counters that SQL Server exports. Two, you can periodically sample tempdb’s sp_spaceused output. And three, you can use the fn_virtualfilestats() function to track the I/O that SQL Server writes to the database files that tempdb uses. If tempdb is growing, the likely cause is the physical I/O associated with certain queries. And if that’s the case, monitoring fn_virtualfilestats data can help you narrow down the queries responsible for the tempdb growth. Any of these techniques let you track tempdb growth over a specific time period.

To correlate tempdb growth with the queries that caused the growth, you can run a SQL Server Profiler trace while tracking tempdb growth. There’s no direct way to find out which query caused tempdb to grow by how much, but you can make some assumptions that will help you track down the potentially offending queries. The primary assumption is that any query that can make tempdb grow by large amounts, such as the DBA above saw, will take a long time to run compared to typical queries. These queries would also likely show a relatively high value in the WRITES data column of a Profiler trace. Seeing SPOOL operators in the query plan is another sign that SQL Server is performing large numbers of writes to tempdb. The SPOOL operator means that SQL Server is “spooling” an interim result set to a secondary location (tempdb) so that it can do something with that data later in the plan.

If you’ve captured data that helps you understand when the tempdb files were growing or experiencing large amounts of I/O and you’ve captured a list of queries run during the same period, you should be able to analyze the query list. Look for queries that have a start or end time that maps to a period of high growth, then further limit the list to queries that took a long time to run (probably at least a few hundred milliseconds) and performed a reasonably high amount of I/O operations (relative to other queries running at the time). This analysis will give you a short list of queries that could be causing the dramatic tempdb growth.

Here’s another idea to consider: In my experience, a full or partial Cartesian product can cause massive tempdb growth-and don’t forget to look for those SPOOL operators! Profiler has an event called Missing Join Predicate in the errors and warnings event class that will capture situations in which SQL Server thinks that a JOIN predicate is missing. I’ve never tested the logic to see how SQL Server determines a predicate is missing, so I can’t say that it always finds a missing JOIN predicate. But I include this event in my standard trace analysis and have successfully identified many Cartesian products (that were previously unknown to various customer development teams that I was consulting for) by looking for instances of this event.

Learn more about SQL Server Magazine, including how to subscribe, at http://www.sqlmag.com.