I have been porting my on-premises SQL Server to Azure. After porting it to Azure and make minor adjustments, I have noticed that my sync stored procedure is taking much longer now. The current setup is to start by calling various web services to download data to an intermediate table (It's a regular table not a temp table or table variable as I need the table around for all web services) after all these web services are completed we get about 25K records in this table.
Once the intermediate table is ready I call my sync stored procedure which does few calculations and updates few columns in this intermediate table. Once the intermediate table is updated, it deletes the main table and insert the new values. This stored procedure takes about 5 min on Azure versus 30 sec on previous system. I have tried the usual No Lock on tables and using summary tables etc but not much improvement. After looking at the execution plan, I have noticed my bottle neck is scanning and updating clustered indexes. I do need this cluster index on my main data table as it drives lots of procedure but there is absolutely no need to have this clustered index on my intermediate table. The intermediate table clustered/primary columns is not ever being updated during the initial calculation of the intermediate table however it take 40% of the entire update process.
Azure does require clustered index on every table and it is a big performance hit when you put this on intermediate table. I can’t think of any way to improve this bottle neck and would appreciate if you can give me any feedback.
UPDATE The updating process slowed down more and more and eventually got to the point that locks the entire database. After hours of digging I found the following:
SQL Azure - One session locking entire DB for Update and Insert
The issue seems to resolved itself after backing up the database and restoring it on another azure server. So much for cloud high availability or as it said in the post above:
"So essentially the aspect that makes Sql Azure highly available is causing databases to become randomly unavailable. I'd laugh at the irony if it wasn't killing us."
