Aaron Bertrand

More showplan improvements? Yes, please!

December 8, 2015 by in SQL Optimizer, SQL Plan | No Comments
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

Showplan ImprovementsSince it seems like Microsoft is in an investing mood, given their recent addition of Actual Rows Read (see this post from Kendra Little for more details), I thought I would point out a few other enhancements I think would be great ideas for improving the lives of query tuners.

The first is one I posted just the other day, asking for the XML to reveal the interpreted data type of a parameter (whether it comes from a variable, input parameter, or literal):

Another is the following from Hugo Kornelis, who asks for more accurate row counts on loop join operators (background in Connect #491342). Paul White and Erland Sommarskog agree:

Speaking of Paul White, he recommends that unique filtered indexes should actually be recognized by the optimizer as unique; currently, suboptimal plans can be produced (but this was brushed away with no comment):

And Paul also recommends distinction in the plan when a GROUP BY has been simplified away (also closed as won't fix, without much further information at all):

(Paul also has four active bugs that you should read up on, IMHO.)

Finally, Adam Machanic complains about a pervasive issue where clicking on showplan XML in Management Studio yields raw XML instead of the graphical plan:

I'm sure you have other ideas for showplan improvements, including Connect items I missed here. But I hope you can find the time to read, understand, and vote and comment on these issues – while it may not feel like it, doing so is worthwhile, even if the item is currently closed.