Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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.

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...