Execution Plans … In the Cloud!

If you’re moving to a fully-fledged SQL database as part of Azure you may never even touch SQL Server Management Studio (SSMS). Instead you may do all the management of your SQL database through the Management Portal. You can actually do everything you need to through this tool. This includes writing and testing T-SQL statements. Believe it or not, it includes execution plans. But, if you’re used to seeing this from a query:

Then you may be a little bit surprised at this:

Believe it or not, those are identical plans. Yeah, the graphics are different, radically so. But the plans, those are the same.

I have a small test database that I have loaded into my local server and onto my Azure SQL Server database. It has identical structures and, thanks to SQL Data Generator, identical data sets. This means that when I run a query, assuming the statistics are the same, I should get identical plans.

For this post, I’m just going to scratch the surface and take a look at plans for this query:

SELECT * FROM dbo.Movie AS m
WHERE m.MovieId = 4138;

As you can see, both plans include the SELECT operator and a Clustered Index Seek operator. But as I’m constantly saying, just looking at the graphical part of the plan is not enough. You have to look at the properties. Let’s start with the SELECT operator. Here’s the properties from SSMS:

For comparison purposes, here’s what we get from the Management Portal. I’m going to post it up in pieces so that we can see it all.

When you click on an operator within the portal you’ll see a small set of properties, almost like the tool tip in SSMS. There’s a button there also “View More.” Clicking on that expands out the full set of properties. Here’s the first screen shot:

Obviously the order is different. But there are also some data differences. For example, the NodeID. In a plan in SSMS the nodes are numbered in a physical processing order, starting at zero. It’s one way to tell how the query was processed. In the Management Portal you can see that the first node has an ID of 1. The second one is 2. This follows a logical processing order. Also, the SELECT operator is given a NodeID. It does not have one, even in the XML, within SSMS. The other information is pretty standard and exists in both. I’ll keep scrolling down:

Here there are more differences. At the top, Statement Id. This is not displayed within the properties of an SSMS plan, but is available within the XML. It just shows which statement you’re working with if you have multiple T-SQL statements. Other pieces are the same, including exactly the same Subtree cost (which means the statistics are identical and the cost is estimated by the optimizer in both Azure and my SQL Server 2012 instance in the same way). Both plans were TRIVIAL and used simple parameterization.The Optimizer Hardware Dependent Properties show differences, but that’s to be expected. However, it does give you a glimpse at what is exposed to SQL Server within the Azure system. For example, the max degree of parallelism for this system is 3. I’ve got an EstimatedAvailableMemoryGrant of 4500062. Compare that with the 208838 on my 16gb laptop and you can see how much bigger the hardware is for the Azure system. Let me scroll down a little more:

The last major difference is more visible in this screen capture than the last one. The “Reason for NonParallel Plan” is not visible in the 2012 properties. It’s also not in the XML. This is new information exposed about this query and the execution plan generated.

With this one operator we can see that these plans, while similar, are not identical. I’ll post more about these in the coming weeks.

For more details on query tuning and execution plans, including Azure, please attend my pre-conference seminar at the PASS Summit 2012.