CREATE STATISTICS (Transact-SQL)

Статья
05/21/2024

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

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

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

Примечание.

Дополнительные сведения о статистике в Microsoft Fabric см. в статье "Статистика в хранилище данных Fabric".

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure и Управляемый экземпляр SQL Azure.

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

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

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

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

<comparison> ::=
        column_name <comparison_op> constant

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

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_constant ]

Синтаксис для Системы платформы Azure Synapse Analytics и Analytics (PDW).

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

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

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

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

<comparison> ::=
        column_name <comparison_op> constant

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

Синтаксис Для Microsoft Fabric.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

Аргументы

statistics_name

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

table_or_indexed_view_name

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

column [ ,...n]

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

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

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

WHERE <filter_predicate>

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

Область применения: SQL Server 2016 (13.x) с пакетом обновления 1 (SP 4), SQL Server 2017 (14.x) CU 1 и более поздних версий

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

При опущении SQL Server использует выборку для создания статистики и определяет размер выборки, необходимый для создания плана запросов высокого качества.

В Хранилище в Microsoft Fabric поддерживаются только одноколонок и одноколоночная FULLSCAN SAMPLEстатистика. Если параметр не включен, SAMPLE создается статистика.

SAMPLE number { PERCENT | ROWS }

Указывает приблизительный процент или количество строк в таблице или индексированном представлении для оптимизатора запросов, используемого при создании статистики. Для PERCENT, число может быть от 0 до 100 и ROWSдля , число может быть от 0 до общего числа строк. Фактическое процентное соотношение или число строк, отбираемых оптимизатором запросов, может не совпадать с заданным значением. Например, оптимизатор запросов просматривает все строки на странице данных.

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

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

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

В Хранилище в Microsoft Fabric поддерживаются только одноколонок и одноколоночная FULLSCAN SAMPLEстатистика. Если параметр не включен, FULLSCAN создается статистика.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Если ONстатистика сохраняет процент выборки создания для последующих обновлений, которые явно не указывают процент выборки. Когда OFFпроцент выборки статистики сбрасывается до выборки по умолчанию в последующих обновлениях, которые явно не указывают процент выборки. Значение по умолчанию — OFF.

Примечание.

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

STATS_STREAM = stats_stream

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

NORECOMPUTE

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

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

Предупреждение

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

Дополнительные сведения о параметре см. в AUTO_STATISTICS_UPDATE разделе "ПАРАМЕТРЫ ALTER DATABASE SET". Дополнительные сведения об отключении и повторном включении обновления статистики см. в разделе Статистика.

INCREMENTAL = { ON | OFF }

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

При ONсоздании статистики для каждой секции создается статистика. Если OFFстатистика объединяется для всех секций. Значение по умолчанию — OFF.

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

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

MAXDOP = max_degree_of_parallelism

Область применения: SQL Server 2016 (13.x) с пакетом обновления 2 (SP 2), SQL Server 2017 (14.x) CU 3 и более поздних версий

Переопределяет параметр конфигурации максимальной степени параллелизма во время статистической операции. Дополнительные сведения см. в разделе "Настройка максимальной степени параллелизма" (параметр конфигурации сервера). Используется MAXDOP для ограничения количества процессоров, используемых в параллельном выполнении плана. Максимальное число процессоров — 64.

Параметр max_degree_of_parallelism может иметь одно из следующих значений:

1: подавляет параллельное создание плана.
>1: ограничивает максимальное число процессоров, используемых в параллельной операции индекса, указанным числом.
0 (по умолчанию): использует фактическое количество процессоров или меньше на основе текущей системной рабочей нагрузки.

update_stats_stream_option

AUTO_DROP = { ON | OFF }

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

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

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

Примечание.

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

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

Разрешения

Требуются одно из указанных далее разрешений.

ALTER TABLE
Пользователь является владельцем таблицы
Членство в предопределенной роли базы данных db_securityadmin

Замечания

SQL Server может использовать tempdb для сортировки примеров строк перед сборкой статистики.

Статистика для внешних таблиц

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

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

Статистика с отфильтрованным условием

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

Когда следует использовать CREATE STATISTICS

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

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

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

Ограничения

Обновление статистики во внешних таблицах не поддерживается. Для обновления статистики во внешней таблице удалите и повторно создайте статистику.
Каждый объект статистики может содержать до 64 столбцов.
Параметр MAXDOP не совместим с STATS_STREAMпараметрами ROWCOUNTи PAGECOUNT параметрами.
Параметр MAXDOP ограничивается параметром MAX_DOP группы рабочей нагрузки Resource Governor (если применимо).
CREATEи DROP STATISTICS внешние таблицы не поддерживаются в База данных SQL Azure.

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

А. Использование CREATE STATISTICS с примером числа PERCENT

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

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

B. Использование CREATE STATISTICS с FULLSCAN и NORECOMPUTE

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

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

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

В следующем примере создается отфильтрованная статистика ContactPromotion1. Ядро СУБД отбирает 50 процентов данных и выбирает из них все строки, в которых EmailPromotion имеет значение 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Создание статистики для внешней таблицы

Помимо указания списка столбцов при создании статистики для внешней таблицы необходимо определить, следует ли создавать статистику с помощью выборки строк или путем сканирования всех строк. CREATEи DROP STATISTICS внешние таблицы не поддерживаются в База данных SQL Azure.

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

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

Е. Использование CREATE STATISTICS с FULLSCAN и PERSIST_SAMPLE_PERCENT

В следующем примере показано, как создать NamePurchase статистику для всех строк в BusinessEntityID таблице и EmailPromotion столбцах Person таблицы, а также задать процент выборки на 100 процентов для всех последующих обновлений, которые явно не указывают процент выборки.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

В примерах используется база данных AdventureWorksDW.

F. Создание статистики по двум столбцам

В следующем примере создается статистика CustomerStats1 на основе столбцов CustomerKey и EmailAddress таблицы DimCustomer. Статистика создается на базе статистически значимой выборки строк в таблице Customer.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Создание статистики с помощью полной проверки

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

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Создание статистики путем указания процента выборки

В следующем примере создается статистика CustomerStatsSampleScan на основе проверки 50 % строк в таблице DimCustomer.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Использование CREATE STATISTICS с AUTO_DROP

Чтобы использовать статистику автоматического удаления, просто добавьте следующее в предложение WITH для создания или обновления статистики.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

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

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

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

CREATE STATISTICS (Transact-SQL)

Синтаксис

Аргументы

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Разрешения

Замечания

Статистика для внешних таблиц

Статистика с отфильтрованным условием

Когда следует использовать CREATE STATISTICS

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

Ограничения

Примеры

А. Использование CREATE STATISTICS с примером числа PERCENT

B. Использование CREATE STATISTICS с FULLSCAN и NORECOMPUTE

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

D. Создание статистики для внешней таблицы

Е. Использование CREATE STATISTICS с FULLSCAN и PERSIST_SAMPLE_PERCENT

В примерах используется база данных AdventureWorksDW.

F. Создание статистики по двум столбцам

G. Создание статистики с помощью полной проверки

H. Создание статистики путем указания процента выборки

I. Использование CREATE STATISTICS с AUTO_DROP

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

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

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

CREATE STATISTICS (Transact-SQL)

Синтаксис

Аргументы

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Разрешения

Замечания

Статистика для внешних таблиц

Статистика с отфильтрованным условием

Когда следует использовать CREATE STATISTICS

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

Ограничения

Примеры

А. Использование CREATE STATISTICS с примером числа PERCENT

B. Использование CREATE STATISTICS с FULLSCAN и NORECOMPUTE

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

D. Создание статистики для внешней таблицы

Е. Использование CREATE STATISTICS с FULLSCAN и PERSIST_SAMPLE_PERCENT

В примерах используется база данных AdventureWorksDW.

F. Создание статистики по двум столбцам

G. Создание статистики с помощью полной проверки

H. Создание статистики путем указания процента выборки

I. Использование CREATE STATISTICS с AUTO_DROP

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

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

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