SQL Azure and Query plans / Performance

I showed you a while ago the improved SQL Azure management portal. After having nothing in 2008, Project Houston was a great step towards a Cloud only management portal. But December 2011 they renewed the complete SQL Azure management portal. Besides a more Metro look-and-feel they added also some extreme helpful tools and utilities.

sqlazureperf1

When clicking on Administration in the lower left corner, you get an overview of your Database health etc. Information about the utilization of your database, connections and users. In this case the database was fairly new and unused, so the query usage is empty.

sqlazureperf12

On a database with more activities this part of the portal looks like this. Yes, my SQL Azure database are not very busy Winking smile.

sqlazureperf14

As you click on the Query Performance link, you get detailed information about the queries on your database and their performance. This gives you a first impression about the overall performance of your SQL Azure instance.

sqlazureperf13

Imagine you have a problem with a certain query. I am talking about the performance of course Winking smile. To get a good view of the query you need a Query plan. Previous your only tool was SQL Server Management Studio and a few SQL Azure Management views.

sqlazureperf10

Ok, this was good. But the downside of this approach was, there was some latency between your client and the SQL Azure instance etc. So it wasn’t perfect.

Now with the new portal there support for query plans in the Cloud Winking smile. Via the portal you can create a new query and this query of course can be executed. But now there a two more options. So there is RUN, Actual Plan and Estimate plan.

sqlazureperf2

With RUN you get the result of your query. Simple as you are used to with the Project Houston site.

sqlazureperf3

But when choosing for Actual Plan, you get an extra tab: Query Plan.

sqlazureperf4

On this tab you get a graphical representation of the query plan. And due to the cool Silverlight 5 environment it looks nicer then the ‘old’ SQL Server Management Studio. There are three options: Total, CPU and IO.

sqlazureperf5

sqlazureperf6

sqlazureperf7

Besides that you can also choose for more details by selecting a Grid or tree representation.

sqlazureperf8

sqlazureperf9

More detail can also be in the imaged version of the query plan, if you play with the zoom level.

sqlazureperf11

So how cool is that! There is almost no need for a Client tool like SQL Server Management Studio. Almost everything can be done in the cloud.

Leave a Reply