What is Stored in Tempdb?
Tempdb is used to store three different categories of temporary data:
– User Objects
– Internal Objects
– Version Stores
• Local and global temporary tables and indexes
• User-defined tables and indexes
• Table variables
• Tables returned in table-valued functions
Note: These lists are not designed to be all inclusive.
• Work tables for DBCC CHECKDB and DBCC CHECKTABLE.
• Work tables for hash operations, such as joins and aggregations.
• Work tables for processing static or keyset cursors.
• Work tables for processing Service Broker objects.
• Work files needed for many GROUP BY, ORDER BY, UNION, and SELECT DISTINCT operations.
• Work files for sorts that result from creating or rebuilding indexes (SORT_IN_TEMPDB).
• Storing temporary large objects (LOBs) as variables or parameters (if they won’t fit into memory).
• The version store is a collection of pages used to store row level versioning of data.
• There are two types of version stores:
1. Common Version Store: Used when:
– Building the inserted and deleted tables in after triggers.
– When DML is executed against a database using snapshot transactions or read-committed row versioning isolation levels.
– When multiple active result sets (MARS) are used.
2. Online-Index-Build Version Store: Used for online index builds or rebuilds. EE edition only.
Tempdb doesn’t act as an other databases:
• Tempdb only uses simply recovery model.
• Manydb options not be able to change.
• Tempdb may not be dropped, attached or detached.
• Tempdb may not backed up, restore, can’t implement any HA options.
Types of Tempdb Problems:
• Generally, there are three major problems you run into with Tempdb:
1. Tempdb is experiencing an I/O bottleneck, hurting server performance.
2. Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.
3. Tempdb has run out of space.
Use performance Monitor:
And also DMV’S are useful what is going on Tempdb
• sys.dm_db_file_space_usage: Returns one row for each data file in Tempdb showing space usage.
• sys.dm_db_task_space_usage: Returns one row for each active task and shows the space allocated and deallocated by the task.
• sys.dm_db_session_space_usage: Returns one row for each session, with cumulative values for space allocated and deallocated by the session.
Monitoring Tempdb Space:
• SQL Server: Database: Data File(s) Size (KB): tempdb
• SQL Server: Database: Log File(s) Used Size (KB): tempdb
• SQL Server: Transactions: Free Space in tempdb (KB)
Errors in tempdb running slow check in error logs:
• Check the SQL Server error log for these errors:
– 1101 or 1105: A session has to allocate more space in tempdb in order to continue
– 3959: The version store is full.
– 3967: The version store has been forced to shrink because tempdb is full.
– 3958 or 3966: A transaction is unable to find a required version record in tempdb.
Note: Be sure auto growth is turned on for tempdb, and ensure that you have enough available free disk space.
Operations that cannot be performed on the tempdb database:
> 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 dbo.
> Creating a database snapshot.
> Dropping the database.
> Dropping the guest user from the database.
> 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.
Post a Comment