Performance issue in Azure SQL database stored procedure
This is the scenario: we have 2 Azure SQL databases, one across each environment (Dev and Prod). The tier of the Dev database is lower than that of the Prod database.
In Prod, an Azure Data Factory job is scheduled every 5 minutes that syncs data from an on-premise MySQL database into the Azure SQL database (via incremental load and merge operations) whereas the data in Dev is on an adhoc basis.
The amount of data across both Dev and Prod databases are the same.
Issue: the stored procedure in Dev take ~5 seconds, whereas the same stored procedure in Prod take ~2 minutes.
Note: we tried disabling the Prod job of every 5 minutes and test out the stored procedure performance, but the performance was the same – ~2 minutes.
We have already tried these steps :
- Compared the execution plans across both the databases and it was different. So try executing the stored procedure with “Option recompile”
- Restarted the Azure SQL database
- Republished the DACPAC
- Rebuilt indexes
- Updated the statistics
But we still have the same issue.
We then restored the .BACPAC of the prod database as another database and the performance improved.
We want to know what is that got resolved by restoring the .BACPAC file.
Because now there is another stored procedure with a similar issue with the only change in the above scope is the execution plan is same still there is performance diff. Database with higher tier is having an issue
Read more here: Source link
