SQL Azure Indexes and Performace

Question

Hi ,

We have a 28 GB database in Azure and same copy in our local environment (with same set of indexes). Out of 28 GB, data occupies only 8 GB, while the remaining space is consumed by indexes. Now we are working to remove/restructure indexes in SQL Azure. Please go through below queries and give me your valuable advices.

1.       The data volume is same in SQL Azure and On Premises, but index size is different. Why it is like that? Due to this on premise database size is only 20 GB and in SQL Azure, it is around 28 GB.

2.       One index size in on premise is 2083856 KB but the same on Azure is 3823064 KB. Why could this be?

3.       DATA COMPERESSION=ROW is not available in Azure. What is the similar option/command on Azure?

4.       Is there any way to identify defragmentation on Azure? or what will be the best option to detect fragmentation and rebuild the indexes ?

5.       We don’t have any mechanism to create jobs in SQL Azure. So how we can shedule and run rebuild index in a scheduled intervals ?

6.       Same query executed on SQL Azure/On Premises gives different execution times. SQL Azure is comparatively slow. As per my understanding, SQL Azure should have given better results.

7.       We have multiple set of databases under the same instance, but similar query being executed on each of these databases return different results. Why could this be?

8.       Will it be possible to get the server configuration details in SQL Azure? Suppose if we have 5 databases under the same instance, will they be deployed on the same machine or will they be on different machines? If they are deployed on different servers, will we be able to know to know the performance of each of those servers?

 

Please let me know the important guide lines to follow to improve performance when working with SQL Azure.

 

Thank you,

Ranjith

Answer 1

Hi Ranjith,

With regard to 6. - Have you done SQL execution plans on SQL Azure and On Premises.  I have recently been doing a bunch of execution plans in verifying my own indexes, and I can say that with identical data/identical schema/and identical indexes the execution plans for identical queries between SQL Compact and SQL Azure are different (to my surprise, the SQL compact ones were better......SQL Compact is using index seeks while SQL Azure is using index scans (effectively table scans)......I have no explanation for it).  It might be possible that your indexes aren't actually doing what you think they are doing......the only way to know is to check.

Answer 2

Thanks Brian. One more issue which i identified is that, index deployment is taking very long time than compared to local. We are trying hard to find out a solution for the above issues.

Regards,

Ranjith