Оценка кардинальности (SQL Server)

Логика оценки кратности, называемая оценщиком кратности, перепроектирована в SQL Server 2014 для повышения качества планов запросов и, следовательно, для повышения производительности запросов. Новый оценщик кратности включает предположения и алгоритмы, которые хорошо работают на современных рабочих нагрузках OLTP и хранилища данных. Он основан на подробных исследованиях оценки кардинальности современных рабочих нагрузок и на нашем опыте, накопленном за последние 15 лет в процессе улучшения оценщика кардинальности SQL Server. Отзывы клиентов показывают, что, хотя большинство запросов выиграют от изменений или останутся без изменений, незначительное количество может показать ухудшение по сравнению с предыдущим оценщиком кардинальности.

Рекомендации по тестированию производительности и настройке

Новый оценщик кратности включен для всех новых баз данных, созданных в SQL Server 2014. Однако обновление до SQL Server 2014 не активирует новый оценщик кардинальности для существующих баз данных.

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

1. Обновите все существующие базы данных для использования нового механизма оценки кратности. Для этого используйте уровень совместимости ALTER DATABASE (Transact-SQL) для задания уровня совместимости базы данных значение 120.

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

3. После того как ваша рабочая нагрузка запущена с новым оценщиком кардинальности (уровень совместимости базы данных 120 (SQL Server 2014)), и производительность конкретного запроса ухудшилась, вы можете запустить этот запрос с флагом трассировки 9481, чтобы использовать версию оценщика кардинальности, применяемую в SQL Server 2012 и более ранних версиях. Сведения о выполнении запроса с флагом трассировки см. в статье "Включение поведения оптимизатора запросов SQL Server, влияющего на план", которое можно контролировать различными флагами трассировки на определенном уровне запроса.

4. Если вы не можете одновременно изменить все базы данных для использования нового оценщика кардинальности, можно использовать предыдущий оценщик кардинальности для всех баз данных с помощью ALTER DATABASE Compatibility Level (Transact-SQL), чтобы установить уровень совместимости базы данных на 110.

5. Если рабочая нагрузка выполняется с уровнем совместимости базы данных 110 и хотите протестировать или запустить конкретный запрос с новым оценщиком кратности, можно запустить запрос с флагом трассировки 2312, чтобы использовать версию оценки кратности SQL Server 2014. Сведения о выполнении запроса с флагом трассировки см. в статье базы знаний "Включение поведения оптимизатора запросов SQL Server, влияющего на выполнение плана, которое может быть контролируемо различными флагами трассировки на уровне конкретного запроса".

Новые XEvents

Существует два новых query_optimizer_estimate_cardinality XEvents для поддержки новых планов запросов.

• query_optimizer_estimate_cardinality возникает, когда оптимизатор запросов оценивает кратность реляционного выражения.

• query_optimizer_force_both_cardinality_estimation_behaviors возникает при включении трассировок 2312 и 9481, при попытке одновременно принудительно задать и старое, и новое поведение оценки кратности.

Примеры

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

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

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

SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';  

В этом примере новые строки добавляются в таблицу Sales каждый день, запрос запрашивает продажи, которые произошли 12.19.2013, а статистика была обновлена 12.18.2013. Предыдущий оценщик кратности предполагает, что значения 12.19.2013 не существуют, так как дата превышает максимальную дату, а статистика не была обновлена, чтобы включить значения 12.19.2013. Эта ситуация, известная как проблема восходящего ключа, возникнет, если вы загрузите данные в течение дня и затем выполните запросы к данным до обновления статистики.

Это поведение изменилось. Теперь, даже если статистика не была обновлена для последних данных по возрастанию, добавленных с момента последнего обновления статистики, новый оценщик кратности предполагает наличие значений и использует среднее кратность для каждого значения в столбце в качестве оценки кратности.

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

В этом примере предположим, что таблица Cars содержит 1000 строк, Make имеет 200 соответствий для "Honda", модель имеет 50 соответствий для "Civic", и что все Civics являются Honda. Таким образом, 20% значений в столбце "Make" — "Honda", 5% значений в столбце "Модель" — "Civic", а фактическое число Honda Civic равно 50. Предыдущие оценки кратности предполагают, что значения в столбцах Make и Model не зависят друг от друга. Предыдущий оптимизатор запросов оценивает, что существует 10 Honda Civics (0.05 * 0.20 * 1000 строк = 10 строк).

SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';  

Это поведение изменилось. Теперь новые оценки кратности предполагают, что столбцы Make и Model имеют некоторую корреляцию. Оптимизатор запросов оценивает более высокую кардинальность, добавляя экспоненциальный компонент в уравнение оценки. Оптимизатор запросов теперь оценивает, что 22,36 строки ( .05 * SQRT(.20) * 1000 строк = 22,36 строки ) соответствуют предикату. Для этого сценария и конкретного распределения данных 22.36 строки ближе к фактическим 50 строкам, возвращаемым запросом.

Обратите внимание, что новая логика оценки кратности сортирует выборки предиката и увеличивает экспонент. Например, если селективности предиката были .05, .20 и .25, оценка кратности будет равна (.05 * SQRT(.20) * SQRT(SQRT(.25)).

Пример C. Новые оценки кратности предполагают, что отфильтрованные предикаты в разных таблицах независимы

В этом примере предыдущий оценщик кратности предполагает, что фильтры предиката 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. В практическом плане предположение заключается в том, что игрушки возвращаются каждый день и не только в определенный день. В этом случае новые оценки кратности будут меньше, чем предыдущие оценки кратности.

См. также

Наблюдение и настройка производительности