TempDB is a system database in SQL Server that is used to store temporary objects and data. It plays a crucial role in the overall performance of the server. In this article, we will explore what is stored in TempDB and how it can impact your SQL Server environment.
What is Stored in TempDB?
TempDB is used to store various temporary objects and data, such as:
- Temporary tables
- Table variables
- Temporary stored procedures
- Temporary views
- Cursor worktables
- Sort and hash operations
- Row versioning information for snapshot isolation
These temporary objects and data are created and used by SQL Server during query execution and other operations. They are stored in TempDB to provide a workspace for these temporary operations.
Checking What is Stored in TempDB
If you want to see what is currently stored in your TempDB, you can run the following query:
SELECT tb.name AS [Temporary Object Name],
ps.row_count AS [# rows],
ps.used_page_count * 8 AS [Used space (KB)],
ps.reserved_page_count * 8 AS [Reserved space (KB)]
FROM tempdb.sys.partitions AS prt
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON prt.partition_id = ps.partition_id
AND prt.partition_number = ps.partition_number
INNER JOIN tempdb.sys.tables AS tb
ON ps.object_id = tb.object_id
ORDER BY tb.name
This query will provide you with a detailed list of the temporary objects stored in TempDB, along with the number of rows, used space, and reserved space for each object.
By analyzing the result of this query, you can gain insights into what is actually stored in your TempDB. Based on this information, you can make informed decisions on whether to keep or remove certain temporary objects.
It is important to note that when you restart your SQL Server, TempDB is automatically reset to its original state. Therefore, you should avoid storing anything in TempDB that you may need persistently in the future.
Conclusion
TempDB is a critical component of SQL Server that stores temporary objects and data. Understanding what is stored in TempDB can help you optimize its usage and improve the overall performance of your SQL Server environment. By regularly monitoring and managing TempDB, you can ensure efficient utilization of system resources.