Cardinality Issues

John Couch 2013-10-25 03:12:21

I broke the query out into two pieces only because when combined the execution time was 3 minutes. I tried different index combinatins on both tables to no success. Splitting them out gets the execution down to 2 seconds, however, I notice a large cardinality issue on the join. I realize it has to do with statistics but I am not sure whether I can or should fix this.

SQLkiwi 2013-10-25 04:11:29
The inaccurate cardinality estimate is caused by the residual predicate on the Clustered Index Seek being a lot more selective than the optimizer predicted.

This disparity means the query reserves 531MB of workspace memory where a much smaller grant would be sufficient. Improving the estimate would not necessarily make your query run faster (though it would if 531MB were not immediately available) but reserving that much memory can reduce concurrency and may mean that valuable index and data pages are uncached to make room.

It is difficult to suggest improvements from an anonymized plan, because "ScalarString18" could be anything. In principle though, you may be able to improve the estimate by providing filtered or multi-column statistics. A computed column on "ScalarString18" may also help.

The other alternative is to materialize the 15,660 rows into another temporary table before performing the aggregation.

John Couch 2013-10-25 14:57:37
link text

I have tried creating multicolumn statistic, index, filtered/non filtered. The index without the filter helps. with the filter it had the same result as without the filter.

create statistics stat__bi_order_detail__sales_document_type__sales_document_number on bi_order_detail (sales_document_number, sales_document_type) where sales_document_type = 'ZQT'

drop index bi_order_detail.nidx_Test_01
go
create nonclustered index nidx_Test_01 on bi_order_detail (sales_document_type, sales_document_number) include (ZCONT_ID)
where sales_document_type = 'ZQT'
go