Back in 2009, Adam Machanic (b|t) started an initiative that is still going strong: T-SQL Tuesday. This is a monthly blog party. Every second Tuesday of each month, lots of people blog about the same topic, selected by the host for that month. And now, in the 99^th installment, I decided to finally join in!

The April 2018 edition is hosted by Jens Vestergaard (b|t). His challenge for this month: write about the tools, other than SSMS and VSDT, that we use for our work.

Essential tools

The full invitation even includes words such as “essential” tools, and “depend on said tool”. Well, that narrows it down pretty extensively in my case. Sometimes I get to work on my own machine, when customers give me access to their database or just send me a copy, or send me only an execution plan to work on. But often I have to work on the tools that the customer has installed, and I do not want to force any customers to first have to install my favorite tools before I can even start helping them.

I have always made sure that I can do my work using just the standard tools. That does not mean that I don’t like using tools. There are a few tools on my own computer that I will install on any new machine because of how much time they save me. But I do not depend on them, they are not essential. When a customer has different tools, or even no tools at all, I am still able to do my job – though, perhaps, sometimes a bit slower.

Code formatting

When I am asked to tune a slow running query, I mostly need to look at two things: the query and the execution plan. And while the T-SQL language can be hard to learn for someone not yet familiar with the concept of set-based processing, the actual number of keywords is quite small. So once you get the hang of it, reading queries is not that hard. When they are properly formatted.

But for some reason, not all queries are properly formatted. Some developers just don’t understand the One True Formatting Style ™. (In fact, I think only a single person in the world uses the OTFS and that’s me). That can make their code a bit harder to read. Or, depending on the style they do use, even more than just a bit.

Or even worse, you have code that is (hopefully!) generated by some tool and does not care about formatting at all. I have seen entire queries on a single line!

SQL Prompt

My standard method to quickly unravel the secrets of ill-formatted and nigh on unreadable queries is to paste the query in my SSMS window and hit Ctrl-K – Ctrl-Y. This is the keyboard shortcut for SQL Prompt to reformat the code. SQL Prompt is a tool that has a lot of functionality, but its code formatting is what I use most and what saves me the most time. Now I will grant you that this tool, too, does not understand the OTFS. But any formatting style is a huge improvement over some of the code I receive.

Other tools

But as I said in the introduction, I do not depend on just a single tool. If a customer wants me to work on their own infrastructure, I will. And that means I often do not have SQL Prompt available.

In those cases, when I am confronted with an ill-formatted query, I typically open an internet browser, go to a search engine and type “SQL formatter”. There are several webpages that allow me to paste in some SQL and have it reformatted for me. I just click one and go with what I get back. As mentioned before, when I need a query to be reformatted I do not care much about what style it gets, as long as the readability improves I am happy.

Execution plans

For digging into the execution plan, I usually just use SSMS. There are lots of tools that are targeted at making it easier to work with execution plans but I am mostly happy with what SSMS has to offer and I find that I have little need for tools.

Plan Explorer

There is, however, one tool that I do install on every machine I own, and that I would mention to customers if they asked me for a recommendation: SentryOne’s excellent Plan Explorer. I like this, and I regularly switch to it, because it has some very nice features that are not present or harder to find in SSMS.

However, unlike some other people I know Plan Explorer is not my default tool. Even on machines that have Plan Explorer available, I still use SSMS as the standard tool for working with execution plans and only switch to Plan Explorer when I need one of its improved features. And if I had to choose one execution plan tool for the rest of my life, it would be SSMS.

Properties

The reason that I prefer SSMS over Plan Explorer is that, if I want to really understand an execution plan, I need to look at the properties of the operators. All of them.

Both tools have a window that pops up when I hover over an operator, and that shows some of the properties. Not all. And I cannot select which properties are shown, that is chosen by the tool’s creators.

Both tools also allow access to all other properties, but they do so in a different way. In Plan Explorer, I need to switch away from the Plan Diagram tab to another tab (Plan Tree or Top Operations), then go to the column chooser, add properties I want to see, and then try to find the operators I am looking for in the list. And I need to repeat that for every property (of which there are far too many to just leave all of them always included in the list). This interface is absolutely fantastic for comparing the value of the same property for different operators in the execution plan, much easier than with SSMS. But for exploring and understanding the plan, it is cumbersome.

In SSMS the process is far simpler. I right-click an operator, select “Properties” and then pin the properties window. I then only need to click on a different operator and the window immediately changes to reflect the properties of that operator. Even the ones I did not yet know exist for that operator. For looking at an execution plan and trying to understand how exactly it works, this is a very simple and accessible method. But, obviously, it does not offer the at-a-glance value comparison that Plan Explorer has.

Conclusion

In this post I talked about some of the tools that I use for my daily work. But they do not qualify as “essential” for me. I do not want to depend on any tool, because that would become annoying if I ever need to work for a client where that tool is not available.

I never spent a lot of time looking for tools. The tools I use (and describe above) are not tools I selected after comparing several candidates. They are the tools that, at one point in my career, I “ran into” and then liked enough that I kept using them. Perhaps there are other tools out there, way better than the ones I use, and I just don’t know it yet.

That’s why I am happy with Jens’ choice of subject for this T-SQL Tuesday. I will definitely read through many of the other posts and see if there are any gems out there that I do want to add to my toolbox.

However, no matter how good they are, I will always refuse to become dependent on any tool.