Статистика

Статья
2025-01-02

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure базе данных SQL Azure Synapse Analytics в Microsoft Fabric

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

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

Статистика

Статистика для оптимизации запросов представляет собой большие двоичные объекты (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 с меньшей плотностью, чем производитель.

Примечание.

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

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

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

Статистика фильтрации

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

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

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

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

Помечая статистику как устаревшую на основе изменений строк, возникает даже при AUTO_UPDATE_STATISTICS отключении параметра. Если параметр AUTO_UPDATE_STATISTICS отключен, статистика не обновляется, даже если они помечены как устаревшие. Планы продолжают использовать устаревшие объекты статистики. Параметр AUTO_UPDATE_STATISTICS OFF может привести к неоптимальным планам запросов и снижению производительности запросов. Рекомендуется задать для параметра AUTO_UPDATE STATISTICS значение ON.

До SQL Server 2014 (12.x) ядро СУБД использует порог перекомпиляции на основе количества строк в таблице или индексированном представлении во время вычисления статистики. Пороговое значение будет разным независимо от того, является ли таблица временной или постоянной.

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

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

Начиная с SQL Server 2016 (13.x) и с уровнем совместимости базы данных 130, ядро СУБД также использует снижение порогового значения перекомпиляции динамической статистики, которая корректируется в соответствии с кратностью таблицы во время вычисления статистики. С этим изменением статистика по большим таблицам обновляется чаще. Однако если база данных имеет уровень совместимости ниже 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(1,000 * n) )`

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

Внимание

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

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

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

Оптимизатор запросов проверяет наличие устаревшей статистики перед компиляцией запроса и до выполнения кэшированного плана запроса. Перед компиляцией запроса оптимизатор запросов использует столбцы, таблицы и индексированные представления в предикате запроса, чтобы определить, какая статистика может быть устаревшей. Перед выполнением кэшированного плана запроса ядро СУБД проверяет, ссылается ли план запроса на статистику up-to-date.

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

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

Параметр AUTO_UPDATE_STATISTICS всегда имеет значение OFF для таблиц с оптимизацией памяти.

AUTO_UPDATE_STATISTICS_ASYNC

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

Примечание.

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

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

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

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

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

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

Примечание.

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

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

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

Примечание.

Параметр конфигурации ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY базы данных доступен в База данных SQL Azure, Управляемый экземпляр SQL Azure и в SQL Server, начиная с SQL Server 2022 (16.x).

Параметр AUTO_DROP

Область применения: База данных SQL Azure, Управляемый экземпляр SQL Azure и начиная с SQL Server 2022 (16.x)

В SQL Server до SQL Server 2022 (16.x), если статистика создается пользователем или сторонним средством в пользовательской базе данных, эти объекты статистики могут блокировать или вмешиваться в изменения схемы, которые могут потребоваться.

Начиная с SQL Server 2022 (16.x), параметр автоматического удаления включен по умолчанию для всех новых и перенесенных баз данных. Свойство AUTO_DROP позволяет создавать объекты статистики таким образом, чтобы последующие изменения схемы не блокировались объектом статистики, а статистические данные удалялись по необходимости. Таким образом, вручную созданная статистика с включенным автоматическим удалением ведет себя как автоматически созданная статистика.

Примечание.

Попытка задать или отменить свойство автоматического удаления в автоматически созданной статистике может вызвать ошибки. Автоматически созданная статистика всегда использует автоматическое удаление. Некоторые резервные копии при восстановлении могут неправильно задать это свойство до следующего обновления объекта статистики (вручную или автоматически). Однако автоматически созданная статистика всегда ведет себя как статистика автоматического удаления. При восстановлении базы данных в SQL Server 2022 (16.x) из предыдущей версии рекомендуется выполнить sp_updatestats в базе данных, установив соответствующие метаданные для функции автоматического удаления статистики.

Например, чтобы вручную создать объект статистики dbo.DatabaseLog в таблице:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Например, чтобы обновить параметр автоматического удаления объекта статистики dbo.DatabaseLog в таблице:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

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

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

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

INCREMENTAL

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

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

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

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

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

Когда создавать статистику

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

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

Примечание.

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

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

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

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

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

Примечание.

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

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

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

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

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

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

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

Например, используя AdventureWorks2022, каждый продукт в таблице принадлежит одной из четырех категорий в Production.ProductProduction.ProductCategory таблице: Bikes, Components, Clothingи Accessories. Каждая из категорий имеет другое распределение данных для веса: вес велосипеда диапазон от 13,77 до 30,0, весы компонентов варьируются от 2,12 до 1050,00 с некоторыми NULL значениями, весы одежды все NULL, а аксессуары также NULL.

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

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

USE AdventureWorks2022;
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
     INNER 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 Profiler указывает, когда статистика отсутствует. Дополнительные сведения см. в статье Категория событий "Ошибки и предупреждения" (ядро СУБД).

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

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

Если статистика по базе данных только для чтения или моментальному снимку только для чтения отсутствует или устарела, ядро СУБД создает и сохраняет временную статистику.tempdb Когда ядро СУБД создает временную статистику, имя статистики добавляется суффиксом _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.
Информация об обновлении статистики для всех пользовательских и внутренних таблиц в базе данных см. в хранимой процедуре sp_updatestats.
Дополнительные сведения о пороговых значениях для автоматического обновления статистики см. в статье Параметр AUTO_UPDATE_STATISTICS.

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

Обнаружение устаревшей статистики

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

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

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

Примеры обновления статистики вручную см. в разделе UPDATE STATISTICS.

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

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

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

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

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

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

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

Такие операции, как перестроение, дефрагментация или реорганизация индекса, не изменяют распределение данных. Поэтому не нужно обновлять статистику после выполнения операций 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) SP1 CU4, используйте параметр PERSIST_SAMPLE_PERCENT в #B1 CREATE STATISTICS или UPDATE STATISTICS, чтобы задать и сохранить определенный процент выборки для последующих статистических обновлений, которые явно не указывают процент выборки.

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

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

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

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

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

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

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

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

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

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

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

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

Например, следующая хранимая процедура Sales.GetRecentSales изменяет значение параметра @date в случае@date.NULL
```
USE AdventureWorks2022;
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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO
```
Если первый вызов хранимой процедуры Sales.GetRecentSales передает NULL для параметра @date, оптимизатор запросов компилирует хранимую процедуру с оценкой кардинальности для @date = NULL, даже когда предикат запроса не вызывается с @date = NULL. Такая оценка количества элементов может значительно отличаться от количества строк в фактическом результате запроса. В итоге оптимизатор запросов может выбрать неоптимальный план запроса. Чтобы избежать подобной ситуации, можно переписать хранимую процедуру, разбив ее на две процедуры следующим образом:
```
USE AdventureWorks2022;
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));
    EXECUTE 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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO
```

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

Чтобы улучшить оценки кратности для локальных переменных, можно использовать OPTIMIZE FOR <value> подсказки или OPTIMIZE FOR UNKNOWN подсказки RECOMPILEзапроса. Дополнительные сведения см. в подсказках к запросам.

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

USE AdventureWorks2022;
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 AS h, Sales.SalesOrderDetail AS 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, чтобы управлять работой запроса, пока идет согласование изменений приложения с поставщиком приложения. Дополнительные сведения о структурах планов см. в разделе Руководства планов.

В базе данных SQL Azure рекомендуется использовать указания хранилища запросов для принудительного исполнения планов вместо структур планов. Дополнительные сведения см. в разделе Указания хранилища запросов.

Поделиться через

Статистика

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

Статистика

Гистограмма

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

Статистика фильтрации

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

Параметр AUTO_CREATE_STATISTICS

параметр AUTO_UPDATE_STATISTICS

AUTO_UPDATE_STATISTICS_ASYNC

Параметр AUTO_DROP

INCREMENTAL

Когда создавать статистику

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

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

При запрос определяется пропущенная статистика

Когда обновлять статистику

Обнаружение устаревшей статистики

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

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

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

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

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

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

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

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

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

Связанный контент

Обратная связь

Дополнительные ресурсы