How to Handle TEMPDB Space Effectively in Your SQL Server

In this article, we will discuss how to keep an eye on the space occupied by tempdb, avoiding I/O bottleneck by controlling DDL operations and importance of objects and version storage.  

A tempdb in SQL Server is a global resource that is used to store user objects, internal objects, temporary objects and procedures. SQL Server provides single tempdb for each of its instances. Successive Data definition and manipulation operations can make tempdb overloaded. Obviously if your tempdb is overloaded it means applications that are running your server will slow down eventually.

Most common issues that users face related to tempdb are:

• Run out of storage
• Slow query processing due to I/O bottlenecking
• Excess of Data definition language operations
• Dispute in Allocation

Free Space in the tempdb counter can be used for monitoring the total space being used by tempdb. Database Administrators often use this to determine if tempdb is running out of space.

TEMPDB occupied space

Following query return the space tempdb has occupied.

Select 
    Sum(unallocated_extent_page_count)*8 as FREESPACE-IN-KB
    Sum(internal_object_reserved_page_count)*8 as INTERNALOBJECTS-IN-KB
    Sum(user_object_reserved_page_count)*8 as USEROBJECTS-IN-KB
    Sum(version_store_reserved_page)*8 as VERSIONSTORE-IN-KB
From 
    sys.dm_db_file_space_usage
where
    database_id = 1

Version storage, internal objects and user objects can be a cause of space issue in tempdb and we need to should fix them all before they damage SQL server.

User Objects

Talking about user objects, they are not owned by a session specifically. So we need to understand how they are created and adjust tempdb size in accordance to it. Following cursor enumerate all the objects related to tempdb.

DECLARE userobjects_cursor CURSOR FOR 
Select 
          sys.schemas.name +’.’+ sys.objects.name
From  
          sys.schemas, sys.objects
where 
         object_id > 50 AND 
         sys.schemas.schema_id = sys.objects.schema_id  AND
         type_desc = ‘USER_TABLES’ 
go
open userobjects_cursor 
go 
DECLARE @variable(200) 
Fetch userobjects_cursor into @variable 
While (@@FETCH_STATUS <1)
begin 
         exec sp_spaceused @objname = @variable 
         fetch userobjects_cursor into @variable 
end
close userobjects_cursor

Row Versions and Long Transactions

Across various sessions row versions are shared. Long running transactions often prevent row versions cleanup process and we need to find and stop them. Following query gives top five longest running transactions depending upon the version in version stores.

Select top 5 
    transaction_sequence_num as SEQUENCE-NUMBER,
    transaction_id as TRANSACTION-ID,
    elapsed_time_seconds as ELAPSED-TIME(SEC)
from sys.dm_tran_active_snapshot_database_transactions 
order by elapsed_time_seconds DESC

We should consider stopping transaction with longer time because it will free the amount of version store. To free up desired space we may need to stop more than one transactions. We can roughly estimate version store size needed by the use of following formula.

Version store size = 2 * (data generated/min) * (Longest running time of transaction(min))

We can also use Version cleanup rate and version generation rate counter for tuning the computations. If the rate of version cleanup is zero then it means version store cleanup is prevented by transactions running for a long time.

Internal objects on the other hand are destroyed and created for every statement. If large amount of tempdb space is assigned, then we should determine which tasks are using the space and then take restorative action.

Session Space Usage & Allocated Page Count

To track the tempdb SQL Server provides us two DMVs, sys.dm_db_task_space_usage and sys.dm_db_session_space_usage. Only after completion of tasks, space which is used by tasks is considered under sessions.

We can use following query to figure out the top sessions to which internal objects are allocated.

SELECT 
    session_id as ID,
    internal_objects_dealloc_page_count as DEALLOC-PAGECOUNT
    internal_objects_alloc_page_count as ALLOC-PAGECOUNT
FROM
    sys.dm_db_session_space_usage 
ORDER BY
    Internal_objects_alloc_page_count DESC

After we isolate the task that is causing lots of allocation, we can find out its T-SQL statement and query plan to get more details.

SELECT
            S1.SQL_HANDLE,
            S1.PLAN_HANDLE,
            S1.SESSION_ID,
            S1.T_ALLOC,
            S1.REQUEST_ID,
            S1.STATEMENT_END_OFFSET
            S1.T_DEALLOC,
            S1.STATEMENT_START_OFFSET
FROM
           sys.dm_exec_requests as S2,
           (SELECT REQUEST_ID, 
                          SESSION_ID,
                          SUM(internal_objects_dealloc_page_count) as T_DEALLOC,
                          SUM(internal_objects_alloc_page_count) as T_ALLOC
            FROM sys.dm_db_task_space_usage 
            GROUP BY (REQUEST_ID, SESSION_ID) as S1,
WHERE 
            S1.REQUEST_ID = S2.REQUEST_ID 
            AND
            S2.SESSION_ID = S1.SESSION_ID
ORDER BY S1.TASK_ALLOC DESC

Handles

Plan_handle and sql_handle can be used to fetch query plans and SQL statements as shown below

Select * from sys.dm_exec_query_plan 
--for plan handle
Select text from sys.dm_exec_sql_text 
--for sql handle

There is a possibility that query plan might not be in cache when you are willing to access it. Polling the cache regularly and saving the results in table guarantees availability of query plans. tempdb go back to its initial configured size after SQL Server is being restarted. It further grows depending on requirements. This ultimately lead up to tempdb fragmentation and exposure to overhead. This impacts the workload performance and that is why it is suggested to pre-allocate your tempdb to suitable size.

Usually most of the tables in tempdb are heaps. Hence delete, drop and insert operations can result in corruption on free space pages. If these pages use deal location or mixed extent for allocations then this will put heavy dispute on Shared Global Allocation pages.

Waiting Tasks

To minimize the allocation dispute, SQL Server caches one IAM page and one data page for the local temp tables. When we cache a query execution plan, work tables are truncated but not dropped across the multiple execution of plan. Following query can be used to load all the tasks that are waiting on pages of tempdb into table of waiting_tasks.

DECLARE @currenttime datetime
--get current time 
SELECT @currenttime = getdate()
Insert into waiting_tasks
--inserting data in table for analysis
           SELECT 
                  resource_description,
                  wait_duration_ms,
                  session_id,
                  @currenttime
           FROM
                  sys.dm_os_waiting_tasks
           WHERE
                  resource_description like ‘1:%’
                  AND
                  wait_type like ‘PAGE%LATCH_%’

Whenever you see task waiting to get latches on tempdb page, you can see if it is due to SGAM or PFS pages by analyzing it and if it is, then it implies allocation dispute on tempdb. If there is dispute on other pages of tempdb and if that page belong to system tables then it means that the dispute is due to excess of Data definition language operations. If you are sure that dispute is due to DDL then you should look at your applications and figure out whether DDL operations can be minimized or not.

SQL Server Corruption

If your SQL Server database gets corrupted due to data manipulation operations on heaps, then you can use third-party tool to repair SQL Server and recover all data from the corrupted database.

Author Introduction:

Upton Mark is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and word recovery software products. For more information visit www.datanumen.com