Bad Query Plan after SQL Server Upgrade

dataczar 2018-05-15 10:47:45

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.

Aaron Bertrand 2018-05-15 11:37:36
Have you done any maintenance since the upgrade? For example, rebuilt indexes or at least updated statistics? See this article by Erin Stellato.
dataczar 2018-05-15 14:46:57
We have been running our regular index and statistic maintenance.
Aaron Bertrand 2018-05-15 15:00:02
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)?
dataczar 2018-05-15 17:34:11
My version is 11.3.1.0. I hsve posted the other plan you asked about.
SQLSaurus 2018-05-15 11:01:27
Maybe try it with OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));?
dataczar 2018-05-15 13:48:59
It ran in 1 second with the OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) versus 105 seconds without it.