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.

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

avatar image By uravireddy 1 asked Nov 06 at 07:40 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By SDyckes2 81 answered Nov 08 at 10:22 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Thanks for your reply. I have checked that our SQL Servers are updated with Service Pack 2.

When I ran the below query

SELECT @@VERSION

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?

avatar image By uravireddy 1 answered Nov 09 at 02:17 PM
more ▼
(comments are locked)
avatar image SDyckes2 Nov 09 at 03:56 PM

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. https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

But there are other potential issues you may be encountering. Here is another example: https://support.microsoft.com/en-us/help/2952101/fix-poor-cardinality-estimation-when-the-ascending-key-column-is-brand

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x89
x5

asked: Nov 06 at 07:40 PM

Seen: 23 times

Last Updated: Nov 09 at 03:56 PM