Автор Liwei Yin

Опубликовано 06.09.2021

Оптимизатор SQL Server оценивает запросы на основе затрат ресурсов. Для каждого конкретного запроса SQL Server создает план на основе оценки числа возвращаемых из таблицы строк. Именно поэтому понимание того, как SQL Server вычисляет предполагаемое число возвращаемых строк помогает находить и устранять неоптимальные элементы плана запроса. Эта статья начинает серию рассказов о SQL Server Cardinality Estimation (SQL Server CE) старых и новых версий.

Способ, используемый SQL Server для оценки числа строк, возвращаемых запросом с одним предикатом прост и понятен. Всё становится немного сложнее когда в запросе к SQL Server присутствует больше одного предиката. В отличие от случая с одним предикатом, у SQL Server разные стратегии в старой и новой реализации SQL Server CE.

Для прежних версий SQL Server CE предполагается что распределение данных по разным столбцам не зависят друг от друга. В новом SQL Server CE предполагается, что распределение данных по разным столбцам коррелируется. Короче говоря, ожидаемое количество строк у нового CE предсказывается лучше, чем количество строк для такого же запроса, но с CE прежних версий.

Ниже пример, в котором для демонстрации используется OLTP база данных AdventureWorks2019.

------  Подготовка данных -------------------------------------------
alter database [AdventureWorks2019] set compatibility_level=150
go
use [AdventureWorks2019]
go
if exists(select 1 from sys.tables where name='SalesOrderDetail')
drop table SalesOrderDetail
go
-- импортируем всё в новую таблицу SalesOrderDetailSalesOrderDetail
select * into SalesOrderDetail from Sales.SalesOrderDetail 
go
------  Подготовка данных -------------------------------------------
------  Для демонстрации будем использовать представленный ниже запрос T-SQL, который возвращает 533 строки:

select * from SalesOrderDetail where ProductID=711 and UnitPrice=20.1865

Прежний CE

1. Используем флаг трассировки 9481, который указывает оптимизатору использовать старый CE

select * from SalesOrderDetail 
where ProductID=711 
and UnitPrice=20.1865 
option(querytraceon 9481,recompile) --форсируем использование прежнего CE

2. Предполагаемое число возвращаемых строк - 38, а фактически возвращено было - 533 строки. Давайте посмотрим, как получилось, что в этом примере предполагаемое число строк оказалось равным 38. Обратите внимание: если у Вас предполагаемое число строк отличается от того, что в этой статье, обновите статистику с полным просмотром таблицы.

update statistics SalesOrderDetail with fullscan

3. Покажем статистики:

select * from sys.stats where object_id = object_id('SalesOrderDetail')

Поскольку в предложении where указаны два разных столбца, автоматически будет создана статистика для каждого из этих двух столбцов.

4. Два предиката связаны по AND с селективностью P0 и P1. Комбинированная селективность: P0*P1. Давайте погрузимся в вычисления:

1)  Предполагаемое число строк: P0*P1*Card.
    P0 — селективность первой колонки, P1 — селективность второй колонки. 
    Порядок не имеет значения.
    Card — это общее количество строк, в нашем случае 121317.
2)	Селективность по ProductID (P0): 0.02547046
    ProductId: 711
    Предполагаемое число строк: 3090
    Селективность: 3090/121317=0.02547046

    dbcc show_statistics(SalesOrderDetail,_WA_Sys_00000005_0E8E2250)

3)	Селективность по UnitPrice: 0.01230660
    UnitPrice:20.1865
    Предполагаемое число строк:1493
    Селективность: 1493/121317=0.01230660

    dbcc show_statistics(SalesOrderDetail,_WA_Sys_00000007_0E8E2250)

4)	Итоговая оценка: P0*P1*'table cardinality' =0.02547046*0.01230660*121317=38.0273914872384120, 
    округляется до 38.

5) Если имеется больше двух предикатов, просто умножаются предикаты: P0*P1*P2….*Pn.

Новый СЕ

1) Формула расчёта комбинированной селективности в последних версиях CE совершенно другая: P0 *P1^(1/2) *P2^(1/4) *P3^(1/8)

2) P0, P1, P2, P3 селективность предикатов, где P0<P1<P2<P3.

3) Если в предложении where более 4 столбцов, учитываются только первые 4, остальные игнорируются.

Пожалуйста, если на практике используется более 2 предикатов, применяйте следующую формулу:

4) Выполните следующие запросы с настройками по умолчанию для уровня совместимости 150, чтобы заработал новый CE:

   select * from SalesOrderDetail where ProductID=711 and UnitPrice=20.1865

5. Предполагаемое количество строк теперь равно 238, что несколько ближе к актуальному, чем у старого CE, там значение равнялось 38. Давайте углубимся в вычисления, чтобы увидеть, как получилось предполагаемое число строк 238.

6. Давайте посмотрим, порядок расчёта:

1)  В нашем запросе есть два предиката, поэтому комбинированный предикат P0*P1^(1/2)
2)  Селективность по ProductID: 0.02547046
    ProductId: 711
    Предполагаемое число строк: 3090
    Селективность: 3090/121317=0.02547046

    dbcc show_statistics(SalesOrderDetail,_WA_Sys_00000005_0E8E2250)

3)   Селективность по UnitPrice: 0.01230660
     UnitPrice:20.1865
     Предполагаемое число строк: 1493
     Селективность: 1493/121317=0.01230660

     dbcc show_statistics(SalesOrderDetail,_WA_Sys_00000007_0E8E2250)

4)  Поскольку селективность UnitPrice (0,0125629219) меньше, чем селективность ProductID (0,02547046)
    P0=0.01230660
    P1=0.02547046
    Комбинированная селективность: P0*P1^(1/2)=0.01230660*sqrt(0.02547046)=0.00196406781623722
    Общее число предполагаемых строк:
    P0*P1^(1/2)*Card =0.00196406781623722*121317=238.274815262451, 
    округляется до 238.

Статистика и гистограмма не изменяются после обновления до новых версий SQL Server (2019/2022), зато способ использования сервером статистики и гистограмм изменяется, и, в итоге, могут быть получены разные оценки числа строк, что повлечёт за собой создание совсем другого плана запроса, полученного на основе другой формулы оценки возвращаемого числа строк.

Следовательно, необходимо проверить рабочую нагрузку под новым CE после обновления версии. Если будет зафиксировано снижение производительности и это будет связано с разницей расчётов в CE, вам может потребоваться вернуться к устаревшему CE (задав нужный уровень в опциях запроса или указав старый уровень совместимости для всей базы данных).

SQL Server Cardinality Estimation: несколько статистик по одному столбцу