Jan 14 2013

Execution Plans on Azure SQL Database Portal

If you’ve been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You’ll get back results, timing, everything you need to observe behavior within the system. What’s that? You never heard of this? Let’s figure it out real quick. Here’s my portal to my Azure SQL Databases:

Azure SQL Database Portal showing usage overview

Azure SQL Database Portal showing usage overview

SQLAzureManageButtonYes, all sorts of things you can do from here, but we’re focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below:

Part of the Azure SQL Management Portal showing some of what can be done.

Part of the Azure SQL Management Portal showing some of what can be done.

Pretty prominent in the upper left corner, you can see the New Query button. I’m sure you’ll be shocked to find that clicking on that gives you another window in the portal as shown below. Worth noting though is the listing under My Work on the left side of the screen. These are more or less windows that you can switch between, without losing your work. It’s almost like tabs in SSMS. You can open multiple queries as you see. I have two unnamed queries I’m playing with and a file, TraceCaptureDef.1.sql, which will generate all sorts of fun errors if I try to run it on Azure. But the main point is, don’t feel like you have to open multiple tabs in your browser, you have a lot of control right there in the Management Portal.

Azure Portal with a Query Window open

Azure Portal with a Query Window open

I have a query there ready to go. A few more points about that query. Note that you do have some color coding, showing key words highlighted in blue. I typed this query and formatted in Management Studio before pasting it into the query window here, but that formatting went away to a degree. Something to keep in mind. Also note, that you don’t have properties about this query that you can set. If you want to see statitistc io or time, which you can, you’ll have to set them manually using T-SQL code. But once set, you can get messages showing how long the query ran and what sort of resources were used as you see here:

 

Messages shown after running a query in Azure SQL Database

Messages shown after running a query in Azure SQL Database

You can also choose to look at an Estimated execution plan by clicking the appropriate button, or toggle the inclusion of an Actual Execution plan by again, clicking the button at the top of the window. Here’s what you get for an Actual plan within Azure SQL Database:

Execution plan for the query outlined above as displayed in Azure Management Portal

Execution plan for the query outlined above as displayed in Azure Management Portal

I’ve posted in the past about some of the details that you can access when looking at execution plans within Azure SQL Databases. I’ll be posting some more on the Management Portal, execution plans, and query tuning in Azure. ¬†Worth mentioning, if you look at my posts from several month ago, how they’re displaying icons in the plans has already changed. This is a seriously moving target.

Before I go, compare that execution plan with the same one from SSMS:

SSMS Version of the same query against an identical structure

SSMS Version of the same query against an identical structure

A couple of things to point out. Obviously, it’s different. But the real point here is that these are identical, empty, databases. The structures are 100% the same. The queries are 100% the same. But the Azure SQL Database plan includes a scan where there’s a seek in SSMS. More fun little indications that the optimizer that exists in Azure is not the same as in the base product (and yes, I’ve got the latest SP and CU installed). Something to keep in mind when you start writing queries against your Azure SQL Database instance.

9 Comments

  • By Grant Fritchey, January 14, 2013 @ 2:23 pm

    This is from Feodor Georgiev:

    Grant, I am afraid that there is a slight bug in the “Usage overview” bar graph – I have never seen this one move. No matter how much of the database is used, it does not show anything else besides ‘Usage data not available’.
    Usage data aside, I personally have a problem with execution plans, especially when it comes to their predictability.
    In other words, as a DBA I work very hard so I can have predictable performance of my systems at all times (when I expect a seek – I get a seek, when I expect a scan – I get a scan – not more not less).
    A while back I fiddled a bit with SQL Azure, and I managed to find out what the names of the physical machines are of the SQL Servers – at least the physical machines involved with my databases. Here is a blog post I did on the topic: http://sqlconcept.com/2012/11/23/fiddling-with-sql-azure/
    What bothers me the most is the fact that these machines are not only for my use, they are shared in a random manner with other databases which may or may not have scans, seeks or any random amount of performance issues.
    In this sense Azure reminds me of those russian communal apartments of the 19th century which Dostoevsky describes so vividly (there is always someone drunk, someone asleep, someone overexcited making noise and so on).

  • By Grant Fritchey, January 14, 2013 @ 2:26 pm

    I’ve got a review of Herve Roggero’s chapter in Pro SQL Server 2012 Practices coming out next week. He introduces SQL Databases. His argument is that performance is sacrificed for scalability and uptime. That it’s intentional. I see and understand both points of view.

  • By Kevin, January 14, 2013 @ 2:46 pm

    Just curious, Grant. But how can you get an execution plan “down” from the cloud? Is it only available through the SET SHOWPLAN commands? Thanks, -Kev

  • By Feodor, January 15, 2013 @ 6:59 am

    @Kevin: There should not be a problem to get the execution plans. We can connect via SSMS to an Azure database and we can view the plans in Management Studio.
    In this article Grant is refering to the web UI of Azure, but there are more robust features in SSMS when it comes to exploring Azure.

  • By Grant Fritchey, January 15, 2013 @ 7:02 am

    Hey Kevin,

    Hang on, hang on. I’m working on that blog post now. Should be up next week.

Other Links to this Post

  1. Something for the Weekend - SQL Server Links 18/01/13 — January 18, 2013 @ 9:04 am

  2. Windows Azure Community News Roundup (Edition #51) - Windows Azure Blog — January 20, 2013 @ 3:15 am

  3. Accessing Execution Plans on Azure SQL Database | Home Of The Scary DBA — January 23, 2013 @ 10:04 am

  4. Azure SQL Database Execution Plan Differences | Home Of The Scary DBA — January 30, 2013 @ 8:46 am

RSS feed for comments on this post. TrackBack URI

Leave a comment