SQL Jobs are Running slow in Azure SQL MI

Question

SQL Jobs are Running slow in Azure SQL MI

Thyagarajulu B M 121

Hi Team,

Recently , We have migrated all SQL jobs from on-premises SQL Server to Azure SQL MI. SQL Jobs are running slow compared with on-premises SQL Server.

Please help on this issue.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-05T13:19:03.7333333+00:00

You need to provide more information. What sort of jobs are these? T-SQL job steps? How much slower do they run? Do all jobs slower, or only some?

It is worth pointing out that jobs are just container for executing code. So if you have a piece of T-SQL code, there is not really any difference if you run it as a job or directly from SSMS.
Thyagarajulu B M 121 Reputation points

2023-07-05T18:01:06.0933333+00:00

Jobs are running long time . Failed with error: 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.
Thyagarajulu B M 121 Reputation points

2023-07-05T18:03:25.2866667+00:00

Yes . I'm running the couple of stored procedures one by one with SQL server agents. after some time job is getting failed with :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.

3 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-05T13:19:03.7333333+00:00

You need to provide more information. What sort of jobs are these? T-SQL job steps? How much slower do they run? Do all jobs slower, or only some?

It is worth pointing out that jobs are just container for executing code. So if you have a piece of T-SQL code, there is not really any difference if you run it as a job or directly from SSMS.
Thyagarajulu B M 121 Reputation points

2023-07-05T18:01:06.0933333+00:00

Jobs are running long time . Failed with error: 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.
Thyagarajulu B M 121 Reputation points

2023-07-05T18:03:25.2866667+00:00

Yes . I'm running the couple of stored procedures one by one with SQL server agents. after some time job is getting failed with :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.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Please verify what types of waits are prevalent during the execution of jobs. Here you will find ways to capture waits for the jobs. If you see on the top waits IO related waits then the workload your jobs execute is IO intensive and IO latency could be 10 ms on General Purpose. As a workaround and improve some performance, reserve enough storage on all log files to not allow them to auto-grow with DML operations. Backups may show poor performance and automated backups may impact some tasks on the server also.

Make sure you defragment and update statistics after migration, to improve some performance.

If the top waits are not IO related, please share them with us. As Erland mentioned on the comments, please share more information about the type of workload your jobs execute.

Thyagarajulu B M 121 Reputation points

2023-07-05T18:00:54.7066667+00:00

Jobs are running long time . Failed with error: 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.
Thyagarajulu B M 121 Reputation points

2023-07-05T18:06:20.0533333+00:00

My tempdb configuration in azure SQL MI:
Thyagarajulu B M 121 Reputation points

2023-07-05T18:06:36.8333333+00:00
Thyagarajulu B M 121 Reputation points

2023-07-05T18:11:16.1766667+00:00

Azure SQL MI compute and storage :

service Tier : General purpose

Hardware generation : VCore 8 , storage 2200 GB

Please let us know Is there any memory limitation for tempDB storage on selected service tier.
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-07-05T18:34:31.0866667+00:00

Please configure TempDB as explained in this video to get better performance. See Rahul recommendations to increase storage space for TempDB
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-05T18:39:40.39+00:00

We still don't know anything about what you are doing in your jobs. But the above amounts to 192 GB of tempdb, which is not exactly a small size. Then again, it is not outlandishly excessive either. Do find it normal that you need this much tempdb space? For instance, are you filling up big temp tables?

Or could it be a sort or hash operation that spills to disk beyond belief? Actually that is what the message says. So this smells very much like a misbehaving query. And in that case you configure tempdb until you are blue in the face, but it is not going to help much.

Answer 2

Thyagarajulu B M 121

tempdb

Answer 3

Hi @Thyagarajulu B M

Here is the possible recommendation/solution for “TEMPDB ran out of space during spilling” issue which you are getting.

There is a limit in tempdb size for both General Purpose and Business Critical tier. If the limit is reached, it will not automatically scale. You need to add more vCores to get more TempDB space. Resource limits - Azure SQL Managed Instance | Microsoft Docs

Recommendations to mitigate tempdb issue:

Long running transactions causes tempdp full issue and it is automatically resolved once the transaction is killed. Consider killing the long-running session if it is impacting the database performance.
Increase vcores to get more TempDB space.
Design your transactions to be as short lived as possible.
4. If multiple queries are consuming tempdb space concurrently, consider serializing the workloads.

Below snippet taken from Resolve tempdb-related errors in Azure SQL Database - Microsoft Community Hub

User's image

Please refer this document to find more information on troubleshooting issues of Managed Instance Tempdb Azure SQL DB and Managed Instance tempdb - Microsoft Tech Community

Hope this helps!

Thank you!

Share via

SQL Jobs are Running slow in Azure SQL MI

3 answers

Your answer