Monitoring SQL Server TempDB with Dynamic Management Views

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.

SELECT

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

,(SUM(version_store_reserved_page_count)*1.0/128) AS [Used Space by VersionStore(MB)]

,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Used Space by InternalObjects(MB)]

,(SUM(user_object_reserved_page_count)*1.0/128) AS [Used Space by UserObjects(MB)]

FROM tempdb.sys.dm_db_file_space_usage;

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.

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.

Tip: Enabling the Accelerated Database Recovery (ADR)feature in SQL Server 2019 will change this behavior and row versions are kept in the database filegroup

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.

SELECT TOP 150 SO.AccountNumber FROM

Sales.SalesOrderHeader SO

INNER JOIN Sales.SalesOrderDetailEnlarged SD

ON SO.ModifiedDate = SD.ModifiedDate

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.

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

SELECT

Sh.AccountNumber ,SUM(Sh.SubTotal),Sd.CarrierTrackingNumber

FROM Sales.SalesOrderHeader Sh

INNER JOIN Sales.SalesOrderDetail Sd

ON Sd.SalesOrderID =Sh.SalesOrderID

CROSS APPLY

(

SELECT MAX(th.Quantity)

FROM Production.TransactionHistory AS Th

WHERE

TH.ModifiedDate = Sd.ModifiedDate

GROUP BY ()

) AS th (Quantity)

GROUP BY Sh.AccountNumber ,Sd.CarrierTrackingNumber

ORDER BY Sd.CarrierTrackingNumber

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.

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.

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

The output of this event will be as follows:

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

1 2	select * from sys.dm_db_session_space_usage where session_id =126

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:

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.

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

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.

CREATE TABLE #TempTest

(CNumber VARCHAR(100))

INSERT INTO #TempTest WITH(TABLOCK)

SELECT CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged

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

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.

Author
Recent Posts

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