Solutions to space and performance related issues associated with tempdb in Sql Server 2005

Last week I was given a bunch of stored procedures and informed to modify in such a way that it should not cause TEMPDB space errors in the future (one of the usual error is - "The transaction log for database 'PRO_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".)

Prior to this, the task had been assigned to my Colleague Deepesh Pathak contributed to adding Begin Transaction and commit transaction for every batch update statement which they found it to resolve nearly 5-10% of the issue. Print messages added with time record for every T-SQL statement, gave clear hints that there were couple of long running transactions. Particulary some transactions nearly took 14 hours to complete and main traps were the index build statements.

So to troubleshoot the issue, I read about "Working with tempdb in Sql Server 2005" a very useful article written by Wei Xiao, Matt Hink, Mirek and Sunil Agarwal . Compiling this article with the extract of other articles like Troubleshotting Performance Problems and Sql Server Security, Performance & Tuning , I was able to implement a solution that not only resolved the tempdb issues but also improved performance remarkably. Following are usual steps involved:

1) Read about the Tempdb objects and when, why and how they are used. Best idea will be to create a test environment.

2) Performance Counters to get tempdb usage statistics(Database Log file(s) Size KB, Log File(s) Used, Free Space in Tempdb,Version store size (KB), Version generation rate (KB), Version Clean rate(KB)) .

3) Using the rich set of DMVs (Dynamic Management Views) provided by Sql Server 2005 : sys.dm_db_file_space_usage, sys.dm_db_session_file_usage and Sys.dm_db_task_space_usage helps to further investigate.
eg:
The following query shows the top five sessions that have allocated a maximum space for user objects and internal objects in tempdb

SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

The following query shows the top five tasks that are currently executing tasks and consuming the most tempdb space

SELECT top 5 *
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

4) Version Store: If the version store is not shrinking, it is likely that long running queries is preventing version store clean up. You can query the sys.dm_db_task_space_usage DMV to find out which tasks are consuming the most space in tempdb. and can kill them if appropriate.

SELECT top 5 transaction_id, transaction_sequence_num,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC

5) Tempdb can only be configured in the Simple recovery model. An active long running query can use up all the space preventing the transaction log clean up. sys.dm_tran_active_transactions DMV can be used to find the longest running transactions and can kill the appropriate ones.

Off Tempdb issue, Bit an essential performance tip
7) Tuning the I/O intensive queries (Cursor based) require:
a) Usage of related Performance counters for learning about the Current state of I/O.
b) Monitoring contentions caused by DML and DDL operations by using DMVs such as sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_query_plan. Eg: Mixing set-based queries wherever cursors can be replaced for a better performance. This will speed up by taking advantage of the best query plan eventually.
c) Use of task Manager to monitor the resource usage.
d) Making sure that we have Minimal I/O bandwidth (replace with a better configuration)

8) Tempdb settings:
a) Enabling Auto grow setting leads to fragmentation usually when the growth exceeds the allocated space. This can be applied as a last resort when there are many unplanned exceptions.
b) Avoid shrinking files as there are limitations tied up with this.