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.
Everyting about the query is same except the compatibility level
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.
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
So SP2 is applied and still the same issue. Any other suggestions?