Руководство по проектированию использования реплицированных таблиц в пуле SQL Synapse

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

Необходимые условия

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

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

• Насколько велика таблица?
• Как часто обновляется таблица?
• Есть ли таблицы фактов и измерений в пуле SQL?

Что такое реплицированная таблица?

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

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

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

Рассмотрите использование реплицированной таблицы, когда:

• Размер таблицы на диске меньше 2 ГБ независимо от количества строк. Чтобы найти размер таблицы, можно использовать команду DBCC PDW_SHOWSPACEUSED: DBCC PDW_SHOWSPACEUSED('ReplTableCandidate')
• Таблица используется в соединениях, которые в противном случае требуют перемещения данных. При присоединении таблиц, не распределенных по одному и тому же столбцу, например, хэш-распределенной таблицы к таблице с циклическим распределением, для выполнения запроса требуется перемещение данных. Если одна из таблиц небольшая, рассмотрите реплицированную таблицу. В большинстве случаев мы рекомендуем использовать реплицированные таблицы вместо таблиц с равномерным распределением. Чтобы просмотреть операции перемещения данных в планах запросов, используйте sys.dm_pdw_request_steps. BroadcastMoveOperation — это типичная операция перемещения данных, которую можно устранить с помощью реплицированной таблицы.

Реплицированные таблицы могут не обеспечить лучшую производительность запросов, если:

• В таблице часто выполняются операции вставки, обновления и удаления. Для операций языка обработки данных (DML) требуется перестроение реплицированной таблицы. Частая реконструкция может привести к снижению производительности.
• Пул SQL часто масштабируется. Масштабирование пула SQL изменяет количество вычислительных узлов, которое приводит к перестроению реплицированной таблицы.
• В таблице имеется большое количество столбцов, но операции с данными обычно обращаются только к небольшому количеству столбцов. В этом сценарии вместо репликации всей таблицы она может оказаться более эффективной для распространения таблицы, а затем создать индекс для часто доступных столбцов. Если запрос требует перемещения данных, пул SQL перемещает данные только для запрошенных столбцов.

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

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

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

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

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

SELECT EnglishProductName
FROM DimProduct
WHERE EnglishDescription LIKE '%frame%comfortable%';

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

Если у вас уже есть таблицы round-robin, рекомендуется преобразовать их в реплицированные таблицы, если они соответствуют критериям, описанным в этой статье. Реплицированные таблицы повышают производительность по сравнению с таблицами с циклическим перебором, так как они устраняют необходимость перемещения данных. В таблице с циклическим распределением для соединений всегда требуется перемещение данных.

В этом примере CTAS используется для изменения DimSalesTerritory таблицы на реплицированную таблицу. Этот пример работает независимо от того, распределяется ли DimSalesTerritory по хэш-принципу или по принципу round-robin.

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]
WITH
  (
    HEAP,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE')

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Пример производительности запроса с использованием метода round-robin и репликации

Реплицированная таблица не требует перемещения данных для соединений, так как вся таблица уже присутствует на каждом вычислительном узле. Если таблицы измерений распределены способом round-robin, соединение копирует таблицу измерений целиком на каждый вычислительный узел. Для перемещения данных план запроса содержит операцию с именем BroadcastMoveOperation. Этот тип операции перемещения данных замедляет производительность запросов и устраняется с помощью реплицированных таблиц. Чтобы просмотреть шаги плана запросов, используйте представление системного каталога sys.dm_pdw_request_steps .

Например, в следующем запросе к схеме AdventureWorks, таблица FactInternetSales распределена по хэш-коду. Таблицы DimDate и DimSalesTerritory являются меньшими таблицами измерений. Этот запрос возвращает общий объем продаж в Северной Америке за финансовый год 2004:

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

Мы повторно создали DimDate и DimSalesTerritory как циклические таблицы. В результате запрос показал следующий план запроса, который имеет несколько операций перемещения широковещательного канала:

Мы повторно создали DimDate и DimSalesTerritory как реплицированные таблицы и снова выполнили запрос. Результирующий план запроса гораздо короче и не содержит операций широковещательной передачи данных.

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

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

Асинхронные перестроения активируются первым запросом к реплицированной таблице после:

• Данные загружаются или изменяются
• Экземпляр Synapse SQL масштабируется до другого уровня.
• Определение таблицы обновляется

Перестроения не требуются после:

• Приостановка операции
• Возобновить работу

Перестроение не происходит сразу после изменения данных. Вместо этого перестроение активируется при первом выборе запроса из таблицы. Запрос, который активировал перестроение, немедленно считывается из главной версии таблицы, а данные асинхронно копируются на каждый вычислительный узел. До завершения копирования данных последующие запросы будут продолжать использовать главную версию таблицы. Если какое-либо действие происходит с реплицированной таблицей, которое заставляет другое перестроение, копирование данных становится недействительным, а следующий оператор SELECT активирует копирование данных снова.

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

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

Загрузка пакетных данных

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

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

• Загрузка из источника 1.
• Оператор Select активирует перестроение 1.
• Загрузка из источника 2.
• Команда Select запускает перестроение версии 2.
• Загрузка из источника 3.
• Оператор SELECT активирует третье перестроение.
• Загрузка из источника 4.
• Оператор Select активирует перестроение 4.

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

• Загрузка из источника 1.
• Загрузка из источника 2.
• Загрузка из источника 3.
• Загрузка из источника 4.
• Оператор SELECT инициирует перестроение.

Восстановление реплицированной таблицы после пакетной загрузки

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

Операция "Сборка реплицированного кэша таблиц" может выполнять до двух операций одновременно. Например, если вы пытаетесь перестроить кэш для пяти таблиц, система будет использовать статический rc20 (который не может быть изменен) для одновременной сборки двух таблиц за раз. Поэтому рекомендуется избегать использования больших реплицированных таблиц, превышающих 2 ГБ, так как это может замедлить перестроение кэша на узлах и увеличить общее время.

Этот запрос использует sys.pdw_replicated_table_cache_state DMV для перечисления реплицированных таблиц, которые были изменены, но не перестроены.

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] +']'
FROM sys.tables t 
JOIN sys.pdw_replicated_table_cache_state c 
  ON c.object_id = t.object_id
JOIN sys.pdw_table_distribution_properties p
  ON p.object_id = t.object_id
WHERE c.[state] = 'NotReady'
AND p.[distribution_policy_desc] = 'REPLICATE'

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

SELECT TOP 1 * FROM [ReplicatedTable]

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

Для мониторинга процесса перестроения можно использовать sys.dm_pdw_exec_requests, где записи будут начинаться с 'BuildReplicatedTableCache'. Рассмотрим пример.

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'

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

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

• CREATE TABLE (пул SQL)
• CREATE TABLE AS SELECT (пул SQL)

Общие сведения о распределенных таблицах см. в разделе "Распределенные таблицы".