sys.dm_db_stats_histogram (Transact-SQL)

Статья
05/30/2023

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

Возвращает гистограмму статистики для указанного объекта базы данных (таблицы или индексированного представления) в текущей базе данных SQL Server. Аналогично DBCC SHOW_STATISTICS WITH HISTOGRAM.

Примечание.

Этот DMF доступен начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) CU2

Синтаксис

sys.dm_db_stats_histogram (object_id, stats_id)

Аргументы

object_id
Идентификатор объекта текущей базы данных, для которого запрашиваются статистические свойства. object_id имеет тип int.

stats_id
Идентификатор статистики для указанного аргумента object_id. Идентификатор статистики может быть получен из динамического административного представления sys.stats . stats_id имеет тип int.

Возвращаемая таблица

Имя столбца	Тип данных	Description
object_id	int	Идентификатор объекта (таблицы или индексированного представления), для которого возвращаются свойства объекта статистики.
stats_id	int	Идентификатор объекта статистики. Является уникальным в пределах таблицы или индексированного представления. Дополнительные сведения см. в статье sys.stats (Transact-SQL).
step_number	int	Число шагов в гистограмме.
range_high_key	sql_variant	Верхнее граничное значение столбца для шага гистограммы. Это значение столбца называется также ключевым значением.
range_rows	real	Предполагаемое количество строк, значение столбцов которых находится в пределах шага гистограммы, исключая верхнюю границу.
equal_rows	real	Предполагаемое количество строк, значение столбцов которых равно верхней границе шага гистограммы.
distinct_range_rows	bigint	Предполагаемое количество строк с различающимся значением столбца в пределах шага гистограммы, исключая верхнюю границу.
average_range_rows	real	Среднее число строк с повторяющимися значениями столбцов на шаге гистограммы, за исключением верхней границы (`RANGE_ROWS / DISTINCT_RANGE_ROWS` для `DISTINCT_RANGE_ROWS > 0`).

Замечания

Набор результатов для sys.dm_db_stats_histogram возвращаемой информации, аналогичной DBCC SHOW_STATISTICS WITH HISTOGRAM и также включает object_id, stats_idи step_number.

Так как столбец range_high_key является типом данных sql_variant, может потребоваться использовать CAST или CONVERT если предикат выполняет сравнение с нестрочным константой.

Гистограмма

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

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

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

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

В каждом шаге гистограммы:

Полужирной линией обозначено верхнее граничное значение (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 и не хранит выбранные значения.

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

Разрешения

Требуется наличие у пользователя разрешения на выбор столбцов статистики либо то, чтобы пользователь был владельцем таблицы или членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.

Примеры

А. Простой пример

В следующем примере создается и заполняется простая таблица. Затем создает статистику по столбцу Country_Name .

CREATE TABLE Country
(Country_ID int IDENTITY PRIMARY KEY,
Country_Name varchar(120) NOT NULL);
INSERT Country (Country_Name) VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Country_Stats  
    ON Country (Country_Name) ;

Первичный ключ занимает stat_id номер 1, поэтому призыв sys.dm_db_stats_histogram к stat_id номеру 2 возвращает гистограмму статистики для Country таблицы.

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Country'), 2);

B. Полезный запрос:

SELECT hist.step_number, hist.range_high_key, hist.range_rows, 
    hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'<statistic_name>';

В. Полезный запрос:

В следующем примере выбирается из таблицы Country с предикатом в столбце Country_Name.

SELECT * FROM Country 
WHERE Country_Name = 'Canada';

В следующем примере показана ранее созданная статистика по таблице Country и столбцу Country_Name для шага гистограммы, соответствующего предикату в приведенном выше запросе.

SELECT ss.name, ss.stats_id, shr.steps, shr.rows, shr.rows_sampled, 
    shr.modification_counter, shr.last_updated, sh.range_rows, sh.equal_rows
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE ss.[object_id] = OBJECT_ID('Country') 
    AND ac.name = 'Country_Name'
    AND sh.range_high_key = CAST('Canada' AS CHAR(8));

Следующие шаги

DBCC SHOW_STATISTICS (Transact-SQL)
Связанные с объектами динамические административные представления и функции (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL)

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