6

Is there any way to identify which stored proc is creating a table variable in tempDB?

I am looking at the forward_fetch_count in sys.dm_db_index_operational_stats and we have a few tables that have a very large count (the biggest is over 133 million). The table name comes out in tempDB as a # followed by 8 hex characters. Is there a way to track that back to the originating process so we can fix it?

We are running SQL 2019 on Linux.

1
  • 1
    This hex naming scheme is also used for cached temporary tables so may not be a table variable. See mention of FCheckAndCleanupCachedTempTable here for more details dba.stackexchange.com/a/13412/3690 Aug 28 '21 at 7:58
9

It is not possible to identify a table variable in tempdb's metadata by the variable name, as the table name is created with the hex name there and the mapping to the variable at runtime is done somewhere within the engine.

However, you can track things down to some degree if at least one of the column names that fits in-row is unique-ish enough. Let's say we have a procedure like this:

USE some_database;
GO

CREATE OR ALTER PROCEDURE dbo.flabbermort
AS
  DECLARE @fooblat table(x int, y int, specific_column_name sysname);
  
  INSERT @fooblat(x,y,specific_column_name) 
    SELECT TOP (100) 1,1,name 
    FROM sys.all_columns;

  WAITFOR DELAY '00:30:00';
GO

Go ahead and execute that:

EXEC some_database.dbo.flabbermort;

Now if we don't already know the problematic object_id (e.g. from operational_stats), but we know we have a table variable problem (other ways to track tempdb usage here), we can find the biggest tables this way (with some help from this Paul White post and this Martin Smith answer):

;WITH x AS 
(
  SELECT t.name, t.[object_id], 
    [rows] = SUM(ps.row_count), 
    pages  = SUM(ps.used_page_count)
   FROM tempdb.sys.dm_db_partition_stats AS ps
   INNER JOIN tempdb.sys.tables AS t
   ON ps.[object_id] = t.[object_id]
   WHERE index_id IN (0,1)
     AND t.name LIKE N'#%'
   GROUP BY t.name, t.[object_id]
   HAVING SUM(ps.row_count) > 0
)
SELECT x.*, 
  first_file_id = CONVERT(int, SUBSTRING(au.first_page,6,1) 
    + SUBSTRING(au.first_page,5,1)),
  first_page_id = CONVERT(int, SUBSTRING(au.first_page,4,1) 
    + SUBSTRING(au.first_page,3,1) 
    + SUBSTRING(au.first_page,2,1)
    + SUBSTRING(au.first_page,1,1))
FROM x
INNER JOIN sys.partitions AS p 
  ON x.[object_id] = p.[object_id]
INNER JOIN sys.system_internals_allocation_units AS au 
  ON au.container_id = p.[partition_id]
ORDER BY x.page_count DESC;

This will give you output like this:

name        object_id     rows   pages   first_file_id   first_page_id
---------   -----------   ----   -----   -------------   -------------   
#B0A3AB35   -1331451083   100    2       6               32

Then, for either the object_id you've already identified or the biggest tables in the list, you can inspect DBCC PAGE output to see if there is anything you can use to identify the code that called it (which of course is much more useful if you know the code is in a stored procedure):

DBCC TRACEON(3604);
DBCC PAGE(tempdb,  6, 32, 3);
-- first_file_id --^  ^^-- first_page_id

This produced output like this (abbreviated), but will only do this if the process that created the object is still running (hence the waitfor in the procedure):

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

x = 1                               

Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4

y = 1                               

Slot 0 Column 3 Offset 0x13 Length 12 Length (physical) 12

specific_column_name = bitpos       

Slot 1 Offset 0x7f Length 25

Now I know that this problematic table has a column named specific_column_name, so I can set up a search of all stored procedures that use such a name (this is why columns x and y aren't very useful, as they will produce a lot of false positives and won't be very easy to search for). I can use my handy-dandy sp_ineachdb (grab the most current version from GitHUb):

CREATE TABLE #out(db sysname, obj nvarchar(640));

DECLARE @sql nvarchar(max) = N'INSERT #out(db,obj)
  SELECT DB_NAME(), QUOTENAME(s.name) + N''.'' + QUOTENAME(o.name)
    FROM sys.schemas AS s INNER JOIN sys.objects AS o
      ON s.schema_id = o.schema_id
    WHERE object_definition(o.object_id) LIKE N''%specific_column_name%'';';

EXEC master.dbo.sp_ineachdb @sql;

SELECT db, obj FROM #out;

Output:

db              obj
-------------   -------------------
some_database   [dbo].[flabbermort]

This is very manual and really hard to automate, and does depend on your ability to find a column name or other data on that first page that will help you narrow things down. You may end up with multiple stored procedures to check in on, or you may end up with nothing - meaning you may have to take this search to your application's source control.

And to reiterate there are very few use cases for preferring a table variable over a temp table, so I would consider changing your code to use temp tables, which will be slightly easier to track. For info on the differences see this other post from Martin Smith:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.