Just a further update on this for anyone with similar issues. Firstly we looked at the entire on-premise SQL environment to see what was happening:
1. MAXDOP was set to 8
2. There is no HA (eg - Mirroring) on on-premise
3. Tempdb is set to multiple data files 8 x 5GB / 8 x 2GB
So, to keep everything consistent we applied these to the Azure SQL Servers. Aaaaaand….it was still the same poor performance. We even took the db out of the AG and this gave only a negligible perf gain. Damn! :crazy: Then one thing we did look at was this:
And specifically this one:
"Enable read caching on the disk(s) hosting the data files and TempDB data files."
Thus we shut down the server(s) and applied this and wow this jumped performance up by a few notches. Not as good as on premise however but still magnitude of improvement than we were getting. :satisfied: However I am still left with the issue where specific stored procs (using a cursor) complete in around 3.5 hours for On-premise. The Azure environment takes double that. I am now looking at increasing the disks for more IOPS onthe SQL trans log drives as I ran sp_whoisactive (a brilliant tool if you haven't used it before) and I am getting heavy WRITELOG waits when these stored procs run.
Any further recommendations greatly received as this has almost got me stumped. :pinch:
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]