[Video] How to Troubleshoot Someone Else’s Temp Table Contents

Let’s say you need to troubleshoot someone’s query performance, and they’re using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging. Nic Neufeld showed how to use DBCC PAGE to do this, but today I’m going to cover another way: reading their statistics histogram.

I wrote this blog post on a live stream in my Twitch & my YouTube channels – subscribe to those if you want email notifications whenever I start live streaming stuff like this – or just keep reading below for the code & screenshot walkthrough.

Let’s start by creating a temp table in one window of SSMS:

Then, open another tab in SSMS, meaning another session, and try to read the contents of that other session’s temp table:

Sad trombone.

Let’s try another trick: let’s use the system tables to get the full exact name of the temp table. Whenever you create a temp table, SQL Server actually creates it with a unique name suffixed with the hex equivalent of the number of temp tables that have been created since the last SQL Server instance restart:

Armed with that table name, let’s now try querying it by using the super-secret full object name:

It’s not a matter of not fully qualifying the schema, either, because it’s not in the dbo schema:

This would be where normal people give up, but you know how I am, dear reader: not normal.

Let’s try reading the temp table’s statistics.

Like tables in user databases, SQL Server will automatically create statistics on columns when it needs to make decisions about the number of rows that will match filters on that column. Let’s take a peek in TempDB to see if any statistics exist on that temp table:

Drats! That temp table doesn’t have statistics yet. This is the first weakness in this blog post’s strategy to read someone else’s temp table contents: you’re only going to get statistics when folks have queried specific columns. Let’s trigger that by going back into the original session that created the temp table, and running a query that will trigger automatic stats creation on one of the columns:

Now go back over to the window where we were diagnosing statistics, and check again:

Voila! Now, armed with a few more joins, we can see the contents of that temp table’s statistics:

Here’s the query if you want to look at what’s happening right now in your own TempDB:

Limitations of this technique

In the live stream, CrankyOldGentleman reminded us that statistics only have a max of 201 steps in the histogram, so we won’t see all of the data on real-life-sized tables. In addition, even if there are <200 rows, SQL Server may not use all 201 steps in the histogram because it may not be necessary in order to paint a great picture of our table’s contents.

In addition, as Paul White has so lovingly blogged, temp tables and their statistics can be reused from one session to another. Because of that, you wouldn’t want to say that one specific user’s query activity has produced the statistics that you’re seeing in this diagnostic query. However, that’s actually a good thing: this diagnostic query can show you when someone’s session is running with temp table statistics that may not match up with their current workload.

The biggest limitation that I’ve hit in production environments, though, is that these DMVs don’t always honor isolation level requests. Even if you prefix your queries with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and slap WITH (NOLOCK) on every single one of ’em, that last DMV query in the post still gets blocked by active sessions using their temp tables. This technique is useful in lightly loaded environments, but not heavily loaded environments with thousands of active queries per second.

I recorded this stream and wrote the post way back in August, but I completely forgot about it until I was writing material for my new Fundamentals of TempDB class. There’s so much fun stuff around TempDB.