Jan 30 2013

Azure SQL Database Execution Plan Differences

I’ve been exploring execution plans in Azure SQL Databases a lot lately. I’m getting a presentation together for ┬ásome upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I’ve loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio.

Here’s the property sheet from the SELECT operator for the query run against SQL Server:

AzureSelectProperties

And here’s the property sheet from the SELECT operator for the query run against SQL Database:

AzureSSMSSelectProperties

Two differences jump out. The smaller of the two is that for the same data set, there are differences in estimated values. I noted that before, but it’s fun to see it coming up. By the way, if a good estimate could be defined as “closest without going over” then the optimizer in SQL Database is closer with 1.9 to the SQL Server value of 2.1. Just sayin’. Anyway, the big thing that you may have noticed was the NonParallelPlanReason with a value of CouldNotGenerateValidParallelPlan. Oooh, cool! Not that I would have expected a parallel plan for an estimated cost of .0149347. Assuming SQL Database has a default value of 5 for parallel plans, that’s pretty far off (and I hope they don’t still use that ancient value, nor should you). Some enterprising individual could try to suss out how much cost is needed to get a parallel plan (I don’t have time for that right now). More to come on execution plans and query tuning in Azure SQL Databases.

Jan 23 2013

Saving Execution Plans on Azure SQL Database

In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it’s pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can’t think of at the moment?

To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most obviously, when you look at the plan in SSMS you can just right click on that plan and select “Save Execution Plan As…” from the context menu. This will allow you to create a .sqlplan file on your OS. From there, do what you want. The file saved is XML, but the .sqlplan format is read by SSMS as a graphical plan. You can look at the underlying XML any time you want by right clicking and selecting the appropriate context menu.

And Azure SQL Database?

Well, things are a little different. Let’s start with the query. After you execute the query to get an actual plan or call up the estimated plan you’ll see the plan in the Portal.

SaveQueryPlanAndQuery

You can see the query, the plan, and options with the plan (to be explored in a later post). If you right click on the plan as you would within SSMS, the context menu is singularly unhelpful since it will only have one option ‘Silverlight.’ (yes, we’re still running Silverlight in this part of Azure, don’t shoot the messenger). So I guess there’s no way to save that plan, right? Wrong. Look up at the top of the image above. You’ll see a button that looks like a 3.25″ floppy disk (kids, get granddad to explain what that is) and the phrase “Save As” underneath it. Click on that and you can save your query. Ah, but there’s a drop down under file types and there you’ll see “SQL Plan File.”

SaveSaveAs

Provide a file name and you can save the execution plan down to your machine. Easy.

Ah, but there is a catch. Let’s say we query the Dynamic Management Objects to pull plans out of the cache. This query will run on both SQL Server and Azure SQL Database:

SELECT	deqp.query_plan
FROM	sys.dm_exec_query_stats AS deqs
		CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
		CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE	dest.text LIKE 'SELECT	m.MovieName,
		msd.MovieStageName,
		st.StageTypeDesc%';

The results, when run from SSMS, will look something like this:

SaveURL

That’s an embedded URL that you can click which will then open the execution plan from the cache. But, when the same query is run from SQL Database Management Portal, the results look like this:

SaveNoURL

 

No options for a clickable interface here to open the plans. You can try copying the output and then pasting it into Notepad then saving as a .sqlplan file. Of course, that’s only going to work for small plans since the result set here is not unlimited. You could build an app to query the cache and output to file. But, in general, querying directly to the plan cache from the SQL Database Management Portal gives you very few options for displaying your query plan.

Since this is such an important part of query tuning, here’s hoping that we get further enhancements to the Management Portal in the future.

Jan 21 2013

Pro SQL Server 2012 Practices: Chapter 12

A9781430247708-3d_1I was very privileged to get the opportunity to write a chapter in a book with some of my friends and SQL Family, Pro SQL Server 2012 Practices
. Just as each of us took a chapter to write, each of us going to take a chapter to review. But, being the greedy sort, I’m going to review two. First up, Chapter 12, “Windows Azure SQL Database for DBAs”, by Herve Roggero (b|t)

Personally, I love Azure. And I love Azure SQL Databases. But, I get the fear and trepidation they might cause. I also get the urge to write about them, but I never really felt like I should approach them from a book. Everything changes so much, so quickly in Azure and books just take a while to get out the door. It all seemed like an exercise in terror. But, Herve Roggero has taken a very smart approach here. Instead of a straight how-to, which is what I would have stupidly done, Herve has done a “why” approach. Why would you use this? What does it offer? What business need are you fulfilling? And I really love that approach to technology, not worrying about the technical aspects of it, but the business needs that it answers.

Herve starts with the basic architecture of the SQL Database. His emphasis is on the fact that we’re talking availability and up-time instead of performance. He does a good overview of how things are put together on the server so you can understand what’s happening to your data and why. He then goes on to show differences between what you are presumably familiar with, SQL Server, and SQL Database. He also covers the various management tools available online as part of Azure and security settings. He’s very careful to qualify things so that you know that everything is subject to change, but he still packs a lot of useful information in as he discusses the types of databases available and how to backup your data (you don’t, but you can get around that to a degree).

Herve spends a lot of time talking about how Federations are currently working within SQL Database. This is because Federations are one of the key business needs that the Azure SQL Database answers. He gives a great overview of Federations works then walks through a simple example so that you can understand what he meant. He finishes up that section with a review on the limitations so you will know how it affects your business needs.

Since performance is a secondary consideration everyone can breathe a sigh of relief, right? No! Of course not. And Herve doesn’t let us down. He covers some of the most important performance Dynamic Management Objects (although he calls them DMVs, dude, get with the program. Kidding, kidding.) you’ll need for understanding which of your queries are performing badly. He also provides a nice overview of how execution plans are presented within the SQL Database Management Portal (SDMP, a new abbreviation, I’ll use this). He also shows how the Query Performance dashboard will help you identify problem queries within your SQL Database.

Herve also covers some of the other utilities included with SQL Database such as Reporting Services and SQL Data sync. It’s important to remember, we’re not just talking T-SQL here. This is a full infrastructure to answer complete business needs. Herve doesn’t forget. He concludes with a short discussion on price, something that must be taken into account when talking about Azure SQL Database.

In summary, this is an excellent introduction to the topic of the Azure SQL Database. Herve has supplied you with the information you need to begin to make decisions on whether or not this could be something to satisfy business needs within your organization. Nice work Herve.