Статистика

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

В этом разделе

• Компоненты и основные понятия

• Условия создания статистики

• Условия обновления статистики

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

Компоненты и основные понятия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В начало

Условия создания статистики

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

USE AdventureWorks2012;
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, чтобы определить выражение предиката фильтра.

Например, в базе данных AdventureWorks2012 каждый продукт в таблице 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 AdventureWorks2012;
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. Кроме того, определить отсутствие статистики можно с помощью наблюдения за классом событий Missing Column Statistics с помощью приложения Приложение SQL Server Profiler. Дополнительные сведения см. в разделе Категория событий Errors and Warnings (компонент Database Engine).

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

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

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

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

Если статистика для доступной только для чтения базы данных или доступного только для чтения моментального снимка отсутствует или устарела, компонент Компонент Database Engine создаст и будет поддерживать временную статистику в базе данных tempdb. Когда компонент Компонент Database Engine создает временную статистику, перед именем статистики добавляется суффикс _readonly_database_statistic для проведения различия между временной статистикой и постоянной. Суффикс _readonly_database_statistic зарезервирован для статистики, созданной SQL Server. Скрипты для временной статистики могут создаваться и воспроизводиться в базе данных для чтения и записи. При создании скрипта Среда Management Studio изменяет суффикс имени статистики с _readonly_database_statistic на _readonly_database_statistic_scripted.

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

• Удаление временной статистики с использованием инструкции DROP STATISTICS (Transact-SQL).

• Наблюдение за статистикой ведется с помощью представлений каталога sys.stats и sys.stats_columns. sys_stats включает столбец is_temporary для указания на то, какая статистика является постоянной и какая временной.

Поскольку временная статистика хранится в базе данных tempdb, перезапуск службы SQL Server вызывает исчезновение всей временной статистики.

В начало

Условия обновления статистики

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В начало

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  USE AdventureWorks2012;
  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 AdventureWorks2012;
  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, чтобы указать определенную дату. В следующем примере к процедуре Sales.GetRecentSales добавляется параметр OPTIMIZE FOR.

USE AdventureWorks2012;
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)

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

Создание отфильтрованных индексов