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!