It is useful to know that a SQL Query is slow, but it is even better to know why. To do this, we need to see how SQL Server’s Query Optimizer has translated the query into something that it can execute, and what happened at each stage of execution, and for this we need to see the execution plans.

To some developers, the database is just a black box at which they throw queries. The majority, however, care about database performance, and of the SQL code they produce. They are intrigued to know exactly how SQL Server accesses the tables and gathers the data, compared to what they think is the logical access path.

Does this mean, however, that they take the time to examine and understand the SQL execution plans, with the same zeal as a DBA? Maybe. A very experienced SQL developer recently confessed to me that he only rarely looked at execution plans. To do so was to get too deep into “implementation” rather than the code. He wrote the algorithm, tested that it was functionally correct, got some timings, and then tweaked it until it was within the performance specification. If that didn’t work, it rather meant he had simply come up with the wrong approach, and needed to step back, rethink, and start from scratch with an approach which fitted better with the data model, and so was more likely to scale. To waste time refining a faulty algorithm was like putting lipstick on a pig.

I was intrigued by the idea that in seeking a tool that could, potentially, help to improve faulty code, the developer was ‘wasting time’. There is, of course, the hassle of cranking up another GUI, if a developer has crept outside the comfort zone of Visual Studio to access SQL Server from Python, R, the PowerShell GUI, or a performance profiler. Until relatively recently, it wasn’t even easy to view the execution plan from within Visual Studio. The “include actual execution plan” button, familiar to all who use SQL Server Management Studio (SSMs), is there in SQL Server Data Tools (SSDT), but SSDT was only fully-integrated in VS2013.

Another problem is that, for some developers, execution plans are as easy to read in SSMS as ancient Babylonian. The information is there but not in an intuitive way geared for the occasional user. Regardless of how or where developers write or generate their SQL code, it needs to support not just an easy way to return the execution plan, but also to present it in a digestible format that makes for easier interpretation, and quicker troubleshooting.

It was with these thoughts in mind that we published SQL Server Central’s browser-based tool, http://sqltuneup.sqlservercentral.com/. Just paste in the XML plan text to see a nice visual representation of the plan, complete with clear indications of the expensive and “data heavy” operations that present likely hotspots in the plan. The same tool is now also built into the latest version of Redgate’s ANTS Performance Profiler, meaning that developers can investigate side-by-side any bottlenecks in both their application code and SQL code, and view immediately the execution plans for any seemingly-problematic queries.

Increasingly, there are good tools and educational content to help developers use and understand SQL Server query execution plans. It will be interesting to see if developers really do use them, or if they will remain largely a DBA-only tool. Are there any other down-sides to encouraging developers to delve into execution plans? I’d love to hear your views.