First published on MSDN on May 11, 2017
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
in SQL Server 2017 and SQL Server 2016 SP2.
Note:
in
SQL Server 2017 CU3
, we included a fix for queries with LIKE predicates not showing loaded statistics.
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