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

Рекомендации по использованию выделенных пулов SQL в Azure Synapse Analytics

  • Статья

Эта статья содержит набор рекомендаций, которые помогут добиться оптимальной производительности для выделенных пулов SQL в Azure Synapse Analytics. При работе с бессерверным пулом SQL см. конкретные рекомендации в статье Рекомендации по работе с бессерверными пулами SQL. Ниже вы найдете основные рекомендации и важные аспекты, которые помогут вам при создании решения. В каждом разделе содержится краткое описание определенных понятий и ссылки на статьи с более подробными сведениями.

Загрузка выделенных пулов SQL

Инструкции по загрузке выделенных пулов SQL см. в статье Руководство по загрузке данных.

Снизьте затраты с помощью приостановки и масштабирования

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

Обеспечение статистики

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

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

Чтобы сократить время, затрачиваемое на обслуживание статистики, необходимо указать, какие столбцы содержат данные статистики или требуют наиболее частого обновления. Например, можно ежедневно обновлять столбцы дат, в которые добавляются новые значения. Обратите внимание на ведение статистики для столбцов, участвующих в объединениях, используемых в предложении WHERE и входящих в GROUP BY.

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

Настройка производительности запросов

Группировка инструкций INSERT в пакеты

В зависимости от ваших потребностей, однократная загрузка в небольшую таблицу с помощью инструкции INSERT, например INSERT INTO MyLookup VALUES (1, 'Type 1'), может быть лучшим решением, но для загрузки тысяч или миллионов строк на протяжении дня отдельные инструкции INSERT, скорее всего, не будут оптимальным выбором.

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

Быстрая загрузка и экспорт данных с помощью PolyBase

Выделенный пул SQL поддерживает загрузку и экспорт данных с помощью нескольких инструментов, включая Фабрику данных Azure, PolyBase и BCP. При работе с данными небольшого объема, что не требует высокой производительности, можно использовать любой инструмент.

Примечание.

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

Загрузку данных с помощью PolyBase можно выполнить, используя команды CTAS или INSERT INTO. Функция CTAS позволяет свести к минимуму нагрузку ведения журнала транзакций и быстрее всего выполнить загрузку данных. Фабрика данных Azure также поддерживает загрузки PolyBase и способна достичь производительности, сопоставимой с CTAS. PolyBase поддерживает различные форматы файлов, включая формат GZIP.

Чтобы максимально повысить пропускную способность при использовании текстовых файлов в формате GZIP, разбейте файлы на 60 или больше частей. Это позволит достичь максимальной степени параллелизма для загрузки. Кроме того, для повышения общей пропускной способности можно загружать данные одновременно. Дополнительные сведения на темы, относящиеся к этому разделу, приведены в следующих статьях:

Загрузка внешних таблиц и отправка запросов к ним

Polybase не подходит для запросов. Сейчас таблицы Polybase для выделенных пулов SQL поддерживают только файлы больших двоичных объектов Azure и хранилище Azure Data Lake. Эти файлы не обслуживаются какими-либо вычислительными ресурсами. В результате выделенные пулы SQL не могут разгрузить эту работу и должны считывать весь файл, загружая его в tempdb, чтобы можно было прочитать данные.

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

Хэш-распределение больших таблиц

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

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

Например, если у вас есть таблица заказов с распределением по идентификатору order_id и таблица транзакций также с распределением по идентификатору order_id, то, когда вы объединяете вашу таблицу заказов с таблицей транзакций по идентификатору order_id, эта выборка данных становится сквозным запросом. Затем операции перемещения данных удаляются. Меньше шагов — быстрее запрос. Меньшее перемещение данных также ведет к более быстрому выполнению запросов.

Совет

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

Ссылки на статью, приведенные ниже, содержат дополнительные сведения о повышении производительности с помощью выбора столбца распространения. Кроме того, вы найдете сведения о том, как определить распределенную таблицу в предложении WITH инструкции CREATE TABLE:

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

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

Слишком большое количество секций снижает эффективность кластеризованных индексов columnstore, если в секции содержится менее 1 миллиона строк. Выделенные пулы SQL автоматически секционируют данные на 60 баз данных. Таким образом, если создать таблицу с 100 секциями, результатом будет 6000 секций. Рабочие нагрузки отличаются друг от друга, поэтому рекомендуется поэкспериментировать с секционированием, чтобы выбрать наиболее подходящее количество секций для вашей рабочей нагрузки.

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

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

Уменьшение размера транзакций

Инструкции INSERT, UPDATE и DELETE выполняются в транзакции. В случае сбоя их нужно откатить. Чтобы уменьшить вероятность длительного отката, минимизируйте размеры транзакций по возможности. Это можно сделать, разделив инструкции INSERT, UPDATE и DELETE на части. Например, если вы ожидаете, что операция INSERT будет выполняться 1 час, ее можно разделить на четыре части. Таким образом, время каждого выполнения сократится до 15 минут.

Совет

Используйте специальные варианты минимального ведения журнала, такие как CTAS, TRUNCATE, DROP TABLE или INSERT в пустые таблицы, чтобы уменьшить риск отката.

Еще один способ устранить откаты — использовать операции, включающие только метаданные, например, переключение разделов для управления данными. Например, вместо выполнения инструкции DELETE для удаления всех строк в таблице, для которых в параметре order_date указан октябрь 2001 года, данные можно секционировать по месяцам. Затем можно заменить раздел с данными на пустой раздел из другой таблицы (см. примеры ALTER TABLE).

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

Дополнительные сведения о содержимом, связанном с этим разделом, приведены в следующих статьях:

Уменьшение размера результатов запроса

Уменьшение размера результатов запроса помогает избежать проблем на стороне клиента, вызванных большими результатами запросов. Запрос можно изменить, чтобы уменьшить число возвращаемых строк. Некоторые инструменты создания запросов позволяют добавлять в каждый запрос синтаксис "топ N". Можно также использовать инструкцию CETAS, чтобы записать результат запроса во временную таблицу, а затем использовать экспорт PolyBase для обработки на более низком уровне.

Использование минимального размера столбца

При определении DDL рекомендуется использовать поддерживаемый тип данных с наименьшим размером. Это позволит повысить производительность запросов. Эта рекомендация особенно важна для столбцов CHAR и VARCHAR. Если самое длинное значение в столбце состоит из 25 знаков, столбец необходимо определить как VARCHAR(25). Избегайте задавать все символьные столбцы с большой длиной по умолчанию. Кроме того, определяйте столбцы как VARCHAR, если этого достаточно, вместо использования NVARCHAR.

Дополнительные сведения о основных понятиях, относящихся к приведенным выше сведениям, см. в статьях о таблицах, типах данных таблиц и статьях CREATE TABLE .

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

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

Загрузка данных во временную таблицу выполняется гораздо быстрее, чем загрузка таблицы в постоянное хранилище. Временные таблицы начинаются с символа "#" и доступны только создавшему их сеансу. Следовательно, они подходят только для определенных сценариев использования. Пуловые таблицы определены в предложении WITH инструкции CREATE TABLE. При использовании временной таблицы рекомендуется создавать в ней статистику.

Дополнительные сведения см. в статьях Временные таблицы, CREATE TABLE и CREATE TABLE AS SELECT.

Оптимизация таблиц с кластеризованным колонночным хранилищем

Использование кластеризованных индексов columnstore — это один из наиболее эффективных способов хранения данных в выделенном пуле SQL. По умолчанию таблицы в выделенном пуле SQL создаются как кластеризованное columnstore-хранилище. Чтобы обеспечить лучшую производительность запросов к таблицам Columnstore, важно иметь хорошее качество сегментов. Если во время записи строк в таблицы Columnstore возникает нехватка памяти, качество сегмента Columnstore может ухудшиться.

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

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

Таблицы columnstore обычно не отправляют данные в сжатый сегмент columnstore до тех пор, пока в таблице не будет более 1 миллиона строк. Каждая выделенная таблица пула SQL распределяется в 60 разных дистрибутивов. Таким образом, таблицы columnstore не смогут улучшить запрос, если таблица содержит менее 60 миллионов строк.

Совет

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

При секционировании данных в каждой секции должен быть 1 миллион строк, чтобы можно было получить преимущество от кластеризованного индекса columnstore. Если таблица состоит из 100 разделов, то чтобы получить преимущество от использования кластеризованного столбчатого хранилища, она должна состоять как минимум из 6 миллиардов строк (60 распределений 100 разделов 1 миллион строк).

Если таблица не содержит 6 миллиардов строк, у вас есть два основных варианта. Либо сократите количество партиций, либо рассмотрите возможность использования таблицы типа heap. Может также стоить провести эксперимент, чтобы выяснить, можно ли добиться лучшей производительности, используя таблицу кучи с вторичными индексами вместо таблицы columnstore.

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

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

Чтобы выделить память для выполнения запросов, пулы SQL используют группы ресурсов. Изначально для всех пользователей настроен класс ресурсов небольшого размера, предусматривающий 100 МБ памяти для каждого распределения. Всегда существует 60 распределений. Каждому распределению предоставляется минимум 100 МБ. Общий объем памяти, выделяемой для всей системы, составляет 6 000 МБ или чуть меньше 6 ГБ.

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

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

Использование класса ресурсов небольшого размера для увеличения параллелизма

Если вы заметите длительную задержку при выполнении пользовательских запросов, возможно, ваши пользователи обрабатывают большие классы ресурсов. Такая ситуация увеличивает использование слотов одновременного выполнения, что может привести к постановке других запросов в очередь. Чтобы определить, находятся ли пользовательские запросы в очереди, выполните команду SELECT * FROM sys.dm_pdw_waits, чтобы посмотреть, возвращаются ли какие-нибудь строки.

Дополнительные сведения приведены в статьях Классы ресурсов для управления рабочими нагрузками и sys.dm_pdw_waits.

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

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

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

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

Если вам нужна информация, которой нет в этой статье, выполните поиск на странице часто задаваемых вопросов по Azure Synapse на сайте Майкрософт, где можно задать вопросы другим пользователям, и в группе продукта Azure Synapse Analytics.

Мы регулярно просматриваем этот форум и следим за тем, чтобы другие пользователи или наши специалисты ответили на интересующие вас вопросы. Кроме того, вопросы можно задавать на форуме Stack Overflow в разделе, посвященном Azure Synapse Analytics.