I’m spending a little bit of time each week trying out a few things in SQL Azure. I’m convinced that we’re going to be spending time tuning our SQL Azure databases much the same way as we tune our regular SQL Server databases. That is to say, we won’t tune the databases at all until there’s a major issue; the CIOs favorite report runs too long, developer’s code slows to a stand still, or you hit Microsoft’s resource threshold and they kill the connection. What? That’s not one you thought of? Well, come on, if the CIO starts complaining, you know it’s going to come down on your head pretty quick. oh, you mean that threshold thing? Yeah, there’s a whole list of constraints on SQL Azure. Many of them make perfect sense. A few are probably going to change in the future, but all the way down at the very bottom of the sheet there’s a line that reads:

In order to provide a good experience to all SQL Azure Database customers, your connection to the service may be closed due to the following conditions:

• Excessive resource usage
• Connections that have been idle for 30 minutes or longer
• Failover because of server failures

And a note:

Maximum allowable durations are subject to change depending on the resource usage.

Microsoft is aware that they are going to hear from people regarding performance. After all, you can’t call the server room and suggest something is up with the server. Your information is in the hands of Microsoft. So, they’ve wisely published a performance tuning guide.  It makes for a very interesting read. Seriously, pick it up and read it. I’m not being nasty. I think it’s useful. It’s also informative about where MS sees problems coming up. According to this document, app dev in SQL Azure is going to be almost as much work as it was in SQL Server. They’ve got recommendations on reducing round trips to the system to avoid latency, they’re recommending against the use of cursors (shock), and they have another white paper just on query performance optimization (more on that in a minute). They want you to make sure your stats are up to date and suggest running sp_updatestats regularly. Now that one is interesting. How? There is no SQL Agent, so you need to build something, somewhere, that’s going to go out to your SQL Azure instance and run this maintenance routine.

The best part of the document though is at the bottom (what is it with the bottom of the documents?) where they outline some of the methods and principles being employed in the throttling. Even more important, they actually have return codes that will tell you why your process was killed. This is great information, invaluable, and something anyone that is moving databases into the Microsoft cloud has to know. For example, currently (remember, all this is subject to change), there is what MS is called a “Throttling Cycle” that lasts 10 seconds. That’s how long your system could be throttled, but, if your system was throttled in the preceding Throttling Cycle, it makes it more likely that it will be throttled again (not absolute, just more likely). They even provide you with a list what is being monitored. Although the thresholds are described, the values are not supplied. You’ll have to figure those out yourself, the hard way. The throttling you receive (and that’s a fun sentence to type) will depend on what MS thinks it has to do. It can stop updates & inserted, all writes of any kind and finally, all reads and writes. When it rejects your connection, it will include a reason code stating why. I’d strongly suggest you talk to your developers so that they know about this and write their code to take it into account.

The guide on query performance optimization mentioned above, is just a blog post that shows how to use DMOs to retrieve performance data. It’s useful, but I think you’re going to want more complete guides.

I’ve already put up a blog post about the common performance tuning DMOs that are all still available in SQL Azure. What I haven’t spent much time talking about is execution plans and query performance tuning. I’m setting up more data into my test database and I’ll by playing around with SQL Azure, execution plans and queries, just to see if I find anything interesting. In the mean time, I strongly recommend taking advantage of the information MS is providing.