SQL Server Query Optimizer estimates ~1 row, but eq_rows statistics shows ~12000

Question

I have a (composite) index consisting of columns A, B, C and D. The statics are up to date and show EQ_ROWS value 12000 for value 'x' (that is the RANGE_HI_KEY) for column A. When I do a SELECT * FROM <table> WHERE a = 'x' the Estimated Query Plan shows for all operators (index seek on the mentioned composite index, key lookup and nested loop) an estimated number of rows of ~1. How is this possible? I would have thought it to be 12000 for the index seek for example.

Note that in reality my column names have decent names, but for company security reason I cannot use real names. I hope my explanation is clear enough.

Answer 1

I am afraid that I don't have a good answer for you. I know that there are imperfections in Showplan and sometimes information does not display fully accurately. In this case, I notice that there are a few more filter conditions on the DISPENSEEVENT table than just the CLIENTID. The remain predicates are filtered in the Key Lookup, which is just below the Index Seek. It is possible that the total estimate for the predicates is 1 row.

In any case, it seems to be that there is all reason to consider adding an index on (CLIENTID, MDEMEDICALGUIDANCESTATEID, DISPENSEEVENTPROCESSORIGINID, PARENTDISPENSEEVENTID, MDEAPPROVALSTATEID) INCLUDE (STATE). I need to add the caveat that I only see this query, and I don't know how dynamic these conditions are. That is, if this query exists in multiple permutations, it may not be wise to add indexes for all of them.

Answer 2

What is the general density for the table? A simple query like the one you posted is likely to be auto-parameterised. You can see this in the plan from the fact that it says a = @1 in header of the graphic plan.

Answer 3

Hi ErlandSommarskog,

in reality it was part of a larger query, but I reproduced the exact same thing with this simple select statement. Yes, it was auto-parameterized, but since this was the first query this way, it created a query plan using the value 'x' as compiled value.

The All Density of the table is 4.120898E-09. The All Density of the index used for the index seek for value 'x' is 0.009345794.

Answer 4

I have to see whether I can somehow do that. They use overhere scripts from Ola Hallengren that run overnight for statistics maintenance, so the situation will be different now. I will see whether I can reproduce this behavior using a simple parameterized query and provide the information in an anonymized way.

In the mean time a question. So, if a parameter value equals the RANGE_HI_KEY, the optimizer does not necessarily use the EQ_ROWS value as it is, it may estimate a different number of rows?

The Parameterization option in the database is 'Simple'. Queries are executed from the client application via sp_executesql using @params and @params1.

Answer 5

Hi ErlandSommarskog, these two pictures is all I can provide at the moment, they come from screenshots I had made earlier about the case. I cannot reproduce the issue right now since the statistics have changed overnight. With something simple as this, I would have thought the optimizer would simply use the EQ_ROWS value. Nothing more, nothing less. If you say you still don't have enough information, I will get back to you at some point when I have a case in our production system again (or when I can reproduce it) and when I am able to anonymize it. This is all I can do for now. Thank you for your time so far.