Hi Friends,
Are you facing any issue on SQL Server 2014 while running the same query which is running fine on previous version of SQL Server? If answer is yes then this may be due to the change in SQL Server Query Optimizer cardinality estimation algorithms.
What is SQL Server 2014 cardinality estimation? : The process of finding number of rows, those will be processed by an operator in query execution plan.
Actually cardinality estimation algorithms have been changed in SQL Server 2014 by Microsoft on the basis of workload study since last several years. So now SQL Server 2014 has a new version of Cardinality Estimator. Earlier version was written for SQL Server 7.0 which can be called as legacy Cardinality Estimator now. After doing a study of various types of workload Microsoft has decided to change its algorithms for cardinality estimator to improve query performance.
These changes may provide you great performance benefits. However in some cases you may have seen that performance is not good. In Short we can say it will depend on the type of workload. You can identify these issues by taking a look on your query execution plan. In case of performance benefits you will find out that estimated and actual numbers of rows both are almost equal or you can say very near to each other. In case of performance degradation you may found that estimated and actual numbers of rows both are very far to each other.
Scenario: How can we use New Cardinality Estimator of SQL Server 2014 for newly created databases?
If you have created new databases under SQL Server 2014 then by default it will use new cardinality estimator. Here the reason is newly created database under SQL Server 2014 having compatibility level 120. In simple words you can say that every database created under SQL Server 2014 with compatibility level 120 will use new Cardinality Estimator.
We can check this also. Now let me show you this practically by using a very simple select query on my already created database under SQL Server 2014. Here I am running below query along with Actual Execution Plan option is on. Right click on the left most operator of execution plan and click on properties.
When you will click on properties it will open a new properties window. Here you can check the value of CardinalityEstimationModelVersion. If Value is 120 then it is New CE. If value is 70 then it is Legacy CE.
HAPPY LEARNING!
Regards
Prince Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
