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?
Best Answer
You should perform the following tasks after updating :
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.