I have a simple join like the one below:
select * from fact_sales f join dim_company d on f.company_SK = d.company_SK
The fact table contains a little over 500 million records (with NC columnstore), the query will return all of them, however, the estimated number of rows after the join is only 300 million. Up till the hash join, the estimated number of rows are correct, only after the join it drops to 300 million. Here's the estimated plan for the query:
I've updated the stats on the SK column used in the join in both the fact table (with fullscan) and the dimension table, here's the histogram for each:
This issue only seems to occur for a couple dimension tables in the database, joining the other dimension tables doesn't produce the same type of cardinality estimate issue - any suggestions on how to possibly fix this or further investigate?
If I add a where clause to the query, it correctly estimates the number of rows before/after the join, e.g.
select * from fact_sales f join dim_company d on f.company_SK = d.company_SK where company_SK = 1
will estimate 467,583,000 rows coming out of the join, which matches what's in the histogram.
The issue seems to only occur when I don't have any filter in the query. It's causing a problem in a bigger query (sort spill). I've narrowed it down to this particular join.
I do have an FK constraint, but they have been turned off (
WITH NOCHECK) on the fact table (we were told to turn them off so ETL can go faster). Unfortunately, turning the FK back on is not an option :(
Update: enabling trace flag 2301 solved the issue :p