Оценка количества элементов (SQL Server)Cardinality Estimation (SQL Server)

Логика оценки количества элементов, называемая механизмом оценки количества элементов, переработана в SQL Server 2014SQL Server 2014 в целях улучшения качества планов запросов и тем самым повышения производительности запросов.The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. Новый механизм оценки количества элементов состоит из предположений и алгоритмов, которые отлично подходят для современных рабочих нагрузок OLTP и хранилища данных.The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. Он основан на глубоком исследовании оценки количества элементов для современных рабочих нагрузок и нашем опыте усовершенствования этого механизма, накопленном за последние 15 лет.It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Отзывы от клиентов свидетельствуют, что эти изменения положительно сказываются на большинстве запросов либо все остается по прежнему, хотя производительность небольшого числа запросов может снизиться по сравнению с использованием предыдущего механизма оценки количества элементов.Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

Примечание

Оценка количества элементов — это прогноз количества строк в результате запроса.Cardinality estimates are a prediction of the number of rows in the query result. Оптимизатор запросов использует эти оценки, чтобы выбрать план выполнения запроса.The query optimizer uses these estimates to choose a plan for executing the query. Качество плана запроса имеет прямое влияние на повышение производительности запроса.The quality of the query plan has a direct impact on improving query performance.

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

В SQL Server 2014SQL Server 2014новый механизм оценки количества элементов включается для всех вновь создаваемых баз данных.The new cardinality estimator is enabled for all new databases created in SQL Server 2014SQL Server 2014. Однако при обновлении до SQL Server 2014SQL Server 2014 новый механизм оценки количества элементов не включается для существующих баз данных.However, upgrading to SQL Server 2014SQL Server 2014 does not enable the new cardinality estimator on existing databases.

Чтобы обеспечить наилучшую производительность запросов, следуйте приведенным ниже рекомендациям по тестированию рабочей нагрузки с помощью нового механизма оценки количества элементов, прежде чем включать его в работающей системе.To ensure the best query performance, use these recommendations to test your workload with the new cardinality estimator before enabling it on your production system.

  1. Обновите все существующие базы данных, чтобы они использовали новый механизм оценки количества элементов.Upgrade all existing databases to use the new cardinality estimator. Чтобы сделать это, используйте уровень совместимости ALTER DATABASE (Transact-SQL) задать уровень совместимости базы данных 120.To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120.

  2. Запустите тестовую рабочую нагрузку с новым механизмом оценки количества элементов и устраните все выявленные проблемы производительности таким же образом, как это делается в настоящее время.Run your test workload with the new cardinality estimator, and then troubleshoot any new performance issues in the same manner you currently troubleshoot performance issues.

  3. Если после запуска рабочей нагрузки с новым механизмом оценки количества элементов (при уровне совместимости базы данных в 120 (SQL Server 2014)) производительность конкретного запроса снизилась, можно выполнить запрос с флагом трассировки 9481 для использования версии средства оценки количества элементов, представленного в SQL Server 2012SQL Server 2012 и более ранних выпусках.Once your workload is running with the new cardinality estimator (database compatibility level 120 (SQL Server 2014)), and a specific query has regressed, you can run the query with trace flag 9481 to use the version of the cardinality estimator used in SQL Server 2012SQL Server 2012 and earlier. Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.To run a query with a trace flag, see the KB article Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.

  4. Если не удается изменить все базы данных за один раз, чтобы использовать новый оценщик количества элементов, можно использовать первый оценщик для всех баз данных с помощью уровень совместимости ALTER DATABASE (Transact-SQL) для уровень совместимости базы данных в значение 110.If you cannot change all of the databases at once to use the new cardinality estimator, you can use the former cardinality estimator for all databases by using ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 110.

  5. Если рабочая нагрузка работает при уровне совместимости базы данных, равном 110, и требуется протестировать или выполнить определенный запрос с новым механизмом оценки количества элементов, то можно выполнить этот запрос с флагом трассировки 2312 для использования версии средства оценки количества элементов, представленной в SQL Server 2014.If your workload is running with database compatibility level 110 and you want to test or run a specific query with the new cardinality estimator, you can run the query with trace flag 2312 to use the SQL Server 2014 version of the cardinality estimator. Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.To run a query with a trace flag, see the KB article Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.

Новые события XEventNew XEvents

Для поддержки новых планов запросов появились два новых события XEvents query_optimizer_estimate_cardinality.There are two new query_optimizer_estimate_cardinality XEvents to support the new query plans.

  • query_optimizer_estimate_cardinality возникает, когда оптимизатор запросов определяет количество элементов в реляционном выражении.query_optimizer_estimate_cardinality occurs when the query optimizer estimates the cardinality on a relational expression.

  • query_optimizer_force_both_cardinality_estimation_behaviors возникает, когда включены оба флага трассировки 2312 и 9481 с целью заставить одновременно работать и старый и новый механизмы оценки количества элементов.query_optimizer_force_both_cardinality_estimation_behaviors occurs when both traceflags 2312 and 9481 are enabled, attempting to force both old and new cardinality estimation behavior at the same time.

ПримерыExamples

В следующих примерах показаны некоторые изменения, реализованные в новом механизме оценки количества элементов.The following examples show some of the changes in the new cardinality estimates. Код оценки количества элементов был переписан.The code for estimating cardinality has been rewritten. Логика этого сложна, поэтому предоставлять полный список всех изменений невозможно.The logic is complex and it is not possible to provide an exhaustive list of all changes.

Примечание

Эти образцы приведены в качестве основополагающих сведений.These examples are provided as conceptual information. Каких-либо действий со стороны пользователя для изменения методов проектирования баз данных и запросов не требуется.No action is required on your part to change the way you design databases and queries.

Пример А. В новых механизмах оценки количества элементов используется среднее количество элементов для недавно добавленных восходящих данныхExample A. New cardinality estimates use an average cardinality for recently added ascending data

В этом примере показано, как с помощью нового механизма оценки количества элементов можно улучшить оценку количества элементов для данных по возрастанию, которые превышают максимальное значение в таблице во время последнего обновления статистики.This example demonstrates how the new cardinality estimator can improve cardinality estimates for ascending data that exceeds the maximum value in the table during the most recent statistics update.

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

В данном примере новые строки добавляются в таблицу продаж каждый день, запрос возвращает данные, полученные в 19.12.2013, а статистика последний раз обновлялась 18.12.2013.In this example, new rows are added to the Sales table each day, the query asks for sales that occurred on 12/19/2013, and statistics were last updated on 12/18/2013. Предыдущий механизм оценки количества элементов предполагает, что значения от 19.12.2013 отсутствуют, поскольку эта дата следует после максимальной даты, а значения за 19.12.2013 отсутствуют, так как статистика не обновлялась.The previous cardinality estimator assumes the 12/19/2013 values do not exist since the date exceeds the maximum date and statistics have not been updated to include the 12/19/2013 values. Эта ситуация, известная как проблема ключа по возрастанию, возникает, когда данные загружаются в течение дня, а затем к ним выполняются запросы до обновления статистики.This situation, known as the ascending key problem, will occur if you load data during the day, and then run queries against the data before statistics are updated.

Это поведение изменено.This behavior has changed. Теперь, даже если статистика не обновлена для данных, которые были добавлены с момента последнего обновления статистики, в новом механизме оценки количества элементов предполагается, что значения существуют, и в качестве оценки количества элементов используется среднее количество элементов для каждого значения в столбце.Now, even if statistics have not been updated for the most recent ascending data that is added since the last statistics update, the new cardinality estimator assumes the values exist and uses the average cardinality for each value in the column as the cardinality estimate.

Пример Б. Новые средства оценки количества элементов предполагают, что отфильтрованные предикаты из одной таблицы имеют определенную корреляцию.Example B. New cardinality estimates assume filtered predicates on the same table have some correlation

Для данного примера предположим, что число строк в таблице «Автомобили» равно 1000. Запрос «Марка» имеет 200 совпадений для «Honda», запрос «Модель» имеет 50 совпадений для «Civic», а все модели Civic относятся к марке Honda.For this example, assume the table Cars as 1000 rows, Make has 200 matches for 'Honda', Model has 50 matches for 'Civic', and that all of the Civics are Hondas. Поэтому 20% значений из столбца «Марка» — это Honda, 5% значений из столбца «Модель» — это Civic и фактическое количество вариантов Honda Civic равно 50.Therefore, 20% of the values in the Make column are 'Honda', 5% of the values in the Model column are 'Civic', and the actual number of Honda Civics is 50. В предыдущих оценках количества элементов предполагается, что значения из столбцов «Марка» и «Модель» не зависят друг от друга.The previous cardinality estimates assume the values in the Make and the Model columns are independent of each other. Предыдущий оптимизатор запросов определяет, существует 10 экземпляров Honda Civic (.05 *.20 * 1000 строк = 10 строк).The previous query optimizer estimates there are 10 Honda Civics (.05 * .20 * 1000 rows = 10 rows).

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

Это поведение изменено.This behavior has changed. Теперь при формировании новых оценок количества элементов предполагается, что столбцы марки и модели имеют некоторую корреляцию.Now, the new cardinality estimates assume the Make and Model columns have some correlation. Оптимизатор запросов определяет, что количество элементов больше, путем добавления экспоненциального компонента в формулу оценки.The query optimizer estimates a higher cardinality by adding an exponential component to the estimation equation. Теперь оптимизатор запросов определяет, оценка в 22,36 строки (.05 * SQRT(.20) * 1000 строк = 22,36 строк) соответствуют предикату.The query optimizer now estimates that 22.36 rows ( .05 * SQRT(.20) * 1000 rows = 22.36 rows ) match the predicate. Для этого варианта и определенного распределения данных оценка в 22,36 строки более близка к фактическим 50 строкам, которые будут возвращены запросом.For this scenario and specific data distribution, 22.36 rows is closer to the actual 50 rows that the query will return.

Обратите внимание, что новая логика механизма оценки количества элементов предусматривает сортировку по избирательности предиката и увеличивает экспоненту.Note, the new cardinality estimator logic sorts the predicate selectivities and increases the exponent. Например, если бы избирательности предиката составляли.05,.20 и.25, то оценка количества элементов бы (.05 * SQRT(.20) * SQRT(SQRT(.25))).For example, if the predicate selectivities were .05, .20, and .25, the cardinality estimate would be (.05 * SQRT(.20) * SQRT(SQRT(.25)) ).

Пример В. В новых механизмах оценки количества элементов предполагается, что отфильтрованные предикаты из разных таблиц не зависят друг от другаExample C. New cardinality estimates assume filtered predicates on different tables are independent

В этом примере в предыдущем механизме оценки количества элементов предполагается, что фильтры предикатов s.type и r.date связаны друг с другом.For this example, the previous cardinality estimator assumes that the predicate filters s.type and r.date are correlated. Однако результаты теста современных рабочих нагрузок показали, что фильтры предикатов для столбцов из различных таблиц обычно не связаны друг с другом.However, test results on modern workloads showed that predicate filters on columns in different tables are usually not correlated with each other.

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';  

Это поведение изменено.This behavior has changed. Теперь в новой логике механизма оценки количества элементов предполагается, что фильтр s.type не связан с фильтром r.date.Now, the new cardinality estimator logic assumes that s.type is not correlated with r.date. На практике это означает, что возврат игрушек происходит каждый день, а не только в какой-то определенный день.In practical terms, the assumption is that toys are returned every day and not only on a specific day. В этом случае новые оценки количества элементов будут выражаться меньшим числом, чем предыдущие оценки количества элементов.In this case, the new cardinality estimates will be a smaller number than the previous cardinality estimates.

См. такжеSee Also

Наблюдение и настройка производительностиMonitor and Tune for Performance