Recently, I’ve been getting more and more engrossed in SQL Server Execution plans for various reasons, including editing the forthcoming third edition of Grant Fritchey’s book.

There are certain infographics that ‘speak’ to everyone. They represent highly complex concepts clearly and unequivocally. Unfortunately, the SQL Server graphical execution plan is not one of them. I was thinking more of the London “Tube” map, so well loved by the British public that in 2006 it was awarded the status of “national design icon”. It is the result of hard work and experiment, followed by sudden inspiration. It took many iterations and the work of many draughtsman, engineers, and artists to get there. Early designs were cluttered by background detail, such as roads and parks. A desire to maintain the correct geographical perspective meant that the most important part of the map (central London) was cramped and hard to read. Eventually, in 1931, an electrical draughtsman called Harry Beck applied to the map his knowledge of circuit diagrams and produced the schematic version of the map that is still recognizable today.

Londoners have, for years have been able to tell at a glance their best course of action (“Marble Arch is best by the Victoria line with a change at Oxford Circus“). By contrast, how often do you look at an execution plan and immediately say “Ah, of course, silly me, the best course of action here is to swap the order of the key columns in our compound clustered index on the ProductInventory table!“

After a couple of drinks, most developers will break silence and ruefully admit that they get little or no value from graphical execution plans. They haven’t the time for the long initiation into the art of reading the runes. They have other preoccupations.

Sure, we can, and have, made step-wise improvements to graphical query plans. Third party tools such as Plan Explorer, or ANTS Performance Profiler fix some of the issues of the native graphical plans, and use color to draw one’s attention to the high cost and data heavy operations in the plan. However, the costs are estimates and not always reliable, and sometimes the problem is hidden away in an apparently “0% cost” operation. Working out what’s to be done often requires a trawl through the background properties for each operator, as well as referring to index definitions and so on. In some ways, the execution plan has the reverse problem of the London Tube map, in that it’s very hard to tell what’s going on without exposing the background clutter. It is a tough problem to crack. We really need that moment of inspiration equivalent to Harry Beck’s, in redesigning the Tube Map that will make them more accessible to the developers who need the information they mainly hide.

However obvious the problem might be, execution plans spread out much of the evidence over a wide area. This is fine for the sleuths and detail-freaks amongst us, but the silent majority of developers just want to know who the suspects and to have the evidence against them presented in plain terms. I sometimes wonder if we are sticking too closely to the XML that underlies the execution plan. Is there a more intelligent way to use it to present a visual ‘blow-up profile’ of the prime suspects, showing not only that we have a high cost scan, for example, but making inferences as to the probable cause, such as lack of usable indexes or poor choice of clustered index, or misuse of a function that has prevented a seek operation. Most crimes against relational database are such obvious crimes of passion that the means and motive could at least be suggested to the hapless developer?