Cardinality Estimation for Multiple Predicates in SQL Server

When a query contains more than one column predicate, cardinality estimation becomes more difficult. Selectivity computation in SQL Server 2014 behaves the same as previous versions (and trace flag 4137 works as before) if the database compatibility level is set lower than 120, or if trace flag 9481 is active. Setting the database compatibility level is the official way to use the pre-2014 cardinality estimator in SQL Server 2014. Trace flag 9481 is effective to do the same thing as at the time of writing, and also works with QUERYTRACEON, though it is not documented to do so.

sqlserver2014

This article by Paul White explains how the Cardinality Estimator works in SQL Server 2014 and before, with great examples and illustrations.

Advertisements

5 thoughts on “Cardinality Estimation for Multiple Predicates in SQL Server”

  1. I believe this is among the most significant information for me. And i’m satisfied studying your article. But should observation on some basic things, The website taste is perfect, the articles is truly great : D. Excellent process, cheers

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s