4

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.

marked as duplicate by Tom V, Philᵀᴹ, Kin Shah sql-server Dec 27 '15 at 19:49

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @Martin. Thanks for all comments. I change trace flag 9481 status to ON and my stored procedure run quickly. This trace flag change query optimizer and use version 70 (SQL Server 2012) of query optimizer. Can I set this trace falg to default status and use query optimizer (SQL Server 2014) and also run my stored procedure quickly. – mehdi lotfi Nov 22 '14 at 11:24
  • Are you making a decision to use a regression-correcting trace flag globally (or keep an old compatibility level for the entire database) because of a regression in one stored procedure? Why not just use the trace flag to set the old CE for individual queries that are showing regressions, and leave it alone for the rest? – Aaron Bertrand Nov 22 '14 at 13:29
3

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

0

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.

  • Updating stats or rebuilding the indexes wont be helpful. Either change the compatibility to 2012 or have TF 9481 turned on with compatibility of sql server 2014. – Kin Shah Dec 27 '15 at 19:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.