The Most Important Performance Factor in SQL Server
I had the honor and pleasure to present a session on August 28 as part of SQL Friday. If you're not familiar with SQL Friday, then it's about time. It's a weekly online lunch seminar about the Microsoft Data Platform. It runs every Friday at noon CEST, and it’s hosted by Magnus Ahlkvist (B | T).
My session was about the most important performance factor in SQL Server. No, I'm not talking about indexes. I'm not talking about disk latency either. I'm talking about statistics. Because without statistics, the optimizer is blind, and it's going to have to use wild guesses and come up with really poor execution plans.
In this session I explained what statistics are, how to view metadata about statistics, how to view their content. We also covered different types of statistics, such as column vs. index statistics, automatic vs. manual statistics, multi-column statistics, filtered statistics and incremental statistics. I demonstrated several use cases, such as the ascending key problem, and we learned some interesting techniques to use with these use cases.
We also talked about the cardinality estimator, which is the component responsible for estimating the number of rows for each operator in the execution plan based on statistics. We talked about the new vs. old cardinality estimator, and I demonstrated some interesting behaviors that have changed between versions.