Your Ultimate SQL Server Statistics Best Practices Review

Fabiano Amorim

In this session, I’ll talk about a collection of best practice scripts I’ve created to review the statistics used in a SQL Server environment when query execution times are slow.

Before performing any additional troubleshooting steps, the first thing you should do is to ensure that QO (Query Optimizer) is able to create a high-quality query plan with accurate CE (Cardinality Estimations) and useful and up-to-date statistics about your data distribution.

CE in SQL Server is derived primarily from statistics, so to make sure you generate optimal query plans, it is best to design queries so that the QO can accurately estimate the selectivity of the conditions in your query, and, ensure you have up-to-date statistics. This will increase the chance of a more precise CE and, in turn, faster execution plans.

I’ll demonstrate in this session why, if you don’t take good care of those statistics, it is very likely you will have less-than-optimal query plan choices and poor performance. I’ll also show how, if you want to track all queries with CE issues, you can use the extended event inaccurate cardinality estimate which identifies queries that may be using sub-optimal plans due to cardinality estimate inaccuracy, or use query store.