While I’ve been familiar for many years with the ‘sys.dm_exec_query_plan’ dmv that will produce an XML formatted version of an execution plan (using the ‘query_plan’ column), I had to refresh my memory today on just how to convert it to something a bit more readable. Well, today I get to refresh your memory as well.
I’m probably not exaggerating when I say 99% of us DBA’s prefer to look at the graphical version of an (actual) execution plan over the XML gobbledygook. While even the graphical version can get extremely large and hard to deal with, it’s nothing like trying to find what you need from an XML file of the same plan. Today I wrote a small script to gather performance metrics from joining ‘sys.dm_exec_query_stats’, ‘sys.dm_exec_sql_text’, and ‘sys.dm_exec_query_plan’. The query_plan column produces a very handy hyperlink to the execution plan currently in cache, but by default it comes out in XML format. So, how can I change to a graphical version so that I can much more easily iterate through it and see what scans/seeks/etc are being used?
Just like a waltz, it turns out it is indeed a very simple three-step process…
- Click the actual hyperlink and let the XML plan load in SSMS
- Click File > Save Plan.xml As…
- Save it locally with the file extension “.sqlplan”
Really, that’s all there is to it. Just double-click this new “.sqlplan” file and it should open in SSMS as a graphical plan….ready for you to peruse to your heart’s content. Told you it was easy.
Have a grateful day…Troy
Tags: dmv, execution plan, query-tuning, SSMS
Posted in DMV's, SSMS, T-SQL | Comments (0)
From the “did you know” category…
Did you know you could highlight an object in SSMS, right-click it, and drag it to Excel? I oft have the need to list procedures in Excel and the “old” copy/paste methodology becomes cumbersome when you have to do it over and over again. But as I said; if you right-click and drag an object across your desktop to Excel, you can then un-click and have the option to move or copy it at your destination. This is very handy, particularly in a dual monitor setup where Excel’s open on one side and SSMS on the other.
By the way, before you ask, yes it works just as well with your destination being any of Microsoft’s Office Suite. I tested it in Word & Outlook just now and it works dandy. Not sure why I’m so surprised these Office applications work well together like that; I guess I shouldn’t be.
Have a Grateful day…Troy
Tags: 2000, 2005, 2008, excel, oulook, SSMS, word
Posted in Office, SSMS | Comments (3)
A lot of us DBAs spend day after day looking at our SSMS windows in the “Courier New” font. It’s the default. It’s the one you learned from. It’s comfortable because it’s what we’re used to. Microsoft provides us a big, long list for us to choose from should we wish to mix things up.
Do you indeed change it from the default, or you one of those “leave well enough alone” kind of DBA’s? Choose one below and I’ll post the results in a week or two.
Have a grateful day…Troy
Tags: 2005, 2008, SSMS
Posted in SSMS | Comments (1)
Now, I certainly understand that when a monstrous piece of software as complicated as SQL Server 2008 R2 is released, that there are literally 3.2 gajillion details to get right. And I freely admit that Microsoft got 99% of them right on with this latest release, but this one’s really got me scratching my head.
SSMS allows us to specify a default save location for query execution result sets, just as you would expect an enterprise level product should.
QUERY RESULTS DIALOG
But what SSMS doesn’t allow us to do, is to specify a default save location for the query definition itself. Notice what’s conspicuously missing…
QUERY EXECUTION DIALOG
My current position requires me to consistently save scripts to the same folder throughout the day, then go ahead and create a new folder the next day and save that day’s scripts in there. This folder is of course buried 5 or 6 levels deep on my local machine. The process of having to navigate EVERY time through all six levels just to save the script is maddening to say the least. So, what’s a modern DBA to do to alleviate this situation? Well, my work-around saves me loads of time and really wasn’t difficult to set up as I thought at first it might have been.
The “Save File As” menu option does indeed provide us with this handy bar on it’s left side:
It occurred to me that since they were kind enough to offer me a expressway to my desktop, I could leverage that to resolve my problem. And sure enough all it takes is an ordinary shortcut on the desktop that I only have to change it’s definition each morning and viola, I’m two clicks at the most away from being able to save my script into my multi-nested location.
Notice I’ve labeled it “Today,” so that part never has to change. I simply create the new folder with today’s date (the actual nested one…which I have to do anyway) and then quickly change the path the desktop shortcut points to. That’s really all there is to it. Two clicks and I’m saving my script to the proper place.
I know none of this is mind-blowing cutting edge stuff, but it’s certainly made my life easier and I wanted to share this little epiphany with you kind people.
Have a grateful day…Troy
Tags: 2008, SSMS
Posted in SSMS | Comments (3)