CREATE STATISTICS (Transact-SQL)
Применимо к:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики Synapse Analyticsв Хранилище Microsoft Fabric в Microsoft Fabric
Создает статистику по оптимизации запроса для одного или нескольких столбцов таблицы, индексированного представления или внешней таблицы. Для большинства запросов оптимизатор уже создает достаточно статистики для формирования высококачественного плана запроса, но в некоторых случаях для повышения производительности запросов нужно создать дополнительные статистические данные с помощью инструкции CREATE STATISTICS или изменить структуру запроса.
Дополнительные сведения см. в разделе Статистика.
Примечание.
Дополнительные сведения о статистике в Microsoft Fabric см. в разделе "Статистика" в Microsoft Fabric.
Соглашения о синтаксисе Transact-SQL
Синтаксис
-- Syntax for SQL Server and Azure SQL Database
-- 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_contant ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
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 | = | <> | != | > | >= | !> | < | <= | !<
-- Syntax for 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
}
]
[;]
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
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
Область применения: SQL Server 2016 (13.x) (начиная с SQL Server 2016 (13.x) SP1 CU4) и более поздних версий (начиная с SQL Server 2017 (14.x) CU1)
Вычисляет статистику путем сканирования всех строк. FULLSCAN и SAMPLE 100 PERCENT имеют одинаковые результаты. FULLSCAN не может быть использован с параметром SAMPLE.
При опущении SQL Server использует выборку для создания статистики и определяет размер выборки, необходимый для создания плана запросов высокого качества.
В хранилище в Microsoft Fabric поддерживаются только одноколонок FULLSCAN и одноколоночная статистика на основе sample. Если параметр не включен, создается статистика FULLSCAN.
SAMPLE number { PERCENT | ROWS }
Указывает приблизительное процентное соотношение или число строк в таблице или индексированном представлении для оптимизатора запросов, которые используются при создании статистики. Аргумент number для параметра PERCENT может иметь значение от 0 до 100, а для параметра ROWS аргумент number может иметь значение от 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 }
При включении статистики статистика сохраняет процент выборки создания для последующих обновлений, которые явно не указывают процент выборки. При отключении процент выборки статистики сбрасывается до выборки по умолчанию в последующих обновлениях, которые явно не указывают процент выборки. Значение по умолчанию — 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 (Transact-SQL). Дополнительные сведения об отключении и повторном включении обновления статистики см. в разделе Статистика.
INCREMENTAL = { ON | OFF }
Область применения: SQL Server 2014 (12.x) и более поздних версий.
При значении ON статистики создаются как статистики отдельно по секциям. При значении OFF статистика для всех секций комбинируется. Значение по умолчанию — OFF.
Если статистика секции не поддерживается, создается ошибка. Добавочные статистики не поддерживаются для следующих типов статистических данных.
- Статистики, созданные с индексами, не выровненными по секциям для базовой таблицы.
- Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.
- Статистики, созданные в базах данных, доступных только для чтения.
- Статистики, созданные по фильтрованным индексам.
- Статистика, созданная по представлениям.
- Статистики, созданные по внутренним таблицам.
- Статистики, созданные с пространственными индексами или XML-индексами.
MAXDOP = max_degree_of_parallelism
Область применения: SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) и SQL Server 2017 (14.x) CU3).
Переопределяет параметр конфигурации max degree of parallelism на время выполнения операции со статистикой. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.
Параметр max_degree_of_parallelism может иметь одно из следующих значений:
1
Подавляет формирование параллельных планов.
>1
Ограничивает максимальное количество процессоров, используемых в параллельных операциях со статистиками, заданным или меньшим числом в зависимости от текущей рабочей нагрузки системы.
0 (по умолчанию)
В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.
update_stats_stream_option
Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется.
AUTO_DROP = { ON | OFF }
Область применения: База данных SQL Azure, Управляемый экземпляр SQL Azure и начиная с SQL Server 2022 (16.x)
До 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.
Примеры
Примеры используют AdventureWorks базу данных.
А. Использование 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;
C. Создание отфильтрованной статистики с помощью 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;
Следующие шаги
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по