I'm seeing a huge difference in the estimated number of rows vs actual number of rows. I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned from this index is 16, but the actual number of rows being returned is 565824? if so, then is this a concern? This is for SQL server 2016
If you include
cd_key in your index, it won’t have to do those lookups and the estimates will have less of an impact. Otherwise, correcting that estimate is going to cause it to do a Clustered Index Scan, which could also be slow.
cd_camp is already in there as it’s part of the clustered index key.
But really, the index you want here is:
CREATE CLUSTERED INDEX ixYearCamp ON campdiv (cd_campyr, cd_camp) include (cd_key);