SQL Server 2017 Showplan enhancements


In my previous post on Easy way to get statistics histogram programmatically, I referred to statistics as the building blocks on which the Query Optimizer reasons to compile a good enough plan to resolve queries. Knowing the status of distribution statistics over a given table and set of columns allows the user to have insight into how SQL Server might have used or misused the existing information.

Towards the end of that post, I show an example on how you can leverage some DMFs inline, in order to to get information on which stat and respective histogram steps cover my predicate, in the scope of my table and column.

We are happy to announce that identifying which statistics were in fact used by the Query Optimizer for a given compilation has become much easier: they are now part of Showplan.

When looking at the properties of the root node, here’s what we know: one statistic object [IX_CustomerStatus] was used by the Query Optimizer, and I can see several statistic properties like SamplingPercent, current ModificationCount and when it was last updated.

 

 

OptimizerStatsUsage is available in cached plans, so getting the “estimated execution plan” and the “actual execution plan” will have this information.

In the above example, I see the ModificationCount is very high (almost as much as the table cardinality itself) which after closer observation, the statistic had been updated with NORECOMPUTE.

And looking and the Seek itself, there is a large skew between estimated and actual rows. In this case, I now know a good course of action is to update statistics. Doing so produces this new result: ModificationCounter is back to zero and estimations are now correct.

 

 

Pedro Lopes (@sqlpto) – Program Manager

Comments (4)

  1. ranta says:

    In the first screenshot, the tooltip ends with “Scalar Operator([@Status])”, and the property pane includes “Parameter List” with value “@Status”. In the second screenshot, the tooltip ends with “Scalar Operator((0))”, and the property pane omits “Parameter List”. Did you change the query between these screenshots or is this some kind of parameter inlining caused by the statistics update?

    1. Good catch, I had, to include RECOMPILE hint on the 2nd case. I corrected the screenshots and they both show the same query hash and other properties now.

  2. could you please add the “SQL Server 2017” – Tag that everybody can find this article via https://blogs.msdn.microsoft.com/sql_server_team/tag/sql-server-2017/

Skip to main content