2

We have recently upgraded our SQL Server Database from SQL 2005 to SQL 2014. We are experiencing slowness in both SSMS and application as well while executing a SP in SQL 2014. After checking through the sql profiler. This is the difference.

Source Server : SQL 2005
JDBC VERSION  : 4.1
Source Data type : Big Decimal
Conversion on DB side : It is converted as Numeric (Checked through sql profiler)
Execution Time : 1 Second

The below target is the server we have upgraded from 2005.

Target Server : SQL 2014
JDBC VERSION  : 6.2
SOURCE Datatype : Big Decimal
Conversion on DB Side : It is converted as Decimal
Execution Time : 25 Seconds.

Any idea what I can do to fix this?

Improve this question
4
  • I doubt this is the reason. Stop looking at profiler, did you check the execution plans? After the upgrade, did you (a) update statistics and (b) update the compatibility level?
    – Aaron Bertrand
    Commented Mar 13, 2018 at 14:50
  • 1
    Have fun with the differences between the 2014 Cardinality Estimator ( CE ) and previous versions. Find a query that's under-performing and set the trace flag with OPTION( QUERYTRACEON 9481 ) to it to use the old estimator, for the time being.
    – Avarkx
    Commented Mar 13, 2018 at 15:11
  • 3
    @Avarkx My guess is that this is because of stats and/or still using the old CE (and that's the CE he's using now, unless he did update the compatibility level manually after the upgrade, which most people don't). Regressions under the new CE are bad, but they're uncommon.
    – Aaron Bertrand
    Commented Mar 13, 2018 at 15:17
  • @AaronBertrand Excellent point, they could try using flag 2312 as well to force the new estimator - I immediately go to a 9481 check on account of the persisting shell-shock from the month after we did our 2000->2008->2014 upgrades without thorough testing :$
    – Avarkx
    Commented Mar 13, 2018 at 15:23

1 Answer 1

Reset to default
0

You should perform the following tasks after updating :

  1. Upgrade Database Compatibility Level
    • The database compatibility level needs to be manually changed on the 2014 instance
  2. Run DBCC CHECKDB WITH DATA_PURITY
    • Checks for invalid data values based on column data type
  3. Run DBCC UPDATEUSAGE
    • Corrects page count inaccuracies to reflect accurate information when using sp_spaceused
  4. Run sp_refreshview
    • Ensures that views are up to date
  5. Update Statistics
    • Brings database statistics are up to date to allow the query optimizer to choose the best query plan
  6. Take a Full Backup of the Database
  7. Make sure you can recover the database with all of the migration efforts
  8. Monitor Performance between Cardinality Estimators

It’s important to note: SQL Server 2014 received a major overhaul of the cardinality estimator. For the vast majority of queries, performance will improve due to better row estimations. However, in some scenarios estimations could be worse which may result in performance degradation. It’s important to monitor most commonly used queries to see if any performance degradation is resulting from poor estimations. It is possible to utilize the old cardinality estimator by leveraging a database compatibility level of 110 or lower or by leveraging trace flag 9481.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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