Category - SQL Server

General about SQL Server

New Cardinality Estimator Part 1

SQL Server 2014’s main favorites are definitely In-Memory OLTP engine and cluster column stored indexes and if you find proper use cases for them you can significantly improve performance and usage of your OLTP system. However, for both of them you can decide when, how, and in which degree you will implement them.

In the series of articles I would explore changes in a database engine which will affect your workload immediately, as soon as you migrate to the newest SQL Server version and put your databases in the latest compatibility level (120).

These changes are about new cardinality estimator. After log time Microsoft decided to fully rewrite and consolidate it and make open for changes required by future workload distribution challenges. The new CE version should work better for most of customer workloads, most of the time. Although these changes are important everyone who migrate to 2014, almost nothing about it is documented.

On 21st December on the SQL Saturday Slovenia I have presented new cardinality estimator in SQL Server 2014. An important source of information for reasons and intention behind the Microsoft’s decision to rewrite the cardinality estimator was the paper Testing Cardinality Estimation Models in SQL Server written by Campbell Fraser, Leo Giakoumakis, Vikas Hamine, Katherine F. Moore-Smith. All the other informations are result of my playing with queries and comparing estimations and execution plans generated by old and new cardinality estimator.

New CE is available for SQL Server 2014 CTP1 and CTP2 only. For all databases in compatibility mode 120 a new cardinality estimator is automatically used. If you want to use it in a database which is not in compatibility level 120 you have to finish your statement with the following option:

OPTION (QUERYTRACEON 2312)

This option instructs the database engine to use new CE for generating execution plan for the statement containing it.

Old CE is automatically used in databases that are not in compatibility mode 120. In addition to this, analog to new CE, the usage of old CE can be forced by trace flag.

OPTION (QUERYTRACEON 9481)

At the time I am writing this post, there are still no details about new cardinality estimator published by Microsoft. A great introduction to new CE you can find in this excellent article written by Benjamin Nevarez (blog | twitter).

With this post I start a series of articles about new cardinality etimator. In each article I will touch one typical estimation scenario (one specific operator, ascending keys, simple JOIN, predicate correlation etc.), explore the changes (if there are changes) and how is this scenario affected by new CE in general.

I would appreciate your feedback and comments.

Thanks for reading.