How Many Temporary Tables are Created So Far in SQL Server? – Interview Question of the Week #149

Question: How many temporary tables have been created in this SQL Server instance since it last started?

Answer: This is one of the more interesting interview questions I have heard, partly because the most tempting shortcut gives the wrong answer. I also do not think a lifetime table count, by itself, tells you much about tempdb pressure. A workload that briefly creates many tiny tables can be less troublesome than one that holds a large object for a long time.

My original example created #Test8, read the last characters of a name in tempdb.sys.tables, and suggested converting that hexadecimal suffix to a decimal lifetime count. I would not use that as the answer today. SQL Server appends a unique suffix so that different sessions can use the same local temporary table name. Microsoft describes it as part of the internal name, not as a documented counter of every temporary table ever created. Tables that went out of scope are also no longer a reliable historical inventory. MAX(RIGHT(name, 12)) cannot recover that missing history.

What can we see now? SQL Server exposes an Active Temp Tables performance counter. This read-only query shows its current value for the instance. It includes temporary tables and table variables in use, so it does not answer the since-restart question.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:General Statistics%'
  AND counter_name = N'Active Temp Tables';

There is also a Temp Tables Creation Rate counter for the number of temporary tables and table variables created per second. Monitor that rate over an interval using Performance Monitor or your existing monitoring system. To answer an exact “since restart” question, collection must have been running for that period; a snapshot taken today cannot reconstruct creations that were never recorded.

My interview answer is therefore: there is no dependable way to infer the cumulative number from the names of temporary tables currently present. We can inspect current use and monitor the creation rate going forward. Then I would ask what problem the count is meant to diagnose, because that determines which tempdb measure is worth collecting.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.