This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(156.8, 69%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
I am testing moving our warehousing process from on prem to an Azure Managed Instance. After dropping a large set of stored procedures, the process has a task that will loop through tables, get all columns and recreate a procedure for each table that will handle Inserts, Updates, and Deletes. Basically, it is how we do change detection from our source and destination. For updates, we join on the primary key between source and destination and get results where any single column does not match between the two tables by scripting out the column names in a where clause checking for src.x <> dst.x
This single process takes about an hour on prem. In Azure Managed Instance, it is taking just under 2 hours if I tweak the query slightly or it times out as is when 2 hours is hit. I know I can increase the timeout but I need to speed this process up if we are to use Azure.
Our on prem databases are in simple recovery mode. Yes, I know Managed Instance is full and that can slow things down, but this seems extreme.
I've looked into some recommended steps using the QPI tools. Here's a screenshot of our Top 10 results from running SELECT * FROM qpi.wait_stats ORDER BY wait_time_s DESC. The results are after running the process a few times and stats were reset before beginning.
I'm unsure of how to handle these results and what I should look at possibly altering to see if it improves the times.
Also, are there other Azure tools that I should consider to check for new, updated, or deleted records in our source tables rather than continuing this old script process that we've had for over 10 years?
@HSB Thank you for reaching out.
How big are your data files? how many data files do you have? Also, can you run the attached?
Also, have you considered using change tracking to identify rows that have changed instead your own homegrown solution?
For the data files, I ran the following in both source and destination databases:
-- Connect to a user database
SELECT file_id, type_desc,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;
Destination DB:
Source DB:
I also ran
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'xxxxxx'), 2);
on the source and destination DBs:
@Oury Ba-MSFT
Our source database is restored daily. Can the change tracking option still work if this is the case? We get nightly backups from our 3rd part vendor which we restore and then compare to the destination and this is the piece that is taking 2+ hours in Azure and only and hour on-prem.
@Oury Ba-MSFT Looking at the change tracking option, I don't think this will work. Our source database is reloaded daily. It's basically a daily backup of our EHR system that we don't have direct access to. We restore the backup daily into the source database and then use that to find Inserts, Updates, and Deletes in our destination to then update our local copy of the system as the destination. This in turn is used in our warehouse so we need to track the deletions from the source. We can't just use the daily backup as we would lose records that were deleted.
The option you gave seems to track changes as DML statements run and flags those affected rows for changes.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
it is not clear what is taking 2 hours, creating the procs or running the procs.
if it creating the proc's, then is the process an sql sp or script, or a client application?