Database Reference
In-Depth Information
Figure 12-25. Session showing output from query_optimizer_estimate_cardinality event
The first event visible in Figure 12-25 shows the auto_stats event firing where it loaded the statistics for an index,
Purchasing.ProductVendor.IX_ProductVendor_BusinessEntityID . This means the statistics were readied prior to
the cardinality estimation firing. There were a number of these events, including one for the PK_ProductID column.
Then, the information on the Details tab is the output from the cardinality estimation calculation. The detailed
information is contained as XML in the calculator field and the input_relation field. These will show the types of
calculations and the values used in those calculations.
If you also capture the execution plan for the query, you get additional information in the plan to help you
correlate the cardinality estimations to the operations within the plan. If you look at the properties for the Seek
Operator for the PK_ProductID column, you get a value as shown in Figure 12-26 .
Figure 12-26. The properties of the clustered index seek operator
The value of the StatsCollectionId directly corresponds the event shown in Figure 12-25 and the
stats_collection_id field there. This allows you to match the statistics collection events to specific operators
within the execution plans.
Enabling and Disabling the Cardinality Estimator
If you create a database in SQL Server 2014, it's going to automatically come with the compatibility level set to 120,
which is the correct version for SQL Server 2014. But, if you restore or attach a database from a previous version
of SQL Server, the compatibility level will be set to that version, 110 or before. That database will then use
the SQL Server 7 cardinality estimator. You can tell this by looking at the execution plan in the first operator
( SELECT / INSERT / UPDATE / DELETE ) at the properties for the CardinalityEstimationModelVersion , as shown in
Figure 12-27 .
 
Search WWH ::




Custom Search