Bad Query Plan after SQL Server Upgrade

This query plan has changed significantly after upgrading from SQL Server 2012 to SQL Server 2017.

tmpA28C is the original; 2247-tmpe9f3 is adding the OPTION hint suggested by Jason below.

avatar image By dataczar 1 asked May 15 at 10:47 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 15 at 11:37 AM

Have you done any maintenance since the upgrade? For example, rebuilt indexes or at least updated statistics? See this article by Erin Stellato.

avatar image dataczar May 15 at 02:46 PM

We have been running our regular index and statistic maintenance.

avatar image Aaron Bertrand ♦ May 15 at 03:00 PM

I ask because several of your operations have estimates that are WAY off. Like 51,000 rows estimated and 14 million rows actual. What is the version of Plan Explorer you used to execute the query and collect the plan? It doesn't seem like it was able to pull Index Analysis data correctly (which may have been a permissions issue). I wanted to ask when the stats were last updated for, say, DiaryPOCServiceType, DiaryClaim, and Diary_Inquiry? It's possible that using the option Jason suggested worked because it generated a new plan based on newer stats, not because the old cardinality estimator did anything better implicitly. Could you share the plan from that execution, and also a plan from an execution that uses OPTION (RECOMPILE)?

avatar image dataczar May 15 at 05:34 PM

My version is 11.3.1.0. I hsve posted the other plan you asked about.

10|10000 characters needed characters left

1 answer: sort voted first

Maybe try it with OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));?

avatar image By SQLSaurus ♦♦ 66 answered May 15 at 11:01 AM
more ▼
(comments are locked)
avatar image dataczar May 15 at 01:48 PM

It ran in 1 second with the OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) versus 105 seconds without it.

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:

x683
x494
x125
x116

asked: May 15 at 10:47 AM

Seen: 23 times

Last Updated: May 16 at 12:10 PM