SQLDBA - TempDB usage details

-

 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



Comments

Post a Comment

Popular posts from this blog

DBA- MAXDOP

-
Image
  Have your SQL queries been eating up CPU like it's a buffet? Then it’s time to talk about the most underrated setting in SQL Server:  MAXDOP . Let’s break it down in simple, real-world terms — with best practices, optimized queries, and use cases that matter. What is MAXDOP? MAXDOP stands for Maximum Degree of Parallelism — it controls how many CPU cores SQL Server uses to run a single query in parallel. Sounds powerful? It is — but misconfigured, it can hurt more than help. Too low? Queries run slower, underutilizing hardware. Too high? CPU gets overwhelmed, blocking other tasks. 🔧  Where to Configure MAXDOP : Instance Level (Recommended for Most) EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Database Level (Overrides Instance Setting) ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; Query Level (...
Read more

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more