5

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:

enter image description here

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:

enter image description here

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

  • 4
    That means the cardinality estimator thinks the join might actually filter out some rows since it is not guaranteed to be a match on the join condition. Turning FK off for ETL might be a good idea but you could turn it on again after ETL. Do it with with check check so you validate existing rows. Try and see what it does to the estimate. Or you could try a left outer join instead. – Mikael Eriksson Oct 6 '16 at 17:20
  • 2
    @MikaelEriksson The outer join will work (if the application can tolerate it) but trusted foreign keys might not work as you'd think, sadly. – Paul White Oct 7 '16 at 16:14
3

I was able to reproduce your issue in SQL Server 2014 with the legacy CE and TF 4199 enabled. I used a rowstore fact table because I have little experience with columnstore.

For your query with the filter, the query optimizer rewrites the query to something like this:

select * 
from fact_sales f
join dim_company d on f.company_SK = d.company_SK 
where f.company_SK = 1 and d.company_SK = 1

For that query the optimizer can directly uses the histogram step containing 1 for both tables and you get 467,583,000 X 1 as the estimated number of rows.

The query without a filter will use linear interpolation:

For joins with a single equality or inequality predicate, the legacy CE joins the histograms on the join columns by aligning the two histograms step-by-step using linear interpolation.

Note that the statistics for your dimension table do not have a histogram step with a RANGE_HI_KEY value of 1, which is where almost all of the data is in your fact table. Something is going wrong with the linear interpolation step for that value. If I create your dimension table without a 0 value then the histogram gets a step with a RANGE_HI_KEY value of 1. That fixes the estimate. In short, you got unlucky with your histogram. I don't think there is a supported way to force the histogram on the dimension table to contain a step for 1, unless perhaps you are willing to add additional filters to your query.

I don't know the exact rules of your data, but I can give you two workarounds. The first workaround is to enable trace flag 2301 for the query. That fixes the estimate for this part of the query but may have other negative effects for the rest of your query. This trace flag is documented by MS, but talk to your DBA before using it.

The second workaround is to encourage the query optimizer to use a different method of cardinality estimation for the join. The following query gave a good estimate for me and only does a single scan on your fact table:

select * 
from FACT_SALES f
join (SELECT COMPANY_SK FROM DIM_COMPANY UNION SELECT 1) d
on f.company_SK = d.company_SK;
  • enabling trace flag 2301 solved the problem! however, I still don't understand how the optimizer doesn't seem to properly use the histograms for such a simple join, could it be the fact that the columnstore index is partitioned (NOT by the join column though) and optimizer won't use histogram when joining a partitioned table? – user107507 Oct 17 '16 at 13:55
  • @user107507 I tested without partitions so that's not the issue. See here for more information about TF 2301 if you want to know why it helped your query: blogs.msdn.microsoft.com/ianjo/2006/04/24/… – Joe Obbish Oct 27 '16 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.