SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It’s best to just show you:

Estimated vs actual rows

You can see the estimated and actual number of rows right there on the query plan just like live query plans! You no longer have to waste hours of your life hovering over different parts of the query plan in order to see where the estimated row counts veer off from the actual row counts.

This doesn’t require SQL Server 2019, either.

I am fully prepared to call this the Best Management Studio Ever.

However, one could still look this gift prize-winning thoroughbred in the mouth and point out that it only works on actual plans, not estimates. (It’d be nice to see the estimates on estimated plans.)

Also, the readability on this is pretty bad, especially on large query plans. We desperately need commas to separate thousands and millions, and some color coding for variances would be kinda nice. Here’s a live view of me looking at a large plan and trying to figure out if there’s a variance between tons of large numbers:

Not sure if 59758912 ~= 6851048

Previous Post
We Need to Talk About the Warnings In Your Query Plans.
Next Post
Making The Query Plan Profile Hint More Useful

9 Comments. Leave new

  • Hey Brent,

    Grea- …. never mind….

    I’m an execution plan junkie like you, so I love this new feature in SSMS 18, but (and I can’t believe I’m saying this) but I have to dock it a point because it doesn’t yet work with the Redgate Toolbelt. In all fairness, that should be a point docked from Redgate, but I’ve already docked them like a bajillion points, and someone has to get docked. Good times ahead for performance tuners!

    P.S. Chicago misses you, we got up to 88 yesterday. But of course, in typical Chicago fashion, we’re trending for 61 today.

    Reply
    • Yeah, it’s a new Visual Studio base. I feel sorry for tool vendors – it’s really hard to keep up with Microsoft’s changes to Management Studio, SQL Operations Studio, Azure Data Studio, etc. They have to invest a lot of money to keep their tools in sync with SSMS/SOS/ADS, and that reduces their already-low profit margins on tools like that. It’s a tough call.

      Reply
  • So I could be wrong. But it looks like it rounds to Estimated rows up to the nearest whole number?

    Reply
  • alen teplitsky
    October 4, 2018 2:17 pm

    even with these minor issues it’s pretty awesome when you’re looking at a large plan for a problem query and you can easily tell the tables with tens of thousands or millions of rows apart from those with a few hundred rows when all you see is a dozen table or clustered index scans

    Reply
  • […] Brent Ozar points out a big enhancement to the way SQL Server Management Studio views actual query p…: […]

    Reply
  • SSMS 18 removes SQL debugging, so it’s not all wine and roses…

    Reply
  • More than an graphical query plan, we’ll need a text/grid query plan like Oracle.
    Reading an execution plan with more than 10 joins in SSMS has become impossible. You have to horizontal-scroll all the time. Boring !

    Reply
  • Oh yeah…commas would be really great. This reminds me of the builtin query store reports. If you switch to logical reads you get them in Kilobytes and with no separators whatsoever. I haven’t met someone who cares if his or her query uses 500 kilobytes more or less. However most people care if the query uses 500 Megabytes more or less or even less than that :-).

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}