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.
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
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.
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.
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