13 optimizer statistic problems you didn’t know you had ~ Fabiano N Amorim
Details
WHEN:
Wednesday, February 12, 2024, 2000 GMT
DESCRIPTION of TALK
Title: 13 optimizer statistic problems you didn’t know you had
SQL Server QO (query optimizer) and CE (cardinality estimation) are derived primarily from statistics. For most of the time, QO does a very good job using statistics to create a high-quality query plan; but, in some cases, you may see unexpected situations where it didn’t work quite well.
In this session, we’ll explore 13 different cases with an unusual behavior related to SQL Server statistics. The idea is to understand what happened, what to do, and increase your knowledge on query performance troubleshooting.
We’ll discuss about things like:
- How can I have a CXPACKET wait on this query using OPTION(MAXDOP 1)?
- Why is this query compilation taking so much time?
- What are the problems of columnstore and stats?
- How to improve the time it takes to run update stats with fullscan?
- Why my index rebuild is not running update stats with fullscan?
- Bad tracking of statistic modification counter.
- Cases with inaccurate cardinality estimation due to non-optimal usage of statistics.
-- Temporary tables caching, missing optimization available on TF4199, missing stat column warning, inaccurate estimation using avg_range_rows, filtered statistics…
And much more…
13 optimizer statistic problems you didn’t know you had ~ Fabiano N Amorim