Aaron Bertrand

More showplan improvements? Yes, please!

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

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.


Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

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.