Managing Tempdb-Related Challenges & Resolution in Azure SQL Databases

The Tempdb system database serves as a shared resource accessible to users connected to Azure SQL Database or any SQL Server instance. It is responsible for storing temporary user objects explicitly created by users or applications, as well as internal objects created by the SQL Server database engine. One of the primary challenges related to Tempdb revolves around insufficient space, which can manifest as either reaching the maximum size limit set for Tempdb or encountering limitations within the transaction log.

The amount of space allocated to Tempdb in Azure SQL Database is influenced by two key factors: the service tier (pricing tier) assigned to the database and the nature of the workload performed on the database. These factors play a crucial role in managing and maintaining adequate Tempdb space, and they are the primary aspects to consider when addressing potential space constraints in Tempdb.

Immediate Action - Trigger a Database Failover to Clear Tempdb

To initiate a failover for a single Azure SQL database, you have the following options:

• See the Invoke-AzSqlDatabaseFailover?PowerShell command.
• See the REST API command in the article Databases - Failover .

In the case of a database residing in an Elastic Pool, the aforementioned commands are designed to execute a failover specific to the given database, ensuring that other databases within the same Elastic Pool remain unaffected. However, it's important to note that this failover approach will not address the underlying Tempdb issue. To initiate a failover for an Elastic Pool and address Tempdb-related problems, alternative options should be employed:

• Utilize the Invoke-AzSqlElasticPoolFailover PowerShell command, which is specifically designed for initiating a failover within an Elastic Pool. This command provides the necessary functionality to resolve Tempdb issues effectively.
• Refer to the REST API command provided in the article titled "Elastic Pools - Failover." By accessing this article, you can directly execute the failover command by selecting the Try It button located alongside the first syntax option. Upon successful execution, the command triggers an asynchronous operation to perform the failover for the Elastic Pool, resolving the Tempdb issue concurrently.

These alternative options ensure the appropriate failover is conducted within an Elastic Pool environment, enabling the resolution of Tempdb-related concerns while preserving the stability of other databases within the Elastic Pool.

Temporary Solution - Increase Database or Pool Size to a Higher Service Tier

In situations where you encounter insufficient space in Tempdb, experience production blockages, and have limited time for troubleshooting, an immediate mitigation step is to swiftly scale up the affected database or elastic pool to a larger service tier. This proactive measure can help mitigate the issue promptly, providing a temporary workaround while allowing you the necessary time to analyze and identify the factors consuming space within Tempdb. By scaling up, you can effectively address the space constraints and ensure uninterrupted operation in the interim.

Scaling encompasses two key aspects as part of the mitigation process:

1. Increased Tempdb Space: Scaling up to a higher service tier grants additional space within Tempdb. By upgrading the service tier, you expand the capacity of Tempdb, effectively addressing the initial space constraints.
2. Clearing Current Tempdb: Scaling involves restarting the SQL service, resulting in the creation of a fresh and empty Tempdb. This restart effectively resolves the existing space-related issue, as the new Tempdb is free from the previous problem, providing a clean slate for temporary storage within the database.

The maximum storage capacity allotted to Tempdb relies on the chosen service tier of the database. When contemplating a change in the service tier, you have two options: scaling to a higher level within the current purchasing model or transitioning to a different purchasing model altogether. For databases operating within the DTU (Database Transaction Units) model, the Tempdb space range spans from 13.9 GB to 384 GB, progressing from Basic to Standard S12, with a fixed capacity of 166.7 GB for Premium-tier databases. In the case of the vCore model, the available Tempdb space varies depending on the hardware platform and the utilization of either General Purpose or Business Critical configurations. The range encompasses 32 GB to 4096 GB, offering greater flexibility and scalability for Tempdb storage. Overall, the capacity limitations of Tempdb are contingent upon the selected service tier, with differences between the DTU and vCore models, enabling you to tailor your Tempdb space allocation according to your specific requirements.

Below are the system based queries to fetch size of Tempdb, free space and user objects

SELECT [Source] = 'database_file',
? ? [TEMPDB_Max_Size_MB] = SUM(max_size) * 8 / 1027.0,?
? ? [TEMPDB_Current_Size_MB] = SUM(size) * 8 / 1027.0,?
? ? [FileCount] = COUNT(FILE_ID)
FROM tempdb.sys.database_files
WHERE type = 0 --ROWS


SELECT [Source] = 'dm_db_space_usage',?
? ? [free_space_MB] = SUM(U.unallocated_extent_page_count) * 8 / 1024.0,?
? ? [used_space_MB] = SUM(U.internal_object_reserved_page_count + U.user_object_reserved_page_count + U.version_store_reserved_page_count) * 8 / 1024.0,?
? ? [internal_object_space_MB] = SUM(U.internal_object_reserved_page_count) * 8 / 1024.0,?
? ? [user_object_space_MB] = SUM(U.user_object_reserved_page_count) * 8 / 1024.0,?
? ? [version_store_space_MB] = SUM(U.version_store_reserved_page_count) * 8 / 1024.0
FROM tempdb.sys.dm_db_file_space_usage U?        

Executing these queries will provide insights into the space utilization of individual sessions and their corresponding tasks. This information allows you to identify the amount of space consumed by each session and discern any parallel tasks performed within a session.

/*** space consumed in each session ***
SELECT [Source] = 'dm_db_session_space_usage', 
    [session_id] = SU.session_id, 
    [login_name] = MAX(S.login_name), 
    [database_id] = MAX(S.database_id), 
    [database_name] = MAX(D.name), 
    [program_name] = MAX(S.program_name), 
    [host_name] = MAX(S.host_name), 
    [internal_objects_alloc_page_count_MB] = SUM(internal_objects_alloc_page_count) * 8 / 1024.0, 
    [user_objects_alloc_page_count_MB] = SUM(user_objects_alloc_page_count) * 8 / 1024.0
FROM tempdb.sys.dm_db_session_space_usage SU
INNER JOIN sys.dm_exec_sessions S ON SU.session_id = S.session_id
LEFT JOIN sys.databases D ON S.database_id = D.database_id
WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0
GROUP BY SU.session_id
ORDER BY [user_objects_alloc_page_count_MB] desc, SU.session_id;


/*** sessions and tasks - details about running tasks in each session ***/

-- enable lightweight query profiling and statistics collection to get the actual execution plan
-- NOTE that enabling this feature has a 1.5~2 % CPU overhead; LIGHTWEIGHT_QUERY_PROFILING is ON by default
ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = ON;
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

SELECT [Source] = 'dm_db_task_space_usage', 
    [session_id] = SU.session_id, [request_id] = SU.request_id, 
    [internal_objects_alloc_page_count_MB] = SU.internal_objects_alloc_page_count * 8 / 1024.0, 
    [user_objects_alloc_page_count_MB] = SU.user_objects_alloc_page_count * 8 / 1024.0, 
    [database_id] = S.database_id, 
    [database_name] = D.name, 
    [query_text] = SUBSTRING(T.text, R.statement_start_offset/2 + 1, (CASE WHEN R.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),T.text)) * 2 ELSE R.statement_end_offset END - R.statement_start_offset)/2),
    [query_plan_current] = P1.query_plan, 
    [query_plan_previous] = P2.query_plan, 
    [query_plan_handle] = P1.plan_handle, 
    [open_transactions] = S.open_transaction_count, 
    [login_name] = S.login_name, 
    [program_name] = S.program_name, 
    [host_name] = S.host_name, 
    [start_time] = R.start_time, 
    [status] = R.status
FROM sys.dm_db_task_space_usage SU
INNER JOIN sys.dm_exec_requests R ON (SU.session_id = R.session_id AND SU.request_id = R.request_id)
INNER JOIN sys.dm_exec_sessions S ON R.session_id = S.session_id 
LEFT JOIN sys.databases D ON S.database_id = D.database_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
OUTER APPLY sys.dm_exec_query_statistics_xml(SU.session_id) AS P1 
OUTER APPLY sys.dm_exec_query_plan_stats(P1.plan_handle) AS P2 
WHERE SU.internal_objects_alloc_page_count + SU.user_objects_alloc_page_count > 0
ORDER BY [user_objects_alloc_page_count_MB] desc, session_id, R.request_id;        

There are more technical tips and tricks to be followed in order to overcome Tempdb failures and bottleneck. Please keep watch over latest Microsoft technical whitepapers and documentations to get new tricks for solving such Tempdb issues.

Disclaimer: The information provided above is based on general knowledge and best practices obtained from various sources, including Microsoft documentation, community forums, and other reputable platforms. While efforts have been made to ensure the accuracy and reliability of the information, it is important to note that the content is subject to change as updates and new developments occur. Therefore, it is always recommended to refer to the official documentation and consult with relevant experts or professionals for specific guidance and tailored advice pertaining to your unique circumstances. The usage of any information provided is solely at your own risk, and we disclaim any responsibility for any potential inaccuracies, errors, or damages arising from its use.