none
Forum FAQ: How do I monitor and troubleshoot tempdb space?

    General discussion

  • Symptom

    Since SQL Server 2005, the tempdb database is hugely used. We would like to monitor the tempdb usage and know of some troubleshooting best practice on tempdb.

     

    Solution

    Running out of storage space in tempdb is a common issue with tempdb. The low free space with tempdb leads to a performance and disk space bottleneck for SQL Server. Therefore, to prevent a problem, administrators can monitor the tempdb space usage by using the following two ways:

    1.      Use the following performance counters to monitor space usage in tempdb.

    Database: Log File(s) Size(KB)

    Return the cumulative size of all the log files in the database. This size can grow if you have not set a maximum size for the log in tempdb.

     

    Database: Log File(s) Used (KB)

    Return the cumulative used size of all log files in the database. A large active portion of the log in tempdb can be a warning sign that a long transaction is preventing log cleanup.

     

    Free Space in tempdb (KB) 

    Tracks free space in tempdb in kilobytes. Administrators use this counter to determine if tempdb is running low on free space so they can take appropriate corrective action. This counter accounts for space allocated by all three types of objects in tempdb.

     

    Version Store Size (KB) 

    Monitor the size in KB in both version stores. If a version store is not shrinking, it implies that a long-running transaction is preventing version store cleanup.

     

    Version Generation Rate (KB/s)  

    Monitor the version generation rate in kilobytes per second (KBps) in both version stores.

     

    Version Cleanup Rate (KB/s) 

    Monitor the version cleanup rate in KBps in all version stores. If the version cleanup rate is lower than the version generation rate, the version store will use more space in tempdb. However, if the version cleanup rate is 0 but the version generation rate is not, there is probably a long-running transaction that is preventing the version store cleanup.

     

    Troubleshooting

    ·         If the free space in tempdb is critically low, query the sys.dm_db_task_space_usage DMV to find out which tasks are consuming the most space in tempdb. You can kill such tasks, where appropriate, to free space.

    ·         If the tempdb log file grows quickly or the log file used is high, try to set the recovery model of tempdb to SIMPE. This model automatically reclaims log space to keep space requirements small. Additionally, the transaction log is cleared with the implicit or the explicit checkpoints. An active long-running transaction can prevent transaction log cleanup and can potentially use up all available log space. To identify a long-running transaction, query the sys.dm_tran_active_transactions DMV to find the longest running transaction and, if appropriate, kill it.

    ·         Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full. Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance. We recommend the following general guidelines for setting the FILEGROWTH increment for tempdb files.

    tempdb file size      FILEGROWTH increment 

    0 to 100 MB            10 MB

    100 to 200 MB       20 MB

    200 MB or more    10%*

     

    2.      Use the sys.dm_db_file_space_usage DMV to monitor the disk space used by the different categories in the tempdb.

    The following query returns the tempdb space used by user objects, internal objects and version stores:

    Select

    SUM (user_object_reserved_page_count)*8 as user_objects_kb,

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

    SUM (version_store_reserved_page_count)*8 as version_store_kb,

    SUM (unallocated_extent_page_count)*8 as freespace_kb

    From sys.dm_db_file_space_usage

    Where database_id = 2

     

    Troubleshooting

    ·         A higher % allocation for user objects implies that objects that are created by applications (for example, global and local temporary tables and variables) are the major consumers of tempdb. This is not necessarily a cause of concern.

    ·         A higher % allocation for internal objects implies that the query plans make heavy use of tempdb. This is not necessarily a problem, but you may want to look at the query plans to see if alternate query plans can be generated by creating indexes or by re-formulating the queries so as to minimize tempdb space usage.

    ·         A higher % allocation for the version store implies that version store cleanup cannot keep pace with version generation. See if a long-running transaction is preventing version store cleanup. The following query returns the five transactions that have been running the longest and that depend on the versions in the version store.

    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

     Also, a high transaction throughput might be generating a large number of versions per minute. The background task cleans up versions every minute.

    Friday, March 19, 2010 9:40 AM

All replies

  • I was reading this one, and feel a need to clear some of my doubts.

    1. In the general guidelines for setting tempdb filegrowth increment - I highly doubt how much a % value helps (mainly from performance point-of-view) as database size increases. Just think how much work it will be if SQL Server has do a 10% increment for a multi-gb db, and it can hurt a lot in a busy environment.

    2. I was just thinking, can't we use alerts facility as well to monitor db file size?

    Pradyumna

    Wednesday, April 28, 2010 12:37 PM