Looks like that we have resolved another configuration related issue on Azure. In case someone is facing the same problem without any clue to fix, then read the following.
Our issue was: Asp.NET web site running very well and faster on on-premise server did not run very well and slower, when migrated to Azure VM. The same VM also hosted SQL Server for this web site.
1) Presently each VM with one data disk has 500 IOPS limit.
2) It is likely that installing SQL Server and actively using the instance of SQL Server may break this limit. Azure calls it throttling of IOPS. If you have set up monitoring as suggested in one for replies above you may be able to verify that. However, as per our experience, light to moderate usage of SQL Server (with only one data disk and VM not being configured properly) has capacity to throttle IO limits. So that has to be taken seriously.
The fix this issue you need to increase the IOPS limit. That can be achieved by adding more data disks to your virtual machine. Each disk comes with 500 IOPS capacity.
3) Additionally VM has to be configured correctly to install SQL Server to avoid these types of bottlenecks. Merely adding disks would not solve it. You may do the followings
a) Add maximum number of data disk with 1 TB size (as per current offerings each VM, standard A2 size, can have 4 data disk with 1 TB capacity.). Remember that Azure will charge only the portion you use on the disks even though you have attached all of them
b) Stripe all 4 together with a new storage pool. That can be done using Server Manager - Files and Storage Services tab. However, you can not really use this to further configure it. At this moment there is no GUI available. You will have to use PowerShell to configure it. Configuration involves specifying correct values of columns and interleave properties. You may specify 4 columns and 64 kb interleave
c) Once this is done you may create required virtual drives on this storage pool. You may provide allocation size of 64 kb while formatting the drive used for SQL Server data directory.
4) Beside this you may also consider storing your TembDB on D drive on Azure VM. There is no IOPS cap on D drive. However, this is a temporary drive. Each time VM is rebooted it gets cleared.
If you have already set up your VM and using it, then easier solution (if possible) is to create another VM and de-allocate the existing one, once you have migrated everything.
If you do not want to de-allocate the existing VM and you don't have any data disk attached, then it can be done by adding data disk and configuring storage pools. However, if you have attached data disk already and using them then it is hard. I really do not know how to do. I think storage pools cannot be created for data disks already being used as one of physical drives.
In my case I set up entirely new VM and de-allocated the existing one after the migration. Presently, the site is running way faster, the way it should be on the cloud.
Note: Why did the same site behaved correctly initially when migrated to Azure and ran into issues afterwards? (It took around 3 to 4 weeks before the site ran into issues)
As per support person, it was possible that when we configured our VM initially, the rack space may not have anyone except us. So Azure allowed to move our IOPS and did not care much. As more people allocated resources to the same rack space, Azure started balancing the activities to allocate fair usage to others too. Throttling was not allowed and penalties were imposed in terms of performance.