SSMS hidden gem: Edit Query Text

When I am tuning a query, I usually start with the query text. I execute it to retrieve an execution plan, and then the fun starts. However, very often I first need to find the specific query (or queries) to tune. There are multiple ways to do this, depending on what monitoring tools and which version of SQL Server the customer uses. In some cases, I at first find only an execution plan. Now I do like to see execution plans when tuning, but I also need to have the query!

The old method

So here is an example of what your SSMS screen may look like when you open an execution plan:

As you can see, the top of the screen shows the query text. Or rather, the start of it. Even with large, high resolution monitors, and even with very small fonts, the single line allocated to this is typically too short to show the full query. In most cases, I see SELECT and the first 10 or 15 columns names, but the FROM is already invisible.

This is just a display issue. The actual execution plan contains the first 4000 characters of the query (which luckily in most cases equates to the entire query), it just doesn’t fit in the SSMS window. And until recently, I did not know a simple way to get to this string. So what I had to do was right-click somewhere in the plan area, select “Show Execution Plan XML”, then in the XML look for the property StatementText=“xxxx”. This then shows the entire query string, with line breaks represented as “
” and a few similar XML-friendly (but person-unfriendly) replacements.

This is usually enough to locate the original query in the source code of the application. If that fails, I can copy the entire content of the StatementText property, paste it in a new window, use search and replace to change all that weird XML stuff back to normal text, and then I finally have the query and I can start tuning.

Bottom line: It works, but it ain’t pretty.

Enter: Edit Query Text

However, there is good news. I recently discovered an incredibly useful new function in SSMS 17.4. I do not know when it was first introduced. Perhaps it really is completely new, or perhaps it has been around for a few years already and I just never noticed. Who knows.

Look at the little button to the right of the query text. This is the new function I discovered. If I hover my mouse over it, it reveals its name: “Edit Query Text”. But that’s not what excites me. What does make me happy is what it does. When I click this button, a new SSMS window opens, with the query text that is stored within the execution plan. And no weird 
&#xA shenanigans either – just normal query text, ready to be executed, or to be modified and tuned.

Now obviously, the 4000 character limit does still apply. Only the first 4000 characters of a query are stored in the execution plan. SSMS can not present you data it doesn’t have. But for any query of normal length, this tiny button offers an amazingly simple and useful way to quickly get the text of the query that resulted in this execution plan.

Thank you, Microsoft!

Execution plans 101: (back to) The basics
Plansplaining, part 3. How repeating work saves time

Related Posts

No results found

3 Comments. Leave new

  • The button is new, afaik, previously it was on the right-click menu

    Reply
    • Hugo Kornelis
      March 4, 2018 12:56

      Thanks for your comment, Gail!

      I’ve received similar remarks on Twitter, and I must admit that I was surprised. I’ve worked so much with execution plans and I had never seen it.

      But I also decided to fire up some VMs with old versions of SSMS to find out how long exactly I have been blind, and it turns out that the history of this function is rather interesting.

      So, in SQL Server 2008R2, this option did not exist. At all.

      I did find it in SQL Server 2012 (so that’s just six years of me overloking the obvious – ahem!). However, it did not always appear. If you have a query in the window and request an execution plan (actual or estimated), then you will not see this function in the right-click menu. However, when you open an execution plan by clicking on its XML or by opening a .sqlplan file, then you DO have this option!

      This was not changed in SQL Server 2014. However, in SQL Server 2016 the Edit Query Text button I describe in this post was ADDED. You still do not get the Edit Query Text option when requesting an actual or estimated execution plan from a query that you already have (which, to be honest, does make some sense). But when you open an execution plan from the .sqlplan file, you can now access the query text BOTH by right-clicking AND by using the button.

      And finally, in SSMS 17.4 and 17.5 I see the Edit Query Text button in all cases, both when you obtain the plan from a query window and requesting actual or estimated plan AND when you click the plan’s XML or open a .sqlplan. The right-click option still exists as well – but just like previous editions, this version is not available for actual and estimated plans obtained from a query in the query window.

      Whew!

      Reply
      • Kinda makes sense. There’s not much point in asking for the query for a plan that you got by running the query.

        Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close