Рекомендации по использованию выделенных пулов 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 приведены в этой статье.

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

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

Наглядно это превосходство можно увидеть при объединении больших таблиц фактов.

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

Совет

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

По приведенным ниже ссылкам на статьи вы можете получите дополнительные сведения о повышении производительности с помощью выбора столбца распределения. Кроме того, вы найдете сведения о том, как определить распределенную таблицу в предложении 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).

Для несекционированных таблиц вместо DELETE можно использовать инструкцию CTAS, которая запишет данные в таблицу. Если на выполнение этих двух инструкций требуется одинаковое время, целесообразно использовать инструкцию 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

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

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

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

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

Совет

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

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

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

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

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

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

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

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

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

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

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

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

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

Следующие шаги

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

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

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