Waiting for Azure SQL Server

I have a theory on how long people are prepared to wait to receive a response from a web site and it is as follows:

0-2 seconds is a generally acceptable time to wait (although the closer to zero, the better).

2-5 seconds people will generally notice but reluctantly accept.

5 seconds and above and we are starting to contemplate that life is too precious for this.

So you can imagine my dismay recently when I deployed a tuned query to an SQL Azure instance and started receiving complaints about the delays in displaying the related web page. To show why I thought the performance of the query was acceptable, here are the client statistics I generated on my local SQL Server instance:

Running the same query on SQL Server produced a completely different (and I would argue unacceptable) result:

So why is SQL Azure spending so much time waiting around? To answer this question we need to look into the execution plan which show the following results:

The first thing curious thing I noticed was that the local server had no wait statistics. The second curious thing was the amount of time spent on the SOS_SCHEDULER_YIELD; I say curious because I was under the impression that because all SQL Servers have a non-configrable 4ms timeout before yielding the CPU, that this delay would be the same on SQL Azure as it was on my local version.

I am in the process of fully understanding and documenting the reasons for these delays, but it did remind me of some important lessons (apologies if you feel I am stating the obvious):

1. Never assume that a by tuning a query in one environment you ensure the performance on another. It is worth the investment to have a similarly configured system and performance test instance of SQL Azure set up in conjunction with a Visual Studio SQL Unit test project to validate performance prior to deployment. If the billing tier you use makes this prohibitively expensive, consider creating and destroying the instance as needed.
2. Don't do as I did and focus exclusively on the input/output statistics of the query and assume that if they are the same, the query elapsed time should be the same. In the case of the query in this example, the i/o was roughly the same between the two instances and, for me, fell within the bounds of acceptable performance. However, on closer inspection, it became apparent that there were a number of reasons why the query was reading pages it did not need to access to return relevant data. Eradicating this was both the key to reducing the elapsed time and making sure that this remained stable as the database increases in size.

Happy tuning!