This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Resolve tempdb-related errors and exceptions in Azure SQL Database
The tempdb system database is a global resource available to users who are connected to Azure SQL Database or any instance of SQL Server. It holds temporary user objects that are explicitly created by a user or application, and internal objects that are created by the SQL Server database engine itself. The most common tempdb issue is running out of space, either regarding tempdb's overall size quota or the transaction log.
The available tempdb space in Azure SQL Database depends on two factors: the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database. These are also the main factors to control if you are running out of tempdb space.
Scan the following sections for information and steps that will help resolve your issue.
Emergency mitigation - initiate a database failover to clear tempdb
Consider this step under the following conditions:
- you are running out of space in tempdb
- the issue is occurring for the first time
- your production is blocked
- you don't have sufficient time for troubleshooting
If these conditions do not apply, then do not initiate a failover. Rather continue troubleshooting with the next section "Short-term mitigation - scale database or pool to a larger service tier" instead.
After initiating a failover, the database will be restarted in a similar way as in a planned maintenance event. This clears the current tempdb and a new, empty tempdb will be created, thus freeing the space from the earlier issue. It will also cause a brief unavailability of the database, like in a planned maintenance event. Note that for elastic pools, all databases in the elastic pool will be affected by the restart.
To initiate a failover, see the REST API command in the article Databases - Failover . You can run the failover command directly from the article page by selecting the Try it button on the first syntax option. If successful, it will trigger an asynchronous operation to failover the database or elastic pool in the background.
Short-term mitigation - scale database or pool to a larger service tier
If you are running out of space in tempdb, if your production is blocked, and if you don't have sufficient time for troubleshooting, the quickest mitigation step is to scale the affected database or elastic pool up to a larger service tier. This can help avoiding the issue and will give you time for analyzing what is consuming the space in tempdb.
Scaling up the database will help if you encounter one of the following error messages:
The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (Microsoft SQL Server, Error: 40544)
Msg 40197, Level: 20, State: 1,
The service has encountered an error processing your request. Please try again. Error code 1104.
Error 1104: TEMPDB ran out of space during spilling. Create space by dropping objects and/or rewrite the query to consume fewer rows. If the issue still persists, consider upgrading to a higher service level objective.
Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is ...
The scaling has two aspects towards mitigation:
- It provides more space in tempdb due to the higher service tier.
- It clears the current tempdb by restarting the SQL service. A new, empty tempdb will be created upon restart, thus clearing the space from the earlier issue.
The maximum space for tempdb depends on the selected service tier of the database. When changing the service tier, you can either scale to a higher level within the same purchasing model or consider moving to another purchasing model. For databases in the DTU model, the range is between 13.9 GB and 384 GB (Basic to Standard S12), and is 166.7 GB in Premium. In the vCore model, the range is between 32 GB and 4096 GB, depending on the hardware platform and if using General Purpose vs. Business Critical.
The exact limits for each service tier are documented in the following set of articles:
Features and operations that require tempdb space
The tempdb space is consumed by temporary user objects that are explicitly created by a user or application, and by internal SQL Server objects that are created by the database engine itself.
See the following table for an overview:
|Temporary user objects||Internal objects|
|Global or local temporary tables and indexes||Query plan operators: Sort, Hash Match Join, Hash Aggregate, Table Spool, Index Spool|
|Temporary stored procedures||Create or rebuild index with SORT_IN_TEMPDB = ON|
|Table variables||Intermediate sort results for large GROUP BY, ORDER BY, or UNION queries|
|Tables returned in table-valued functions||Intermediate results for large object (LOB) operations|
|Cursors (e.g. DECLARE c CURSOR FOR SELECT col1 FROM dbo.test)||LOB variables like VARCHAR(max) or XML data types|
Regarding global temporary tables and stored procedures: These are shared for all user sessions in the same Azure SQL database, but user sessions from other Azure SQL databases cannot access them. This makes troubleshooting difficult for elastic pools where several databases are sharing the same tempdb, and any of the pool's databases could be contributing to the tempdb issue.
Regarding table variables: These can be a great feature as long as they hold only a few rows, e.g. about 100 rows or less. They become a bad choice if they contain a lot more than that, like thousands or more rows. There are also no column statistics on table variables, which may lead to wrong query optimizer decisions and bad performance in addition to the tempdb I/O and space contention. If you are using table variables, make sure to keep them small.
Regarding cursors: These are a potential performance bottleneck and contribute to tempdb contention if they return larger resultsets. If your applications are still using cursors, then work on replacing them with set-based operations as much as possible.
Regarding query plan operations like Sort, Hash Match Join, Hash Aggregate, Table Spool, Index Spool, or certain Merge Join operations: These often occur because of missing indexes, high index fragmentation, or outdated statistics. They are the side effect of non-optimal query execution plans and need to be treated as a performance issue. See the corresponding section further below for additional guidance.
Regarding create or rebuild index with SORT_IN_TEMPDB = ON: If the index operation using SORT_IN_TEMPDB runs out of tempdb space, you need either increase the tempdb size by scaling to a larger service tier, or set
SORT_IN_TEMPDB = OFF. For a detailed discussion and further guidance, see Disk Space Requirements.
Monitoring and analyzing tempdb space usage
To troubleshoot tempdb space issues, it is important to get an overview about how much space is available and what resources are consuming the space. The SQL queries in this section will help with gathering this information. Run the following queries in the Azure SQL database for which you have seen or are suspecting tempdb-related errors or issues.
Size of tempdb, free and used space, user objects vs. internal objects
These queries will show you the allocated space on the tempdb file level, and how much space is used by user objects and SQL-internal objects.
Space consumed in each session and each running task per session
These queries will show you the space consumed by each session, and by each task per session in case the session runs several tasks in parallel. See the size results related to user objects and internal objects; these will indicate if the cause is rather on the user and application side or with the query execution.
The tasks query will also provide you with details regarding the active SQL query text and its actual execution plan, the corresponding client application, and some additional details that will help identifying the source of the query. If you run the tasks query in SQL Server Management Studio (SSMS) , you can select the actual query execution plan directly from the resultset to investigate its details in SSMS.
Note that to see the actual execution plan, two database-scoped SET options need to be enabled; these options only apply to queries that have started and completed after the SET options had been enabled. The column "[query_plan_current]" will show you the in-flight, transient execution details while the underlying query continues to run; the column "[query_plan_previous]" will show you the actual details of an execution that has already completed earlier.
Sessions with open transactions in tempdb
This query will return all sessions that have open transactions in tempdb, with their transaction details.
User objects are allocating the tempdb space
Identify the affected sessions, their SQL statements and application name through the system queries in the previous section. Then investigate the applications and SQL statements if they are using any of the user objects listed in section "Features and operations that require tempdb space" above.
The goal is to reduce the number of user objects and/or the data they hold so that they fit within the allocated tempdb space. If this cannot be done, then the second-best mitigation is to scale the database to a larger service tier. See the "Short-term mitigation" section above for further guidance on this.
Internal objects are allocating the tempdb space
If the tempdb is used by internal objects, then the key is to identify the SQL queries that are related to these internal objects. Use the sessions and tasks query from the "Monitoring and analyzing" section above. It will show the session details and the SQL texts together with their individual tempdb space allocations.
Once the corresponding SQL queries are isolated, you need to troubleshoot their actual execution plan. The sessions and tasks query from above is providing two different actual execution plans: "[query_plan_current]" shows the transient, incomplete statistics of the currently-executing query; "[query_plan_previous]" shows the execution statistics of a previous successful execution. Continue your investigation with the plan from "[query_plan_previous]", if available. If you have run the query from a query window in SQL Server Management Studio (SSMS) , you can select the execution plan directly from the
query_plan_previous column for further analysis.
The goal is to identify the query plan operators "Sort", "Hash Match Join", "Hash Aggregate", "Table spool", and "Index spool", as well as any warnings related to them. Warnings will be indicated as a "!" inside a yellow triangle on these operators. Hover the mouse over the operators to see further details. Then investigate if the query can be changed to avoid these operators or remove the warnings.
Warning: "Operator used tempdb to spill data during execution with spill level xx"
This occurs if the query engine has estimated the number of rows too low and hasn't allocated sufficient memory. To execute the query, it needs to spill the overflow intermediate results to tempdb. Check the arrow that is pointing into the Sort operator: if the "Actual Number of Rows" is larger than the "Estimated Number of Rows", then this is the cause for the tempdb usage.
The mitigation is to get a correct value for the estimated number of rows and the required memory:
- If indexes and statistics already exist, then run
UPDATE STATISTICS ... WITH FULLSCANon the related tables.
- Review the query and its tables to make sure that the relevant columns have indexes and/or statistics.
- If possible, create covering indexes to avoid additional join and sort operations in the query plan.
- Make sure that the database compatibility level is set to 150 (SQL Server 2019) or higher, and that the row mode memory grant feedback feature is enabled. This feature allows for adjusting memory grants on repeated executions of a query:
ALTER DATABASE [database name] SET COMPATIBILITY_LEVEL = 160
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON
Hash Match Join, Hash Aggregate operators
Hash match joins can efficiently process large, unsorted, nonindexed inputs. They are useful for intermediate results in complex queries, because these are often not suitably sorted for the next operation in the query plan. Also the size estimates of intermediate result may be inaccurate for complex queries, therefore hash match joins can be the most efficient way to process specific query types. See Hash joins for further details. Hash aggreates work in a similar way, but for data aggregation tasks, e.g. in a
GROUP BY query. Hash aggregates are more sensitive to estimating the required memory incorrectly if the statistics are incorrect.
Smaller hash operator queries can be managed in memory, but larger results will require worktables in tempdb and thus contribute to tempdb space issues. If you want to avoid hash operations, consider the following items:
UPDATE STATISTICS ... WITH FULLSCANon the related tables so that the decision to use a hash operator is correct and not wrongly preferred over another operator.
- Try to minimize the memory footprint by reducing the amount of data that needs to be processed:
- Reduce the number of selected columns as much as possible, ideally so that the columns can be included in a covering index.
- Add selective conditions to the Where clause (instead of filtering the results later)
- Make sure that all columns on the Join and Where clauses are properly indexed. This also helps retrieving sorted data, which allows for using other operators than hash operators.
- Review the join conditions and if their complexity can be reduced, especially if a condition is not a straightforward equality match and involves calculations, conversions, concatenations, or similar. Consider adding a calculated column to the table to reduce the join complexity.
- Add a specific join or group hint to the query so that the hash operation will be replaced by a loop or merge operation. This might negatively impact the overall performance though and requires thorough testing. See Join Hints Examples and Group Hints for further guidance.
It might turn out that the hash operation is the most efficient way to execute the query. In this case, the mitigation is to scale the database to a larger service tier. See the "Short-term mitigation" section above for further guidance on it.
Table Spool, Index Spool
Spools are internal tables in tempdb, and the query optimizer uses them to avoid re-retrieving data from a table or index repeatedly. Spools are often related to the lack of proper indexes, or to the same data being joined several times into the query, e.g. in nested, recursive queries or in Update queries to maintain the initial data.
The mitigation is not straightforward and requires further analysis of the affected query. The goal is either to reduce the data that goes into the spool operation, or to avoid that data needs to be read repeatedly. This requires a review of the Join operations of the query, including sub-queries, recursions, and Where clauses. It also requires a review of the indexes that are servicing these Join operations.
It might turn out that the spool operation is the most efficient way to execute the query. In this case, the mitigation is to scale the database to a larger service tier. See the "Short-term mitigation" section above for further guidance on it.
Running out of transaction log space in tempdb
The most common error when running out of transaction log space in tempdb is the following:
Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is ...
"ACTIVE_TRANSACTION" indicates that the database is running out of virtual log files because of an open transaction. Open transactions keep virtual log files active, as their log records might be required to rollback the transaction. The best way to mitigate this error is to avoid long-running transactions and to design transactions as short-lived as possible.
To find out more about open transactions in tempdb, run the following query in the Azure SQL database for which you have seen or are suspecting the tempdb-related issues.
The next troubleshooting step is to relate the session information to the workload behind these transactions. Check if and how the workload can be changed, for example by configuring smaller batch sizes, or by redesigning the process flow to use shorter transactions.
It might turn out though that the workload cannot be sufficiently changed. In this case, the mitigation is to scale the database to a larger service tier. See the "Short-term mitigation" section above for further guidance on it.
BULK INSERT command using tempdb
BULK INSERT operation might require tempdb space if the the target table has indexes and the inserted data needs to be sorted. If the Bulk Insert runs out of tempdb space, there are two possible mitigation steps:
Use the BULK INSERT ... BATCHSIZE option to configure a smaller batch size.
BATCHSIZEspecifies the number of rows per batch, and each batch is copied to the server as one transaction. By default, all data in the specified data file is inserted in one batch and one transaction, which might exceed the transaction space for large, unsorted source files in either tempdb or the target database.
Use the BULK INSERT ... ORDER option if the data in the source file is sorted according to the clustered index on the target table. If the data file is sorted in a different order, or if there's no clustered index on the table, the ORDER clause is ignored. By default, the bulk insert operation assumes the data file is unordered.
Besides potential tempdb restrictions, consider temporarily increasing the performance level of the database prior to the Bulk Insert operation, especially for a large volume of data. A larger service tier improves the I/O performance of the insert and scaling up reduces the overall duration and the risk of timeouts. Scale down again after the Bulk operation was successful.
CREATE or ALTER INDEX in Hyperscale database
In Hyperscale databases, the option
SORT_IN_TEMPDB for CREATE INDEX and ALTER INDEX is always set to "ON", no matter what you specify when executing either command. The default is "OFF" for all other types of Azure SQL Database. This makes Hyperscale prone to running out of tempdb space when indexes are (re-)built for large tables, as the intermediate sort results are always stored in tempdb.
There is one exception to this rule though.
SORT_IN_TEMPDB is always "ON", unless the
RESUMABLE option is used. So making the operation resumable, as in
CREATE INDEX ... ON ... WITH (ONLINE = ON, RESUMABLE = ON), is implicitly disabling
SORT_IN_TEMPDB on Hyperscale.