I have a large table in Azure SQL Managed Instance. The table has ~ 550 million rows and is ~ 250 gb in size. The table has a clustered index on a surrogate key which is auto generated by the system when data is inserted / imported via ETL.
Data is loaded into the table once a week with a delta type file. The file holds 1 to many rows for the table and what to do with the data; i.e. insert, update or delete. This file is loaded into a staging table and processed against the main table using SQL.
To support this process the main table has a single unique non-clustered index which holds the columns to join the tables together. This allows the correct seeks to happen in the execution plan.
However, when we are performing delete operations the performance of the query is abysmal. Looking at the execution plan I can see the the bottle neck is the non-clustered index delete.
To minimise the amount of transaction log generated we changed to perform the operations in chunks. For example deletions are done in batches of 10,000.
However, when we are performing delete operations the performance of the query is still abysmal. It can take up to 7 minutes to delete 10,000 rows. Nearly all of this time is taken up by the index delete rather than the clustered index delete. In the execution plan shown below the clustered index delete took 8 seconds, but the index delete took 4 minutes 20!
Does anyone have any ideas what could cause this? I am pretty new to using a Azure Managed Instance but have already noticed issues with IO speeds and memory compared to On-Premises.
