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.
- Tip: The memory-optimized table variables do not use the tempdb database
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @TableVar AS TABLE (ID INT, Col1 VARCHAR(100) ); DECLARE @CounterNumber AS INT= 0; WHILE 1 = 1 BEGIN SET @CounterNumber = @CounterNumber + 1; INSERT INTO @TableVar VALUES (@CounterNumber, 'Text' ); WAITFOR DELAY '00:00:00:30'; END ---Don't forget to stop this query after your test |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT t.name AS [Table name], s.row_count AS [Number of rows], s.used_page_count * 8 AS [Used space (KB)], s.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS p INNER JOIN tempdb.sys.dm_db_partition_stats AS s ON p.partition_id = s.partition_id AND p.partition_number = s.partition_number INNER JOIN tempdb.sys.tables AS t ON s.object_id = t.object_id ORDER BY t.name; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @CounterNumber AS INT=0 CREATE TABLE TempSample1 ( Id INT Identity(1, 1), TextVal CHAR(30) ) WHILE @CounterNumber <= 1000000 BEGIN SET @CounterNumber = @CounterNumber+1 INSERT INTO TempSample1 VALUES(CONCAT(@CounterNumber,'ABCDE')) END CREATE TABLE TempSample2 ( Id INT Identity(1, 1), TextVal CHAR(30) ) SET @CounterNumber=0 WHILE @CounterNumber <= 1000000 BEGIN SET @CounterNumber = @CounterNumber+1 INSERT INTO TempSample2 VALUES( CONCAT(@CounterNumber,'FGHIJ') ) END |
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.
|
1 |
UPDATE STATISTICS TempSample1 WITH ROWCOUNT=1 |
- Note: If your database compatibility level is 150, you can disable the Row Mode Memory Grant Feedback option with help of the following query
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF |
We enable the actual execution plan and execute the following query. This query will return a sorted resultset of the TestSample1 table.
|
1 2 |
SELECT TextVal FROM TempSample1 ORDER BY TextVal DESC |
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 wrong.
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.
|
1 2 3 4 5 6 |
SELECT TempSample1.id, TempSample1.TextVal, TempSample2.TextVal FROM TempSample1 JOIN TempSample2 ON TempSample1.id = TempSample2.id OPTION(MAXDOP 1) |
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
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.
|
1 2 3 4 5 6 |
CREATE PROCEDURE #GetTableList @TableName AS VARCHAR(50)='%' AS select name AS [Table_Name] from msdb.sys.tables WHERE name LIKE @TableName + '%' |
We can see the stored procedure under the stored procedure path of the tempdb after the creation.
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.
|
1 |
SELECT * FROM tempdb.INFORMATION_SCHEMA.ROUTINES |
We can execute the temporary stored procedure like the regular ones with parameters or without parameters.
|
1 |
EXECUTE #GetTableList 'a' |
The temporary stored procedure has almost no usage but when we start the activity monitor it uses some local temporary stored procedures.
Conclusion
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023