Tempdb is a critical resource in SQL Server. It is used internally by the database engine for many operations, and it might consume a lot of disk space.
In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.
Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb.
There are 3 types of objects stored in tempdb:
User Objects
Internal Objects
Version Stores
A user object can be a temporary table, a table variable or a table returned by a table-valued function. It can also be a regular table created in the tempdb database. A common misconception is that table variables (@) do not consume space in tempdb, as opposed to temporary tables (#), because they are only stored in memory. This is not true. But there are two important differences between temporary tables and table variables, when it comes to space usage:
Indexes and statistics on temporary tables also consume space in tempdb, while indexes and statistics on table variables don’t. This is simply because you cannot create indexes or statistics on table variables. Well, you can create indexes as part of the table declaration, but this is not common.
The scope of a temporary table is the session in which it has been created, while the scope of a table variable is the batch in which it has been created. This means that a temporary table consumes space in tempdb as long as the session is still open (or until the table is explicitly dropped), while a table variable’s space in tempdb is deallocated as soon as the batch is ended.
Internal objects are created and managed by SQL Server internally. Their data or metadata cannot be accessed. Here are some examples of internal objects in tempdb:
Query Intermediate Results for Hash Operations
Sort Intermediate Results
Contents of LOB Data Types
Query Result of a Static Cursor
Unlike user objects, operations on internal objects in tempdb are not logged, since they do not need to be rolled back. But internal objects do consume space in tempdb. Each internal object occupies at least 9 pages (one IAM page and 8 data pages). Tempdb can grow substantially due to internal objects when queries that process large amounts of data are executed on the instance, depending on the nature of the queries.
Version stores are used for storing row versions generated by transactions in any database on the instance. The row versions are required by features such as snapshot isolation, after triggers and online index build. Only when row versioning is required, the row versions will be stored in tempdb. As long as there are row versions to be stored, a new version store is created in tempdb approximately every minute. These version stores are similar to internal objects in many ways. Their data and metadata cannot be accessed, and operations on them are not logged. The difference is, of-course, the data that is stored in them.
When a transaction that needs to store row versions begins, it stores its row versions in the current version store (the one that has been created in the last minute). This transaction will continue to store row versions in the same version store as long as it’s running, even if it will run for 10 minutes. So the size of each version store is determined by the number and duration of transactions that began in the relevant minute, and also by the amount of data modified by those transactions.
Version stores that are not needed anymore are deallocated periodically by a background process. This process deallocates complete version stores, not individual row versions. So, in some cases, it might take a while till some version store is deallocated. There are two types of version stores. One type is used to store row versions for tables that undergo online index build operations. The second type is used for all other scenarios.
There are 3 dynamic management views, which make the task of troubleshooting tempdb space usage quite easy.
The views are:
All 3 views return a column named “database_id”, so you might think that they return information for all the databases in the instance, right? Wrong!
The first view returns information about the current database only, whether it's tempdb or any other database. The other two return information for the tempdb database only, no matter from which database you reference them, so the value returned in the "database_id" column is always “2” (the database ID of tempdb). We will focus only on tempdb, anyway.
The first view (sys.dm_db_file_space_usage) returns space usage information for each data file in tempdb. It gives a high level distribution of the space occupied by tempdb. For example, when I ran the following query on my local instance of SQL Server 2019:
SELECT
*
FROM
sys.dm_db_file_space_usage;I received the following results:
As you can see, I have 8 data files for tempdb, each has 1,024 pages of 8 KB each, or a total size of 8 MB. For example, the first file contains 352 allocated pages and 672 unallocated pages, and out of the 352 allocated pages, 144 pages (in uniform extents) are reserved for user objects. The rest of the pages (216) are allocated from mixed extents.
In the last file (#9), for example, we can see that a single extent (8 pages) is allocated to user objects, and another extent is allocated to internal objects. All other extents are unallocated. We can also see that no version stores exist in tempdb.
When tempdb becomes unreasonably large, the first thing you will want to do is to figure out whether space is mainly occupied by user objects, internal objects or version stores. This view will give you the answer, plain and simple.
If you found out that the problem is related to user objects or internal objects, then the next step in troubleshooting will be to figure out who is occupying the space for these objects in tempdb. To answer this question, you will use the two other views mentioned above (sys.dm_db_session_space_usage and sys.dm_db_task_space_usage).
The first view returns the number of pages allocated and deallocated for user objects and internal objects by each session. The second view returns the same information for currently running tasks. For example, I created a new connection with session ID = 52. So far this session hasn’t performed any work, so the following query:
SELECT
*
FROM
sys.dm_db_session_space_usage
WHERE
session_id = 52;Returned the following results:
Similarly, the following query:
SELECT
*
FROM
sys.dm_db_task_space_usage
WHERE
session_id = 52; Returned the following results:
Now, let’s create a temporary table in session 52 and insert a single row:
CREATE TABLE
#TempTable
(
Col1 INT NULL ,
Col2 NCHAR(4000) NULL
);
GO
INSERT INTO
#TempTable
(
Col1 ,
Col2
)
VALUES
(
1 ,
REPLICATE (N'X' , 4000)
);
GO The INSERT statement above will allocate an extent in tempdb for a user object (#TempTable). So if we check sys.dm_db_session_space_usage again, we’ll see that the value of “user_objects_alloc_page_count” has increased from 0 to 8.
Note:
if you are using an older version of SQL Server without trace flag 1118, then you might see allocations from mixed extents rather than uniform extents. In this case, you might see a value of 1 instead of 8 in “user_objects_alloc_page_count”. In the same way we will see allocations and deallocations of pages in tempdb for internal objects on a session level.
The space consumed by a currently running task is returned in sys.dm_db_task_space_usage, but not in sys.dm_db_session_space_usage. As soon as the task is completed, the information associated with it is accumulated to the data returned by sys.dm_db_session_space_usage.
So if you want to find the distribution of allocations and deallocations of pages in each session, including currently running tasks, use the following query:
SELECT
SessionId = SessionSpaceUsage.session_id ,
UserObjectsAllocPageCount = SessionSpaceUsage.user_objects_alloc_page_count + SUM (TaskSpaceUsage.user_objects_alloc_page_count) ,
UserObjectsDeallocPageCount = SessionSpaceUsage.user_objects_dealloc_page_count + SUM (TaskSpaceUsage.user_objects_dealloc_page_count) ,
InternalObjectsAllocPageCount = SessionSpaceUsage.internal_objects_alloc_page_count + SUM (TaskSpaceUsage.internal_objects_alloc_page_count) ,
InternalObjectsDeallocPageCount = SessionSpaceUsage.internal_objects_dealloc_page_count + SUM (TaskSpaceUsage.internal_objects_dealloc_page_count)
FROM
sys.dm_db_session_space_usage AS SessionSpaceUsage
INNER JOIN
sys.dm_db_task_space_usage AS TaskSpaceUsage
ON
SessionSpaceUsage.session_id = TaskSpaceUsage.session_id
GROUP BY
SessionSpaceUsage.session_id ,
SessionSpaceUsage.user_objects_alloc_page_count ,
SessionSpaceUsage.user_objects_dealloc_page_count ,
SessionSpaceUsage.internal_objects_alloc_page_count ,
SessionSpaceUsage.internal_objects_dealloc_page_count
ORDER BY
SessionId ASC;
Note that the page counts are accumulative since the session was last created. So it might be more useful to take two snapshots and find the difference between them in order to monitor the rate of page allocations and deallocations per session.
If there is a currently running large query that consumes a lot of space in tempdb due to internal objects, you can use the following query in order to return the batch text and execution plan currently performed by the offending query:
SELECT
SessionId = TasksSpaceUsage.SessionId ,
RequestId = TasksSpaceUsage.RequestId ,
InternalObjectsAllocPageCount = TasksSpaceUsage.InternalObjectsAllocPageCount ,
InternalObjectsDeallocPageCount = TasksSpaceUsage.InternalObjectsDeallocPageCount ,
RequestText = RequestsText.text ,
RequestPlan = RequestsPlan.query_plan
FROM
(
SELECT
SessionId = session_id ,
RequestId = request_id ,
InternalObjectsAllocPageCount = SUM (internal_objects_alloc_page_count) ,
InternalObjectsDeallocPageCount = SUM (internal_objects_dealloc_page_count)
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id ,
request_id
)
AS
TasksSpaceUsage
INNER JOIN
sys.dm_exec_requests AS Requests
ON
TasksSpaceUsage.SessionId = Requests.session_id
AND
TasksSpaceUsage.RequestId = Requests.request_id
OUTER APPLY
sys.dm_exec_sql_text (Requests.sql_handle) AS RequestsText
OUTER APPLY
sys.dm_exec_query_plan (Requests.plan_handle) AS RequestsPlan
ORDER BY
SessionId ASC ,
RequestId ASC;
Finally, if the reason for tempdb becoming so large is due to version stores, you can use the sys.dm_tran_version_store view in order to view the row versions stored in tempdb. You can then use the value in the column “transaction_sequence_num” together with other DMVs in order to find the transactions that generate the row versions. But since this is quite a complex process and since this post is already much longer than I planned it to be, I’ll leave this subject for another post.
So next time your tempdb database is growing too large, you have the tools to troubleshoot and locate the offending sessions or transactions.
Comments