1

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

enter image description here

  • Are the stats up-to-date ? if not, you can do update statistics tableName with fullscan. Also, analyze if the missing index is useful since I see that your query does a key lookup. – Kin Shah Dec 28 '18 at 22:49
  • Sadly, all statistics are already updated, one thing to note is that this table has a clustered index on 3 columns, id, campyr, camp. The non cluster index I am showing is the campyr column. The plan is using the non cluster index to seek all rows where campyr = 2018 then join to the cluster index to get the actual row. I just want to know if the incorrect estimate of 16 rows vs 565824 rows is a concern because this plan is actually really slow – gavin Dec 28 '18 at 23:12
  • 2
    yes it's a concern as you are getting an inappropriate plan with lookups because of this.You should include the histograms from the cross site dupe unless you want to have to explain everything again here. You could also try adding OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604) and look in the messages tab for the cardinality information displayed there – Martin Smith Dec 28 '18 at 23:33
  • That clustered index key isn’t a great choice. If id is basically unique, you don’t add anything by having the other two columns, except to make it wider (and to include two columns in every non clustered index you have). – Rob Farley Dec 28 '18 at 23:34
4

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);
  • Any idea on how to correct the estimate? – gavin Dec 28 '18 at 23:43
  • Having the index I’ve recommended will actually help quite a bit, because the stats associated can involve multiple levels. But create the index and see how performance is, and what the plan looks like. – Rob Farley Dec 29 '18 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.