info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Monitor TempDB Usage
by BF (Principal Consultant; Architecture; Engineering)
2018-12-06








T-SQL Monitor TempDB Usage:


Use TEMPDB
go

SELECT SUM(size)/128 AS [Total TempDB Database Size (MB)] FROM tempdb.sys.database_files

SELECT UPPER(instance_name) AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data File Total Size (MB)],
[Log File(s) Size (KB)]/1024 AS [Log File Total Size (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

SELECT
(SUM(unallocated_extent_page_count)/128) AS [TempDB Free Space (MB)],
SUM(internal_object_reserved_page_count)*8 AS [TempDB Internal Objects (KB)],
SUM(user_object_reserved_page_count)*8 AS [TempDB User Objects (KB)],
SUM(version_store_reserved_page_count)*8 AS [TempDB Version Store (KB)]
FROM sys.dm_db_file_space_usage
WHERE database_id = 2

Select
CASE
Type_Desc when 'ROWS' Then 'DATA' WHEN 'LOG' THEN 'LOG' End as 'TempDB File Type',
count(*) as '# Files'
from tempdb.sys.database_files group by Type_Desc order by Type_Desc DESC

SELECT tb.name AS [Temporary Table Name],
stt.row_count AS [Number of rows],
stt.used_page_count * 8 AS [Used space (KB)],
stt.reserved_page_count * 8 AS [Reserved space (KB)]
FROM tempdb.sys.partitions AS prt
INNER JOIN tempdb.sys.dm_db_partition_stats AS stt ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number
INNER JOIN tempdb.sys.tables AS tb ON stt.object_id = tb.object_id
where stt.row_count > 0
ORDER BY stt.row_count desc

SELECT session_id,
user_objects_alloc_page_count/128 AS user_objs_total_sizeMB,
(user_objects_alloc_page_count - user_objects_dealloc_page_count)/128.0 AS user_objs_active_sizeMB,
internal_objects_alloc_page_count/128 AS internal_objs_total_sizeMB,
(internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128.0 AS internal_objs_active_sizeMB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count DESC

SELECT SERVERPROPERTY('MachineName') AS MachineName,
ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName,
SERVERPROPERTY('ServerName') AS SQLInstance,
DB_NAME(r.database_id) AS [Database],
s.login_name AS LoginName,
t.session_id AS SPID,
s.[status] AS [Status],
s.[program_name] AS ProgramName,
r.command AS StatementCommand,
SUBSTRING( est.[text],
(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN-1
THEN DATALENGTH(est.[text])
ELSE
r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1
) AS QueryText,
QUOTENAME(DB_NAME(r.database_id)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(est.objectid, est.dbid)) + N'.'
+ QUOTENAME(OBJECT_NAME(est.objectid, est.dbid)) AS ProcedureName,
tdb.UserObjectAllocated * 8 AS CurrentUserAllocatedKB,
(t.user_objects_alloc_page_count + tdb.UserObjectAllocated) * 8 AS TotalUserAllocatedKB,
tdb.UserObjectDeallocated * 8 AS UserDeallocatedKB,
(t.user_objects_dealloc_page_count + tdb.UserObjectDeallocated) * 8 AS TotalUserDeallocatedKB,
tdb.InternalObjectAllocated * 8 AS InternalAllocatedKB,
(t.internal_objects_alloc_page_count + tdb.InternalObjectAllocated) * 8 AS TotalInternalAllocatedKB,
tdb.InternalObjectDeallocated * 8 AS InternalDeallocatedKB,
(t.internal_objects_dealloc_page_count + tdb.InternalObjectDeallocated) * 8 AS TotalInternalDeallocatedKB,
r.reads AS RequestedReads,
r.writes AS RequestedWrites,
r.logical_reads AS RequestedLogicalReads,
r.cpu_time AS RequestedCPUTime,
s.is_user_process AS IsUserProcess,
s.original_login_name AS OriginalLoginName,
s.nt_domain AS NTDomain,
s.nt_user_name AS NTUserName,
s.[host_name] AS HostName,
r.start_time AS StartTime,
s.login_time AS LoginTime,
s.last_request_start_time AS LastRequestedStartTime,
s.last_request_end_time AS LastRequestedEndTime
FROM sys.dm_db_session_space_usage AS t
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = t.session_id
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
LEFT JOIN
( SELECT _tsu.session_id,
_tsu.request_id,
SUM(_tsu.user_objects_alloc_page_count) AS UserObjectAllocated,
SUM(_tsu.user_objects_dealloc_page_count) AS UserObjectDeallocated,
SUM(_tsu.internal_objects_alloc_page_count) AS InternalObjectAllocated,
SUM(_tsu.internal_objects_dealloc_page_count) AS InternalObjectDeallocated
FROM tempdb.sys.dm_db_task_space_usage AS _tsu
GROUP BY _tsu.session_id,
_tsu.request_id
) AS tdb
ON tdb.session_id = r.session_id
AND tdb.request_id = r.request_id
OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS est
WHERE t.session_id != @@SPID
AND (tdb.UserObjectAllocated - tdb.UserObjectDeallocated + tdb.InternalObjectAllocated - tdb.InternalObjectDeallocated) != 0
OPTION (RECOMPILE);