Monitoring TempDB Using Key Dynamic Management Views

What is TempDB?

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

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.

Examining the DMVs Before Working with 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.

Column nameData
type
Description
database_idsmallint Database ID.
file_idsmallintFile ID.
file_id maps to file_id in sys.dm_io_virtual_file_stats and to fileid in sys.sysfiles.
filegroup_id smallint AApplies to: SQL Server 2012 (11.x) through SQL Server 2017.
Filegroup ID.
total_page_countbigintApplies to: SQL Server 2012 (11.x) through SQL Server 2017.
Total number of pages in the file.
allocated_extent_page_countbigintApplies to: SQL Server 2012 (11.x) through SQL Server 2017.
Total number of pages in the allocated extents in the file.
unallocated_extent_page_countbigintTotal number of pages in the unallocated extents in the file.
Unused pages in allocated extents are not included.
version_store_reserved_page_count bigintTotal number of pages in the uniform extents allocated for the version
store. Version store pages are never allocated from mixed extents.
IAM pages are not included, because they are always allocated from
mixed extents. PFS pages are included if they are allocated from a uniform
extent.
For more information, see sys.dm_tran_version_store (Transact-SQL)
user_object_reserved_page_countbigintTotal number of pages allocated from uniform extents for user objects in
the database. Unused pages from an allocated extent are included in the
count.
IAM pages are not included, because they are always allocated from
mixed extents. PFS pages are included if they are allocated from a uniform
extent.
You can use the total_pages column in the sys.allocation_units catalogview to return the reserved page count of each allocation unit in the userobject. However, note that the total_pages column includes IAM pages.
nternal_object_reserved_page_countbigintTotal number of pages in uniform extents allocated for internal objects in
the file. Unused pages from an allocated extent are included in the count.
IAM pages are not included, because they are always allocated from
mixed extents. PFS pages are included if they are allocated from a uniform
extent.
There is no catalog view or dynamic management object that returns the
page count of each internal object.
mixed_extent_page_countbigintTotal number of allocated and unallocated pages in allocated mixed
extents in the file. Mixed extents contain pages allocated to different
objects. This count does include all the IAM pages in the file.
modified_extent_page_count bigintBeginning with: SQL Server 2017 (14.x)
Total number of pages modified in allocated extents of the file since last
full database backup.The modified page count can be used to track
differential changes in the database since last full backup to decide if
differential backup is beneficial.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse
The identifier for the node that this distribution is on.
distribution_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse
The unique numeric id associated with the distribution.

Table 1: description of sys.dm_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.

Column nameData typeDescription
session_idsmallintSession ID.
session_id maps to session_id in sys.dm_exec_sessions.
database_idsmallintDatabase ID.
user_objects_alloc_page_countbigintNumber of pages reserved or allocated for user objects by
this session.
user_objects_dealloc_page_countbigintNumber of pages deallocated and no longer reserved for
user objects by this session.
internal_objects_alloc_page_countbigintNumber of pages reserved or allocated for internal objects
by this session.
internal_objects_dealloc_page_countbigintNumber of pages deallocated and no longer reserved for
internal objects by this session.
user_objects_deferred_dealloc_page_count bigintNumber of pages which have been marked for deferred
deallocation.
Note: Introduced in service packs for SQL Server 2012 (11.x)
and SQL Server 2014 (12.x).
pdw_node_id intApplies to: Azure SQL Data Warehouse, Parallel Data
Warehouse
The identifier for the node that this distribution is on.

Table 2: description of sys.dm_db_session_space_usage

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.

Column nameData
type
Description
session_idsmallintSession ID.
request_idintRequest ID within the session.
A request is also called a batch and may contain one or more
queries. A session may have multiple requests active at the same time.
Each query in the request may start multiple threads (tasks), if a parallel
execution plan is used.
exec_context_idintExecution context ID of the task. For more information, see sys.dm_os_tasks (Transact-SQL).
database_idsmallintDatabase ID.
user_objects_alloc_page_countbigintNumber of pages reserved or allocated for user objects by this task.
user_objects_dealloc_page_countbigintNumber of pages deallocated and no longer reserved for user
objects by this task.
internal_objects_alloc_page_countbigintNumber of pages reserved or allocated for internal objects by this
task.
internal_objects_dealloc_page_countbigintNumber of pages deallocated and no longer reserved for internal
objects by this task.
pdw_node_id intApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse
The identifier for the node that this distribution is on.

Table 3: description of sys.dm_db_task_space_usage

Other DMVs

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.

Column name Data
type
Description
transaction_idbigintUnique identification number assigned for the transaction. The transaction
ID is primarily used to identify the transaction in locking operations.
transaction_sequence_numbigintTransaction sequence number. This is a unique sequence number that is
assigned to a transaction when it starts. Transactions that do not generate
version records and do not use snapshot scans will not receive a transaction
sequence number.
commit_sequence_numbigintSequence number that indicates when the transaction finishes (commits or
stops). For active transactions, the value is NULL.
is_snapshotint0 = Is not a snapshot isolation transaction.
1 = Is a snapshot isolation transaction.
session_idintID of the session that started the transaction.
first_snapshot_sequence_num bigintLowest transaction sequence number of the transactions that were active
when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For non-snapshot
transactions, this column shows 0.
max_version_chain_traversedintMaximum length of the version chain that is traversed to find the
transactionally consistent version.
average_version_chain_traversedrealAverage number of row versions in the version chains that are traversed.
elapsed_time_secondsbigintElapsed time since the transaction obtained its transaction sequence
number.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse
The identifier for the node that this distribution is on.

Table 4: description of sys.dm_tran_active_snapshot_database_transactions

Column nameData typeDescription
transaction_sequence_numbigintSequence number of the transaction that generates the record
version.
version_sequence_numbigintVersion record sequence number. This value is unique within the
version-generating transaction.
database_idintDatabase ID of the versioned record.
rowset_idbigintRowset ID of the record.
statustinyintIndicates whether a versioned record has been split across two
records. If the value is 0, the record is stored in one page. If the
value is 1, the record is split into two records that are stored on
two different pages.
min_length_in_bytessmallintMinimum length of the record in bytes.
record_length_first_part_in_bytessmallintLength of the first part of the versioned record in bytes.
record_image_first_partvarbinary(8000)Binary image of the first part of version record.
record_length_second_part_in_bytessmallintLength of the second part of version record in bytes.
record_image_second_part varbinary(8000)Binary image of the second part of the version record.

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.

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.

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.

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

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.