SQL Server Cardinality Estimation Deep Dive

When SQL Server decides which method to use to execute a T-SQL query, cardinality estimation is crucial. To decide whether to use a Loop or a Hash Match strategy for a join, the expected rowcount is the most important metric. When SQL Server gets the cardinality estimation wrong, we will end up with a sub-optimal execution plan. In this Full-day session, we will take a look under the covers to understand which different methods for cardinality estimation SQL Server use. We will dive into statistics histograms and density vectors. We will look at some extended events to help us understand the estimation process. We will compare the so called Legacy Cardinality Estimator (pre SQL Server 2014) and the New Cardinality Estimator (SQL Server 2014 and later) and how they address some common problematic situations, like Ascending Keys and other causes for missing statistics. Lessons. Hands-on labs after lessons 2, 4 and 6. Lesson 1. Introduction to cardinality estimation. Examine statistics. Single-table SELECT statements with one or more predicates. Lesson 2. Cardinality estimation for joins. Thresholds for different join strategies (loop, hash, merge). Lesson 3. Cardinality estimation for multiple predicates. Are predicates related, and how? Lesson 4. When things go wrong What happens when SQL Server gets estimated rowcount completely wrong? Lesson 5. Extended Events and Trace Flags How can we use extended events and trace flags to find out more about decisions made by the SQL Server optimizer? Lesson 6. Teach the Optimizer How can we affect cardinality estimation with query hints, configuration and trace flags?