Esat Erkec
Find the tempdb memory usage through the query store

Monitoring SQL Server TempDB with Dynamic Management Views

December 29, 2021 by

In this article, we will learn how we can detect which operations cause to fill up SQL Server tempdb through the dynamic management views.

What is SQL Server tempdb used for?

Tempdb is one of the system databases, that is used by many activities in SQL Server to temporarily store data. Such as when a user creates a temporary table or declares a table variable, the data contained by these tables will be stored in the SQL Server tempdb database. At the same time, tempdb can be used for various internal activities by the database engine. The following list shows the most known operations that use the tempdb database.

  • Common table expressions (CTE)
  • Temporary tables and table variables
  • Hash Joins
  • Triggers
  • Cursors
  • GROUP BY and ORDER BY statements
  • Cursors
  • Online indexing
  • Snapshot isolation levels

As it is seen, tempdb takes on the various responsibilities of crucial functions that may affect the performance of the database engine. On the other hand, in poorly managed SQL database systems, the tempdb database starts to suddenly grow and this issue may cause a chaotical crisis if the target problem is not detected exactly. Now let’s learn a method that helps to detect which operation fills up the tempdb database.

First Look : sys.dm_db_file_space_usage

Dynamic management views (DMVs) and functions (DMFs) are used to obtain detailed information about the SQL Server’s activities. The sys.dm_db_file_space_usage is one of the DMV’s that helps to monitor the space usage information about the SQL Server database. This view returns information about the space usage of the database files. The main characteristic of this view is to return the point-in-time data which provides the current usage. The following query returns four important space usage information about the tempdb database.

Finding SQL Server tempdb database available size

Free Space

The unallocated space reports the available space in the tempdb database. At the same time, this value can be seen on the properties of the tempdb database.

Tempdb database properties

Used Space by VersionStore

Row-versioning isolation levels allow us to overcome the conflict problems between the read and write operations. In these isolation levels, the working principle is based on storing the previously committed row version in the tempdb. The version_store_reserved_page_count indicates the total number of pages that are allocated for the version store.

Used Space by Internal Objects

SQL Server uses the SQL Server tempdb database to temporarily store some data during the execution of a query to fulfill some internal activities. Such as, table spool operators generate a copy of the input data during the execution of the statement and this input data is stored in the SQL Server tempdb. The following query will allocate some space in the tempdb database because of the table spool operator.

SQL Server table spool

Tempdb space usage

The space usage of the tempdb database because of the internal operations is shown in the internal_object_reserved_page_count column.

Used Space by UserObjects

As a last, the user_object_reserved_page_count column indicates how many pages are allocated when we use the table variables, temporary tables, and similar user objects.

Tracking the SQL Server TempDB space usage by the session level

The dm_db_session_space_usage is another DMV that can be used to track the allocated and deallocated number of the pages by the session level in the SQL Server tempdb. With the help of this useful DMV, we can find out how many pages are allocated and deallocated by the sessions until the session is closed. The main advantage of this view directly points to the problematic session. Now let’s go through details of this view usage on an example query. Firstly, we will enable IO statistics of the session and then enable the actual execution plan.

Enable the actual execution plan

As a second step, we will execute the query and click the message tab and start to analyze IO statistics.

In the output text of the IO statistics, we are seeing WorkTable and WorkFile table names. These two tables are generated in the tempdb during the execution of the query when the SQL Server requires to store some data into tempdb temporarily.

What is worktable and workfile in SQL Server

In our example, we see that 524 pages have been read from the tempdb database. At this point, a new question appears in our mind “What is the reason for this read operation in the tempdb database?”. This question answer is hidden in the execution plan of the query.

SQL execution plan

As seen in the execution plan, there is a warning sign on the sort operator and when we hover over this operator with the mouse, we can figure out something about the reason for this warning sign. The Warnings section gives us extensive detail on the subject and our issue is related to the tempdb spill.

TempDB Spill: The query optimizer estimates how much memory is needed to execute a query and then that requested memory is allocated to that query. However, the inaccurate estimations cause fewer memory requests than the actual need. In this type of case, SQL Server notices that it requires more memory than it was granted that’s why it decides to use the tempdb database. This mechanism’s main disadvantage is using a disk resource is always slower than using the buffer memory so it affects the query performance negatively.

  • Tip: We can monitor the tempdb spills to use to Extended Events sort_warning and hash_warning events. This event captures when a sort or a hash join operations performs a tempdb spill

SQL extended event hash_warning and sort_warning

The output of this event will be as follows:

SQL Server extended event sample

We can monitor this internal SQL Server tempdb usage with help of the dm_db_session_space_usage.

How to use sys.dm_db_session_space_usage

Query store gathers various metrics about the queries and query plans and offers this data with different reports to users. The Top Resource Consuming Queries displays the queries that consume the most resources. We can organize this report according to different metrics:

Query store Top Resource Consuming Queries

Temp Db Memory (KB) used is as one of the metrics where we can get information about the queries that generate workloads on the SQL Server tempdb database. By default, this report is shown in a chart view but we can change it to the grid view.

Find the tempdb memory usage through the query store

To click the grid icon will change the view of the report.

Result of the Top Resource Consuming Queries

The avg temp db memory used column shows, how much memory a query consumes on the SQL Server tempdb database. Our sample query consumes 4352 KB memory and this value can be corrected to use the dm_db_session_space_usage view. This view shows the 544 number of was allocated for this query and the one-page size is 8kb. So when we cross (544*8=4352) these two values we can find the total temp db memory used value.

  • Tip: We don’t see the warning sign on the sort operator of the execution plan because the query store shows the estimated execution plans and tempdb spill issues that occur during the execution of a query

Tracking the SQL Server TempDB space usage by the task level

SQL Server can perform parallel insert operations for the heap tables and this feature is valid for the temporary tables. On the other hand, sys.dm_db_task_space_usage return information about the number of pages allocated and deallocated by each task of a parallel query. For example, when we look at the following query estimated query plan, we see that optimizer decides a parallel query plan.

SQL Server parallel execution plan and tempdb usage

During the execution of this query, sys.dm_db_task_space_usage shows how many pages are allocated by each task.

Result of the sys.dm_db_task_space_usage

Conclusion

SQL Server tempdb is one of the critical system databases that can affect database performance. Knowing which DMVs can help monitor the tempdb database will help resolve any tempdb-related space usage issues.

Esat Erkec
Monitoring, Performance

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

1,678 Views