Aaron Bertrand

Dude, who owns that #temp table?

Download the SentryOne Plan Explorer Extension for Azure Data Studio
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts

You have probably been in a scenario where you were curious about who created a specific copy of a #temp table. Back in June of 2007, I asked for a DMV to map #temp tables to sessions, but this was rejected for the 2008 release (and was swept away with the Connect retirement a couple of years ago).

In SQL Server 2005, 2008 and 2008 R2, you should be able to pull this information from the default trace:

DECLARE @filename VARCHAR(MAX);
 
SELECT @filename = SUBSTRING([path], 0,
 LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  
 
SELECT   
     o.name,   
     o.[object_id],  
     o.create_date, 
     gt.SPID,  
     NTUserName = gt.NTDomainName + '\' + gt.NTUserName,
     SQLLogin = gt.LoginName,  
     gt.HostName,  
     gt.ApplicationName,
     gt.TextData -- don't bother, always NULL 
  FROM sys.fn_trace_gettable(@filename, DEFAULT) AS gt  
  INNER JOIN tempdb.sys.objects AS o   
    ON gt.ObjectID = o.[object_id] 
  WHERE gt.DatabaseID = 2 
    AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
    AND gt.EventSubClass = 1 -- Commit
    AND o.name LIKE N'#%'
    AND o.create_date >= DATEADD(MILLISECOND, -100, gt.StartTime)   
    AND o.create_date <= DATEADD(MILLISECOND,  100, gt.StartTime);

(Based on code by Jonathan Kehayias.)

To determine space usage you could further enhance this to join in data from DMVs like sys.dm_db_partition_stats – for example:

DECLARE @filename VARCHAR(MAX);
 
SELECT @filename = SUBSTRING([path], 0,
   LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  
 
SELECT   
     o.name,   
     o.[object_id],  
     o.create_date, 
     gt.SPID,  
     NTUserName = gt.NTDomainName + '\' + gt.NTUserName,
     SQLLogin = gt.LoginName,  
     gt.HostName,  
     gt.ApplicationName,
     row_count = x.rc,
     reserved_page_count = x.rpc
  FROM sys.fn_trace_gettable(@filename, DEFAULT) AS gt  
  INNER JOIN tempdb.sys.objects AS o   
    ON gt.ObjectID = o.[object_id]
  INNER JOIN
  (
    SELECT 
      [object_id],
      rc  = SUM(CASE WHEN index_id IN (0,1) THEN row_count END), 
      rpc = SUM(reserved_page_count) 
    FROM tempdb.sys.dm_db_partition_stats
    GROUP BY [object_id]
  ) AS x 
    ON x.[object_id] = o.[object_id]
  WHERE gt.DatabaseID = 2 
    AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
	AND gt.EventSubClass = 1 -- Commit
	AND gt.IndexID IN (0,1)
    AND o.name LIKE N'#%'
    AND o.create_date >= DATEADD(MILLISECOND, -100, gt.StartTime)   
    AND o.create_date <= DATEADD(MILLISECOND,  100, gt.StartTime);

Starting in SQL Server 2012, however, this stopped working if the #temp table was a heap. Bob Ward (@bobwardms) furnished a thorough explanation of why this happened; the short answer is there was a bug in their logic to try to filter out #temp table creation from the default trace, and this bug was partially corrected during the SQL Server 2012 work of better aligning trace and extended events. Note that SQL Server 2012+ will still capture #temp table creation with inline constraints such as a primary key, just not heaps.

The Object:Created event actually has 3 subevents: Begin, Commit, and Rollback. So if you successfully create an object you get 2 events: 1 for Begin and 1 for Commit. You know which one by looking at EventSubClass.

 
Prior to SQL Server 2012, only the Object:Created with subclass = Begin has the ObjectName populated. So the subclass = Commit did not contain the ObjectName populated. This was by design to avoid repeating this thinking you could look up the name in the Begin event.

 
As I've said the default trace was designed to skip any trace events where the dbid = 2 and object name started with "#". So what can show up in the default trace are the Object:Created subclass = Commit events (which is why the Object Name is blank).

 
Even though we didn't document our "intentions" to not trace tempdb objects, the behavior was clearly not working as intended.

 
Now move forward to the building of SQL Server 2012. We move to a process of porting events from SQLTrace to XEvent. We decided during this timeframe as part of this XEvent work that the subclass=Commit or Rollback needed the ObjectName populated. The code where we do this is the same code where we produce the SQLTrace event so now the SQLTrace event has the ObjectName in it for the subclass=Commit.

 
And since our filtering logic for default trace has not changed, now you don't see either Begin or Commit events.

How you should do it today

In SQL Server 2012 and up, Extended Events will allow you to manually capture the object_created event, and it is easy to add a filter to only care about names that start with #. The following session definition will capture all #temp table creation, heap or not, and will include all of the useful information that would normally be retrieved from the default trace. In addition, it captures the SQL batch responsible for the table creation (if you want it to), information not available in the default trace (TextData is always NULL).

CREATE EVENT SESSION [TempTableCreation] ON SERVER 
ADD EVENT sqlserver.object_created
(
  ACTION 
  (
    -- you may not need all of these columns
    sqlserver.session_nt_username,
    sqlserver.server_principal_name,
    sqlserver.session_id,
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.sql_text
  )
  WHERE 
  (
    sqlserver.like_i_sql_unicode_string([object_name], N'#%')
    AND ddl_phase = 1   -- just capture COMMIT, not BEGIN
  )
)
ADD TARGET package0.asynchronous_file_target
(
  SET FILENAME = 'c:\temp\TempTableCreation.xel',
  -- you may want to set different limits depending on
  -- temp table creation rate and available disk space
      MAX_FILE_SIZE = 32768,
      MAX_ROLLOVER_FILES = 10
)
WITH 
(
  -- if temp table creation rate is high, consider
  -- ALLOW_SINGLE/MULTIPLE_EVENT_LOSS instead
  EVENT_RETENTION_MODE = NO_EVENT_LOSS
);
GO
ALTER EVENT SESSION [TempTableCreation] ON SERVER STATE = START;

You may be able to do something similar in 2008 and 2008 R2, but I know there are some subtle differences to what is available, and I did not test it after getting this error right off the bat:

Msg 25623, Level 16, State 1, Line 1
The event name, "sqlserver.object_created", is invalid, or the object could not be found

Analyzing the data

Pulling the information from the file target is a little more cumbersome than with the default trace, mostly because it is all stored as XML (well, to be pedantic, it is XML presented as NVARCHAR). Here is a query I whipped up to return information similar to the second query above against the default trace. One important thing to note is that Extended Events stores its data in UTC, so if your server is set to another time zone, you will need to adjust so that the create_date in sys.objects is compared as if it were UTC. (The timestamps are set to match because object_id values can be recycled. I assume here that a two second window is sufficient to filter out any recycled values.)

DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());
 
;WITH xe AS
(
  SELECT 
    [obj_name]  = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
    [object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
    [timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
    SPID        = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
    NTUserName  = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
    SQLLogin    = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
    HostName    = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
    AppName     = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
    SQLBatch    = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
 FROM 
    sys.fn_xe_file_target_read_file(N'C:\temp\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
    CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
) 
SELECT 
  DefinedName         = xe.obj_name,
  GeneratedName       = o.name,
  o.[object_id],
  xe.[timestamp],
  o.create_date,
  xe.SPID,
  xe.NTUserName,
  xe.SQLLogin, 
  xe.HostName,
  ApplicationName     = xe.AppName,
  TextData            = xe.SQLBatch,
  row_count           = x.rc,
  reserved_page_count = x.rpc
FROM xe
INNER JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND,  2, xe.[timestamp])
INNER JOIN
(
  SELECT 
    [object_id],
    rc  = SUM(CASE WHEN index_id IN (0,1) THEN row_count END), 
    rpc = SUM(reserved_page_count)
  FROM tempdb.sys.dm_db_partition_stats
  GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id];

Of course this will only return space and other information for #temp tables that still exist. If you want to see all #temp table creations still available in the file target, even if they don't exist now, simply change both instances of INNER JOIN to LEFT OUTER JOIN.