Analysis of SQL Azure performance tests

In the past few days I performed a few tests of the performance of SQL Azure.

After all I am quite disappointed with the performance, and to be honest, I don’t see how certain performance problems will be overcome, but I guess the time will tell.

The first test I performed was about the connection latency.

It just turned out that SQL Azure has a latency of almost 1 second to establish a connection from my host to SQL Azure.

Why is connection latency a problem?

In general terms the idea of Azure is that it will never host ALL data, but it will help the clients offload some of their less critical data. For security reasons a company will never store data like their client base or contracts data to Azure, or even the employee earnings and contact information.

In other words, security is just one part of the limitations of SQL Azure.

Another limitation is the performance. If a company wants to offload some of its data to Azure, let’s say promotions and product information, and some fast changing item flows – then the problem with the connection latency becomes very obvious.

If we keep part of the data in Azure and other part of the data in-house, then the synchronization and interaction between our data is highly dependent on the connection latency.

In other words, in most cases we are either forced to put all data in Azure (a big no-no by default) or to keep all data in-house (there are some performance issues here, but they are more controllable).

The second test I performed with SQL Azure was the write performance and connection overload of SQL Azure.

Why is write performance important?

This test went from bad to worse, because I managed to raise an incident in Azure after trying 400 concurrent connections from a local application. (I am sure that somewhere someone at a SQL Azure datacenter was sitting and wondering what was happening with their resources. Sorry – nothing personal. Just performing some real life tests. )

The procedure I was using is local to SQL Azure, which meant that the connection latency does not matter, since the time to make the procedure call was about 1 second, but the workload was carried out in the SQL Azure database itself.

In other words, it does not matter really if the application making the request is local or not. The SQL Azure performance would still be bad when it comes to writes.

Over 2 seconds to insert 1000 rows!

And over 2 minutes to insert 100000 rows?!

In conclusion, I would say that I am disappointed with the SQL Azure performance, and I am really hoping that the performance problems will be solved somehow. I really can’t wait to offload some data to Azure whenever it becomes reliable and when it gets better performance.

 

Work on itWell, now...OKGoodGood job! (1 votes, average: 5.00 out of 5)

If you gave a rating less than the maximum, you must leave a comment with suggestions on how to improve the post. :)

Loading ... Loading ...

 

2 comments to Analysis of SQL Azure performance tests

  • [...] I did some performance tests of SQL Azure and established some bottlenecks. [...]

  • Anders Borum

    Hi,

    thanks for taking the time to share your findings and writing the in-depth articles about Windows Azure SQL DB performance. I’m a software architect on a team that builds a product that runs on-premise as well as on Windows Azure, and our team made similar findings (with similar despair).

    We’re relying on bulk copy (the managed SqlBulkCopy API) to move larger amounts of data, although Sql Azure DB has it’s fair share of issues (such as the inability to target temporary tables with the SqlBulkCopy API), I think we’re getting a decent performance with this approach.

    One thing I’d like to bring to the discussion is that I don’t think Windows Azure SQL DB is marketed as a direct competitor to SQL Server; not from a performance perspective at least.

    Our software originated as an on-premise architecture and we’ve made a significant investment in rewriting parts of the architecture to cater for the transient faults, latencies and other snags that occur in a cloud-based (i.e. shared-resource) environment. We’re definitely sure there’s a lot more work ahead of us, but embracing the cloud or not is not as simple as running a few benchmarks.

    If you don’t fancy the PaaS architecture of Windows Azure SQL DB, you’re free to use IaaS and host your own full-blown SQL Server(s) on dedicated servers, basically allowing you to get close to the same performance as your on-premise SQL Server; again, provided your workload fits the largest amount of memory available in the IaaS offerings.

    We’re moving towards a more asynchronous-based messaging architecture, with less OLTP and more data going to queues, topics and table storage.

    What are your thoughts on the matureness of Windows Azure services (e.g. queues, topics, and table store) as of january, 2013?