SQL Server 2016 incorrect query plan estimate despite updated statistic

Question

I'm in the middle of optimizing a query and notice that it becomes really slow because it estimated the number of rows to be 16.6 and the actual number of rows being returned is 565824. I updated the statistic, dropped and recreated but it still gives the incorrect estimate. This is for SQL Server 2016, any help is appreciated.

SQL:

select cd_key 
from dbo.CAMPDIV
where cd_camp = 'a' 
  and CD_CAMPYR = '2018' 
option (recompile)

Histogram for nonclustered index (cd_campyr)

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.02040816                      4                               CD_CAMPYR                       
7.412665E-08                    8                               CD_CAMPYR, CD_ID                
7.184833E-08                    18                              CD_CAMPYR, CD_ID, CD_CAMP       

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                0                               792181                          0                               1                               
1979                            0                               230                             0                               1                               
1980                            0                               332                             0                               1                               
1981                            0                               604                             0                               1                               
1982                            0                               622                             0                               1                               
1983                            0                               330                             0                               1                               
1984                            0                               1762                            0                               1                               
1985                            0                               868                             0                               1                               
1986                            0                               551                             0                               1                               
1987                            0                               190                             0                               1                               
1988                            0                               352                             0                               1                               
1989                            0                               519                             0                               1                               
1990                            0                               38829                           0                               1                               
1991                            0                               439486                          0                               1                               
1992                            0                               366357                          0                               1                               
1993                            0                               375469                          0                               1                               
1994                            0                               369176                          0                               1                               
1995                            0                               367691                          0                               1                               
1996                            0                               376979                          0                               1                               
1997                            0                               388239                          0                               1                               
1998                            0                               391408                          0                               1                               
1999                            0                               402551                          0                               1                               
2000                            0                               413392                          0                               1                               
2001                            0                               422470                          0                               1                               
2002                            0                               461895                          0                               1                               
2003                            0                               458726                          0                               1                               
2004                            0                               459876                          0                               1                               
2005                            0                               473357                          0                               1                               
2006                            0                               464213                          0                               1                               
2007                            0                               472373                          0                               1                               
2008                            0                               457623                          0                               1                               
2009                            0                               462268                          0                               1                               
2010                            0                               465633                          0                               1                               
2011                            0                               470338                          0                               1                               
2012                            0                               472091                          0                               1                               
2013                            0                               481586                          0                               1                               
2014                            0                               484236                          0                               1                               
2015                            0                               492460                          0                               1                               
2016                            0                               514569                          0                               1                               
2017                            0                               551739                          0                               1                               
2018                            0                               571969                          0                               1                               
2019                            0                               552550                          0                               1                               
2020                            0                               54                              0                               1                               
2021                            0                               33                              0                               1                               
2022                            0                               21                              0                               1                               
2023                            0                               8                               0                               1                               
2025                            1                               1                               1                               1                               
2099                            0                               1                               0                               1                               

Answer 1

It is a bit strange what you tell but in any case... a covering index may help or even produce a drastic increase. Please, try to create the index:

CREATE INDEX IX_CampDiv_CD_Camp_CD_CampYR ON dbo.CAMPDIV (cd_camp, CD_CAMPYR  ) 
INCLUDE (cd_key)

At least will prevent the Nested Loop what improves the plan.

Please, share the results.