Database Reference
In-Depth Information
Figure 12-27. Property in the first operator showing the cardinality estimator in use
The value shown for SQL Server 2014 will correspond to the version, 120. That's how you can tell what version
of the cardinality estimator is in use. This is important because, since the estimates can lead to changes in execution
plans, it's really important that you understand how to troubleshoot the issues in the event that you get a degradation
in performance caused by the new cardinality estimations.
If you suspect that you are experiencing problems from the upgrade, you should absolutely compare your actual
rows returned to the estimated rows returned in the operations within the execution plan. That's always a great way
to determine whether statistics or cardinality estimations are causing you issues. You have the option of disabling the
entire upgrade by setting the compatibility level to 110, but that also disables other SQL Server 2014 functionality, so
it might not be a good choice. You can run a trace flag against the restore of the database using OPTION ( QUERYTRACEON
9481), you'll target just the cardinality estimator for that database. If you determine in a given query that you're having
issues with the new cardinality estimator, you can take advantage of trace flags in the query in the same way.
SELECT p.Name,
p.Class
FROM Production.Product AS p
WHERE p.Color = 'Red' AND
p.DaysToManufacture > 15
OPTION(QUERYTRACEON 9481);
Conversely, if you have turned off the cardinality estimator using the trace flag or compatibility level, you can
selectively turn it on for a given query using the same functionality as earlier but substituting 2312 for the trace
flag value.
Statistics Maintenance
SQL Server allows a user to manually override the maintenance of statistics in an individual database. The four main
configurations controlling the automatic statistics maintenance behavior of SQL Server are as follows:
New statistics on columns with no index (auto create statistics)
Updating existing statistics (auto update statistics)
The degree of sampling used to generate statistics
Asynchronous updating of existing statistics (auto update statistics async)
You can control the preceding configurations at the levels of a database (all indexes and statistics on all tables)
or on a case-by-case basis on individual indexes or statistics. The auto create statistics setting is applicable for
nonindexed columns only, because SQL Server always creates statistics for an index key when the index is created.
The auto update statistics setting, and the asynchronous version, is applicable for statistics on both indexes and WHERE
clause columns with no index.
Automatic Maintenance
By default, SQL Server automatically takes care of statistics. Both the auto create statistics and auto update statistics
settings are on by default. As explained previously, it is usually better to keep these settings on. The auto update
statistics async setting is off by default.
 
Search WWH ::




Custom Search