Wrong statistics in my query plan
By Tulio Henrique 3 asked Sep 29, 2014 at 11:52 PM
The estimated row counts are pretty reasonable in most places in this plan. There are a few areas where the discrepancies are larger, but this is not at all unexpected in a query with six joins and seven non-join predicates.
The statistics maintained by SQL Server are single-column only, with a maximum of 200 histogram steps. Even if you were to create all potentially-useful multi-column statistics, there are definite limits to what can be achieved in situations like this. The expected accuracy degrades with each operation, because we are now dealing with derived statistics on the output of, say, a join.
I don't see anything in the uploaded plans that would cause me concern. The one plan that does include performance information shows a total execution time of 381 ms. If you're experiencing a particular performance problem, you should update your question to explain exactly what that is, and what you would like to achieve.
In general, where an inaccurate cardinality estimation does lead to a performance-affecting plan selection, and additional statistics do not help, the query can be broken in steps using temporary tables (not table variables) to give the optimizer information about the size and value distribution of intermediate results.
By SQLkiwi ♦ 6.6k answered Oct 01, 2014 at 07:56 AM