sys.dm_db_stats_histogram (Transact-SQL)

Относится к: SQL Server 2016 (13.x) и более поздние версии Azure SQL DatabaseAzure SQL Managed InstanceSQL Database в Microsoft Fabric

Возвращает гистограмму статистики для указанного объекта базы данных (таблицы или индексированного представления) в текущей базе данных 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.
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 .

Примеры

А. Базовый пример

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

CREATE TABLE Region
(
    Region_ID INT IDENTITY PRIMARY KEY,
    Region_Name VARCHAR (120) NOT NULL
);

INSERT Region (Region_Name)
VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Region_Stats ON Region(Region_Name);

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

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Region'), 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>';

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

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

SELECT * FROM Region
WHERE Region_Name = 'Canada';

В следующем примере показана ранее созданная статистика по таблице Region и столбцу Region_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 AS ss
     INNER JOIN sys.stats_columns AS sc
         ON ss.stats_id = sc.stats_id
        AND ss.object_id = sc.object_id
     INNER JOIN sys.all_columns AS 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) AS shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) AS sh
WHERE ss.[object_id] = OBJECT_ID('Region')
      AND ac.name = 'Region_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)