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):

SELECT sod.SalesOrderID,
       sod.SalesOrderDetailID,
       sod.CarrierTrackingNumber,
       soh.ShipDate
FROM   Sales.SalesOrderDetail AS sod
JOIN   Sales.SalesOrderHeader AS soh
  ON   soh.SalesOrderID = sod.SalesOrderID
WHERE  soh.SalesPersonID = 285;

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.