Updated on May 23, 2013
Tempdb is a piece of simple stuff that often overlooked in SQL Server planning. Many DBAs ask for more CPU, RAM, and storage but let alone tempdb stays in drive C. Tempdb can be the big bottleneck in system performance, because it contains many stuff for SQL Server operation.
What’s in tempdb, actually?
Here are the short list of tempdb contents:
- User objects: your @tablevariable and #tempTable are 2 common example
- Internal objects: required for internal SQL Server to run, but not directly created by users. Example: worktable in hash join and rebuilding index maintenance
- Version store: obviously, when snapshot isolation is enable then SQL Server need to keep version store in tempdb
To summarize: when the application query uses alots of temporary operation, tempdb will grow very fast as well. How do we forecast tempdb size? Sad to say but true: there is no exact formula to calculate its size.
Sunil Agarwal from SQL product group has done a series of writing on tempdb monitoring and maintenance. I suggest you to read it for better understanding. His posts collection are here: http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/
I presented this topic for SQL Server Group in MS Office – Jakarta on Febr 28, 2013. Below is the summary of tempdb tuning and optimization:
The complete PPT and sample code are available for download here: