SQL-Ex blog

Статистика SQL Server и оценщик кардинального числа

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

SELECT * INTO TempSalesOrder FROM Sales.SalesOrderHeader

Что означает предикат?

Предикат определяет условное выражение, которое применяется к строкам таблицы для фильтрации результирующего набора запроса.

Пример простого предиката:

В этом первом примере мы хотим вернуть только определенные строки, для которых значение столбца Freight равно указанному значению. Перед выполнением запроса включим фактический план выполнения.

SELECT * FROM TempSalesOrder WHERE Freight=89.4568 

Как видно из плана выполнения запроса, Estimated Number of Rows for all Execution (предварительное число строк для всех выполнений) было оценено CE в 1551 строк, и эта оценка вычислялась с использованием определенной статистики. Подробности использованной статистики можно увидеть в группе атрибута OptimizerStatsUsage плана выполнения.



С помощью команды DBCC SHOW_STATISTICS мы можем получить подробную информацию о плотности распределения статистики и её гистограмме.



Столбец EQ_ROWS данных гистограммы показывает число строк, которые имеют такое же значение, как и в предикате. 178-я строка гистограммы соответствует строке предиката и вычисление оценки строк равно 1551.

Пример двойного предиката:

В этом втором примере мы применим два предиката в одном запросе. В этом случае оценщик кардинального числа использует сложную формулу для вычисления оценки числа строк. Сначала мы выполним следующий запрос и проверим расчетное количество строк.

SELECT *  FROM TempSalesOrder WHERE Freight =89.4568 

	AND CurrencyRateId IS NULL

Как видно, расчет оптимизатором числа строк дал 1154.49, и эта оценка сделана при использовании следующей формулы:

Estimated Number of Rows = (P0*P1^(1/2)*P2^(1/4)*P3^(1/8))*Card

Выражение P() показывает селективность предикатов, я выражение Card есть общее число строк в таблице. Селективность вычисляется делением числа строк в таблице на расчетное число строк. В качестве первого шага мы рассчитаем селективность, используя гистограммы.

DBCC SHOW_STATISTICS ('TempSalesOrder','_WA_Sys_00000016_027D5126')

Как видно, оценка количества строк равна 1551, а общее число строк в таблице составляет 315645. Мы применяем следующую формулу для вычисления селективности первого предиката:

P() = Оценка числа строк / общее число строк в таблице

P0 = 1551/31565

P0 = 0.049137

DBCC SHOW_STATISTICS ('TempSalesOrder','_WA_Sys_00000013_027D5126')

Используем ту же формулу для вычисления селективности второго предиката:

P1 = 17489/31565

P1 = 0.55406

Составная селективность: P0*P1 =0,036575

Оценка числа строк = 31565*1154,493

Оценка числа строк = 1154,493

Как можно увидеть, мы получили расчетное число строк с помощью статистики SQL Server. Однако следует иметь в виду один момент: прежде, чем начать вычисления, нам необходимо выполнить сортировку селективности предикатов в возрастающем порядке, а затем применять формулу.

P0 < P1 < P2 < P3

Пример с множеством предикатов:

В этом последнем примере мы добавим три предиката в наш тестовый запрос и будем использовать событие query_optimizer_estimate_cardinality. Это событие может быть использовано для диагностики проблем оценки кардинального числа. С помощью следующего запроса мы создадим сессию события и запустим её.

SELECT * FROM TempSalesOrder WHERE Freight =89.4568 

  AND CurrencyRateId IS NULL AND TerritoryID =4

  OPTION (RECOMPILE)

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



Сначала отсортируем селективность предикатов, а затем применим формулу:

P0=0,049

P1=0,197

P2=0,554

Оценка числа строк = 0,049 * SQRT(0,197) * SQRT(SQRT(0,554)) * 31565

Оценка числа строк = 592,26

Как можно увидеть, результат, который мы получили, совпадает с оценкой числа строк, возвращаемых запросом.

Статистика SQL Server и устаревший оценщик кардинального числа

В программном обеспечении термин "устаревший код" (Legacy) разработки означает наследование кода из более старой версии программного обеспечения.

Microsoft внес радикальные изменения в алгоритм оценщика кардинального числа в SQL Server 2014, однако устаревший оценщик все еще доступен в SQL Server.

Мы можем использовать в запросах хинт FORCE_LEGACY_CARDINALITY_ESTIMATION, чтобы включить этот оценщик кардинального числа. Если мы хотим включить устаревший оценщик на уровне базы данных, мы можем применить следующие изменения к нашей базе данных:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Альтернативным методом является изменение параметра legacy cardinality в конфигурации базы данных.



Однако если мы применим это изменение к любой базе данных, кэшированный план запроса будет удален, и если вы не имеете серьезной причины использовать унаследованный оценщик, будет более разумно использовать хинт FORCE_LEGACY_CARDINALITY_ESTIMATION на уровне запроса. Использование унаследованного оценщика кардинального числа изменит оценку числа строк из-за устаревшего алгоритма расчета числа строк. Выполним следующий запрос, используя хинт запроса FORCE_LEGACY_CARDINALITY_ESTIMATION.

SELECT *  FROM TempSalesOrder WHERE Freight =89.4568 

  AND CurrencyRateId IS NULL

  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'),RECOMPILE)

Как можно увидеть, оценка числа строк изменилась при использовании устаревшего оценщика кардинального числа. Устаревший оценщик кардинального числа для вычисления числа строк использует следующую формулу:

Оценка числа строк = P0*P1*P2*…*Pn*Card

Оценка числа строк = 0,049137 0,55406 31565

Оценка числа строк = 859,3523

Результат, который мы получили, в точности совпадает с оценкой числа строк, возвращаемых запросом.

Параллельное обновление статистики в SQL Server

Начиная с SQL Server 2016 SP2 мы можем использовать хинт MAXDOP при создании или обновлении статистики вручную. Однако как поведет себя SQL Server при операциях автоматического создания или обновления статистики? Лучшим способом выяснить это является мониторинг активности, которая выполняется SQL Server за сценой, когда статистика создается автоматически. Для демонстрации этого сценария нам потребуется удалить всю автоматически созданную статистку для тестовой таблицы.

DROP STATISTICS dbo.TempSalesOrder._WA_Sys_0000000D_149C0161

  GO

  DROP STATISTICS dbo.TempSalesOrder._WA_Sys_00000013_149C0161

  GO

  DROP STATISTICS dbo.TempSalesOrder._WA_Sys_00000016_149C0161

  GO

Создадим сессию события для захвата действий, выполняемых SQL Server.

CREATE EVENT SESSION [MonitorAutoCreatedStatistics] ON SERVER 

  ADD EVENT sqlserver.auto_stats(

      ACTION(sqlserver.sql_text)

      WHERE ([sqlserver].[session_id]=(125))),

  ADD EVENT sqlserver.object_created(

      ACTION(sqlserver.sql_text)

      WHERE ([sqlserver].[session_id]=(125))),

  ADD EVENT sqlserver.sp_statement_completed(

      ACTION(sqlserver.sql_text)

      WHERE ([sqlserver].[session_id]=(125))),

  ADD EVENT sqlserver.sql_statement_completed(

      ACTION(sqlserver.sql_text)

      WHERE ([sqlserver].[session_id]=(125)))

  WITH (STARTUP_STATE=OFF)

  GO

    ALTER EVENT SESSION MonitorAutoCreatedStatistics ON SERVER   STATE = START

На последнем шаге мы выполним наш простой запрос. Во время выполнения этого запроса SQL Server создаст три отдельных статистики.

SELECT * FROM TempSalesOrder WHERE Freight =89.4568 

  AND CurrencyRateId IS NULL AND TerritoryID =4

  OPTION (RECOMPILE)

После выполнения запроса, давайте займемся интерпретацией данных расширенных событий.



Событие object_created показывает нам создание трех отдельных статистик SQL Server, и эти статистики созданы для каждого предиката в запросе. Событие auto_stats показывает, что эти статистики используются оптимизатором после того, как были созданы.

StatMan является внутренней функцией агрегации и используется для операций статистики. Мы можем увидеть использование этой функции в захваченных данных события.



Можно увидеть хинт MAXDOP в конце запроса, где используется функция Statman; и это говорит нам о том, что процесс генерации статистики в SQL Server выполняется параллельно. Короче говоря, мы можем сказать, что операции автоматического создания и обновления статистики в SQL Server может выполняться параллельно.

Заключение

Статистки SQL Server исключительно критичные объекты в плане возможности оптимизатора генерировать более эффективные планы запросов. А оценщик кардинального числа является ключевой функциональностью оптимизатора, поэтому понимание взаимодействия оценщика и статистики может быть очень полезно для более четкой интерпретации планов запросов.