Рекомендации по производительности в PolyBase для SQL Server

  • Статья

Область применения: SQL Server 2016 (13.x) — Windows и более поздних версий SQL Server 2017 (14.x) — Linux и более поздних версий Azure Synapse Analytics

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

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

Статистика

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

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

Начиная с SQL Server 2022, ядро СУБД анализирует входящие запросы пользователей для отсутствующих статистических данных. Если статистика отсутствует, оптимизатор запросов автоматически создает статистику по отдельным столбцам в условии предиката запроса или соединения, чтобы улучшить оценки карта inality для плана запроса. Автоматическое создание статистики выполняется синхронно, поэтому при отсутствии статистики может наблюдаться незначительное снижение производительности запросов. Время создания статистики для одного столбца зависит от размера выбранных файлов.

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

Статистику с одним столбцом для пути OPENROWSET можно создать с помощью sys.sp_create_openrowset_statistics хранимой процедуры, передав запрос выбора с одним столбцом в качестве параметра:

EXEC sys.sp_create_openrowset_statistics N' 
SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows';

По умолчанию экземпляр использует все данные, предоставляемые в наборе данных, для создания статистики. При необходимости можно указать размер выборки в процентах с помощью параметров TABLESAMPLE. Чтобы создать статистику с одним столбцом для нескольких столбцов, выполните для sys.sp_create_openrowset_statistics каждого столбца. Невозможно создать статистику с несколькими столбцами для пути OPENROWSET.

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

EXEC sys.sp_drop_openrowset_statistics 
N'SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows 
';

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

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

CREATE STATISTICS sVendor 
ON tbl_TaxiRides (vendorID) 
WITH FULLSCAN, NORECOMPUTE;

Параметры WITH являются обязательными, а для размера выборки допустимыми вариантами являются FULLSCAN и SAMPLE n PERCENT.

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

Запрос по секционированным данным

Данные часто упорядочены в вложенных папках, которые также называются секциями. Вы можете указать экземпляру SQL Server запрашивать только определенные папки и файлы. Это сокращает количество файлов и объем данных, необходимых для чтения и обработки запроса, что приводит к повышению производительности. Этот тип оптимизации запросов называется очисткой секций или ликвидацией секций. Вы можете исключить секции из выполнения запроса с помощью функции filepath() метаданных в WHERE предложении запроса.

Сначала создайте внешний источник данных:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net'
);
GO

Следующий пример запроса считывает файлы данных NYC Yellow Taxi только за последние три месяца 2017 года:

SELECT 
    r.filepath() AS filepath 
    ,r.filepath(1) AS [year] 
    ,r.filepath(2) AS [month] 
    ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
        BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
        DATA_SOURCE = 'NYCTaxiExternalDataSource', 
        FORMAT = 'parquet' 
    ) 
WITH ( 
    vendorID INT 
) AS [r] 
WHERE 
    r.filepath(1) IN ('2017') 
    AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
    r.filepath() 
    ,r.filepath(1) 
    ,r.filepath(2) 
ORDER BY filepath;

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

Если вы используете внешние таблицы, а filename() функции поддерживаются, filepath() но не в предложенииWHERE. Вы по-прежнему можете отфильтровать filename их в вычисляемых столбцах или filepath использовать их. Следующий пример демонстрирует это:

CREATE EXTERNAL TABLE tbl_TaxiRides ( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT, 
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning 
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 
GO 
 
SELECT * 
      FROM tbl_TaxiRides 
WHERE 
      [year]=2017             
      AND [month] in (10,11,12);

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

Отправка вычислений в Hadoop

Применимо только к SQL Server 2016 (13.x), SQL Server 2017 (14.x) и SQL Server 2019 (15.x)

PolyBase отправляет некоторые вычисления во внешний источник, чтобы оптимизировать запрос в целом. Оптимизатор запросов принимает решение принудительно отправить вычисления в Hadoop, если это улучшит производительность запросов. Для принятия такого решения оптимизатор запросов использует статистику из внешних таблиц. При включении вычислений создаются задания MapReduce и применяются распределенные вычислительные ресурсы Hadoop. Дополнительные сведения: Вычисления pushdown в PolyBase.

Масштабирование вычислительных ресурсов

Применимо только к SQL Server 2016 (13.x), SQL Server 2017 (14.x) и SQL Server 2019 (15.x)

Для повышения производительности запросов можно использовать группы горизонтального масштабирования PolyBaseв SQL Server. Это обеспечивает параллельную передачу данных между экземплярами SQL Server и узлами Hadoop, а также добавляет вычислительные ресурсы для работы с внешними данными.

Важно!

Поддержка для групп горизонтального увеличения масштаба Microsoft SQL Server PolyBase будет прекращена. Функции группы горизонтального масштабирования будут удалены из продукта в SQL Server 2022 (16.x). Виртуализация данных PolyBase будет по-прежнему полностью поддерживаться как функция вертикального увеличения масштаба в SQL Server. Дополнительные сведения см. в разделе Параметры больших данных на платформе Microsoft SQL Server.

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