• Eitan Blumin

Troubleshooting tempdb Space Usage

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:

  1. User Objects

  2. Internal Objects

  3. 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:

  1. 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.

  2. 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:

  1. Query Intermediate Results for Hash Operations

  2. Sort Intermediate Results

  3. Contents of LOB Data Types

  4. 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.

Since the release of SQL Server 2005 there are 3 dynamic management views, which make the task of troubleshooting tempdb space usage quite easy. The views are:

  1. sys.dm_db_file_space_usage

  2. sys.dm_db_session_space_usage

  3. sys.dm_db_task_space_usage

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! These views return information for the tempdb database only, so the value returned in this column is always “2” (the database ID of tempdb).

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 2008:

PgSQL

SELECT * FROM sys.dm_db_file_space_usage;

1

2

3

4

SELECT

*

FROM

sys.dm_db_file_space_usage;

I received the following results:

The results illustrate that 6 uniform extents are allocated for internal objects, 5 uniform extents are allocated for user objects, and no space is allocated for version stores. We can also see that there are currently 19 mixed extents in tempdb (this view doesn’t tell us what are the individual pages allocated for). And the last thing we can learn from this view is that there are currently 98 unallocated extents 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 = 58. So far this session hasn’t performed any work, so the following query:

PgSQL

SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = 58;

1

2

3

4

5

6

SELECT

*

FROM

sys.dm_db_session_space_usage

WHERE

session_id = 58;

Returned the following results:


Similarly, the following query:

PgSQL

SELECT * FROM sys.dm_db_task_space_usage WHERE session_id = 58;

1

2

3

4

5

6

SELECT

*

FROM

sys.dm_db_task_space_usage

WHERE

session_id = 58;

Returned the following results:


Now, let’s create a temporary table in session 58 and insert a single row:

PgSQL

CREATE TABLE #TempTable ( Col1 INT NULL , Col2 NCHAR(4000) NULL ); GO INSERT INTO #TempTable ( Col1 , Col2 ) VALUES ( 1 , REPLICATE (N'X' , 4000) ); GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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 a page 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 1. 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:

PgSQL

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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:

PgSQL

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

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.

#tempdb #troubleshooting #maintenance

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

JOIN OUR MAILING LIST

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle