Which Queries Fill The Tempdb

 

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.

 

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?

  1. Created as temp;
  2. Objects created internally;
  3. 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.

 

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.

 

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)“.

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.

 

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.

dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply