Which Queries Fill The Tempdb

dbtut

6 years ago

Tempdb is the most important of the system databases in terms of performance.

Therefore, it is necessary to correctly configure tempdb in instances that use tempdb frequently.

You can find details on how to configure tempdb in the article “SQL Server System Databases“.

One night I got an error mail from the alerts which I have defined on the instance. The error is as follows.

Insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb.

Transaction (id=xsn=3669532288 spid=elapsed_time=) has been marked as victim and it will be rolled back if it accesses the version store.

If the problem persists, the likely cause is improperly sized tempdb or long running transactions.

Please refer to BOL on how to configure tempdb for versioning.

This error can be caused by the filling of the tempdb. First, I right-clicked tempdb and looked at the size on the General tab.

I saw that the size of Tempdb was 3.2 TB. Normally tempdb was not used that much.

The first thing to come to mind in such a situation is how much of this 3.2 TB is being used right now.

I ran the following script to determine how much of tempdb was used, and I saw that 16 GB of 3.2 TB was empty.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

SELECT SUM(unallocated_extent_page_count) AS [free pages],

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

FROM sys.dm_db_file_space_usage;

This means that queries currently running are filling the TEMPDB with approximately 3.2 TB of data.

Because, the data in tempdb is erased when the session ends, it is not kept in tempdb later.

What causes tempdb to fill?

Created as temp;
- Global or local temp tables(I would recommend reading the article titled “How To Create Temp Table On SQL Server“)
- Temp stored procedures
- Table variables(I would recommend reading the “What is Table Variables“)
- Cursor(I would recommend reading the article named “Cursor Usage On SQL Server“)
Objects created internally;
- Spools( I would recommend reading the article named “Spool Concept in Execution Plan(Eager Spool, Lazy Spool)“
- Sorting(Sort operations in query)
Because of Row Versioning,
- Some isolation levels use row versioning and row versioning is done in tempdb. This means that if you take the Isolation Level to Snapshot Isolation or Read Committed Snapshot Isolation Level, your tempdb database must be configured correctly and there must be enough disk space. You can read “Isolation Level 1“, “Isolation Level 2” and “Isolation Level 3” for more detailed information about isolation levels.
- Online Index Operations
- (MARS)Multiple Active Result Sets(It is a feature that comes with SQL Server 2005. Ability to run multiple batches concurrently over a connection)
- AFTER Triggers(You can find the details in the article titled “SQL Server Trigger Types“)

Is the tempdb database filled up by Row Versioning?

If you are using SNAPSHOT or Read Committed Snapshot Isolation Level, you should find the number of pages used by the version store with the following script.

As you can see in the screenshot below, the tempdb usage for the version store is very low.

We see that the problem is not this.

USE tempdb
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

USE tempdb

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

If the size of the tempdb database is full because of the version store, we need to find long running queries using the script below.

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

1 2	SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Is the tempdb database filled up by internally created objects?

The following query shows the number and size of pages created internally in tempdb.

The answer to my problem was this script.

A single query filled out tempdb due to spools.

So I wrote the article “Spool Concept in Execution Plan(Eager Spool, Lazy Spool)“.

Use tempdb
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Use tempdb

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

Is the tempdb database filled up by temporarily created objects?

With the help of the following query, you can also see the size of temp objects in tempdb by created users.

Use tempdb
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Use tempdb

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

You can also find out the currently running queries in tempdb with the help of the following script.

First of all, you should follow the steps I mentioned above to find out why tempdb is filled.

You can then fix the problem by finding the query you are looking for with the following script.

In my case, a query was filling almost all of the 3.2 TB.

When I communicated with the developer, he said that he made joins from 3 databases that are over 5 TB and he took the data for the last 3 years as a report.

I told the developer to take the data with 3 monthly periods.

So when each part of the query is finished running, the space created internally in tempdb will be emptied and the next piece will use that empty space.

The problem has improved in this way.

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

;WITH task_space_usage AS (

-- SUM alloc/delloc pages

SELECT session_id,

request_id,

SUM(internal_objects_alloc_page_count) AS alloc_pages,

SUM(internal_objects_dealloc_page_count) AS dealloc_pages

FROM sys.dm_db_task_space_usage WITH (NOLOCK)

WHERE session_id <> @@SPID

GROUP BY session_id, request_id

)

SELECT TSU.session_id,

TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

EST.text,

-- Extract statement from sql text

ISNULL(

NULLIF(

SUBSTRING(

EST.text,

ERQ.statement_start_offset / 2,

CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset

THEN 0

ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END

), ''

), EST.text

) AS [statement text],

EQP.query_plan

FROM task_space_usage AS TSU

INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)

ON TSU.session_id = ERQ.session_id

AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL

ORDER BY 3 DESC;