Incorrect cardinality estimation of 1 for table

Radu Gheorghiu 2016-11-14 16:37:33

I have a query which joins two temporary tables – Objcet15 and Object8 with a series of persisted tables. (Attached is the anonymized execution plan)

One of the problems I'm seeing and couldn't find a way to solve is that the RID Lookups and Index Seek operations on Object12 have a cardinality estimator of 1 row, but they actually have 11.7k rows. Due to this, I think that the Nested Loop operator is actually the wrong operation to be performed here and a merge would be more appropriate, but I can't figure out a way to make the Optimizer choose this kind of plan.

I've checked and updated my stats for this table, but things haven't changed. I'm not sure what I can look at next. Any suggestions are greatly appreciated.

Vlady Oselsky 2016-11-14 19:48:44
Here is what I see when I look at your plan

alt text

This says that estimate row count was 3609 but actual is 11701 which is much different than 1 row you said it estimated. I think what you are referring to is "1.0 rows per iteration" message on next to the red mark. This simply means that nested operation was done 1 row at the time, not that 1 row was estimated.

Your problem in this query comes from the use of multiple heaps. By placing a non-clustered index on a heap SQL Server is able to get most of the data it needs but if it is missing any columns it does very costly RID Lookup operation.

My recommendations are as follows.

  1. Create clustered indexes on all HEAPS
  2. If plausible modify non-clustered indexes to become "covered" indexes. In the case of RID Lookup on Object11, only 1 extra column is included. In the case of RID Lookup on Object12, only 2 columns are included. If these columns are added to the index with INCLUDE keyword you could avoid RID lookups.

Lastly, it is always the best to run query from within Plan Explorer to allow application to gather extra statistics to get actual costs of the operations instead of estimates that come from SSMS.

Radu Gheorghiu 2016-11-15 08:07:43
This is very strange. To be honest, I looked only in SSMS at the estimates, took the query XML, pasted it in Plan Explorer, anonymized it and posted it here.

However, I only looked at the estimates in SSMS, I didn't check the estimates in plan explorer also. Surprisingly in SSMS the plan shows the estimate of 1 which I mentioned, but Plan Explorer shows 3609. So, the information is in the XML, but is not reflected in the execution plan.

alt text

Anyway, I did try adding a clustered index to the table as I noticed it was a heap, and that is why the cost percentages are different than in the plan I posted at first, but the execution time is about the same.

I'm assuming that this is a problem with Management Studio, since it's an older version, from 2008R2.

Also, since you mentioned running the query from Plan Explorer, using the program is very difficult in the last time. I've been testing the new version and an older version on two different machines and it's very slow and unresponsive. It takes about 15-20 seconds to switch tabs and show the information after pasting in the XML plan.

Vlady Oselsky 2016-11-15 13:16:24
I recommend downloading the latest version of the Plan Explorer and just copying and pasting the SQL code you were running in SSMS. Also, I recommend running SSMS 2016 as now it has become standalone download and it can be used against older versions of SQL Server. After you run the query in Plan Explorer post new anonymized plan. I believe next step to resolve your issue would be creating covered indexes but to say for sure would have to see new plan generated by Plan Explorer.