SSMS 18.5 – small change, huge effect!

The latest version of SSMS has just been released. Version 18.5.

And I need all of you to update your version. Now. Yes, right now. Here’s a link to download it. I’ll wait.

Why the rush, you ask? Because hidden in between all the little (and some big) improvements and fixes, there is one true gem. One I wish Microsoft had done … oh, let’s say two decades ago?

Misleading information

If you have been to any of my talks about execution plan, you probably have heard me fuming about (and warning you for) the way estimated and actual number of rows are shown in execution plans.

But for those that haven’t met me yet, here is a short explanation. Or rather, a repro. Just execute this query against any AdventureWorks database, and request an execution plan plus run-time statistics (formerly known as actual execution plan):

Here’s the execution plan you should see. And included in the screenshot is the property popup you get when you hover your mouse over the arrow between the Clustered Index Seek and the Nested Loops:

If you look at this, you’ll see a big giant red flag. The Clustered Index Seek is estimated to return 3.86 rows, yet it actually returns 245 rows. That looks like a terrible estimate, and many people would jump right in to investigate.

But in reality, this is just SQL Server being deliberately confusing. Let’s also look at the properties of the Clustered Index Seek itself. You’ll see the same values as above (after all the properties shown on an arrow are by definition a subset of the properties of the right-hand side operator of that arrow), but you’ll see many other properties too.

What you need to be aware of is: this operator is on the outer (lower) input of a Nested Loops join operator. And that means that you need to translate the values. In the execution plan XML, the value for the Estimated Number of Rows property is always computed as the average number of rows for each execution. This operator has Estimated Number of Execution = 16, so this means that the optimizer estimates that the operator will execute 16 times, returning 3.86 rows on average per execution, to return a grand total of 61.7 rows.

The Actual Number of Rows in the execution plan XML is listed as 245, which is the grand total counted across the (actual) Number of Execution of 16. So 15.3 per execution on average.

Of course, 15.3 versus  3.86 (or 245 versus 61.7) is still an incorrect estimate and might require attention, but it’s not nearly as bad as it looked at first.

Of course, the same anomaly between how estimated and actual row count are shown can also result in an apparent mismatch when in reality the estimates are perfect, or in an apparent perfect estimation when in reality the estimate is much too low!

The simple fix in SSMS 18.5

Management Studio 18.5 has fixed this. Now of course Management Studio is a client tool. It cannot change what is in the execution plan XML that is returned by SQL Server. But it can change how it displays information.

Let’s quickly download and install SSMS 18.5. And then load and execute the same query again. Here’s what you now see when you hover your mouse over the same arrow in the execution plan (left), or over the Clustered Index Seek operator (right):

Here are the changes you can see:

  1. The property Actual Number of Rows has been renamed to Actual Number of Rows for All Executions. The value is unchanged..
  2. The property Estimated Number of Rows has been renamed to Estimated Number of Rows Per Execution. Also with unchanged value.
  3. A new property has been added: Estimated Number of Rows for All Executions. This does not come directly from the execution plan XML; it is computed by SSMS by multiplying the Estimated Number of Rows Per Execution with the Estimated Number of Executions.

Just the renaming of the two existing properties is already a huge step forward. Now the very name of the two properties makes it clear that they cannot just be compared as is.

And the addition of a computed property Estimated Number of Rows for All Executions that can be directly compared to the Actual Number of Rows for All Executions is the icing on the cake.

Well done, Microsoft!

Thanks!

Going forward

I’m going to need to reserve some time over the next days and weeks to properly investigate this change. A very quick test in a parallel plan (where Number of Executions can also be non-zero, but where the estimated number of rows in the execution plan is still the grand total) shows that SSMS is aware of this difference; it does not cause errors. But I have not yet found the time to see if I can make it fail once I manage to get enough confusion and complexity in the execution plan.

Before doing that, though, I will have to verify, and probably do a lot of updates in, the Execution Plan Reference. And I’ll need to change some of the material in the Execution Plan Video Training (nice timing, Microsoft! sigh). I’ll post a quick update here when the videos are up to date again!

EDIT: I promised, above, to post an update once the Execution Plan Reference and the Execution Plan Video Training are updated to reflect the changes in Management Studio 18.5. And of course, after making those changes I promptly forgot that I had promised this update. So if you are still waiting to check the reference, or to watch the training videos, and you don’t want to start until my changes are done … sorry to have kept you waiting for much longer than needed; the changes are done now and have in fact been done quite a while ago already.

Announcing … the execution plan video training
Query Store, force my plan on that other server too, please?

Related Posts

No results found.

11 Comments. Leave new

  • Bobby Owens
    April 8, 2020 11:59

    Beware of this one. I installed it and it’s now totally screwed up my RedGate SQL Toolbelt. Even after uninstalling the toolbelt and installing afresh, none of it works. 🙁

    Reply
    • Hugo Kornelis
      April 8, 2020 12:22

      Thanks for the heads up, Bobby! Knowing RedGate, I am confident that a fix for this won’t be long in the making, but until that time people should definitely weigh the benefit of better information in the execution plans versus the issues with SQLPrompt and other RedGate utilities.
      I suggest keeping an eye on the RedGate website to see when they have a fix live

      Reply
    • Hugo Kornelis
      April 8, 2020 18:09

      This should be fixed now. I have left a link (for those who need it) in another comment.

      Reply
  • Bobby Owens
    April 8, 2020 12:50

    Update: I’ve uninstalled 18.5 for now and reinstalled 18.4 and it’s back to how it was. I rely heavily on the RedGate suite so need it working. The Execution Plans issues I’ve put up with for many years, as have you, so a little longer won’t help.

    BTW: Did you see that SSMS 18.x was the 1st SSMS where if you rename an item in object explorer and press delete, it actually deletes the next character. That one bugged me for years.

    Reply
  • Bobby Owens
    April 8, 2020 12:59

    Update: Response from RedGate:

    Hi Bobby,

    I am sorry that you’ve run into this problem with the latest SSMS.

    The developers are aware and are currently investigating a fix. Unfortunately, it’s not going to be quite as straight forward as first hoped and we don’t have a defined ETA for this yet but we’ve been guided towards a release before the end of the week, Friday.

    In the meantime, you will need to roll back to an earlier version of SSMS. Our apologies for the inconvenience this causes. We shall be back in touch with an update as soon as we have one.

    Reply
    • Hugo Kornelis
      April 8, 2020 18:08

      RedGate has released an update, and it works (for me). If you update Prompt before upgrading SSMS, you should have no problems anymore (fingers crossed!)
      If you are on SSMS 18.5 already, then the update might not auto-install. In that case, navigate to https://t.co/BETbcYOvuJ to download the upgrade installer, and you’ll get Prompt to work again without having to downgrade SSMS.

      Reply
  • […] Hugo Kornelis recommends that you update as soon as possible: […]

    Reply
  • Bobby Owens
    April 9, 2020 10:25

    I’ve now installed the latest RedGate and SSMS 18.5. My code snippets are back which is the most important but SQL Search & Tab History still don’t work but I also use SSMS 17 so can use that for those features until fixed.

    Now to look at the EP changes.

    Thx for the post Hugo. 🙂

    Reply
  • Alex Friedman
    April 13, 2020 18:25

    Wow, it’s about time!

    Reply
  • Chris Miller
    April 14, 2020 14:08

    Thank you! To calculate estimated rows times executions to see “the truth”, is an interesting thing I was not aware about!

    Reply
  • […] And while doing that, something realty curious happens with the Estimated Number of Rows property (shown as Estimated Number of Rows Per Execution in the execution plan, since I am running SSMS 18.5). […]

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close