New cardinality estimator (SQL Server 2014) is way off -

New cardinality estimator (SQL Server 2014) is way off -

i have info warehouse database , i'm facing problems new cardinality estimator of sql server 2014.

after upgrading database server sql server 2014 have observed big difference in query performance. queries executing much slower (30 sec in sql 2012 vs. 5 minutes in sql 2014). after researching execution plans i've seen cardinality estimates on sql server 2014 way off , can't find reason it.

here's illustration of query execution plan (top-left operator) in sql 2012 vs. sql 2014:

some details:

my queries typical info warehouse fact table load queries. query transactional table , bring together lot (15-20) dimension tables (there's either 0 or 1 record joined dimensional table).

i have updated statistics of tables (with fullscan) sure statistics up-to-date.

the business keys of dimension tables indexed (unique non-clusted index). seems me because of uniqueness of index old cardinality estimator (sql 2012) correctly assumes there's max. 1 record joins (the estimated number of records not alter in execution plan).

i tried narrow downwards issue simplest illustration – select 2 joins:

here's cardinality estimation on operators 1 , 2 in sql 2012 vs. sql 2014:

| est.rows - sql2012 | est.rows - sql2014 operator 1 | 7653 | 7653 operator 2 | 7653 | 10000

as can see, sql server 2014 misses estimation more 30% (10000 vs. 7653). because have cca. 15-20 joins in typical query, final estimate goes way off.

i can set database in lower compatibility mode (110) , works fine (same on sql server 2012), know reason behaviour. why result of cardinality estimator of sql server 2014 wrong?

i think there no simple reply today interesting question. best reply know next video: http://channel9.msdn.com/events/teched/northamerica/2014/dbi-b331#fbid=. has numerous examples of new , old estimators. video 50+ mins long worth time.

a summary of video relates question:

old assumptions of cardinality estimates:

uniformity – info uniformly distributed. independence – column 1 has no relation column 2. containment – when 2 attributes might same, assumed same. inclusion – there should match.

to utilize sql server 2012 cardinality estimator in sql server 2014 utilize next option:

option (querytraceon 9481) --revert 2012

what new estimator doing (based on video):

sql server uses average selectivity in index , estimates number of rows multiplying density of key total numbers of rows in index. new estimator not work jagged distributions. most differences between estimators based on clause. new cardinality estimator believes there correlation between tables. you can create filtered statistics improve queries. (http://msdn.microsoft.com/en-us/library/ms188038.aspx )

to / checklist:

1. auto create / update stats 2. check database compatibility mode (120/110) 3. test using query trace flags 4. xml showplan

sql-server sql-server-2014