SQL Server TempDB Monitoring by Using Dynamic Management Views (DMV)

What is TempDB in MS SQL Server?

TempDB is a system database in Microsoft SQL Server used as a store of internal objects, row versions, work tables, temporary tables, and indexes. TempDB is available for use to all participants connected to a SQL Server instance (it is a global resource). For those familiar with other database flavors, the tempDB database is similar to the TEMP tablespace in Oracle. To put it mildly, anything that cannot fit your instance memory spills over to the tempdb data files.

TempDB is typically used in the following cases:

1. Temporary tables are created with the # naming convention. I have seen one or two applications in my shop that create a whole lot of these conventions. As a result, it may affect performance. For each temporary table, TempDB distributes pages whose size depends on the table size. In addition, there may be some conflicts if several concurrent sessions are creating such TempTables simultaneously.
2. The READ_COMMITTED_SNAPSHOT isolation level is enabled. We had to do this on two of our instances a few years ago because there were two applications that were experiencing a serious number of deadlocks. Using the snapshot isolation levels is one way to deal with deadlocks particularly if you do not want to go back and forth with developers on proper coding. You should be aware that this is expected to help improve performance. However, it uses optimistic concurrency control which may not be desirable in all cases with respect to data integrity.
3. Indexes are built or rebuilt with the SORT_IN_TEMPDB=ON option. It tends to remove the burden of sorting from the database which owns the index while the rebuilding process is ongoing. It may help to examine the total impact of this option on the instance performance as a whole.
4. The Multiple Active Result Sets (MARS) functionality is enabled. No, MARS is not a planet in this scenario, it is a feature that allows an application to submit several batches via a single connection. It is disabled by default and can be explicitly enabled by including MultipleActiveResultSets=True into a connection string.
5. Internally, SQL Server also used TempDB to create work tables which are commonly used in cursor operations – calls by the GROUP BY, ORDER BY, or UNION clauses. Work tables can be thought of as temporary tables created by the SQL Server engine activity rather than by a user activity directly.

Relevant Dynamic Management Views SQL Server

The following relevant dynamic management views (DMVs) are helpful when investigating the activity in TempDB:

1. sys.dm_db_file_space_usage: This DMV returns some information about the space usage of files in the databases you are interested in. It can be used to examine any database in the instance and the output pertains only to that database. In the context of this article, we shall be using the DMV to examine TempDB.
2. sys.dm_db_session_space_usage: This DMV is exclusive to the TempDB database and returns the number of pages allocated and deallocated by each session for a given database. The page allocations are typically maintained till the session is terminated.
3. sys.dm_db_task_space_usage: This DMV is also exclusive to the TempDB database and provides some information about the number of pages allocated and deallocated by each task for a given database.
4. sys.dm_tran_active_snapshot_database_transactions: This DMV returns the active transactions that generate and may access row versions. This view is relevant when options such as ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled.
5. sys.dm_tran_version_store: This DMV provides some information about all version records in the version store. In an active production server, the records in this table could grow significantly. Thus, we need to be careful when querying the DMV.

Examing the DMV Commands Before Working with TempDB Database

sys.dm_file_space_usage

We can obtain some descriptions of these DMVs from Microsoft documentation.

Table 1 shows the description of sys.dm_file_space_usage. The query in Listing 1 displays the file space usage for TempDB and the WideWorldImporters databases respectively. Pictures 1 and 2 return the outputs of this query with both TempDB and the WideWorldImporters databases respectively.

[table id=44 /]

Table 1: description of sys.dm_file_space_usage

-- List 1: Check space usage on database files
-- Check space usage on tempdb
use tempdb
go
select db_name(database_id) [Database_name]
,file_name(file_id) [File_name]
,filegroup_id
,total_page_count [TPC]
,total_page_count*8/1024 [TSU (MB)]
,allocated_extent_page_count [AEPC]
,allocated_extent_page_count*8/1024 [AEPC (MB)]
,version_store_reserved_page_count [VSRP]
,version_store_reserved_page_count*8/1024 [VSRP (MB)]
,user_object_reserved_page_count [UORP]
,user_object_reserved_page_count*8/1024 [UORPC (MB)]
,internal_object_reserved_page_count [IORPC]
,internal_object_reserved_page_count*8/1024 [UORPC (MB)]
,mixed_extent_page_count [MEPC]
,mixed_extent_page_count*8/1024 [MEPC (MB)]
from sys.dm_db_file_space_usage;
-- Check space usage on WideWorldImporters
use WideWorldImporters
go
select db_name(database_id) [Database_name]
,file_name(file_id) [File_name]
,filegroup_id
,total_page_count [TPC]
,total_page_count*8/1024 [TSU (MB)]
,allocated_extent_page_count [AEPC]
,allocated_extent_page_count*8/1024 [AEPC (MB)]
,version_store_reserved_page_count [VSRP]
,version_store_reserved_page_count*8/1024 [VSRP (MB)]
,user_object_reserved_page_count [UORP]
,user_object_reserved_page_count*8/1024 [UORPC (MB)]
,internal_object_reserved_page_count [IORPC]
,internal_object_reserved_page_count*8/1024 [UORPC (MB)]
,mixed_extent_page_count [MEPC]
,mixed_extent_page_count*8/1024 [MEPC (MB)]
from sys.dm_db_file_space_usage;

Fig. 2 Output of Query on sys.dm_file_space_usage (WideWorldImporters)

Note that the total_page_count column shows the exact size of the database file in question. In addition, the LOG and FILESTREAM file types are not displayed. Also, as expected, allocated_extent_page_count matches the used space in the WWI_UserData data file.

Fig. 3 File Sizes on the WideWorldImporters Database

sys.dm_db_session_space_usage

Table 2 shows the usage of the sys.dm_db_session_space_usage DMV. Listing 2 is the output of the query. Note that the number of rows returned matches the current number of sessions (active or inactive) in the instance. Also, recall that this DMV pertains ONLY to TempDB.

[table id=45 /]

Table 2: description of sys.dm_db_session_space_usage

-- Listing 2: Check space allocation per session in the instance
-- Applies on to tempdb database
select
session_id
,db_name(database_id) [Database_name]
,user_objects_alloc_page_count [UOAPC]
,user_objects_alloc_page_count*8/1024 [UOAPC (MB)]
,user_objects_dealloc_page_count [UODPC]
,user_objects_dealloc_page_count*8/1024 [UODPC (MB)]
,internal_objects_alloc_page_count [UOAPC]
,internal_objects_alloc_page_count*8/1024 [UOAPC (MB)]
,internal_objects_dealloc_page_count [UODPC]
,internal_objects_dealloc_page_count*8/1024 [UODPC (MB)]
from sys.dm_db_session_space_usage;
select count(*) from sys.dm_exec_sessions;

sys.dm_db_task_space_usage

Table 3 shows the implementation of the sys.dm_db_task_space_usage DMV. Listing 3 displays its output.

[table id=46 /]

Table 3: description of sys.dm_db_task_space_usage

-- List 3: Check space allocation per task int the instance
-- Applies on to tempdb database
select
task_address
,is_remote_task
,session_id
,request_id
,exec_context_id
,db_name(database_id) [Database_name]
,user_objects_alloc_page_count [UOAPC]
,user_objects_alloc_page_count*8/1024 [UOAPC (MB)]
,user_objects_dealloc_page_count [UODPC]
,user_objects_dealloc_page_count*8/1024 [UODPC (MB)]
,internal_objects_alloc_page_count [UOAPC]
,internal_objects_alloc_page_count*8/1024 [UOAPC (MB)]
,internal_objects_dealloc_page_count [UODPC]
,internal_objects_dealloc_page_count*8/1024 [UODPC (MB)]
from sys.dm_db_task_space_usage;

Other DMV Queries in MSSQL TempDB

The other two DMVs seem to be empty before any database activity is executed. Tables 4 and 5 show a description of both tables. In the next section, we will take a look at how data in the DMVs changes when we generate database activity.

[table id=47 /]

Table 4: description of sys.dm_tran_active_snapshot_database_transactions

[table id=48 /]

Table 5: description of sys.dm_tran_version_store

Examining the DMV After Database Activity is Completed

Let’s generate some activity.

To do this, run the simple queries in listing 4.

-- Listing 4
-- Query 1
use WideWorldImporters
go
select * from
[Sales].[Invoices];
-- Query 2
use WideWorldImporters
go
select * from
[Sales].[Invoices]
order by ContactPersonID;

As you can see, there are no significant allocations or deallocations from this small query so we raise the bar by creating a temporary table using the script in Listing 5.

-- Listing 5
use WideWorldImporters
go
select *
into [#Invoices]
from [Sales].[Invoices];
use tempdb
go
drop table [#Invoices];

We now get a significant number of pages allocated and reserved for our active session 62 (see Fig. 8). Also, pay attention to the significant number of allocated extents and reserved pages shown in sys.dm_db_file_space_usage (Fig. 9).

When we go ahead and drop the Temptable #Invoice, we see that the pages allocated in TempDB are released as shown in Fig. 12. However, the output of the dm_db_session_space_usage DMV still indicates that some pages are allocated to the session. We see that this is released as soon as we close the session (session ID 62).

Next, we open another session and create an index using the queries in Listing 6. In Query 1 excluding the SORT_IN_TEMPDB option and in Query 2, we use this option after dropping the index. No significant allocations happen for the first case but we see some changes while using SORT_IN_TEMPDB. This change is very slight given the size of the index in question (see Fig. 16 and 17).

We shall perform the final test. We find out that till this moment the DMVs such as sys.dm_tran_active_snapshot_database_transactions and sys.dm_tran_version_store return no rows when queried.

Let’s enable the SNAPSHOT_ISOLATION and the READ_COMMITTED_SNAPSHOT isolation levels (Listing 7). Once done, a single update statement creates a record in the sys.dm_tran_version_store DMV. Putting this same query is a transaction we use to see that a record is also generated in the sys.dm_tran_active_snapshot_database_transactions during the transaction.

-- Listing 7
ALTER DATABASE WideWorldImporters
SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE WideWorldImporters
SET READ_COMMITTED_SNAPSHOT ON;

-- Listing 8
-- Query 1
 update [WideWorldImporters].[Sales].[Invoices]
 set DeliveryInstructions='Shop 50, 1476 Poddar Lane'
 where InvoiceID=48;
-- Query 2
-- Transaction
BEGIN TRAN
 update [WideWorldImporters].[Sales].[Invoices]
 set DeliveryInstructions='Shop 50, 1476 Poddar Lane'
 where InvoiceID=48;
 WAITFOR DELAY '00:00:30';
COMMIT TRAN;
GO

Conclusion

We have briefly explored that we can monitor TempDB activity using five key DMVs exposed by SQL Server. When using this level of the monitoring process in a production environment, we can determine whether we need more space in TempDB and data files. Also, we can analyze the impact of the SNAPSHOT isolation if we have enabled this feature before.

The output data of these DMVs could also be helpful in clearly showing application developers the impact of their code behavior on tempDB and the instance as a whole. Overall, it is quite rewarding to understand the use of these DMVs as a DBA concerned with performance and space management.

References

Snapshot Isolation in SQL Server
Using Multiple Active Result Sets
Description of sys.dm_tran_version_store
Description of sys.dm_db_task_space_usage
Description of sys.dm_file_space_usage
Description of sys.dm_db_session_space_usage
Description of sys.dm_tran_active_snapshot_database_transactions