I am in the process of migrating a data warehouse to Azure from our on premise solution. The nightly load process for the on premise solution takes between three and four hours to run depending on the incoming data. I am trying to move to an Azure implementation while changing absolutely as little as possible, so the code running on premise and in Azure is the same. Using the same source data and running the same code the Azure process takes over seven hours to complete the same nightly load.
I have started investigating the major bottlenecks and they seem to be queries that either update data in a table or delete data from a table. Since these queries are running on the Azure instance, against data already in the Azure instance, it seems unlikely that data latency is causing much of an issue. Using the query store in the Azure instance and the on premise server I have found that the queries running on the two are the same and are generating the same execution plan, but the queries in Azure are taking significantly longer.
The database on premise and the one in Azure are both in full recovery mode, so I do not think there is a logging time difference. The memory and processors are similar on premise and in Azure, but Azure actually has more memory available. I am not sure what else to look for as a difference between the systems. I am new to Azure and have been digging through documentation but have not found anything helpful yet. I am looking for some setting or configuration in Azure that is causing long update and delete transactions.
1条答案
按热度按时间ff29svar1#
What we were able to eventually determine was that we had an issue with file throughput as well as a memory limitation, because memory in Azure is tied to the number of CPUs on the managed instance. The throughput issue was the hardest to determine because we were seeing wait times on our insert/update statements as the longest waits and it was not obvious that throughput was the cause. By adjusting the file size we were able to increase the max throughput and IOPS as outlined on this page https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits?view=azuresql
In addition, increasing the total number of cores and moving to the Premium-series - memory optimized - Intel Ice Lake, 13,6 GB RAM/vCore hardware configuration improved run times significantly. We saw the run time decrease from about 12 hours to three hours by implementing these changes.
The final configuration for the SQL managed instance that we settled on was for a balance of performance and cost. With the following configuration we were able to have the total runtime of the process at just over three hours on average.
Service Tier: General Purpose (4-64 vCores, 32 GB-16 TB storage capacity, Fast storage) - for most production workloads
Computer Hardware Hardware Generation: Premium-series - memory optimized - Intel Ice Lake, 13,6 GB RAM/vCore vCores: 16 Storage in GB: 4640
The storage in GB is larger than needed in order to contain all of our data for the server, but in order to get the log and data files for the two databases on the server over 1 GB each, we had to increase above 4 GB. The 1 GB file threshold is where we saw major improvements in throughput and IOPS.
Additional links that we found useful in troubleshooting this issue.
https://azure.microsoft.com/en-us/blog/key-causes-of-performance-differences-between-sql-managed-instance-and-sql-server/
https://medium.com/azure-sqldb-managed-instance/log-rate-throughput-cap-on-managed-instance-bbf29aa19f80
https://techcommunity.microsoft.com/t5/azure-sql-blog/how-to-improve-data-loading-performance-on-sql-managed-instance/ba-p/3064534
Resource limits and file sizing
https://techcommunity.microsoft.com/t5/azure-sql-blog/announcing-major-performance-improvements-for-azure-sql-database/ba-p/1701256