0

We have data warehouse based on SQL Server 2012. Often we get poor cardinality estimates for joins impacting downstream operators and slow execution, memory spills, etc.

Is there a good article or whitepaper or blog or video or something that explains how cardinality for joins are calculated? Update stats with full scan only helps for sometime/some cases.

Also any guidance links on how to remediate join cardinality errors would be very helpful.

1

To answer your first question, Microsoft released a white paper for the new cardinality estimator in SQL Server 2014. That white paper describes how join cardinality was calculated in the 2012 CE. I also know of a blog post that dives into the internals of join cardinality quite a bit.

To answer your second question, I can go over a few techniques.

  1. Trace flag 2301 changes some aspects of cardinality estimation along with other things. It may improve performance of your queries. Test carefully before using.
  2. Trace flag 2371 causes statistics to be updated more frequently on large tables. This is on by default in SQL Server 2016. This will not help you if your statistics have already been updated with a FULLSCAN.
  3. Trace flag 4139 can provide better estimates when your queries suffer from the ascending key problem. That is, you are filtering on new data on an ascending column and your filter range is outside of all histogram steps.
  4. Trace flag 4199 contains a rollup of query optimizer fixes that are on by default in SQL Server 2016. I don't know if any of them directly affect join cardinality estimates but they might improve the performance of your queries in other ways.
  5. If you upgrade to SQL Server 2014 you can use the new cardinality estimator which drastically changes the algorithms for cardinality estimation. It could offer a significant improvement for your workload.
  6. You can create filtered statistics to get a full histogram with up to 200 steps on a table filtered down to the data that you're interested in. This can be useful for certain types of data skew when you know that queries will be commonly filtering on a specific value.
  7. You can create multi-column statistics when you need a more accurate value for the density of both columns. The histogram for the multi-column statistics is only on the first column. SQL server will not automatically create multi-column statistics for you.
  8. For some queries I've only had luck with putting an intermediate result set into a temp table, gathering stats on the temp table, and referencing that temp table in the query. Sometimes even putting just a few thousand rows into a temp table can make a huge improvement in overall cardinality estimates.

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.