• Статья

Использование статистики для повышения производительности запросов

Оптимизатор запросов использует статистику для создания планов запросов, которые повышают производительность запросов. Для большинства запросов оптимизатор уже создает необходимую статистику для формирования высококачественного плана запроса, но в некоторых случаях для достижения наилучших результатов нужно создать дополнительные статистические данные или изменить структуру запроса.

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

  • Что такое статистика для оптимизации запросов?

  • Использование параметров статистики на уровне базы данных

  • Определение условий создания статистики

  • Определение условий обновления статистики

  • Создание запросов, эффективно использующих статистику

Дополнительные сведения о плане запроса и о его связи с производительностью запроса см. в разделе Анализ запроса.

Что такое статистика для оптимизации запросов?

Статистика для оптимизации запросов — это объекты, содержащие статистические сведения о распределении значений в одном или нескольких столбцах таблицы или индексированного представления. Оптимизатор запросов использует эти статистические сведения для оценки количества элементов, то есть числа строк, в результатах запроса. Такая оценка количества элементов позволяет оптимизатору запросов создать высококачественный план запроса. Например, оптимизатор запросов может использовать оценочное количество элементов, чтобы выбрать оператор index seek вместо оператора index scan, который потребляет больше ресурсов, и благодаря этому повысить производительность запроса.

Каждый объект статистики создается для списка из одного или нескольких столбцов таблицы и содержит гистограмму, в которой отображается распределение значений в первом столбце. Объекты статистики для нескольких столбцов также хранят статистические сведения о корреляции значений между столбцами. Эти статистические данные корреляции называются значениями плотности и получаются из числа уникальных строк значений столбцов. Дополнительные сведения об объектах статистики см. в разделе Инструкция DBCC SHOW_STATISTICS (Transact-SQL).

Отфильтрованная статистика

Отфильтрованная статистика может повысить производительность запросов, которые выполняют выборку из четко определенных подмножеств данных. Отфильтрованная статистика использует предикат фильтра для выбора подмножества данных, включенных в статистику. Грамотно отфильтрованная статистика может улучшить план выполнения запроса по сравнению со статистикой по полной таблице. Дополнительные сведения о предикате фильтра см. в разделе CREATE STATISTICS (Transact-SQL). Дополнительные сведения об условиях создания отфильтрованной статистики см. в подразделе Определение условий создания статистики этого раздела. Пример использования можно просмотреть в записи блога Использование отфильтрованных статистик с секционированными таблицами на веб-сайте SQLCAT.

Использование параметров статистики на уровне базы данных

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

  • AUTO_CREATE_STATISTICS

  • AUTO_UPDATE_STATISTICS

  • AUTO_UPDATE_STATISTICS_ASYNC

Использование параметра AUTO_CREATE_STATISTICS

Если включен параметр AUTO_CREATE_STATISTICS (автоматическое создание статистики), то оптимизатор запросов в случае необходимости создает статистику по отдельным столбцам в предикате запроса, чтобы улучшить оценку количества элементов для плана запроса. Такая статистика по отдельным столбцам создается для столбцов, у которых отсутствует гистограмма в существующем объекте статистики.

С помощью следующего запроса можно определить, создал ли оптимизатор запросов статистику для столбца предиката запроса. Этот запрос обращается к представлениям каталога sys.stats и sys.stats_columns, чтобы вернуть имя объекта базы данных, имя столбца и имя статистики для всех столбцов, имеющих статистику по отдельным столбцам. Если оптимизатор запросов создает статистику по отдельным столбцам в результате использования параметра AUTO_CREATE_STATISTICS, имя статистики начинается с _WA.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

Параметр AUTO_CREATE_STATISTICS не определяет, создается ли статистика для индексов. Кроме того, этот параметр не создает отфильтрованную статистику. Он применяется строго к статистике по отдельным столбцам для всей таблицы.

Использование параметра AUTO_UPDATE_STATISTICS

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

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

Параметр AUTO_UPDATE_STATISTICS применяется к объектам статистики, создаваемым для индексов, отдельных столбцов в предикатах запросов и к статистике, создаваемой инструкцией CREATE STATISTICS. Этот параметр также применяется к отфильтрованной статистике.

Включение параметров AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS

Параметр AUTO_CREATE_STATISTICS (автоматическое создание статистики) и параметр AUTO_UPDATE_STATISTICS (автоматическое обновление статистики) включены по умолчанию, и для большинства пользовательских баз данных рекомендуется использовать эти значения по умолчанию. Чтобы просмотреть текущие значения этих параметров для всех пользовательских баз данных, можно использовать следующую инструкцию SELECT:

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

В следующем примере для базы данных AdventureWorks2008R2 параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS устанавливаются в значение ON:

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

Дополнительные сведения о том, как задавать эти параметры статистики, см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).

Отключение и повторное включение параметра AUTO_UPDATE_STATISTICS для некоторой статистики

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

  • Используйте системную хранимую процедуру sp_autostats. Это позволит отключить или вновь включить обновление статистики для таблицы или индекса.

  • Укажите параметр NORECOMPUTE в инструкции UPDATE STATISTICS. Чтобы вновь включить обновление статистики, повторно выполните инструкцию UPDATE STATISTICS без параметра NORECOMPUTE.

  • Укажите параметр NORECOMPUTE в инструкции CREATE STATISTICS. Чтобы вновь включить обновление статистики, удалите статистику с помощью инструкции DROP STATISTICS, а затем выполните инструкцию CREATE STATISTICS без параметра NORECOMPUTE.

  • Укажите параметр STATISTICS_NORECOMPUTE в инструкции CREATE INDEX. Чтобы вновь включить обновление статистики, можно выполнить инструкцию ALTER INDEX с параметром STATISTICS_NORECOMPUTE = OFF.

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

Выбор между синхронным и асинхронным обновлением статистики

Обновление статистики может выполняться синхронно (режим по умолчанию) или асинхронно. При синхронном обновлении статистики запросы всегда компилируются и выполняются с актуальной статистикой. Если статистика оказывается устаревшей, то оптимизатор запросов ожидает появления обновленной статистики перед компиляцией и выполнением запроса. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, то оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.

Параметр AUTO_UPDATE_STATISTICS_ASYNC, который управляет асинхронным обновлением статистики на уровне базы данных, определяет, какой режим обновления статистики использует оптимизатор запросов, синхронный или асинхронный. По умолчанию параметр асинхронного обновления статистики отключен, и оптимизатор запросов обновляет статистику синхронно. Параметр AUTO_UPDATE_STATISTICS_ASYNC применяется к объектам статистики, создаваемым для индексов, отдельных столбцов в предикатах запросов и к статистике, создаваемой инструкцией CREATE STATISTICS.

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

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

Синхронную статистику рекомендуется использовать в следующем сценарии.

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

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

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

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

Определение условий создания статистики

Оптимизатор запросов самостоятельно создает статистику следующим образом.

  1. Оптимизатор запросов создает статистику для индексов таблиц или представлений в момент создания индекса. Такая статистика создается по ключевым столбцам индекса. Если индекс является отфильтрованным, то оптимизатор запросов создает отфильтрованную статистику по тому же подмножеству строк, которое указано для отфильтрованного индекса. Дополнительные сведения об отфильтрованных индексах см. в разделах Рекомендации по проектированию отфильтрованных индексов и CREATE INDEX (Transact-SQL).

  2. Если включен параметр AUTO_CREATE_STATISTICS, оптимизатор запросов создает статистику для отдельных столбцов в предикатах запросов.

Для большинства запросов эти два метода создания статистики обеспечивают создание высококачественного плана запроса. В некоторых случаях план запроса можно усовершенствовать, создав дополнительную статистику с помощью инструкции CREATE STATISTICS. Эта дополнительная статистика может фиксировать статистическую корреляцию, которую не учитывает оптимизатор запросов при создании статистики для индексов или отдельных столбцов. Приложение может иметь дополнительные статистические корреляции в данных таблицы; если учесть такие корреляции в объекте статистики, то оптимизатор запросов может усовершенствовать планы запросов. Например, план запроса можно улучшить путем использования отфильтрованной статистики по подмножеству строк данных или статистики по нескольким столбцам предиката запроса.

Если статистика создается инструкцией CREATE STATISTICS, рекомендуется оставлять параметр AUTO_CREATE_STATISTICS включенным, чтобы оптимизатор запросов продолжал регулярно создавать статистику по отдельным столбцам предиката запроса. Дополнительные сведения о предикатах запросов см. в разделе Условие поиска (Transact-SQL).

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

  • Помощник по настройке ядра СУБД (Database Engine) рекомендует создание статистики.

  • Предикат запроса содержит несколько коррелирующих столбцов, которые еще не включены в один индекс.

  • Запрос выполняет выборку из подмножества данных.

  • Для запроса отсутствует статистика.

Помощник по настройке ядра СУБД рекомендует создание статистики

Помощник по настройке ядра СУБД (Database Engine) — это средство анализа влияния рабочей нагрузки на производительность в одной или нескольких базах данных. Он предлагает рекомендации по повышению производительности (например, создание индексов) и может предложить использовать инструкцию CREATE STATISTICS для создания статистики оптимизации запросов. Следует выполнить эту рекомендацию. Дополнительные сведения о помощнике по настройке ядра СУБД (Database Engine) см. в разделе Настройка физической структуры базы данных.

Предикат запроса содержит несколько коррелирующих столбцов

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

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

Во время создания статистики по нескольким столбцам порядок столбцов в определении объекта статистики влияет на эффективность применения плотности для оценки количества элементов. Объект статистики хранит значения плотности для каждого префикса ключевых столбцов в определении объекта статистики. Дополнительные сведения о плотности см. в разделе Инструкция DBCC SHOW_STATISTICS (Transact-SQL).

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

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO

В этом примере объект статистики LastFirst содержит значения плотности для префиксов следующих столбцов: (LastName), (LastName, MiddleName) и (LastName, MiddleName, FirstName). Для (LastName, FirstName) плотность недоступна. Если в запросе используются LastName и FirstName, но не используется MiddleName, то плотность будет недоступна для оценки количества элементов.

Запрос выполняет выборку из подмножества данных

Когда оптимизатор запросов создает статистику по отдельным столбцам и индексам, статистика создается по значениям во всех строках. Если запросы выполняют выборку из подмножества строк и в этом подмножестве присутствует уникальное распределение данных, то отфильтрованная статистика может улучшить планы запросов. Отфильтрованную статистику можно создать с помощью инструкции CREATE STATISTICS с предложением WHERE, чтобы определить выражение предиката фильтра.

Например, используем образец База данных AdventureWorks2008R2, где каждый продукт в таблице Production.Product относится к одной из четырех категорий в таблице Production.ProductCategory: велосипеды, запасные части, одежда и аксессуары. Каждая из этих категорий содержит различные данные, распределенные по весу: вес велосипеда находится в диапазоне от 13,77 до 30,0, вес запчастей — в диапазоне от 2,12 до 1050,00, иногда встречаются значения NULL, значения веса одежды и аксессуаров также равны NULL.

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

Следующая инструкция создает отфильтрованную статистику BikeWeights по всем подкатегориям из категории Bikes. Отфильтрованное выражение предиката определяет велосипеды, выполняя перечисление всех подкатегорий велосипедов со сравнением Production.ProductSubcategoryID IN (1,2,3). В предикате нельзя использовать имя категории Bikes, поскольку оно хранится в таблице Production.ProductCategory, а все столбцы в критерии фильтра должны быть в одной таблице.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

Оптимизатор запросов может использовать отфильтрованную статистику BikeWeights для улучшения плана запроса в следующем запросе, выбирающем все велосипеды, имеющие вес, превышающий 25.

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S 
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

Пропущенные статистики запроса

Если в результате ошибки или другого события оптимизатору запросов не удается создать статистику, то он создает план запроса, не используя статистику. Оптимизатор запросов помечает статистику как отсутствующую и пытается восстановить ее перед следующим выполнением запроса.

Потерянная статистика показывается в виде предупреждений (имя таблицы красным шрифтом), когда план выполнения запроса графически отображается в среде Среда SQL Server Management Studio. Дополнительные сведения см. в разделе Графическое отображение планов выполнения (SQL Server Management Studio). Кроме того, наблюдение за классом событий Missing Column Statistics с помощью приложения Приложение SQL Server Profiler позволяет определить отсутствие статистики. Дополнительные сведения см. в разделе Категория событий Errors and Warnings (компонент Database Engine).

Если статистика отсутствует, выполните следующие действия.

  • Убедитесь, что включены параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS.

  • Убедитесь, что база данных доступна не только для чтения. Если база данных доступна только для чтения, оптимизатор запросов не сможет сохранить статистику.

  • Создайте отсутствующую статистику инструкцией CREATE STATISTICS.

Определение условий обновления статистики

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

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

При обновлении статистики инструкцией UPDATE STATISTICS или хранимой процедурой sp_updatestats рекомендуется оставлять параметр AUTO_UPDATE_STATISTICS включенным, чтобы оптимизатор запросов продолжал регулярно обновлять статистику. Дополнительные сведения об обновлении статистики по столбцу, индексу, таблице или индексированному представлению см. в разделе UPDATE STATISTICS (Transact-SQL). Сведения об обновлении статистики по всем определяемым пользователем таблицам и внутренним таблицам в базе данных см. в описании хранимой процедуры sp_updatestats (Transact-SQL). Например, следующая команда вызывает процедуру sp_updatestats для обновления всей статистики для базы данных.

EXEC sp_updatestats

Для определения времени последнего обновления статистики пользуйтесь функцией STATS_DATE.

Обновление статистики рекомендуется в следующих ситуациях.

  • Запросы выполняются медленно.

  • Выполняются операции вставки в ключевые столбцы, отсортированные по возрастанию или по убыванию.

  • После операций обслуживания.

Запросы выполняются медленно

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

Выполняются операции вставки в ключевые столбцы, отсортированные по возрастанию или по убыванию

Для статистики по ключевым столбцам, отсортированным по возрастанию или убыванию (например, столбец IDENTITY или столбцы отметок реального времени), может понадобиться более частое обновление, чем выполняемое оптимизатором запросов. Операции вставки добавляют новые значения в столбцы, отсортированные по возрастанию или по убыванию. Число добавляемых строк может оказаться слишком маленьким и не вызвать обновление статистики. Если статистика не является актуальной и запросы выполняют выборку из недавно добавленных строк, то в текущей статистике не будет оценки количества элементов для этих новых значений. Это может привести к неправильной оценке количества элементов и замедлить выполнение запроса.

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

После операций обслуживания

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

Не обновляйте статистку после таких операций, как перестроение, дефрагментация и реорганизация индекса. Эти операции не изменяют распределения данных и не оказывают влияния на статистику. Однако обратите внимание, что оптимизатор запросов обновляет статистику при перестроении индекса для таблицы или представления с помощью инструкции ALTER INDEX REBUILD или DBCC DBREINDEX. Этот побочный эффект внутренней реализации этих операций и не выполняется, поскольку статистике необходимо обновление. Оптимизатор запросов не обновляет статистику после выполнения дефрагментации и реорганизации, поскольку эти операции не удаляют и не пересоздают индекс.

Создание запросов, эффективно использующих статистику

Некоторые особенности реализации запросов, например использование локальных переменных и сложных выражений в предикате запроса, могут привести к созданию неоптимальных планов запросов. Этого можно избежать, если следовать рекомендациям по конструированию запросов. Дополнительные сведения о предикатах запросов см. в разделе Условие поиска (Transact-SQL).

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

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

Улучшение оценки количества элементов для выражений

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

  • По возможности упрощайте выражения, содержащие константы. Оптимизатор запросов не вычисляет все функции и выражения, содержащие константы, перед оценкой количества элементов. Например, выражение ABS следует упростить до 100 (-100) to 100.

  • Если в выражении используется несколько переменных, рекомендуется создать вычисляемый столбец для выражения, а затем создать статистику или индекс по вычисляемому столбцу. Например, предикат запроса WHERE PRICE + Tax > 100 может иметь лучшую оценку количества элементов, если создать вычисляемый столбец для выражения Price + Tax.

Улучшение оценки количества элементов для переменных и функций

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

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

  • Для хранения результатов функции (возвращающей табличное значение) с несколькими инструкциями рекомендуется использовать стандартную или временную таблицу. Оптимизатор запросов не создает статистику для функций (возвращающих табличное значение) с несколькими инструкциями. Такой подход позволяет оптимизатору запросов создавать статистику по столбцам таблицы и использовать их для создания улучшенного плана запроса. Дополнительные сведения о функциях (возвращающих табличное значение) с несколькими инструкциями см. в разделе Типы функций.

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

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

    Например, следующая хранимая процедура Sales.GetRecentSales изменяет значение параметра @date, если @date is NULL.

    USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END;
GO

    Если при первом вызове хранимой процедуры Sales.GetRecentSales для параметра @date передается значение NULL, то оптимизатор запросов выполнит компиляцию хранимой процедуры с оценкой количества элементов для @date = NULL, даже если при вызове предиката запроса не указывалось @date = NULL. Такая оценка количества элементов может значительно отличаться от количества строк в фактическом результате запроса. В итоге оптимизатор запросов может выбрать неоптимальный план запроса. Чтобы избежать подобной ситуации, можно переписать хранимую процедуру, разбив ее на две процедуры следующим образом:

    USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNullRecentSales;
GO
CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    EXEC Sales.GetNonNullRecentSales @date;
END
GO
IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNonNullRecentSales;
GO
CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
AS BEGIN
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END;
GO

Улучшение оценки количества элементов с помощью подсказок в запросах

Чтобы улучшить оценку количества элементов для локальных переменных, можно использовать подсказки в запросах OPTIMIZE FOR и OPTIMIZE FOR UNKNOWN с параметром RECOMPILE. Дополнительные сведения см. в разделе Подсказки в запросах (Transact-SQL).

Для некоторых приложений повторная компиляция запроса при каждом выполнении может занять слишком продолжительное время. Подсказка в запросе OPTIMIZE FOR может повысить производительность даже в случае, когда параметр RECOMPILE не используется. Например, можно добавить параметр OPTIMIZE FOR к хранимой процедуре Sales.GetRecentSales, чтобы указать определенную дату. В следующем примере параметр OPTIMIZE FOR добавляется к процедуре Sales.GetRecentSales.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

Улучшение оценки количества элементов с помощью руководств плана

Для некоторых приложений рекомендации по конструированию запросов могут не действовать, поскольку запрос невозможно изменить, или подсказка в запросе RECOMPILE может вызвать слишком много повторных компиляций. С помощью структур плана можно указывать другие подсказки, такие как USE PLAN, чтобы управлять работой запроса, пока идет согласование изменений приложения с поставщиком приложения. Дополнительные сведения о структурах планов см. в разделе Оптимизация запросов в используемых приложениях с помощью структур планов.

См. также

Справочник

CREATE STATISTICS (Transact-SQL)

UPDATE STATISTICS (Transact-SQL)

sp_updatestats (Transact-SQL)

Инструкция DBCC SHOW_STATISTICS (Transact-SQL)

Параметры ALTER DATABASE SET (Transact-SQL)

DROP STATISTICS (Transact-SQL)

CREATE INDEX (Transact-SQL)

ALTER INDEX (Transact-SQL)

Основные понятия

Поиск и настройка сходных запросов с помощью хэширования запросов и планов запросов

Контрольный список для анализа медленно выполняемых запросов

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

Рекомендации по проектированию отфильтрованных индексов

Другие ресурсы

Анализ запроса

Настройка физической структуры базы данных