CREATE STATISTICS (Transact-SQL)

  • Статья

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

Отфильтрованная статистика может повысить производительность запросов, которые выполняют выборку из четко определенных подмножеств данных. Отфильтрованная статистика использует предикат фильтра в предложении WHERE для выбора подмножества данных, включенных в статистику. Операции CREATE STATISTICS можно использовать в базе данных tempdb для сортировки образцов строк при построении статистики.

Дополнительные сведения о статистике, а также условия использования инструкции CREATE STATISTICS, см. в разделе Статистика.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

CREATE STATISTICS statistics_name 
ON { table_or_indexed_view_name } ( column [ ,...n ] ) 
    [ WHERE <filter_predicate> ]
    [ WITH 
        [ [ FULLSCAN 
          | SAMPLE number { PERCENT | ROWS } 
          | STATS_STREAM = stats_stream ] [ , ] ] 
        [ NORECOMPUTE ] 
    ] ;

<filter_predicate> ::= 
    <conjunct> [AND <conjunct>]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,…)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Аргументы

  • statistics_name
    Имя создаваемой статистики.

  • table_or_indexed_view_name
    Имя таблицы или индексированного представления, для которого требуется создать статистику. Статистику можно создавать на основе таблиц или индексированных представлений другой базы данных; для этого нужно указать полное имя таблицы.

  • column [ ,…n]
    Задает ключевой столбец или список ключевых столбцов, на основе которых создается статистика. Можно указать любой столбец, который может указываться в качестве ключевого столбца индекса, за исключением следующих столбцов.

    • Нельзя указывать столбцы Xml, полнотекстовые столбцы и столбцы FILESTREAM.

    • Вычисляемые столбцы могут быть указаны только в том случае, если параметры ARITHABORT и QUOTED_IDENTIFIER базы данных имеют значение ON.

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

  • WHERE <filter_predicate>
    Указывает выражение для выбора подмножества включаемых строк при создании объекта статистики. Статистика, создаваемая с предикатом фильтра, называется отфильтрованной. Предикат фильтра использует простую логику сравнения и не может ссылаться на вычисляемый столбец, столбец определяемого пользователем типа, столбец типа пространственных данных или столбец типа hierarchyID. Сравнения с помощью литералов NULL с операторами сравнения недопустимы. Вместо этого используются операторы IS NULL и IS NOT NULL.

    Далее приведено несколько примеров использования предикатов фильтра для таблицы Production.BillOfMaterials:

    WHERE StartDate > '20000101' AND EndDate <= '20000630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

    Дополнительные сведения о предикатах фильтра см. в разделе Создание отфильтрованных индексов.

  • FULLSCAN
    Вычисляет статистику путем просмотра всех строк в таблице или индексированном представлении. FULLSCAN и SAMPLE 100 PERCENT имеют одинаковые результаты. FULLSCAN не может быть использован с параметром SAMPLE.

  • SAMPLE number { PERCENT | ROWS }
    Указывает приблизительное процентное соотношение или число строк в таблице или индексированном представлении для оптимизатора запросов, которые используются при создании статистики. Аргумент number для параметра PERCENT может иметь значение от 0 до 100, а для параметра ROWS аргумент number может иметь значение от 0 до общего числа строк. Фактическое процентное соотношение или число строк, отбираемых оптимизатором запросов, может не совпадать с заданным значением. Например, оптимизатор запросов просматривает все строки на странице данных.

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

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

    Не рекомендуется указывать значения 0 PERCENT и 0 ROWS. Если для PERCENT или ROWS указано значение 0, объект статистики будет создан без статистических данных.

  • NORECOMPUTE
    Отключает параметр автоматического обновления статистики AUTO_STATISTICS_UPDATE для объекта statistics_name. Если этот параметр указан, то оптимизатор запросов завершит все текущие операции обновления статистики для объекта statistics_name и отключит ее обновление в будущем.

    Чтобы вновь включить обновление статистики, удалите статистику с помощью инструкции DROP STATISTICS, а затем выполните инструкцию CREATE STATISTICS без параметра NORECOMPUTE.

    ПредупреждениеВнимание!

    Использование этого параметра может привести к созданию неоптимальных планов запросов. Рекомендуется ограничить использование этого параметра, причем использовать его надлежит только опытным системным администраторам.

    Дополнительные сведения о параметре AUTO_STATISTICS_UPDATE см. в разделе Параметры ALTER DATABASE SET (Transact-SQL). Дополнительные сведения об отключении и повторном включении обновления статистики см. в разделе Статистика.

  • STATS_STREAM **=**stats_stream
    Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется.

Замечания

Каждый объект статистики может содержать до 32 столбцов.

Условия использования инструкции CREATE STATISTICS

Дополнительные сведения об условиях использования CREATE STATISTICS см. в разделе Статистика.

Упоминаемые зависимости для отфильтрованной статистики

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

Разрешения

Необходимо разрешение ALTER TABLE, либо пользователь должен быть владельцем таблицы или индексированного представления или членом предопределенной роли базы данных db_ddladmin.

Примеры

А.Использование инструкции CREATE STATISTICS с аргументом «SAMPLE число PERCENT»

В следующем примере создается статистика ContactMail1, использующая случайную 5-процентную выборку из столбцов BusinessEntityID и EmailPromotion таблицы Contact из базы данных AdventureWorks.

USE AdventureWorks2012;
GO
CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

Б.Использование инструкции CREATE STATISTICS с аргументами FULLSCAN и NORECOMPUTE

В следующем примере создается статистика ContactMail2 по всем строкам для столбцов BusinessEntityID и EmailPromotion таблицы Contact, при этом автоматический перерасчет статистики блокируется.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2012.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

В.Создание отфильтрованной статистики с помощью инструкции CREATE STATISTICS

В следующем примере создается отфильтрованная статистика ContactPromotion1. Компонент Компонент Database Engine просматривает 50 процентов данных, а затем выбирает все строки с EmailPromotion, равным 2.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = N'ContactPromotion1'
    AND object_id = OBJECT_ID(N'Person.Person'))
DROP STATISTICS Person.Person.ContactPromotion1
GO
CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

См. также

Справочник

UPDATE STATISTICS (Transact-SQL)

sp_updatestats (Transact-SQL)

Инструкция DBCC SHOW_STATISTICS (Transact-SQL)

DROP STATISTICS (Transact-SQL)

sys.stats (Transact-SQL)

sys.stats_columns (Transact-SQL)

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

Статистика