Database Reference
In-Depth Information
Another new cardinality estimation was introduced with SQL Server 2014. In SQL Server 2012 and earlier, when
a value in an index that consisted of an increasing or decreasing increment, such as an identity column or a datetime
value, introduced a new row that fell outside the existing histogram, the optimizer would fall back on its default
estimate for data without statistics, which was one row. This could lead to seriously inaccurate query plans, causing
poor performance. Now, there are all new calculations.
First, if you have created statistics using a
FULLSCAN
, explained in detail in the “Statistics Maintenance” section,
and there have been no modifications to the data, then the cardinality estimation works the same as it did before.
But, if the statistics have been created with a default sampling or data has been modified, then the cardinality
estimator works off the average number of rows returned within that set of statistics and assumes that instead of
a single row. This can make for much more accurate execution plans, but assuming only a reasonably consistent
distribution of data. An uneven distribution, referred to as
skewed data
, can lead to bad cardinality estimations that
can result in behavior similar to bad parameter sniffing, covered in detail in Chapter 16.
You can now observe cardinality estimations in action using Extended Events using the event
query_optimizer_
estimate_cardinality
. I won't go into all the details of every possible output from the events, but I do want to show
how you can observe optimizer behavior and correlate it between execution plans and the cardinality estimations.
For the vast majority of query tuning, this won't be all that helpful, but if you're unsure of how the optimizer is making
the estimates that it does, or if those estimates seem inaccurate, you can use this method to further investigate the
information.
First, you should set up an Extended Events session with the
query_optimizer_estimate_cardinality
event.
I've created an example including the auto_stats event. Then, I ran a query.
SELECT so.Description,
p.Name AS ProductName,
p.ListPrice,
p.Size,
pv.AverageLeadTime,
pv.MaxOrderQty,
v.Name AS VendorName
FROM Sales.SpecialOffer AS so
JOIN Sales.SpecialOfferProduct AS sop
ON sop.SpecialOfferID = so.SpecialOfferID
JOIN Production.Product AS p
ON p.ProductID = sop.ProductID
JOIN Purchasing.ProductVendor AS pv
ON pv.ProductID = p.ProductID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = pv.BusinessEntityID
WHERE so.DiscountPct > .15;
I chose a query that's a little complex so that there are plenty of operators in the execution plan. When I run the
query, I can then see the output of the Extended Events session, as shown in Figure
12-25
.
