Cardinality estimation is an important concept in SQL Server that affects query performance. In a previous blog post, we discussed the different cardinality estimation models in SQL Server 2014. Today, we will explore how to determine the cardinality estimation of a query that has just been executed.
Method 1: Execution Plan Properties
One way to determine the cardinality estimation of a query is by examining the execution plan properties. To do this, enable the execution plan by pressing CTRL+M before executing the query. Once the query has been executed, right-click on one of the operators in the execution plan and select “Properties”. Alternatively, you can use the shortcut key F4 to open the properties window on the right side of SQL Server Management Studio (SSMS).
In the properties window, look for the property called “CardinalityEstimationModelVersion”. If the value of this property is 70, it indicates that the cardinality estimator algorithm is from a previous version of SQL Server. If the value is 120, it means that the cardinality estimator algorithm is from the latest SQL Server version.
Method 2: XML Execution Plan
Another method to determine the cardinality estimation of a query is by examining the XML execution plan. Similar to the previous method, enable the execution plan by pressing CTRL+M before executing the query. Once the query has been executed, right-click on the execution plan and select “Show XML execution plan”. This will open a new window displaying the XML plan.
In the XML execution plan, look for the property called “CardinalityEstimationModelVersion”. If the value of this property is 70, it indicates that the cardinality estimator algorithm is from a previous version of SQL Server. If the value is 120, it means that the cardinality estimator algorithm is from the latest SQL Server version.
By using either of these methods, you can easily determine the cardinality estimation of a query execution. This information can be useful in troubleshooting query performance issues and optimizing your SQL Server environment.
Stay tuned for more articles on SQL Server concepts and best practices!