When I am running the same query with comaptibility level set to 110 the query is giving the results in 40-50ms but when I am switching the compatibility level to 120 the same query with all the same details the query is giving the results in 500ms. Everyting about the query is same except the compatibility level
By uravireddy 1 asked Nov 06 at 07:40 PM
This sounds like an issue with the new Cardinality Estimator that is introduced in SQL2014, compatibility mode 120. Service Pack 1 resolved some of the issues being experienced, determine what your patch level is, then further investigate potential fixes for your situation. There are several trace flags that may be useful in your situation.
Here is one potential issue you can encounter. There are plenty of others as well.
By SDyckes2 81 answered Nov 08 at 10:22 PM
Thanks for your reply. I have checked that our SQL Servers are updated with Service Pack 2.
When I ran the below query
I got below result
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64) Aug 17 2017 12:07:38 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )
So SP2 is applied and still the same issue. Any other suggestions?
By uravireddy 1 answered Nov 09 at 02:17 PM