Have you ever wondered why your tempdb database is growing so fast? This article explores how to find the culprit SPID that is causing tempdb growth.
The SQL Server tempdb database is one of SQL Server's most important system databases. It stores temporary user objects, such as temporary or staging tables defined by the user or returned from table-valued function execution, temporary stored procedures, table variables, or Online Index rebuilds.
The TempDB also stores the internal objects for various operations such as sorting, spooling, cursor, or aggregate operations. Additionally, the operations such as DBCC commands, Version stores, Multiple active record sets (MARS), Snapshot isolation, and read committed snapshot isolation actively use the TempDB.
SQL Server recreates the TempDB each time the SQL Service is restarted. Therefore, it uses a clean copy of the database, and all existing objects drop during restart.
Sometimes, DBA observes that TempB grows fast and occupies high disk space continuously. It might fill up the entire disk space allocated to TempDB.
How will you find which session is consuming the most TempDB space and responsible for the rapid growth of the TempDB database?
Let's explore this in this article.
For my demo SQL instance, I have the following SQL Server tempdb database configurations.
The following script (#1)displays the data and log file size of the SQL Server tempdb database.
SELECT instance_name AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data file (MB)],
[Log File(s) Size (KB)]/1024 AS [Log file (MB)],
[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]
FROM (SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
AND instance_name = 'tempdb') AS A
PIVOT
(MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)])) AS B
GO
Let's set up an extended event that captures the TempDB data and log file growth. Take the script from GitHub and execute it to start an extended event session.
For the demonstration, I am running below T-SQL that will cause grow TempDB rapidly.
SELECT *
FROM sys.configurations
CROSS JOIN sys.configurations A
CROSS JOIN sys.configurations B
CROSS JOIN sys.configurations C
CROSS JOIN sys.configurations D
CROSS JOIN sys.configurations E
CROSS JOIN sys.configurations F
CROSS JOIN sys.configurations G
CROSS JOIN sys.configurations H
ORDER BY A.name,
A.value,
C.value_in_use DESC; Let's run the select statement to increase the TempDB size and monitor its size. You can see that the data file size is increasing(script #1).
To identify which session is causing the TempDB growth, you can use the following ways.
sp_whoIsactive:
It is an open-source stored procedure written by Adem. You can download it from URL http://whoisactive.com/ and execute it. In the output, you can see that session-id 51 uses maximum TempDB allocations. The column sql_text captures the SQL query running in the SPID 51.
Monitor using the DMV
The following query joins the DMV sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_db_task_space_usage to find the session id, TempDB internal object size. As shown below, the SPID 51 is the culprit for the growing SQL Server tempdb database space.
SELECT s.session_id, dbu.database_id
, dbu.internal_objects_alloc_page_count, dbu.internal_objects_dealloc_page_count
, (dbu.internal_objects_alloc_page_count - dbu.internal_objects_dealloc_page_count) * 8192 / 1024 kbytes_used_internal
, r.total_elapsed_time
FROM sys.dm_Exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_db_task_space_usage dbu ON dbu.session_id = r.session_id
AND dbu.request_id = r.request_id
WHERE internal_objects_alloc_page_count > 0
ORDER BY kbytes_used_internal DESC;
Monitor using the extended event session
The extended events help monitor live or historical data. For example, suppose someone reports the following error message – Could not allocate space for object <temporary system object> in database 'tempdb.'
You can get the query from GitHub Link and run the monitoring script. As show
It returns the following output highlighting SPID, query text, data file or log file file growth event, its duration and growth timestamp.
Once you have identified the problematic SPID, you can look at its execution plan and query steps to find why SQL Server is growing TempDB too fast due to its execution.
