Dmitry Piliugin

Dmitry Piliugin

Cardinality Estimation Process in SQL Server

March 29, 2018 by

In this post, we are going to take a deeper look at the cardinality estimation process. We will use SQL Server 2014, the main concepts might also be applied to the earlier versions, however, the process details are different.


The algorithms responsible for performing the cardinality estimation in SQL Server 2014 are implemented in the classes called Calculators.

Read more »

Cardinality Estimation Concepts in SQL Server

March 28, 2018 by

In this blog post we are going to talk about the principles and the main concepts which are used by the optimizer to perform an estimation, and also, we will do a little bit math, so be prepared.

Base Statistics

A cardinality estimation mechanism, as a first step, usually uses base statistics to estimate the expected number of rows that should be returned from the base table. You may look at these statistics using DBCC command – DBCC SHOW_STATISTICS.

Read more »

Cardinality Estimation Place in the Optimization Process in SQL Server

March 27, 2018 by

In this blog post, I’m going to look at the place of the Cardinality Estimation Process in the whole Optimization Process. We’ll see some internals, that will show us, why the Query Optimizer is so sensitive to the cardinality estimation. To understand that we should observe the main steps that a server performs when the query is sent for execution.

Plan Construction Process

Below you may see the picture of the general plan-building process in SQL Server from the moment when a server receives a query till the storage engine is ready to retrieve the data. Take a quick look first, and next I’ll provide explanations.

Read more »