Статистика

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure даAzure Synapse Analytics даПараллельное хранилище данных

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

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

Статистика

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

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

Гистограмма

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

Примечание

Гистограммы в SQL Server создаются только для одного столбца, которым является первый столбец в наборе ключевых столбцов объекта статистики.

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

Если говорить подробнее, SQL Server создает гистограмму на основе упорядоченного набора значений столбцов за три шага:

  • Инициализация гистограммы: на первом шаге обрабатывается последовательность значений, начиная с начала сортированного набора; собирается до 200 значений range_high_key, equal_rows, range_rows и distinct_range_rows (range_rows и distinct_range_rows всегда равны нулю на этом шаге). Первый шаг заканчивается, когда все входные значения обработаны или найдено 200 значений.
  • Сканирование со слиянием корзин: каждое дополнительное значение из первого столбца ключа статистики обрабатывается на втором шаге в порядке сортировки; каждое последующее значение либо добавляется в последний диапазон, либо в конце создается новый диапазон (это возможно потому, что входные значения сортированы). Если создается новый диапазон, одна пара существующих, расположенных по соседству диапазонов сворачивается в один. Эта пара диапазонов выбирается с целью уменьшения информационных потерь. Этот способ использует алгоритм максимальной разности для сведения к минимуму числа шагов в гистограмме и вместе с тем максимального увеличения разницы между граничными значениями. Число шагов после сворачивания диапазонов на протяжении этого шага остается равным 200.
  • Консолидация гистограммы: на третьем шаге могут быть свернуты дополнительные диапазоны, если это не приведет к значительной потере данных. Число шагов гистограммы может быть меньше, чем количество различающихся значений, даже для столбцов, в которых число граничных точек меньше 200. Таким образом, даже если в столбце содержится более 200 уникальных значений, гистограмма может содержать менее 200 шагов. Для столбца, состоящего только из уникальных значений, консолидированная гистограмма будет содержать как минимум три шага.

Примечание

Если гистограмма была построена на основе выборки, а не полного сканирования, значения equal_rows, range_rows, distinct_range_rows и average_range_rows будут рассчитаны и не обязательно будут являться целыми числами.

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

Гистограмма

Для каждого шага гистограммы (см. выше):

  • Полужирной линией обозначено верхнее граничное значение (range_high_key) и количество его вхождений (equal_rows).

  • Закрашенная область слева от range_high_key обозначает диапазон значений столбца и среднее количество вхождений каждого значения столбца (average_range_rows). В первом шаге гистограммы значение average_range_rows всегда равно 0.

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

Вектор плотностей

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

Примечание

Частота определяет число вхождений каждого уникального значения в первый ключевой столбец объекта статистики и рассчитывается как (число строк) * плотность. В столбцах с уникальными значениями максимальная частота равна 1.

Вектор плотностей содержит по одной плотности для каждого префикса столбцов объекта статистики. Например, если объект статистики имеет ключевые столбцы CustomerId, ItemId и Price, плотность вычисляется для каждого из следующих префиксов столбцов.

Префикс столбца Префикс, по которому вычисляется плотность
(CustomerId) Строки с совпадающими значениями CustomerId
(CustomerId, ItemId) Строки с совпадающими значениями CustomerId и ItemId.
(CustomerId, ItemId, Price) Строки с совпадающими значениями CustomerId, ItemId и Price.

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

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

Параметры статистики

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

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

  • Вплоть до версии SQL Server 2014 (12.x) Компонент Database Engine использует пороговое значение повторной компиляции в зависимости от числа строк в таблице или индексированном представлении во время оценки статистики. Пороговое значение будет разным независимо от того, является ли таблица временной или постоянной.

    Тип таблицы Кардинальность таблицы (n) Пороговое значение повторной компиляции (количество модификаций)
    Временные таблицы n < 6 6
    Временные таблицы 6 <= n <= 500 500
    Постоянно n <= 500 500
    Временная или постоянная n > 500 500 + (0,20 * n)

    Например, если таблица содержит 20 000 строк, расчет выполняется так: 500 + (0.2 * 20,000) = 4,500. В таком случае статистика обновляется через каждые 4500 модификаций.

  • Начиная с версии SQL Server 2016 (13.x); и при уровне совместимости базы данных 130, Компонент Database Engine также использует понижаемое динамическое пороговое значение для повторной компиляции, которое изменяется в зависимости от кардинальности таблицы в момент оценки статистики. Благодаря этому изменению статистика для больших таблиц будет обновляться чаще. Но если уровень совместимости для базы данных ниже 130, применяются пороговые значения SQL Server 2014 (12.x).

    Тип таблицы Кардинальность таблицы (n) Пороговое значение повторной компиляции (количество модификаций)
    Временные таблицы n < 6 6
    Временные таблицы 6 <= n <= 500 500
    Постоянно n <= 500 500
    Временная или постоянная n >= 500 MIN (500 + (0,20 * n), SQRT (1000 * n))

    Например, если таблица содержит 2 миллиона строк, значение вычисляется как минимальное значение 500 + (0.20 * 2,000,000) = 400,500 и SQRT(1,000 * 2,000,000) = 44,721. Это означает, что статистика будет обновляться каждые 44 721 изменений.

Важно!

В SQL Server 2008 R2 до SQL Server 2014 (12.x) или в SQL Server 2016 (13.x); и более поздних версий в категории уровень совместимости базы данных 120 и ниже включите флаг трассировки 2371, чтобы служба SQL Server использовала пороговое значение динамического обновления статистики, которое понижается.

Хотя это и рекомендуется для всех сценариев, включение флага трассировки не является обязательным. Но вы можете использовать следующие рекомендации для включения флага трассировки 2371 в среде более ранней версии, чем SQL Server 2016 (13.x);:

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

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

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

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

AUTO_UPDATE_STATISTICS_ASYNC

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

Примечание

Чтобы задать параметр асинхронного обновления статистических данных в SQL Server Management Studio, нужно установить значение True для параметров Автоматическое обновление статистики и Асинхронное автоматическое обновление статистики в разделе Параметры окна Свойства базы данных.

Обновление статистики может выполняться синхронно (режим по умолчанию) или асинхронно.

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

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

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

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

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

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

Примечание

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

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

В База данных SQL Azure и Управляемый экземпляр SQL Azure можно избежать потенциальных проблем с параллелизмом с помощью асинхронного обновления статистики, если включить конфигурацию уровня базы данных ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Если эта конфигурация включена, фоновый запрос будет ожидать получения блокировки модификации схемы (Sch-M) и сохранять обновленную статистику в отдельной низкоприоритетной очереди, что позволяет другим запросам продолжать компиляцию запросов с использованием существующей статистики. Если никакой другой сеанс не удерживает блокировку объекта метаданных статистики, фоновый запрос получит блокировку изменения схемы и обновит статистику. В маловероятном случае, когда фоновый запрос не может получить блокировку в течение времени ожидания, равного нескольким минутам, обновление асинхронной статистики будет прервано, а статистика не будет обновлена до тех пор, пока не будет активировано другое автоматическое обновление статистики или пока статистика не будет обновлена вручную.

Примечание

Конфигурация уровня базы данных ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY доступна в База данных SQL Azure и Управляемый экземпляр SQL Azure, и ее планируется включить в одну из будущих версий SQL Server.

INCREMENTAL

Если параметр INCREMENTAL инструкции CREATE STATISTICS имеет значение ON, статистика создается по секциям. Если этот параметр имеет значение OFF, дерево статистики удаляется и SQL Server повторно вычисляет статистику. Значение по умолчанию — OFF. Этот параметр переопределяет свойство уровня базы данных INCREMENTAL. Дополнительные сведения о создании добавочной статистики см. в разделе CREATE STATISTICS (Transact-SQL). Дополнительные сведения об автоматическом создании статистики по секциям см. в разделах Свойства базы данных (страница параметров) и Параметры ALTER DATABASE SET (Transact-SQL).

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

Если статистики по секциям не поддерживаются, параметр пропускается и выводится предупреждение. Добавочные статистики не поддерживаются для следующих типов статистических данных.

  • Статистики, созданные с индексами, не выровненными по секциям для базовой таблицы.
  • Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.
  • Статистики, созданные в базах данных, доступных только для чтения.
  • Статистики, созданные по фильтрованным индексам.
  • Статистика, созданная по представлениям.
  • Статистики, созданные по внутренним таблицам.
  • Статистики, созданные с пространственными индексами или XML-индексами.

Область применения: SQL Server 2014 (12.x) и более поздних версий.

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

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

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

    Примечание

    Начиная с выпуска SQL Server 2014 (12.x), статистические данные не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. После обновления базы данных с секционированными индексами можно заметить разницу в гистограммах для этих индексов. Это изменение в поведении может не влиять на время выполнения запросов. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

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

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

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

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

  • Помощник по настройке ядра СУБД ( Компонент Database Engine ) рекомендует создание статистики.
  • Предикат запроса содержит несколько коррелирующих столбцов, которые еще не включены в один индекс.
  • Запрос выполняет выборку из подмножества данных.
  • Для запроса отсутствует статистика.

Примечание

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

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

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

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

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

Чтобы получить значения плотности, полезные для оценки количества элементов, столбцы в предикате запроса должны совпадать с одним из префиксов столбцов в определении объекта статистики. Например, в следующем примере создается объект статистики по нескольким столбцам: 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. Дополнительные сведения см. в статье Категория событий "Ошибки и предупреждения" (компонент 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.
  • Мониторинг статистики ведется с помощью представлений каталога 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).

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

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

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

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

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

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

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

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

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

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

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

Совет

Начиная с версии SQL Server 2016 (13.x); с пакетом обновления 1 и накопительным пакетом обновления 4 используйте параметр PERSIST_SAMPLE_PERCENT для CREATE STATISTICS (Transact-SQL) или UPDATE STATISTICS (Transact-SQL), чтобы задать и сохранить определенный процент выборки для последующих обновлений статистических данных, в которых такой процент явно не указан.

Автоматическое управление индексами и статистикой

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Например, хранимая процедура Sales.GetRecentSales изменяет значение параметра @date, если @date имеет значение 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 <value> или OPTIMIZE FOR UNKNOWN с параметром RECOMPILE. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

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

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)
Создание отфильтрованных индексов
Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server (Управление поведением Autostat (AUTO_UPDATE_STATISTICS) в SQL Server)
STATS_DATE (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)
sys.stats
sys.stats_columns (Transact-SQL)
Адаптивная дефрагментация индексов