In this article, we will discuss the best practices that should be followed in order to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.
TempDB overview
The TempDB database is one of the most important SQL Server system databases, that is used to store temporary user objects, such as the temporary tables that are defined by the user or returned from table-valued function execution, temporary stored procedures, table variables or indexes.
In addition to the user objects, TempDB will be used to store internal objects that are created by the SQL Server Database Engine during the different internal operations, such as intermediate sorting, spooling, aggregate or cursor operations.
The TempDB system database is used also to store the rows versions in order to support the features that require tracking the changes that are performed on the table rows, such as the snapshot isolation level of the transactions, Online Index rebuilds or the Multiple Active Result Sets feature.
TempDB database will be dropped and recreated again each time the SQL Server service is restarted, starting with a new clean copy of the database. Based on that fact, all the user and internal database objected that are stored on this database will be dropped automatically when the SQL Server service is restarted or when the session where these objects created is disconnected. Therefore the backup and restore operations are not available for the TempDB.
Why TempDB
The TempDB system database plays an important role in SQL Server performance tuning process. Because it is used as caching storage to store different types of user database objects and to store the system internal objects in order to speed up the SQL Server Database Engine related processes.
Due to this vital role that the TempDB system database plays in enhancing SQL Server instance overall performance, it is very important to apply the initial size, auto-growth and location best practices on the TempDB database data and log files. Applying these best practices in a way that fits your SQL Server instance workload will prevent expanding the TempDB database data and log files very frequently, taking into consideration that the file expands process is an expensive process in which SQL Server Engine will request extra space from the operating system, that will perform zeroing on that space before allocating it to SQL Server Engine. You can imagine the time and resources required for this allocation process.
If you already apply all best practices and an unexpected growth operation occurred, you can speed up the allocation process by using the database instant file initialization option, in which the operating system will trust the SQL Server service and allocate the required space without performing the time and resources wasting operation on the allocated space.
Best practices
There are a number of TempDB system database configuration best practices that are recommended in order to get the best performance from that shared resource and protect your SQL Server instance from any unexpected growth in the TempDB database data or log files that may fill the underlying disk space.
These configurations include:
- The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed. If the number of logical processors on that machine is greater than eight, set the number of data files to eight, with the ability to extend it by multiple of 4 in case you have a TempDB contention issue
- Make sure that the TempDB files have equal size within the same filegroup to provide the highest possible degree of parallel operations efficiency, due to the Proportional Fill writing mechanism that is used to write the data inside the data files. For more information, check Understanding the SQL Server Proportional fill algorithm
- Configure the TempDB database data and log files with a reasonable initial size and auto-growth amounts that fits the expected amount of data that will be written on these files, and the different types of workloads performed in your SQL Server instance, including user activities and index maintenance operations, in order to avoid the frequent small increment amounts that affect the overall instance performance
- Locate the TempDB files on a fast I/O subsystem in order to avoid any I/O bottlenecks
- Locate the TempDB on a separate disk drive, away from the user databases and other system databases. In this way, the heavy read and write operations performed on TempDB will not affect or be affected by the workload of the user databases, enhancing the overall SQL Server instance performance
For more information about the new enhancements in the TempDB performance, check the TempDB Database document.
Where to apply
Prior to SQL Server 2016 version, the TempDB size allocation can be performed after installing the SQL Server instance, from the Database Properties page. Where you can control the number of database data files that fit the current SQL Server instance, the initial size for each database data and log file, the proper value for the Autogrowth option and the location of the database data and log files.
To perform these changes, expand the Databases node in SQL Server Management Studio, expand the System Databases node then right-click on the tempdb database and choose the Properties option as below:
From the opened Database Properties window, browse the Files page and check or change the different database data and log files settings shown below:
Starting from SQL Server 2016 version, all these changes, with the best practices that fit the current server resources already suggested by the installation wizard can be checked and performed during the SQL Server instance installation process, using the SQL Server installation Wizard, under Database Engine Configurations | TempDB, as shown below:
Track TempDB growth
Tracking the growth of the TemDB database files and the queries that are consuming the TempDB resources can be performed using the Performance Monitor counters associated with the TempDB files and the SQL Profiler tool that listens to a specific database workload type.
A more database administrator’s friendly way to track the growth of the TempDB database files is querying the following Dynamic Management Views:
- sys.dm_db_file_space_usage that returns the space usage information for each file in the database, without showing which session or task consumed that space
- sys.dm_db_session_space_usage that returns the number of allocated and deallocated pages per each session
- sys.dm_db_task_space_usage that returns the number of allocated and deallocated pages per each task
Note that the result returned by the sys.dm_db_session_space_usage DMV summarized the result returned from the sys.dm_db_task_space_usage DMV when all tasks are completed successfully, due to the fact that, multiple tasks related to the same query will be performed under the same session. Another advantage of these DMVs is that it will show the type of object that consumes the TempDB resources and if this object is a user object, such as a temporary or variable table or an internal object, such as index maintenance or sort operation. So, based on the number of pages consumed by each object, you can specify if TempDB is consumed by an internal or user object and optimize that consumer.
sys.dm_db_file_space_usage
The sys.dm_db_file_space_usage DMV can be used to monitor the disk space used in the TempDB database files. The queries below show the unallocated space, the space used to store rows versions, the space consumed by the internal objects and finally the space consumed by the user objects, as follows:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS TempDB_FreeSpaceAmount_InMB FROM sys.dm_db_file_space_usage; SELECT (SUM(version_store_reserved_page_count)*1.0/128) AS TempDB_VersionStoreSpaceAmount_InMB FROM sys.dm_db_file_space_usage; SELECT (SUM(internal_object_reserved_page_count)*1.0/128) AS TempDB_InternalObjSpaceAmount_InMB FROM sys.dm_db_file_space_usage; SELECT (SUM(user_object_reserved_page_count)*1.0/128) AS TempDB_UserObjSpaceAmount_InMB FROM sys.dm_db_file_space_usage; |
From the result, which is returned from my local SQL Server instance, you can see that, there is 60 MB unused in the TempDB files, with only 0.5MB used for internal objects and 1.5MB used for the user objects, as shown below:
sys.dm_db_task_space_usage and sys.dm_db_session_space_usage
sys.dm_db_task_space_usage and sys.dm_db_session_space DMVs are used to check the number of pages allocated and deallocated by each task or session in the TempDB database. In this way, you will be able to see which user or an internal object that is consuming the TempDB space.
For example, the below result is returned from querying the sys.dm_db_task_space_usage DMV under TempDB database, where it will return the number of pages allocated and deallocated for each user or internal task, as follows:
But the DBA will not require that many details to start tracking the query that is consuming the TempDB resources. So, we need to summarize the previous result for each session, using the query below:
|
1 2 3 4 5 6 7 8 |
SELECT session_id, SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask, SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask, SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask, SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask FROM sys.dm_db_task_space_usage GROUP BY session_id ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC |
And the summarized pages consumption result per each session will be like below:
The same result can be returned by querying the sys.dm_db_session_space_usage DMW, which summarizes the pages allocation and deallocation for all tasks running under the same session, as shown below:
Let us perform some actions on TempDB database using both internal and user objects as below:
Then execute the consolidated query below, that return the number of allocated and deallocated pages by internal and user objects, and show both the completed operations (using sys.dm_db_session_space_usage DMV) and the uncompleted operations (using sys.dm_db_task_space_usage DMV):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count + SUM(SpacePerTask.internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask, SpacePerSession.internal_objects_dealloc_page_count + SUM(SpacePerTask.internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask, SpacePerSession.User_objects_alloc_page_count + SUM(SpacePerTask.User_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask, SpacePerSession.User_objects_dealloc_page_count + SUM(SpacePerTask.User_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask FROM sys.dm_db_session_space_usage AS SpacePerSession INNER JOIN sys.dm_db_task_space_usage AS SpacePerTask ON SpacePerSession.session_id = SpacePerTask.session_id GROUP BY SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_objects_alloc_page_count,SpacePerSession.User_objects_dealloc_page_count ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC |
And the previous operations result will reflect the query output, showing the number of pages allocated and deallocated by creating the user temporary table and sorting the data during the index rebuild process, as shown below:
To get the text of the query that consumes the TempDB resources, you can use your SQL development skills by joining the result of the ys.dm_db_task_space_usage DMV with other DMVs such as sys.dm_exec_requests and sys.dm_exec_sessions with the sys.dm_exec_sql_text Dynamic Management Function, where it will display the queries that are consuming the TempDB space now, as in this query.
The result will be similar to the below:
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021