Why would my Azure SQL Database performance suddenly drop?

1
votes

I had a 'Business' edition SQL database that started timing out about the same time MS announced the rollout of new SQL features.

Suspicious timing aside, a simple COUNT(*) query against one of my tables is taking nearly 2 minutes. It's got about 4.5 million rows which is about the same row count as another 'Web' tier database that is returning COUNT results in about 1 second. These two databases are on the same server in Azure.

Assuming the worst 'noisy neighbor' scenario, I renamed the DB, copied it and migrated it to Standard S1 tier. I'm having the same performance issues on the new database. I'm not sure what else to try and I'm not sure why my performance fell off a cliff so suddenly.

Any suggestions or 'me too' would be helpful. Thanks!

1
sql-serverazureazure-sql-database
A few pointers to dba.stackexchange.com: Dramatic decrease in SQL Azure performance after scaling to new edition and SQL Azure Performance of new servers. Hope you get some idea what might be wrong or just different ! – user272735
jboeke, did you see any changes in 3 days? Did you find any solutions? We are facing exactly same issue since Dec 11. Microsoft support was not helpful so far. Instead of admitting a problem they suggested to rebuild indexes. But even rebuilding indexes that was part of our weekly maintenance now takes forever. – mykola
@mykola: I'm not a DBA but as I understand it, I reached some arbitrary row count that caused performance to degrade severely. My solution was to move about 3 million rows of my data into an archive. I also switched back from S1 to the Business tier. Performance is now, finally, back to where it was before. I was fortunate that my business case allowed me to offload the data into an archive. If you need to keep all of your data in the same table, I'm really not sure what else to do. Good luck. – jboeke
@jboeke Lots of data = Lots of IO. Each level has its own IO limits. If your statement causes a table scan, if you don't have appropriate indexes or updated statistics, the IO for your query can end up using more IO than you have. The same can happen on a physical database with a slow disk – Panagiotis Kanavos

1 Answers

2
votes

I don't believe Microsoft have suddenly reduced Business Edition performance. I have performed a series of tests against Business Edition, in both July 2014 and January 2015 and compared. Some aspects of performance seem more volatile now, but not definitively less than in July 2014. See my blog post for details: https://cbailiss.wordpress.com/2015/01/26/has-azure-sql-database-business-edition-got-slower/

In addition, if you were having performance problems in Business Edition, then Standard S1 in the new service tiers is unlikely to provide sufficient performance. Databases with heavy workloads in business edition will likely need Premium P1 in the newer service tiers. S2 may be sufficient for moderate workloads. I have a series of performance tests on my blog, e.g. see: https://cbailiss.wordpress.com/2014/12/17/azure-sql-database-v12-performance-tests-show-significant-performance-increase/

Finally, Microsoft have released a new version of Azure SQL Database - v12. v12 is currently in preview but offers significantly better performance compared to v11. It also has a new performance level, S3, which looks like it will provide a better price/performance match to Business Edition. Again, see the above blog post for details.