TempDB usage details that you can use for monitoring or troubleshooting in SQL Server. I’ll include the most common DMV queries and what each one tells you.
🔍 TempDB Usage – Key Details & Diagnostic Queries
1. TempDB Space Usage (By File)
Shows how much space is used/free in each TempDB data file.
SQLSELECT name AS FileName, size/128 AS TotalMB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128 AS UsedMB, (size - FILEPROPERTY(name, 'SpaceUsed'))/128 AS FreeMBFROM tempdb.sys.database_files;Show more lines
2. TempDB Usage by Session
Helps identify which sessions are consuming TempDB.
SQLSELECT session_id, user_objects_alloc_page_count AS UserAllocatedPages, internal_objects_alloc_page_count AS InternalAllocatedPages, user_objects_dealloc_page_count AS UserDeallocatedPages, internal_objects_dealloc_page_count AS InternalDeallocatedPagesFROM sys.dm_db_session_space_usageORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;Show more lines
3. TempDB Usage by Running Requests
Shows TempDB grants/consumption per active request.
SQLSELECT r.session_id, r.status, t.text AS SQLText, r.tempdb_space_used, r.tempdb_space_allocatedFROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) tORDER BY r.tempdb_space_used DESC;Show more lines
(Available in SQL Server 2019+)
4. Version Store Usage (Common cause of TempDB growth)
Tracks TempDB version store size used for RCSI/Snapshot.
SQLSELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStoreMBFROM sys.dm_db_file_space_usage;Show more lines
5. TempDB File I/O Stats
Useful to see if TempDB is experiencing heavy writes.
SQLSELECT file_id, num_of_reads, num_of_writes, io_stall, io_stall_read_ms, io_stall_write_msFROM sys.dm_io_virtual_file_stats(2, NULL);Show more lines
🧠 What Typically Consumes TempDB?
Sorting (ORDER BY, GROUP BY)
Hash operations & Hash joins
Temp tables, table variables
Row versioning (RCSI / Snapshot Isolation)
Online index rebuilds
Worktables for large queries
Triggers
Cursors
USE [staging]
GO
/****** Object: Table [dbo].[tempdb_session_usage_detail] Script Date: 2/20/2026 2:32:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Use Staging
GO
CREATE TABLE [dbo].[tempdb_session_usage_detail](
[tempdb_session_usage_detail_id] [int] IDENTITY(1,1) NOT NULL,
[session_id] [int] NULL,
[InternalPages] [bigint] NULL,
[UserPages] [bigint] NULL,
[TotalUsedMB] [decimal](10, 2) NULL,
[start_time] [datetime] NULL,
[host_name] [nvarchar](128) NULL,
[login_name] [nvarchar](128) NULL,
[command] [nvarchar](128) NULL,
[event_info] [nvarchar](max) NULL,
[status] [nvarchar](128) NULL,
[program_name] [nvarchar](256) NULL,
[transaction_isolation_level] [nvarchar](50) NULL,
[database_name] [nvarchar](128) NULL,
[dop] [int] NULL,
[parallel_worker_count] [int] NULL,
[sql_text] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[run_date] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[tempdb_session_usage_detail_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [msdb]
GO
/****** Object: Job [ADM - High Tempdb Usage Queries] Script Date: 2/20/2026 2:27:39 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Batch - H]] Script Date: 2/20/2026 2:27:40 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - H]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - H]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - High Tempdb Usage Queries',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'To Capture High Tempdb ussage Queires',
@category_name=N'[Batch - H]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA ALERT', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [High Tempdb Use queries] Script Date: 2/20/2026 2:27:40 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'High Tempdb Use queries',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
use tempdb
go
INSERT INTO staging.dbo.tempdb_session_usage_detail (
session_id,
InternalPages,
UserPages,
TotalUsedMB,
start_time,
host_name,
login_name,
command,
event_info,
status,
program_name,
transaction_isolation_level,
database_name,
dop,
parallel_worker_count,
sql_text,
query_plan,
run_date
)
SELECT U.session_id, U.InternalPages, U.UserPages, U.[TotalUsedMB], r.start_time, s.host_name,
s.login_name,r.command,i.event_info,r.status,s.program_name
,CASE s.transaction_isolation_level
WHEN 0 THEN ''Unspecified''
WHEN 1 THEN ''ReadUncommitted''
WHEN 2 THEN ''ReadCommitted''
WHEN 3 THEN ''Repeatable''
WHEN 4 THEN ''Serializable''
WHEN 5 THEN ''Snapshot'' END AS transaction_isolation_level, DB_NAME(r.database_id) AS database_name,
r.dop, r.parallel_worker_count,
st.text,
eqp.query_plan AS [Query Plan],getdate() as run_DATE
FROM
(
SELECT
tsu.session_id,
SUM(internal_objects_alloc_page_count) AS [InternalPages],
SUM(user_objects_alloc_page_count) AS [UserPages],
SUM((internal_objects_alloc_page_count + user_objects_alloc_page_count) * 8 / 1024 ) AS [TotalUsedMB]
FROM sys.dm_db_task_space_usage AS tsu
WHERE tsu.session_id <> @@SPID -- Exclude your own session
GROUP BY
tsu.session_id
HAVING
SUM ( (internal_objects_alloc_page_count + user_objects_alloc_page_count) * 8 / 1024 ) > 100
)U
JOIN sys.dm_exec_requests as r
on r.session_id = U.session_id
JOIN sys.dm_exec_sessions as s
on s.session_id = r.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) as i
--OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st
OUTER APPLY ::fn_get_sql (r.sql_handle) As st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS eqp',
@database_name=N'tempdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'run every 5 min',
@enabled=1,
@freq_type=8,
@freq_interval=127,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20250905,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=NULL;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
No comments:
Post a Comment