SQL server performance - tempdb
When tempdb is used?
User objects:
- User-defined tables and indexes
- System tables and indexes
- Global temporary tables and indexes
- Local temporary tables and indexes
- Table variables
- Tables returned in table-valued functions
Internal Objects:
- Work tables for cursor or spool operations and temporary large object (LOB) storage.
- Work files for hash join or hash aggregate operations.
- Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
Version Stores:
- Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
- Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Temp db optimization:
- Set the tempdb to single recovery model. Less logs.
- Allow for tempdb files to automatically grow as required. Rule for file size increment is 2 min * the speed of IO system on which the tempdb files are located. Example: if the I/O system can initialize the database file at 50 MB/s, then auto growth increment is 50 *2 *60= 6 GB
- Preallocate the enough large disk space for tempdb files to accommodate the typical workload in the environment.
- Create a data file for each cpu, if the cpu is dual-core, consider it as 2 cpus.
- Make each file the same size. This allows for optimal proportional-fill performance.
- Put the tempdb database on disks that differ from those that are used by user databases.
Monitor the space usage:
use tempdb
go
select * from sys.dm_db_session_space_usage
select * from sys.dm_db_task_space_usage
select * from sys.dm_db_file_space_usage
http://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx