Оценка количества элементов (SQL Server)
Логика оценки кратности, называемая оценщиком кратности, была перепроектирована в SQL Server 2014 г., чтобы улучшить качество планов запросов и, следовательно, повысить производительность запросов. Новый механизм оценки количества элементов состоит из предположений и алгоритмов, которые отлично подходят для современных рабочих нагрузок OLTP и хранилища данных. Он основан на глубоком исследовании оценки количества элементов для современных рабочих нагрузок и нашем опыте усовершенствования этого механизма, накопленном за последние 15 лет. Отзывы от клиентов свидетельствуют, что эти изменения положительно сказываются на большинстве запросов либо все остается по прежнему, хотя производительность небольшого числа запросов может снизиться по сравнению с использованием предыдущего механизма оценки количества элементов.
Примечание
Оценка количества элементов — это прогноз количества строк в результате запроса. Оптимизатор запросов использует эти оценки, чтобы выбрать план выполнения запроса. Качество плана запроса имеет прямое влияние на повышение производительности запроса.
Рекомендации по тестированию и настройке производительности
Новый оценщик кратности включен для всех новых баз данных, созданных в SQL Server 2014 г. Однако обновление до SQL Server 2014 не включает новый оценщик кратности в существующих базах данных.
Чтобы обеспечить наилучшую производительность запросов, следуйте приведенным ниже рекомендациям по тестированию рабочей нагрузки с помощью нового механизма оценки количества элементов, прежде чем включать его в работающей системе.
Обновите все существующие базы данных, чтобы они использовали новый механизм оценки количества элементов. Для этого используйте инструкцию ALTER DATABASE Compatibility Level (Transact-SQL), чтобы задать уровень совместимости базы данных равным 120.
Запустите тестовую рабочую нагрузку с новым механизмом оценки количества элементов и устраните все выявленные проблемы производительности таким же образом, как это делается в настоящее время.
После выполнения рабочей нагрузки с новым оценщиком кратности (уровень совместимости базы данных 120 (SQL Server 2014)) и регрессии определенного запроса можно выполнить запрос с флагом трассировки 9481, чтобы использовать версию оценщика кратности, используемую в SQL Server 2012 и более ранних версиях. Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.
Если вы не можете одновременно изменить все базы данных для использования нового оценщика кратности, можно использовать бывший оценщик кратности для всех баз данных с помощью инструкции ALTER DATABASE Compatibility Level (Transact-SQL), чтобы задать уровень совместимости базы данных равным 110.
Если рабочая нагрузка работает при уровне совместимости базы данных, равном 110, и требуется протестировать или выполнить определенный запрос с новым механизмом оценки количества элементов, то можно выполнить этот запрос с флагом трассировки 2312 для использования версии средства оценки количества элементов, представленной в SQL Server 2014. Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.
Новые события XEvent
Для поддержки новых планов запросов появились два новых события XEvents query_optimizer_estimate_cardinality.
query_optimizer_estimate_cardinality возникает, когда оптимизатор запросов определяет количество элементов в реляционном выражении.
query_optimizer_force_both_cardinality_estimation_behaviors возникает, когда включены оба флага трассировки 2312 и 9481 с целью заставить одновременно работать и старый и новый механизмы оценки количества элементов.
Примеры
В следующих примерах показаны некоторые изменения, реализованные в новом механизме оценки количества элементов. Код оценки количества элементов был переписан. Логика этого сложна, поэтому предоставлять полный список всех изменений невозможно.
Примечание
Эти образцы приведены в качестве основополагающих сведений. Каких-либо действий со стороны пользователя для изменения методов проектирования баз данных и запросов не требуется.
Пример А. В новых механизмах оценки количества элементов используется среднее количество элементов для недавно добавленных восходящих данных
В этом примере показано, как с помощью нового механизма оценки количества элементов можно улучшить оценку количества элементов для данных по возрастанию, которые превышают максимальное значение в таблице во время последнего обновления статистики.
SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';
В данном примере новые строки добавляются в таблицу продаж каждый день, запрос возвращает данные, полученные в 19.12.2013, а статистика последний раз обновлялась 18.12.2013. Предыдущий механизм оценки количества элементов предполагает, что значения от 19.12.2013 отсутствуют, поскольку эта дата следует после максимальной даты, а значения за 19.12.2013 отсутствуют, так как статистика не обновлялась. Эта ситуация, известная как проблема ключа по возрастанию, возникает, когда данные загружаются в течение дня, а затем к ним выполняются запросы до обновления статистики.
Это поведение изменено. Теперь, даже если статистика не обновлена для данных, которые были добавлены с момента последнего обновления статистики, в новом механизме оценки количества элементов предполагается, что значения существуют, и в качестве оценки количества элементов используется среднее количество элементов для каждого значения в столбце.
Пример Б. Новые средства оценки количества элементов предполагают, что отфильтрованные предикаты из одной таблицы имеют определенную корреляцию.
Для данного примера предположим, что число строк в таблице «Автомобили» равно 1000. Запрос «Марка» имеет 200 совпадений для «Honda», запрос «Модель» имеет 50 совпадений для «Civic», а все модели Civic относятся к марке Honda. Поэтому 20% значений из столбца «Марка» — это Honda, 5% значений из столбца «Модель» — это Civic и фактическое количество вариантов Honda Civic равно 50. В предыдущих оценках количества элементов предполагается, что значения из столбцов «Марка» и «Модель» не зависят друг от друга. Предыдущий оптимизатор запросов предполагает наличие 10 экземпляров Honda Civic (.05 * .20 * 1000 строк = 10 строк).
SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';
Это поведение изменено. Теперь при формировании новых оценок количества элементов предполагается, что столбцы марки и модели имеют некоторую корреляцию. Оптимизатор запросов определяет, что количество элементов больше, путем добавления экспоненциального компонента в формулу оценки. Теперь оптимизатор запросов оценивает, что предикату соответствуют 22,36 строки ( .05 * SQRT(.20) * 1000 строк = 22,36 строк). Для этого варианта и определенного распределения данных оценка в 22,36 строки более близка к фактическим 50 строкам, которые будут возвращены запросом.
Обратите внимание, что новая логика механизма оценки количества элементов предусматривает сортировку по избирательности предиката и увеличивает экспоненту. Например, если бы избирательности предиката составляли 0,05, 0,20 и 0,25, то оценка количества элементов выглядела бы следующим образом (0,05 * SQRT (0,20) * SQRT (SQRT (0,25)) ).
Пример В. В новых механизмах оценки количества элементов предполагается, что отфильтрованные предикаты из разных таблиц не зависят друг от друга
В этом примере в предыдущем механизме оценки количества элементов предполагается, что фильтры предикатов s.type и r.date связаны друг с другом. Однако результаты теста современных рабочих нагрузок показали, что фильтры предикатов для столбцов из различных таблиц обычно не связаны друг с другом.
SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';
Это поведение изменено. Теперь в новой логике механизма оценки количества элементов предполагается, что фильтр s.type не связан с фильтром r.date. На практике это означает, что возврат игрушек происходит каждый день, а не только в какой-то определенный день. В этом случае новые оценки количества элементов будут выражаться меньшим числом, чем предыдущие оценки количества элементов.