It’s all about TempDB

Temporary user objects that are specifically created are stored in the tempdb system database, which is a global resource. They consist of temporary stored procedures, global or local temporary tables and indexes, table variables, tables returned in table-valued functions, and cursors. It only has a lifespan equal to the length of a SQL Server session. When compared to other databases, Tempdb’s state is not durable.

SQL Server’s backbone, TempDB, performs a variety of tasks to support internal and system processes. With this workload, a lot of database writes will be processed by TempDB, necessitating low-latency, high-throughput underlying storage.

The tempdb database is lost when SQL Server shuts down. Also, the automatic growth of the tempDB files when they reach capacity is not persistent and will be undone upon the subsequent restart of the SQL Server service. In other words, every time a SQL Server instance is started, this database is generated from scratch. Any data in the tempdb is permanently erased when the server instance is terminated. The tempdb database cannot be backed up.

The tempdb database is recreated, brand-new, and ready for use when SQL Server restarts. Table creation in tempdb is similar to table creation in other databases, however, it goes more quickly because most internal operations don’t produce logs in tempdb because rollback isn’t necessary.

Global and local temporary tables are also created here in tempdb. Additional objects that can be created in tempdb include internal objects created by the database engine, temporary stored procedures, table variables, and cursors.

TempDB database is shared across a whole instance and hence the IO performance of this database is very critical.

In order to see the total tempdb disk space allocated to each category use the below query which fetches the information from the dynamic management view (DMV) sys.dm_db_file_space_usage.

SELECT
SUM(unallocated_extent_page_count) AS FreePages,
SUM(unallocated_extent_page_ count)/128.0 AS FreeSpaceMB,
SUM(version_store_reserved_page_count) AS VersionStorePages,
SUM(version_store_reserved_page_ count)/128.0 AS VersionStoreMB,
SUM(internal_object_reserved_page_ count) AS InternalObjectPages,
SUM(internal_object_reserved_page_ count)/128.0 AS InternalObjectsMB,
SUM(user_object_reserved_page_count) AS UserObjectPages,
SUM(user_object_reserved_page_ count)/128.0 AS UserObjectsMB
FROM sys.dm_db_file_space_usage;