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.


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.”


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,

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


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:



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.

Feb 07 2012

Avoiding Bad Query Performance

There’s a very old saying, “When you find yourself in a hole, stop digging.”

And my evidence today is:


That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard.

If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows.

Please, stop digging.

Aug 22 2011

Expert? Ha!

dunceHow do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book.

Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before.

I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew a lot. Then, in two weeks I find four different trace flags that I’ve never heard of, addressing interesting issues that maybe I should have known about. Holy cow!

I’ll try to put up a blog post on some of these trace flags that I’ve found. I’m also doing a little searching to see how many others I’ve missed. Here are the four:

2861: Includes zero cost plans in the cache.

2335: Extra memory messes up execution plans (by extra they mean >512GB)

2389: Ascending values stats cheat (pretty cool)

2390: A second, complimentary, ascending values stats cheat

I’ve talk about other trace flags in the past (and the fact that I was discovering them for the first time too). Microsoft’s “official” list is pretty small and only includes a single one relating to execution plans. Although, to be fair, that one is actually huge. But there are lots of others documented in various Knowledge Base articles but not listed as such in the core documentation.

More to learn, more to learn. That is a great, cool and humbling thing.

By the way, if you’ve ever been in the room when I got introduced as an “expert” and you saw me laugh at the introduction, this is why. I’m not an expert.

Oh, and worth mentioning, you should be very, very cautious when using trace flags. Check out this short blog post from Paul Randal (blog|twitter) on just that topic.

Oct 30 2008

Procedure Cache and Dynamic Management Views

I’m just going through the chapter on the procedure cache in the new book and I’m having a blast playing with the dynamic management views and functions that let you access the procedure cache. It’s just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints. I’ll get into those things another day, but they’re pretty exciting all by themselves. Finally, you can also get information about currently executing queries, which may or may not be in the cache, through sys.dm_exec_requests. These are all views, so you can just query them directly. Unfortunately, the first two don’t offer any chance to filter the information returned by database or spid, etc., but sys.dm_exec_requests does.

So, getting basic information about the size of a plan or how long it’s been cache or the accumulated time that it’s run is all interesting and everything, but what about getting a peek at the execution plan itself. Enter the dynamic management function sys.dm_exec_query_plan( plan_handle ). Just join it with one of the other views and suddenly you’ve got some real information:

FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) p

You’ll get an XML plan, which, when you click on it in 2008 opens up as a graphical plan since, to Management Studio in 2008, they’re the same thing. Easy-peasy.

But what about that query that’s hanging up and blocking all the othe query’s? What the heck did the developers do this time? Let’s try out the DMF sys.dm_exec_sql_text ( plan_handle ).

FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) t

You’ll see the query, as it was passed, in the text field. Great stuff. Really handy.

There is a ton to explore here and it’s all very useful stuff.  These are available in SQL Server 2005 in addition to SQL Server 2008.

Sep 09 2008

Query Fingerprints and Plan Fingerprints

SQL Server 2008 has been released, but the full capabilities of the product are still be discovered. This isn’t a case of discovery so much as Microsoft, in the person of Bart Duncan, has shown us something new. Read the article. This is one of the most exciting things I’ve seen in a long time. It’s especially exciting as we see databases becoming less and less manageable through the implementation of tools like nHibernate.

As I noted in a previous post, nHibernate will create a lot of execution plans. With the capabilities here, we’ll be able to easily and quickly aggregate some of those plans to identify the costly queries coming out of nHibernate without having to resort to 24 hour Profiler monitoring.

Great stuff.