Demisfying tempdb database

27. November 2024 Mehedi SQL Server Comments (0)

tempdb is the unsung hero of SQL Server, silently powering temporary objects, internal operations, and session-specific tasks. Understanding how it works and how to optimize it is crucial for maintaining performance and avoiding bottlenecks in your production environment.

Diagram-1: tempdb

What is tempdb

tempdb is a system database of Sql Server. As the name suggested, it stores temporary objects like tables, variables, and intermediate result sets. It also supports internal operations like sorting, versioning for row modifications, and maintaining worktables for certain queries. Every time Sql Server instance is started, tempdb is recreated.

Why tempdb is Essential

tempdb plays a critical role in database performance because it is used extensively for temporary objects and internal operations. If it becomes a bottleneck due to contention (like latch or allocation bottlenecks) or insufficient disk I/O, queries relying on tempdb can slow down significantly. Without tempdb, many SQL Server processes would fail, making it essential for smooth database operations. Ensure that it doesn’t hinder overall system performance.

What is held in tempdb

There are three different types of objects stored in tempdb.

  1. User Objects - user generated explicit temporary objects like:
    • User defined tables and indexes
    • Local and global temporary tables, bulk insert and BCP intermediate results
    • Index rebuilds with "SORT IN TEMPDB" option.
  2. Version Store - row versioning information like:
    • Snapshot Isolation / Read Committed Snapshot Islotaion
    • Triggers (After Triggers). Instead of triggers doesn’t generate versions.
    • MARS (Multiple Active Result Sets)   
    • Index Rebuilds
  3. Internal Objects - intermediate objects created by Sql Server engine for example:   
    • Intermediate runs for sort.
    • Intermediate results for hash join and hash aggregates.
    • XML variables or other large object (LOB) data type variables. (text, image, ntext, varchar(max), varbinary(max))
    • Queries that need a spool to store intermediate results.
    • Keyset cursors to store the keys.
    • Static cursors to store a query result.
    • Service Broker to store messages in transit.
    • INSTEAD OF triggers to store data for internal processing.
    • DBCC CHECK internally uses a query that may need to spool intermediate results.
    • Query notification and event notification use Service Broker.

What activities will fill up tempdb

Below activities will fill up your tempdb:

  • Sorting command that requires more memory than initial allocation, database engine will force to do its work in tempdb   
  • DBCC CheckDB('any database') will perform its work in tempdb — on larger databases, this can consume quite a bit of space
  • DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb 
  • Large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often use tempdb
  • Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb
  • Use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection
  • Table value functions that pull large data sets hold their data in tempdb
  • Using Snapshot isolation on a database with a high number of transactions the snapshot data is stored in tempdb

Restricted Operations on tempdb

Below operations can't be performed on tempdb:

  • Adding filegroups
  • Backing up or restoring the database
  • Changing collation. The default collation is the server collation
  • Changing the database owner. tempdb is owned by sa
  • Creating a database snapshot
  • Dropping the database
  • Dropping the guest user from the database
  • Enabling Change Data Capture
  • Participating in database mirroring
  • Removing the primary filegroup, primary data file, or log file
  • Renaming the database or primary filegroup
  • Running DBCC CHECKALLOC
  • Running DBCC CHECKCATALOG
  • Setting the database to OFFLINE
  • Setting the database or primary filegroup to READ_ONLY

Best Practices for tempdb Performance

  • Default settings of mdf, ldf and ndf files are inherited from model database. Usually, it's size is 8MB. Configure space for all tempdb files large enough to fit your workload which prevents tempdb from expanding too often and improves performance.
  • Set all files are as autogrow reasonable size based on your workload and set it to the same increment in all data files.
  • Make the every data files to equal size within each filegroup, as database engine uses a proportional-fill algorithm that favors allocations in files with more free space.
  • Divide tempdb into multiple data files of equal size provides a high degree of parallel efficiency in operations that use tempdb.
  • Place tempdb files in fastest drive to obtain better performance
  • Put single secondary file per processor of the server - up to 8
  • Keep tempdb to a different location other than the database files.

Planning for tempdb Capacity

For determining capacity in your production environment, analyze below in your test environment

  • Set autogrow on for tempdb.
  • Monitor tempdb space, running individual queries or workload trace files   
  • Execute index maintenance operations such as rebuilding indexes, and monitor tempdb space.   
  • Use above value to predict your total workload usage. Adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

Check tempdb Disk Space Usage

Monitor your tempdb's disk space regularly and take corrective measures as stated in above sections. Otherwise, it may prevent to complete the running queries. Use below queries to check it:

-- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Conclusion

tempdb is at the heart of SQL Server’s operations, handling everything from temporary data storage to complex query processing behind the scenes. By understanding its role, identifying common bottlenecks, and implementing best practices, you can ensure a smoother and efficient database experience.

Tags:

Add comment