SQL Server Managed Instance slower than local SQL Server

Question

SQL Server Managed Instance slower than local SQL Server

Zohair Mehtab 0

I have a database hosted on-premises with SQL Server. I use a 16 GB ram and core i-7 processor. Recently I migrated the database to SQL Server Managed Instance (with elastic pool). I use BCGen5 Elastic Pool with 10 vcores and 512 GB of space.

My database is using all the cores of elastic pool. But when I run a query on the cloud database it takes about 4 minutes to execute. Whereas, on my on-premises database it takes about 1 minute.

All metrics in the cloud database remains near 0% but the speed is about 5 times slower.

What can the issue be since the logs are not showing high usage for either CPU or any of the other metrics?

Kara, Salih 190 Reputation points

2023-07-06T13:27:16.5133333+00:00
Hmmm, That is indeed not the expected results. Here are some steps you can take to investigate and potentially address the issue:

Evaluate query execution plans: Compare the execution plans of the query in your on-premises DB and the cloud DB. Use the SQL Server Management Studio (SSMS) or Azure Data Studio to analyze the plans and look for any significant differences. This can help identify potential performance bottlenecks or areas for optimization. (documentation: Troubleshoot Azure SQL Database and Azure SQL Managed Instance performance issues with Intelligent Insights)

Check for missing indexes: Ensure that the necessary indexes are created in the cloud DB, similar to your on-premises database. Missing or improperly designed indexes can significantly impact query performance. Review the execution plans to identify any missing index recommendations.

Monitor resource utilization: Although the metrics show low CPU and other resource utilization, it's essential to monitor resource consumption during query execution. Use Azure SQL Analytics, Azure Monitor, or Extended Events to capture resource utilization data during query execution. This will help identify any unexpected resource spikes or constraints that could impact performance.

Analyze query statistics and wait times: Analyze the query statistics and wait times in the cloud database. Look for any unusual wait types or prolonged wait times during query execution. This can indicate resource contention or blocking issues that may be affecting performance.

Hope this helps!
Zohair Mehtab 0 Reputation points

2023-07-07T08:22:17.8433333+00:00
Hello Kara,

Thank you for the response. I have:

Checked for missing indexes: I have enabled the option that Azure SQL provides to automatically index missing indexes and that is working properly.

Monitored resource utilization: I monitored the resource during the query execution. I ran only that single query and monitored the resource utilization simultaneously but all metrics were showing around 0% usage.

Evaluate Query Expression and analyze wait times: When analyzing the query execution plan I did notice that the wait times for each block was too long in the Azure database but it was very less in the on-premises db. I am not sure why the wait time is longer in cloud and how it can be reduced.

Please let me know if you have further suggestions regarding the issue.

Thank you
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2023-07-07T08:41:32.39+00:00

Have you checked the query plans in the two environments and compared them? Would it be possible for you to share them on https://www.pastetheplan.com?
Zohair Mehtab 0 Reputation points

2023-07-08T06:47:22.61+00:00

Hello Erland,

The link that you provided does not work. The site does not load.
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2023-07-08T07:37:44.82+00:00

Try https://www.brentozar.com/pastetheplan/ instead. (It's the same thing. But apparently the direct URL has some issue.)
Zohair Mehtab 0 Reputation points

2023-07-08T08:17:52.78+00:00

Execution plan for the local instance:
https://www.brentozar.com/pastetheplan/?id=SkBHc5UYh

Execution plan for Azure Instance:
https://www.brentozar.com/pastetheplan/?id=rJQ2c9LKh

Note: I know that the queries can be optimized but for comparing the performance we are testing it with a query that takes some time to run. Furthermore, we switched from Business critical to General Purpose Serverless tier after I posted this issue. So, the execution plan of Azure is from a GP Service Tier. However, the time that it takes to execute the query is still about 4 minutes (same as business critical).

Thank you
SSingh-MSFT 16,461 Reputation points Moderator

2023-07-10T09:02:55.91+00:00

Hi
Zohair Mehtab,

Following up to see if the below suggestions helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

2 answers

Your answer

Kara, Salih 190 Reputation points

2023-07-06T13:27:16.5133333+00:00

Hmmm, That is indeed not the expected results. Here are some steps you can take to investigate and potentially address the issue:

Evaluate query execution plans: Compare the execution plans of the query in your on-premises DB and the cloud DB. Use the SQL Server Management Studio (SSMS) or Azure Data Studio to analyze the plans and look for any significant differences. This can help identify potential performance bottlenecks or areas for optimization. (documentation: Troubleshoot Azure SQL Database and Azure SQL Managed Instance performance issues with Intelligent Insights)

Check for missing indexes: Ensure that the necessary indexes are created in the cloud DB, similar to your on-premises database. Missing or improperly designed indexes can significantly impact query performance. Review the execution plans to identify any missing index recommendations.

Monitor resource utilization: Although the metrics show low CPU and other resource utilization, it's essential to monitor resource consumption during query execution. Use Azure SQL Analytics, Azure Monitor, or Extended Events to capture resource utilization data during query execution. This will help identify any unexpected resource spikes or constraints that could impact performance.

Analyze query statistics and wait times: Analyze the query statistics and wait times in the cloud database. Look for any unusual wait types or prolonged wait times during query execution. This can indicate resource contention or blocking issues that may be affecting performance.

Hope this helps!
Zohair Mehtab 0 Reputation points

2023-07-07T08:22:17.8433333+00:00

Hello Kara,

Thank you for the response. I have:

Checked for missing indexes: I have enabled the option that Azure SQL provides to automatically index missing indexes and that is working properly.

Monitored resource utilization: I monitored the resource during the query execution. I ran only that single query and monitored the resource utilization simultaneously but all metrics were showing around 0% usage.

Evaluate Query Expression and analyze wait times: When analyzing the query execution plan I did notice that the wait times for each block was too long in the Azure database but it was very less in the on-premises db. I am not sure why the wait time is longer in cloud and how it can be reduced.

Please let me know if you have further suggestions regarding the issue.

Thank you
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2023-07-07T08:41:32.39+00:00

Have you checked the query plans in the two environments and compared them? Would it be possible for you to share them on https://www.pastetheplan.com?
Zohair Mehtab 0 Reputation points

2023-07-08T06:47:22.61+00:00

Hello Erland,

The link that you provided does not work. The site does not load.
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2023-07-08T07:37:44.82+00:00

Try https://www.brentozar.com/pastetheplan/ instead. (It's the same thing. But apparently the direct URL has some issue.)
Zohair Mehtab 0 Reputation points

2023-07-08T08:17:52.78+00:00

Execution plan for the local instance:
https://www.brentozar.com/pastetheplan/?id=SkBHc5UYh

Execution plan for Azure Instance:
https://www.brentozar.com/pastetheplan/?id=rJQ2c9LKh

Note: I know that the queries can be optimized but for comparing the performance we are testing it with a query that takes some time to run. Furthermore, we switched from Business critical to General Purpose Serverless tier after I posted this issue. So, the execution plan of Azure is from a GP Service Tier. However, the time that it takes to execute the query is still about 4 minutes (same as business critical).

Thank you
SSingh-MSFT 16,461 Reputation points Moderator

2023-07-10T09:02:55.91+00:00

Hi
Zohair Mehtab,

Following up to see if the below suggestions helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Answer 1

The plans are not identical, but they are similar. Since they are only estimated plans, it is difficult to say if those differences matter or not. (Often bad performance is due to misestimates.)

However, in the plans there are to cases of Nested Loops + Index Scan which stand out like a sore thumb. It seems to me than an index on ProcurementTrimsPurchaseOrderDetails.TrimsPrePurchaseOrderID would do wonders.

I also get a feeling that the query itself can be improved. For instance, I am not sure that we need to access ProcurementTrimsPurchaseOrderDetails twice, but without further knowledge of table and data, this is a little precarious do, so I leave this for the moment.

One small thing, though. Chance FORMAT(OrderDate,'yyyy-MM-dd') to convert(char(10), OrderDate, 121). FORMAT is implemented in the CLR and the context to the CLR can add insult to injury when there are many rows returned.

Answer 2

Anonymous

Hi @Zohair Mehtab

I found this link that describes the main reasons for the performance difference between SQL Managed Instance and SQL Server: https://azure.microsoft.com/en-us/blog/key-causes-of-performance-differences-between-sql-managed-instance-and-sql-server/, maybe you can use it as a reference.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya

0 comments

Share via

SQL Server Managed Instance slower than local SQL Server

2 answers

Your answer