Monitoring Tempdb Usage: A Guide For Sql Server Admins

Monitoring the usage of the tempdb database in SQL Server is crucial for performance optimisation and troubleshooting. Tempdb is a system database used to temporarily store data and is shared by all users connected to an instance of SQL Server. It holds temporary user objects such as tables, indexes, and stored procedures, as well as internal objects created by the database engine.

To monitor tempdb usage, you can use Dynamic Management Views (DMVs) and functions (DMFs) to obtain detailed information about its space usage. Specifically, the sys.dm_db_file_space_usage DMV provides information about the space usage of the database files, including the amount of free space, space used by the version store, internal objects, and user objects. Additionally, the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage DMVs can help track space usage at the session and task level, respectively.

By regularly monitoring tempdb usage, administrators can identify potential issues, optimise queries, and ensure efficient utilisation of system resources.

Monitor tempdb database files and gather other information

To monitor the SQL Server tempdb database statistics, you can execute the following query:

Sql

-First part of the script

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

• 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

The first part of the query reveals the currently allocated size of the data file, log file, and space used by the log file.

The second part of the query shows exactly when the tempdb was created and which recovery model it is utilizing:

Sql

-Second part of the script

SELECT create_date AS [Creation date],

Recovery_model_desc [Recovery model]

FROM sys.databases WHERE name = 'tempdb'

To get the total database size without details, use this query:

Sql

SELECT SUM(size)/128 AS [Total database size (MB)]

FROM tempdb.sys.database_files

Since SQL Server automatically creates the tempdb database from scratch on every system start, and the fact that its default initial data file size is 8 MB (unless it is configured and tweaked differently per user’s needs), it is easy to review and monitor database files statistics by using the query above.

If needed, as for any other regular database, users can monitor indexes contained in the tempdb database.

Monitor the specific tempdb objects space usage

Monitoring the specific tempdb objects space usage is crucial to understanding the performance and space usage of the tempdb database. Here are some detailed instructions and queries to help you monitor specific tempdb objects space usage:

Understanding Tempdb Objects:

Tempdb objects can be categorised into two types: internal objects and user objects.

• Internal objects: These are created by SQL Server to process queries. Examples include hash joins, sorts spilling into tempdb, and spool operations.
• User objects: These are created by users, such as temporary tables, table variables, and the results of table-valued functions.

Monitoring Queries:

To monitor the specific tempdb objects space usage, you can use the following queries:

Monitoring Internal and User Objects:

Sql

SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],

SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)],

SUM(user_object_reserved_page_count)*8 AS [User objects (KB)],

SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]

FROM sys.dm_db_file_space_usage

-database_id '2' represents tempdb

WHERE database_id = 2;

Monitoring Temporary Tables:

Sql

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;

Additional Notes:

• The above queries will help you monitor the space usage of specific tempdb objects, including internal objects, user objects, and temporary tables.
• Keep in mind that temporary objects will be removed upon SQL Server service or machine restart, so you can only retrieve information about them during the active user session.

Monitor temporary tables space usage

Monitoring the space usage of temporary tables in SQL Server is crucial to ensure optimal performance and prevent issues caused by excessive TempDB growth. Here are some detailed instructions and queries to help you monitor temporary tables space usage:

Understanding Temporary Tables and TempDB:

Temporary tables are created by users and stored in the TempDB database, which is a system database in SQL Server. TempDB is used to temporarily store data for various activities, such as when a user creates a temporary table or declares a table variable. It is also utilised by the database engine for internal activities.

Monitoring Temporary Tables Space Usage:

To monitor the space usage of temporary tables, you can use the following queries:

Query 1:

Sql

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;

This query will provide information about the name of the temporary table, the number of rows it contains, the used space in kilobytes, and the reserved space in kilobytes.

Query 2:

Sql

SELECT SS.session_id,

CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) AS [Total Allocation User Objects MB],

CAST((SS.user_objects_alloc_page_count - SS.user_objects_dealloc_page_count) / 128 AS DECIMAL(15, 2)) AS [Net Allocation User Objects MB]

FROM sys.dm_db_session_space_usage SS;

This query calculates the total and net allocation of user objects (such as temporary tables) for each session. The number of pages allocated and deallocated by each session is divided by 128 to get the space allocation in megabytes, as each page is 8 kilobytes in size.

Query 3:

Sql

SELECT TS.session_id,

CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) AS [Total Allocation User Objects MB],

CAST((TS.user_objects_alloc_page_count - TS.user_objects_dealloc_page_count) / 128 AS DECIMAL(15, 2)) AS [Net Allocation User Objects MB]

FROM sys.dm_db_task_space_usage TS;

Similar to the previous query, this query calculates the total and net allocation of user objects for each task within a session. This can help identify which tasks are utilising more space in temporary tables.

Best Practices and Recommendations:

• Monitor TempDB growth regularly to prevent performance issues.
• Preallocate space for TempDB files to prevent frequent autogrow events that can impact performance.
• Set reasonable growth increments for TempDB files to avoid excessive autogrow events.
• Place TempDB on a fast I/O subsystem and separate disks from user databases to improve performance.
• Optimise TempDB metadata by using memory-optimised TempDB metadata, available in SQL Server 2019 and later.

Monitor the page allocation or deallocation activity in tempdb

To monitor the page allocation or deallocation activity in TempDB at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space.

Sql

SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id;

This query will return the session ID, the total number of pages allocated for internal objects, and the total number of pages deallocated for internal objects for each session.

You can also obtain the space consumed by internal objects in the current session, for both running and completed tasks, using the following query:

Sql

SELECT R2.session_id, R1.internal_objects_alloc_page_count + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count, R1.internal_objects_dealloc_page_count + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count FROM sys.dm_db_session_space_usage AS R1 INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id GROUP BY R2.session_id, R1.internal_objects_alloc_page_count, R1.internal_objects_dealloc_page_count;

This query joins the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage views to provide a summary of the space consumed by internal objects for the current session, including both running and completed tasks.

It is important to monitor TempDB usage as it can affect the performance of a SQL Server instance. If TempDB runs out of disk space, it can cause significant disruptions and prevent applications from completing operations. By using the dynamic management views and queries provided, you can proactively monitor and manage TempDB usage to ensure optimal performance.

Monitor tempdb growth

Monitoring the growth of your tempdb is crucial to ensure the optimal performance of your SQL Server. The tempdb is a shared resource available to all users connected to a single SQL Server instance and is used to hold temporary user objects, query results, and other objects created by the SQL Server Database Engine.

Using Dynamic Management Views (DMVs)

DMVs provide detailed information about the SQL Server's activities. To monitor tempdb growth, you can use the following DMVs:

• Sys.dm_db_file_space_usage: This DMV helps monitor space usage information about the SQL Server database. It returns information about the space usage of the database files, including the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.
• Sys.dm_db_session_space_usage: This DMV tracks the allocated and deallocated number of pages by the session level in the SQL Server tempdb. It helps identify problematic sessions that are consuming a lot of space.
• Sys.dm_db_task_space_usage: This DMV returns information about the number of pages allocated and deallocated by each task of a parallel query. It is useful when you want to track space usage by task within a session.

Using Extended Events

Extended Events can be used to track tempdb growth by capturing specific events and providing detailed information about them. For example, you can use the "sqlserver.database_file_size_change" and "sqlserver.databases_log_file_used_size_changed" events to monitor changes in the size of the database files and log files, respectively.

Using Third-Party Tools

Third-party tools like IDERA SQL Diagnostic Manager offer a monitoring solution with charts, views, and alerts specifically for tempdb. These tools provide visual representations of tempdb usage, such as the Tempdb Space Used chart and the Tempdb Contention chart, which help you identify potential issues and take necessary actions.

Using Query Store

The Query Store feature in SQL Server collects various metrics about queries and query plans. One of the metrics it tracks is "Temp Db Memory (KB) used," which provides information about the queries that generate workloads on the SQL Server tempdb database.

By regularly monitoring tempdb growth and taking appropriate actions, you can prevent performance issues and ensure the smooth operation of your SQL Server environment.

Frequently asked questions