Статистика в Synapse SQL

2025-04-10

В этой статье приведены рекомендации и примеры создания и обновления статистики оптимизации запросов с помощью ресурсов Synapse SQL: выделенного пула SQL и бессерверного пула SQL.

Статистика в выделенном пуле SQL

Для чего используется статистика?

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

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

Например, если оптимизатор оценивает, что дата, по которой фильтруется ваш запрос, вернет одну строку, он выберет один план. Если система оценивает, что выбранная дата вернет 1 миллион строк, она предложит другой план.

Автоматическое создание статистики

Выделенный обработчик пула SQL анализирует входящие запросы пользователей для отсутствующих статистических данных при установке ONпараметра базы данных AUTO_CREATE_STATISTICS. Если статистика отсутствует, оптимизатор запросов создает статистику по отдельным столбцам в предикате запроса или условии соединения.

Эта функция используется для улучшения оценок кардинальности для плана запроса.

Это важно

Автоматическое создание статистики в настоящее время включено по умолчанию.

Можно проверить, настроено ли свойство AUTO_CREATE_STATISTICS для хранилища данных, выполнив следующую команду.

SELECT name, is_auto_create_stats_on
FROM sys.databases

Если хранилище данных не включено AUTO_CREATE_STATISTICS, рекомендуется включить это свойство, выполнив следующую команду:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Эти инструкции активируют автоматическое создание статистики:

ВЫБЕРИТЕ
Команда INSERT SELECT
CTAS
Обновление
Удалить
EXPLAIN, если они содержат операцию соединения или обнаружено наличие предиката.

Примечание.

Автоматическое создание статистики не выполняется для временных или внешних таблиц.

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

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

Примечание.

Создание статистики регистрируется в журнале sys.dm_pdw_exec_requests в контексте учетной записи другого пользователя.

При создании автоматической статистики они будут принимать форму: WA_Sys<8-значный идентификатор столбца в Hex>_<8-значный идентификатор таблицы в Hex>. Вы можете просмотреть уже созданные статистики, выполнив команду DBCC SHOW_STATISTICS :

DBCC SHOW_STATISTICS (<table_name>, <target>)

Table_name — это имя таблицы, содержащей статистику для отображения, которая не может быть внешней таблицей. Целевой объект — это имя целевого индекса, статистики или столбца, для которого нужно отобразить статистические данные.

Обновите статистику

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

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

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

Ниже приведены рекомендации по обновлению статистики:

Тип	Рекомендация
Частота обновления статистики	Консервативная: ежедневно После загрузки или преобразования данных
Выборка	Если менее 1 млрд строк, используйте выборку по умолчанию (20 %). Если строк более 1 000 000 000, используйте выборку в 2 %.

Определение последнего обновления статистики

Один из первых вопросов, которые нужно задать при устранении неполадок с запросом: "Обновлена ли статистика?"

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

Динамическое представление управления недоступно для определения того, изменились ли данные в таблице с момента последнего обновления статистики. Знание возраста ваших статистических данных дает вам часть представления.

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

Примечание.

Если в столбце существует существенное изменение распределения значений, следует обновить статистику независимо от последнего обновления.

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

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

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

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

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

Реализация управления статистикой

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

Ниже приведены некоторые основные принципы обновления статистики в процессе загрузки:

Убедитесь, что для каждой загружаемой таблицы обновляется по крайней мере один объект статистики. Тогда в процессе обновления статистики будет обновляться информация о размере таблицы (число строк и страниц).
Сосредоточьтесь на тех столбцах, которые участвуют в предложениях JOIN, GROUP BY, ORDER BY и DISTINCT.
Рекомендуется чаще обновлять столбцы "с возрастающим порядком ключа", например даты транзакций, потому что эти значения не будут включены в гистограмму статистики.
Рекомендуется реже обновлять столбцы со статическим распределением.
Помните, что каждый объект статистики обновляется последовательно. Просто реализовать UPDATE STATISTICS <TABLE_NAME> может быть не идеальным решением, особенно для обширных таблиц с большим количеством объектов статистики.

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

Примеры: создание статистики

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

Создание одностолбцовой статистики с параметрами по умолчанию

Чтобы создать одностолбцовую статистику, достаточно указать имя объекта статистики и имя столбца. В этом синтаксисе все параметры используются по умолчанию. По умолчанию выделенный пул SQL делает выборку 20 процентов таблицы при создании статистики.

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name]);

Рассмотрим пример.

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Создание одностолбцовой статистики путем проверки всех строк

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

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name])
    WITH FULLSCAN;

Рассмотрим пример.

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH FULLSCAN;

Создание одностолбцовой статистики с указанием размера выборки

Еще одним вариантом является указание размера выборки в процентах:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH SAMPLE 50 PERCENT;

Создание одностолбцовой статистики только для некоторых строк

Вы также можете создать статистику по части строк в таблице, которая называется отфильтрованной статистикой.

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

Этот пример создает статистику по диапазону значений. Значения можно легко определить для сопоставления с диапазоном значений в разделе.

CREATE STATISTICS stats_col1
    ON table1(col1)
    WHERE col1 > '2000101' AND col1 < '20001231';

Примечание.

Чтобы оптимизатор запросов рассмотрел возможность использования отфильтрованной статистики, когда выбирает план распределенного запроса, запрос должен быть в пределах определения объекта статистики. Если взять приведенный выше пример, предложение WHERE запроса должно указать значения col1 от 2000101 до 20001231.

Создание одностолбцовой статистики со всеми параметрами

Можно также комбинировать параметры. В приведенном ниже примере создается отфильтрованный объект статистики с настраиваемым размером выборки:

CREATE STATISTICS stats_col1
    ON table1 (col1)
    WHERE col1 > '2000101' AND col1 < '20001231'
    WITH SAMPLE 50 PERCENT;

Для получения полной информации см. CREATE STATISTICS.

Создание многостолбцовой статистики

Для создания объекта статистики с несколькими столбцами используйте предыдущие примеры, но укажите больше столбцов.

Примечание.

Гистограмма, используемая для оценки количества строк в результатах запроса, доступна только для первого столбца, указанного в определении объекта статистики.

В этом примере гистограмма находится на product_category. Статистика пересечения столбцов основана на product_category и product_sub_category:

CREATE STATISTICS stats_2cols
    ON table1 (product_category, product_sub_category)
    WHERE product_category > '2000101' AND product_category < '20001231'
    WITH SAMPLE 50 PERCENT;

Так как корреляция существует между product_category и product_sub_category, объект статистики с несколькими столбцами может оказаться полезным, если к этим столбцам обращаются одновременно. При запросе этой таблицы статистика с несколькими столбцами улучшает оценки кратности для соединений, агрегирования GROUP BY, отдельных счетчиков и фильтров WHERE (если основной столбец статистики является частью фильтра).

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

Одним из способов создания статистики является выдача команд CREATE STATISTICS после создания таблицы:

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

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

В пуле SQL нет системной хранимой процедуры, эквивалентной sp_create_stats в SQL Server. Эта хранимая процедура создает один объект статистики столбцов для каждого столбца базы данных, у которых еще нет статистики.

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

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

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

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

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

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

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

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

Примеры: обновление статистики

Чтобы обновить статистику, можно:

Обновить один объект статистики. Указать имя объекта статистики, который вы хотите обновить.
Обновить все объекты статистики для таблицы. Указать имя таблицы, а не один объект статистики.

Обновление одного указанного объекта статистики

Для обновления указанного объекта статистики используйте следующий синтаксис:

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

Рассмотрим пример.

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

Обновляя определенные объекты статистики, можно свести к минимуму затраты времени и ресурсов на управление статистикой. Для этого действия требуется некоторое мнение о выборе лучших объектов статистики для обновления.

Обновите всю статистику в таблице

Ниже показан простой метод обновления всех объектов статистики для таблицы.

UPDATE STATISTICS [schema_name].[table_name];

Рассмотрим пример.

UPDATE STATISTICS dbo.table1;

Оператор UPDATE STATISTICS прост в использовании. Просто помните, что он обновляет все статистические данные в таблице, запрашивая больше работы, чем необходимо.

Если производительность не является проблемой, этот метод является самым простым и наиболее полным способом гарантировать актуальность статистики.

Примечание.

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

Сведения о UPDATE STATISTICS реализации процедуры см. в временных таблицах. Метод реализации слегка отличается от процедуры CREATE STATISTICS, описанной выше, но результат одинаков. Полный синтаксис см. в разделе "Обновление статистики".

Метаданные статистики

Существует несколько системных представлений и функций, которые можно использовать для поиска информации о статистике. Например, можно увидеть, может ли объект статистики быть устаревшим с помощью функции STATS_DATE(). STATS_DATE() позволяет увидеть, когда статистика была создана или обновлена.

Просмотры каталога для статистики

Вот какие системные представления показывают информацию о статистике:

Вид каталога	Описание
sys.columns	Одна строка для каждого столбца.
sys.objects	Одна строка для каждого объекта в базе данных.
sys.schemas	Одна строка для каждой схемы в базе данных.
sys.stats	Одна строка для каждого объекта статистики.
sys.stats_columns	Одна строка для каждого столбца в объекте статистики. Ссылки обратно к sys.columns.
sys.tables	Одна строка для каждой таблицы (включает внешние таблицы).
sys.table_types	Одна строка для каждого типа данных.

Системные функции для статистики

Эти системные функции полезны для работы со статистикой:

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

Сочетание столбцов и функций статистики в одном представлении

Это представление объединяет столбцы, относящиеся к статистике и результатам функции STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

Примеры DBCC SHOW_STATISTICS()

DBCC SHOW_STATISTICS() показывает данные, удерживаемые в объекте статистики. Эти данные состоят из трех частей:

Заголовок
Вектор плотности
Гистограмма

Заголовок — это метаданные статистики. Гистограмма отображает распределение значений в первом ключевом столбце объекта статистики.

Вектор плотности измеряет корреляцию между столбцами. Выделенный пул SQL вычисляет оценки кардинальности по данным из объекта статистики.

Отображение заголовка, плотности и гистограммы

Этот простой пример показывает все три части объекта статистики.

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Рассмотрим пример.

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

Отображение одной или нескольких частей DBCC SHOW_STATISTICS()

Если вы заинтересованы только в просмотре определенных частей, используйте предложение WITH и укажите, какие части требуется показать:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
    WITH stat_header, histogram, density_vector

Рассмотрим пример.

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
    WITH histogram, density_vector

Отличия DBCC SHOW_STATISTICS()

DBCC SHOW_STATISTICS() применяется в выделенном пуле SQL строже, чем в SQL Server.

Недокументированные возможности не поддерживаются.
Не удается использовать Stats_stream.
Невозможно соединить результаты для определенных подмножеств данных статистики. Например, STAT_HEADER JOIN DENSITY_VECTOR.
NO_INFOMSGS нельзя задать для подавления сообщений.
Невозможно использовать квадратные скобки вокруг имен статистики.
Невозможно использовать имена столбцов для идентификации объектов статистики.
Пользовательская ошибка 2767 не поддерживается.

Статистика в бессерверном пуле SQL

Статистика создается для конкретного столбца и конкретного набора данных (путь к хранилищу).

Примечание.

Невозможно создать статистику для столбцов с большими объектами (LOB).

Для чего используется статистика?

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

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

Например, если оптимизатор оценивает, что дата, по которой выполняется фильтрация запроса, вернет одну строку, он выберет один план. Если по оценкам, выбранная дата вернет 1 миллион строк, она выберет другой план.

Автоматическое создание статистики

Бессерверный пул SQL анализирует входящие запросы пользователей для отсутствующих статистических данных. Если статистика отсутствует, оптимизатор запросов создает статистику по отдельным столбцам в предикате запроса или условии соединения, чтобы улучшить оценку кардинальности для плана запроса.

Инструкция SELECT активирует автоматическое создание статистики.

Примечание.

Для автоматического создания статистики используется метод выборки, и в большинстве случаев процент выборки будет меньше 100%. Этот поток одинаков для каждого формата файла. Помните, что при чтении CSV-файла с анализатором версии 1.0 выборка не поддерживается, а автоматическое создание статистики не будет происходить с процентом выборки менее 100%. Для небольших таблиц с предполагаемой низкой кратностью (число строк) автоматическое создание статистики будет активировано с процентом выборки 100 %. Это в основном означает, что выполняется полное сканирование, и автоматические статистические данные создаются даже для CSV-файла с использованием парсера версии 1.0.

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

Создание статистики вручную

Бессерверный пул SQL позволяет создавать статистику вручную. Если вы используете средство синтаксического анализа версии 1.0 с CSV, вам, вероятно, придется вручную создать статистику, так как эта версия синтаксического анализа не поддерживает выборку. Автоматическое создание статистики в случае синтаксического анализа версии 1.0 не произойдет, если процент выборки не равен 100%.

В следующих примерах приведены инструкции по созданию статистики вручную.

Обновите статистику

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

Бессерверный пул SQL автоматически создает статистику для столбцов OPENROWSET при значительном изменении данных. Каждый раз, когда статистика создается автоматически, также сохраняется текущее состояние набора данных: пути к файлам, их размер и дата последнего изменения.

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

Статы вручную никогда не объявляются устаревшими.

Примечание.

Для автоматического восстановления статистики используется выборка, и в большинстве случаев процент выборки будет меньше 100%. Этот поток одинаков для каждого формата файла. Помните, что при чтении CSV-файла с анализатором версии 1.0 выборка не поддерживается, а автоматическое восстановление статистики не будет происходить с процентом выборки менее 100%. В этом случае необходимо вручную удалить и воссоздать статистику. Ознакомьтесь с приведенными ниже примерами о том, как удалить и создать статистику. Для небольших таблиц с предполагаемой низкой кардинальностью (число строк) автоматическое повторное создание статистики будет запущено с процентом выборки 100%. Это в основном означает, что выполняется полное сканирование, и автоматические статистические данные создаются даже для CSV-файла с использованием парсера версии 1.0.

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

Примечание.

Реализация управления статистикой

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

Ниже приведены основные принципы обновления статистики.

Убедитесь, что в наборе данных обновлен хотя бы один объект статистики. Это обновление сведений о размере (подсчете строк и подсчете страниц) в рамках обновления статистики.
Сосредоточьтесь на столбцах, участвующих в предложениях WHERE, JOIN, GROUP BY, ORDER BY и DISTINCT.
Чаще обновляйте столбцы "по возрастанию ключа", такие как даты транзакций, так как эти значения не будут включены в гистограмму статистики.
Обновлять статические столбцы распределения реже.

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

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

В следующих примерах показано, как использовать различные параметры для создания статистики в бессерверных пулах SQL Azure Synapse. Параметры, используемые для каждого столбца, зависят от характеристик данных и способа использования столбца в запросах. Дополнительные сведения о хранимых процедурах, используемых в этих примерах, см. в sys.sp_create_openrowset_statistics и sys.sp_drop_openrowset_statistics, которые применяются только к бессерверным пулам SQL.

Примечание.

Статистику с одним столбцом можно создать только на данный момент.

Для выполнения sp_create_openrowset_statistics и sp_drop_openrowset_statistics требуются следующие разрешения: "Администрирование массовых операций" или "Администрирование массовых операций базы данных".

Для создания статистики используется следующая хранимая процедура:

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

Аргументы: [ @stmt = ] N'statement_text' — указывает инструкцию Transact-SQL, которая вернет значения столбцов, которые будут использоваться для статистики. С помощью TABLESAMPLE можно указать примеры используемых данных. Если TABLESAMPLE не указан, будет использоваться FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Примечание.

Выборка CSV не работает, если используется средство синтаксического анализа версии 1.0, для CSV-файла поддерживается только FULLSCAN версии 1.0.

Создание одностолбцовой статистики путем проверки всех строк

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

По умолчанию, если при создании статистики вручную не указано, бессерверный пул SQL использует 100% данных, предоставленных в наборе данных при создании статистики.

Например, чтобы создать статистику с параметрами по умолчанию (FULLSCAN) для столбца популяции набора данных на основе файла us_population.csv:


EXEC sys.sp_create_openrowset_statistics N'SELECT 
    population
FROM OPENROWSET(
    BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE)
AS [r]'

Создание одностолбцовой статистики с указанием размера выборки

Вы можете указать размер выборки в процентах:

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Примеры: обновление статистики

Чтобы обновить статистику, необходимо удалить и создать статистику. Дополнительные сведения см. в sys.sp_create_openrowset_statistics и sys.sp_drop_openrowset_statistics.

Хранимая sys.sp_drop_openrowset_statistics процедура используется для удаления статистики:

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

Примечание.

Аргументы: [ = ] @stmt N'statement_text' — указывает тот же оператор Transact-SQL, используемый при создании статистики.

Чтобы обновить статистику для столбца года в наборе данных, основанном на population.csv файле, необходимо удалить и создать статистику:

EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'
GO

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Примеры. Создание статистики для внешнего столбца таблицы

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

Примечание.

Статистику с одним столбцом можно создать только на данный момент.

Чтобы создать одностолбцовую статистику, достаточно указать имя объекта статистики и имя столбца.

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

Аргументы: external_table указывает внешнюю таблицу, для которой следует создать статистику.

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

Параметр "SAMPLE number PERCENT" указывает приблизительный процент или количество строк в таблице или индексированном представлении, которые оптимизатор запросов использует при создании статистики. Число может быть от 0 до 100.

Нельзя использовать SAMPLE с опцией FULLSCAN.

Примечание.

Создание одностолбцовой статистики путем проверки всех строк

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Создание одностолбцовой статистики с указанием размера выборки

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

Примеры: обновление статистики

Чтобы обновить статистику, необходимо удалить и создать статистику. Сначала удалите статистику:

DROP STATISTICS census_external_table.sState

И создайте статистику:

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Метаданные статистики

Примечание.

Метаданные статистики доступны только для внешних столбцов таблицы. Метаданные статистики недоступны для столбцов OPENROWSET.

Просмотры каталога для статистики

Вот какие системные представления показывают информацию о статистике:

Вид каталога	Описание
sys.columns	Одна строка для каждого столбца.
sys.objects	Одна строка для каждого объекта в базе данных.
sys.schemas	Одна строка для каждой схемы в базе данных.
sys.stats	Одна строка для каждого объекта статистики.
sys.stats_columns	Одна строка для каждого столбца в объекте статистики. Ссылки обратно к sys.columns.
sys.tables	Одна строка для каждой таблицы (включает внешние таблицы).
sys.table_types	Одна строка для каждого типа данных.

Системные функции для статистики

Эти системные функции полезны для работы со статистикой:

Системная функция	Описание
STATS_DATE	Дата последнего обновления объекта статистики.

Сочетание столбцов и функций статистики в одном представлении

Это представление объединяет столбцы, относящиеся к статистике и результатам функции STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   st.[user_created] = 1
;

Дальнейшие действия

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

Дополнительные сведения о повышении производительности запросов для бессерверного пула SQL см. в рекомендациях по бессерверному пулу SQL.

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

Статистика в Synapse SQL

Статистика в выделенном пуле SQL

Для чего используется статистика?

Автоматическое создание статистики

Обновите статистику

Определение последнего обновления статистики

Реализация управления статистикой

Примеры: создание статистики

Создание одностолбцовой статистики с параметрами по умолчанию

Создание одностолбцовой статистики путем проверки всех строк

Создание одностолбцовой статистики с указанием размера выборки

Создание одностолбцовой статистики только для некоторых строк

Создание одностолбцовой статистики со всеми параметрами

Создание многостолбцовой статистики

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

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

Примеры: обновление статистики

Обновление одного указанного объекта статистики

Обновите всю статистику в таблице

Метаданные статистики

Просмотры каталога для статистики

Системные функции для статистики

Сочетание столбцов и функций статистики в одном представлении

Примеры DBCC SHOW_STATISTICS()

Отображение заголовка, плотности и гистограммы

Отображение одной или нескольких частей DBCC SHOW_STATISTICS()

Отличия DBCC SHOW_STATISTICS()

Статистика в бессерверном пуле SQL

Для чего используется статистика?

Автоматическое создание статистики

Создание статистики вручную

Обновите статистику

Реализация управления статистикой

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

Создание одностолбцовой статистики путем проверки всех строк

Создание одностолбцовой статистики с указанием размера выборки

Примеры: обновление статистики

Примеры. Создание статистики для внешнего столбца таблицы

Создание одностолбцовой статистики путем проверки всех строк

Создание одностолбцовой статистики с указанием размера выборки

Примеры: обновление статистики

Метаданные статистики

Просмотры каталога для статистики

Системные функции для статистики

Сочетание столбцов и функций статистики в одном представлении

Дальнейшие действия

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

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