Tempdb Space Planning and Space Monitoring
Tempdb Space Planning and Space Monitoring
By : Kasim Wirama, MCDBA
Most of time, users don’t have to worry about running out space on tempdb as long as you have enough disk space to dealing with tempdb growth. Sometimes your tempdb has grown to considerably large so that it consumes all available disk space where the tempdb file resides.
Behavior of tempdb growth is influenced by these factors : snapshot isolation, triggers, MARS, hash join, hash aggregate, online index rebuild. Hash join and hash aggregate consumes space in tempdb, usually optimizer choose them because involved tables doesn’t have correct index or even query hints. If this is the case, you can save unnecessary tempdb space by implement correct index that supports your SQL query.
Planning for tempdb space is not simple tasks. Many factors that I have mentioned above needs to be consider, and usually the estimation is far incorrect than the real one. What you need to do is to do rough estimation and prove it with testing data. This activity will be done iteratively until you get the close number for tempdb space planning. Here is the points as your guidance in determining tempdb space requirement for initial :
a. What are factors that will be included in tempdb.
b. How much space for each factors if queries is done serially. You need to consider maximum space requirement and most common requirement that may apply.
c. How much space whose features will be executed concurrently.
Some users through mailing list ask about how to shrink log file and whether it is necessary to shrink it. I suggest to shrink the log file up to size when the tempdb is under the busiest workload. Let’s say in a month the tempdb size requires 20 GB, but at the end of a month, the tempdb size requires 40 GB, it makes sense if the tempdb is shrink up to 40 GB.
Next activity is about monitoring tempdb space. You can collect tempdb information through execution these queries below based on interval basis and it doesn’t cause performance issue.
SELECT * from sys.dm_db_session_space_usage; SELECT * FROM sys.dm_db_file_space_usage;
You get the important information here. Observe information about version store. If the version store is the source of aggressive tempdb growth issue, consider to make less use of the versioning feature or add additional disk space. But if this version store is not the source of problem, most likely user objects information is the issue. If this is the case, consider to add some additional disk space or you modify SQL statement to make tempdb less pressure regarding to disk space.