How to improve the performance of this query where 5 varchar(max) columns are in the SELECT list with only table join to filter out the records based on PK column.

uravireddy 2017-11-06 19:40:32

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

SDyckes2 2017-11-08 22:22:24
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.

uravireddy 2017-11-09 14:17:21
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?

SDyckes2 2017-11-09 15:56:37
Depending on the issue you are encountering, you may have to apply specific trace flags or query hints to resolve your issue.

Did you read the blog in the hyperlink of my first response? He had to apply trace flag 4199 after SP1 was applied.

But there are other potential issues you may be encountering. Here is another example:

I did not do an in-depth review of your issue, so you will need to research and test the best fix for this specific issue.