SQL Server 2014 Cardinality Estimation : Using New Cardinality Estimator

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.

   

1_SQL_Server_2014_Using_New_Cardinality_Estimator

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.

2_SQL_Server_2014_Using_New_Cardinality_Estimator

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.