Think Like the Cardinality Estimator
April 26, 2017
This is a session about cardinality estimation, a phase during query optimization. I presented the session to following user groups.
- Pass DBA Virtual Chapter on April 25, 2017
- SQL Saturday Rochester (#619) on April 29, 2017
- Rhode Island SQL Server User Group on May 03, 2017
- Maine PASS Chapter on May 18, 2017
- Seacoast SQL Server users group on October 23, 2017
- SQL Saturday Providence (#694) on December 09, 2017
Abstract:
SQL Server uses a phase during query optimization, called cardinality estimation (CE). This process makes estimates bases on the statistics as to how many rows flow from one query plan iterator to the next. Knowing how CE generates these numbers, will enable you to write better TSQL code and in turn influence type of physical operations during a query execution. Based on that estimated rows, the query processor decides how to access an object, which physical join to use, how to sort the data. Do you know how the CE generates these numbers? If your query has only one predicate, the query optimizer will use the histogram to estimate how many rows will be qualified. What happen when you have multiple predicates, range predicates, variable values that are “NOT KNOWN” to the optimizer or you have predicate values increasing in ascending order? Do you know what will happen if your predicate is using a value that is outside of histogram range? In this session, I will show you how cardinality estimator estimates in all of these scenarios. You will walk out of this session with a clear understanding of how the CE generates its numbers and ready to tackle those nasty, hard to solve query plans.
Download Show_Statistics Output
Download CardinalityEstimator_Scripts
If you have any question regarding this presentation please feel free to send me a note.
