Query execute slowly when changed compatibility Level of my database [duplicate]

Question

I use SQL Server 2014 and have a database with compatibility level 110 (SQL Server 2012). also I have a stored procedure on it that execute about 10 second. When I change compatibility level of my database to 120 (SQL Server 2014), execution time of my stored procedure became about 30 minute.

I use following steps but execution time of my stored procedure not changed.

Step 1: Use DBCC CHECKDB command.

Step 2: Execute DBCC UPDATEUSAGE on my database.

step 3: Rebuild all indexes on my database.

step 4: Drop all index and all foreign key on my database and create all again.

After all of above step my stored procedure execution time not changed.

But when compatibility of my database returned to 110, my sp run about 10 second.

I check above step multiple and get a single result.

How must I do. and how can I change compatibility level of my database to 120 (SQL Server 2014).

Thanks in advance.

Answer 1

I have recently seen this exact same problem with an upgrade from SQL Server 2008 R2 to SQL Server 2014 where a very small number of Stored Procedures (SPs) (approx 5 out of 3,000 ) performed worse in SQL 2014 Compatibility Level 120 than in SQL 2014 Compatibility Level 110 or in SQL 2008.

In SQL 2014 the Cardinality Estimator (CE) has changed which may impact how a SP performs in SQL 2014 Compatibility Level 120.

There are a few possible solutions

1. Leave the SQL 2014 Database running in Compatibility Level 120 and add a dbcc Trace Flag to force SQL Server 2014 to use the prior CE <-- I would not recommend this

2. Leave the SQL 2014 Database running in Compatibility Level 120 and Tempoarily add

   OPTION (QUERYTRACEON 9481)

to the offending SP to force just that SP to use the prior CE. The SP should now perform in Compatibility Level 120 as it did with Compatibility Level 110. <-- I prefer this approach as your Database/System gains all of the other advantages of SQL 2014 while the "fix" only impacts the offending SP.

Irrespective of the approach I would then profile the execution plan/read/writes etc of the offending SP on a test system in SQL Server 2014 Compatibility Level 120 and rewrite the SP to cater for the new CE.

Once tested and verified I would then apply to production.

Microsoft have a good article on the Cardinality Estimator changes in SQL 2014

Answer 2

You can try to update statistics (rebuild index does not recompute all of them):

EXEC sp_updatestats

And to recompile them:

EXEC sp_recompile N'schema.yourProcedure';

It is probably better to find a solution and use compatibility level 120 than using the old optimizer.