Explore secrets of the SQL Server tempdb database

In this article, we will uncover some secrets about the SQL Server tempdb database. Tempdb is a system database and
it is used for various internal and user operations. Besides this, the tempdb has many unique characteristics,
unlike the other databases. When we take into account all of these features of the tempdb, there is no doubt that it
is an essential part of the SQL Server.

What is the SQL Server tempdb database

Tempdb is a very special system database that is used to store temporary objects which are created by the users
explicitly or by the internal system process. The following objects or operations use tempdb:

  • Global and local temporary tables
  • Common table expressions (CTE)
  • Table variables
  • Hash Joins
  • CHECKDB and CHECKALLOC commands
  • Cursors
  • Temporary stored procedures
  • GROUP BY and ORDER BY statements
  • Cursors
  • Triggers
  • Online indexing operations
  • Read Committed Snapshot Isolation level
  • Snapshot Isolation level
  • Multiple Active Result Sets (MARS)

As we can see, the tempdb is used by many different operations. That’s why tempdb must be one of the top ten items
on the performance checklist.

Unique characteristics of the tempdb database

The tempdb has some special features that differ from other databases:

  • Tempdb is always recreated after the start of the SQL Server engine thus the data is not stored persisted by this database
  • Doesn’t allowed to take Tempdb backup
  • Doesn’t allowed to restore the Tempdb
  • The recovery model cannot be changed and its recovery model is always in simple mode
  • Tempdb could not be dropped or created manually
  • Doesn’t allowed to change the collation.
  • Doesn’t allowed to add a new filegroup.

After all this brief information about the tempdb, let’s dig into more details about the tempdb.

Table variables and tempdb database

Table variables are the special variable types and they help to store data temporarily in a table structure during
the execution of the batch. The main property of the table variable is that it is created in the tempdb.

The following query will create a table variable and the endless loop insert one row into the table variable every
30 milliseconds. At first, we will execute the following query.

While this query is running, we open a new query window and execute another query. Through the following query, we can find out the underlined table variable row numbers, and size in the tempdb database.

As a result, we have proved that the table variables are created in the SQL Server tempdb database.

TempDB Spills

During the execution of a query, SQL Server uses some amount of memory to sorting or joining the rows. The required memory amount is estimated by the query optimizer considering some options (number of rows, data types, some operators) while creating the execution plan, and then it demands this memory from the database engine. SQL Server reserved the required memory amount for the query to supply their memory need and it is called memory grant. The significant point about the memory grant is that it could not be changed during the execution of the query.

Sort operator and tempdb spill

In the following sort operator execution plan detail, we can see a Used Memory property. This option indicates that the sort operator has used some memory to perform the sort operation during the execution of the query.

The query optimizer may estimate the required memory amount incorrectly for the sort operator due to outdated or missing statistics. In this circumstance, while the query is being executed, more memory needs arise but the memory grant could not change during the execution of the query. In this case, the database engine begins to use tempdb instead of the memory and this mechanism is called the tempdb sort spill. This issue affects query performance negatively because it causes generate more I/O operations. Now we will reinforce this concept with an example. Firstly, we will create two tables and populate them with some data.

Now let’s make a little magic on the statistics information of the TempSample1 table. The ROW COUNT keyword corrupts statistics of the TempTable1 and leads to generate inaccurate statistics
information so the query optimizer can not demand the accurate memory amount.

We enable the actual execution plan and execute the following query. This query will return a sorted resultset of the TestSample1 table.

As we can see, a warning sign seems on the Hash Match operator. When we hover over the on this
operator image on the execution plan, we can find out a detailed explanation about the warning. The problem reason
seems very clear, the huge difference between the Estimated Number of Rows and the Actual Number of Rows values because the granted memory is calculated according to estimated rows and the
tempdb spill issue has occurred.

Hash match operator and tempdb spill

Hash match join is one of the join options used
by SQL Server to join the unsorted tables in the queries. The algorithm of a hash match join is to create a hash
table in the memory to match two table rows. If the query optimizer incorrectly estimates the source input row
numbers for hash the match operator due to outdated or missing statistics, the memory grant calculation will be

As a result, the database engine starts the use of tempdb instead of the memory during the execution of the query.
Needless to say that this issue causes a performance problem. Now we look at this problem with an example.

We enable the actual execution plan and execute the following query.

Tempdb spill and hash match operator

As we explained just before, the hash match operator indicates a tempdb spill problem. The following suggestion can
help us to overcome the tempdb spill issue:

  • Update the statistics
  • Using the cover indexes
  • Rewriting the query to using the performance practices
  • Enabling the Row Mode Memory Grant Feedback feature in SQL Server 2019
  • Tip: Tempdb spill events can be captured using extended events. The sort_warning and hash_warning events capture and give information when the tempdb spill occurs

Detecting the tempdb database spills with extended events

Temporary stored procedures and tempdb database

The temporary stored procedures are among the very unknown features of the SQL Server. This type of stored procedure
is not any different from the regular stored procedures but they are not persisted until the connection is closed
that is created. The temporary stored procedure has two types:

  • Local temporary stored procedure
  • Global temporary stored procedure

To create a local temporary stored procedure, we add a single hash (#) sign before the name of the
procedure and for the global one, we add a double hash (##) sign. For example, the
following local temporary stored procedure returns the msdb database table names.

We can see the stored procedure under the stored procedure path of the tempdb after the creation.

Temporary stored procedure list in the tempdb database

You may ask, why we see two temporary stored procedures with the same name. I think it is a bug because when we execute the following query it returns only one stored procedure.

We can execute the temporary stored procedure like the regular ones with parameters or without parameters.

Execution of a temporary stored procedure

The temporary stored procedure has almost no usage but when we start the activity monitor it uses some local
temporary stored procedures.

Getting the temporary stored procedures list


In this article, we have learned the tempdb database features and then explore some secrets about it. Tempdb has
unique characteristics and some operations that we never think of using the tempdb. In light of this idea monitoring
and performance tuning of the tempdb have to be a very important task for database administrators.

Esat Erkec
Latest posts by Esat Erkec (see all)

Author: admin