Статистика таблицы для выделенного пула SQL в Azure Synapse Analytics

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

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

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

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

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

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

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

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

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

Можно проверить настроен ли параметр выделенного пула SQL AUTO_CREATE_STATISTICS, запустив следующую команду:

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

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

• SELECT
• INSERT SELECT
• CTAS
• UPDATE
• DELETE
• EXPLAIN, если они содержат операцию соединения или обнаружено наличие предиката.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Запрос 1: Найдите разницу между числом строк из статистики (stats_row_count) и фактическим числом строк (actual_row_count).

Запрос 2: Выясните возраст статистики, проверив время последнего обновления статистики в каждой таблице.

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

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

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

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

Дополнительные сведения можно получить в общем руководстве по статистике.

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

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

Загрузка данных происходит, когда таблицы часто меняют свой размер и (или) распределение значений. Именно при загрузке данных логично реализовать некоторые процессы управления.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Полные справочные сведения см. в статье CREATE STATISTICS (Transact-SQL).

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

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

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

Поскольку существует корреляция между Product_Category и product_sub_category, объект статистики с несколькими столбцами может быть полезен, если доступ к этим столбцам осуществляется одновременно.

Один из способов создать статистику — выполнить команды CREATE STATISTICS после создания таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Полный синтаксис приведен в разделе об обновлении статистики.

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

Существует несколько системных представлений и функций, которые можно использовать для поиска информации о статистике. Например, можно узнать, устарел ли объект статистики, воспользовавшись функцией 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().

Примеры DBCC SHOW_STATISTICS()

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

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

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

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

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

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

Отличия DBCC SHOW_STATISTICS()

В выделенном пуле SQL используется более строгая реализация DBCC SHOW_STATISTICS(), чем в SQL Server:

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

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

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