0

I am working with Azure Managed Instances for hosting a data warehouse. For the large table loads the indexes are removed and rebuilt instead of inserting with the indexes in place. The indexes are re-created using a stored procedure that builds them from a list kept in an admin table. When moving from our on-prem solution to the managed instance, we have seen considerable decrease in performance when building the indexes. The process takes roughly twice as long when running in Azure vs when running on-prem.

The specs for the server are higher in the Azure Managed Instance, more cores and more memory. We have looked at IO time and tried increasing file size to increase IO but it has had a minimal impact.

Why would it take longer to build indexes on the same data using the same code in an Azure Managed Instance than it does on an on-pre SQL Server?

Is there a setting or configuration in Azure that could be changed to improve performance?

Dave
  • 61
  • 6

1 Answers1

0

Could you please check the transaction log file for the database. Monitor log space use by using sys.dm_db_log_space_usage. This DMV returns information about the amount of log space currently used and indicates when the transaction log needs truncation. Please see the referral link here sys.dm_db_log_space_usage (Transact-SQL) - SQL Server | Microsoft Docs

As creating the index will easily reach throughput limit either for data or log files, you might need to increase individual file sizes. Resource limits - Azure SQL Managed Instance | Microsoft Docs

You also can use this script managed-instance/MI-GP-storage-perf.sql at master · dimitri-furman/managed-instance · GitHub to determine if the IOPS/throughput seen against each database file.

ShaktiSingh-MSFT
  • 314
  • 1
  • 4
  • Thank you for the resources, but this is not the issue. We are in the 7500/250 limit and I am only seeing MaxIOPS of 975.47 and MaxThroughputMBPS of 9.92. – Dave Oct 14 '22 at 16:17