Considerações de desempenho no PolyBase para SQL Server

Aplicável a: SQL Server 2016 (13.x) - Windows e versões posteriores SQL Server 2017 (14.x) - Linux e versões posteriores Azure Synapse Analytics

No PolyBase para SQL Server, não há um limite fixo para o número de arquivos ou a quantidade de dados que podem ser consultados. O desempenho da consulta depende da quantidade de dados, do formato dos dados, da maneira como os dados estão organizados e da complexidade das consultas e uniões.

Este artigo aborda importantes tópicos e orientações de desempenho.

Estatísticas

Coletar estatísticas sobre seus dados externos é uma das coisas mais importantes que você pode fazer para a otimização de consultas. Quanto mais a instância souber sobre seus dados, mais rapidamente ele poderá executar consultas. O otimizador de consulta do mecanismo de SQL é um otimizador baseado no custo. Ele compara o custo de vários planos de consulta e, em seguida, escolhe o plano com o menor custo. Na maioria dos casos, ele escolhe o plano que será executado mais rapidamente.

Criação automática de estatísticas

A partir do SQL Server 2022, o Mecanismo de Banco de Dados analisa as consultas de entrada do usuário em busca de estatísticas ausentes. Se faltarem estatísticas, o otimizador de consulta criará automaticamente estatísticas sobre colunas individuais no predicado da consulta ou na condição de junção a fim de melhorar as estimativas da cardinalidade para o plano de consulta. A criação automática de estatísticas é feita de forma síncrona. Portanto, você pode observar um desempenho de consulta ligeiramente degradado se suas colunas estiverem faltando estatísticas. O tempo para criar estatísticas para uma única coluna depende do tamanho do arquivo de destino.

Criar estatísticas manuais de OPENROWSET

As estatísticas de coluna única para o caminho OPENROWSET podem ser criadas usando o procedimento armazenado sys.sp_create_openrowset_statistics, passando a consulta selecionada com uma única coluna como um parâmetro:

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';

Por padrão, a instância usa 100% dos dados fornecidos no DataSet para criar estatísticas. Opcionalmente, você pode especificar o tamanho da amostra como um percentual usando as opções de TABLESAMPLE. Para criar estatísticas de coluna única para várias colunas, execute sys.sp_create_openrowset_statistics para cada uma das colunas. Não é possível criar estatísticas de várias colunas para o caminho de OPENROWSET.

Para atualizar as estatísticas existentes, remova-as primeiro usando o sys.sp_drop_openrowset_statistics procedimento armazenado e, em seguida, recrie-as usando 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 
';

Criar estatísticas manuais de tabela externa

A sintaxe para a criação de estatísticas em tabelas externas é semelhante à usada para tabelas de usuário comuns. Para criar estatísticas em uma coluna, forneça um nome para o objeto de estatísticas e o nome da coluna:

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

As opções de WITH são obrigatórias e, para o tamanho da amostra, as opções permitidas são FULLSCAN e SAMPLE n PERCENT.

• Para criar estatísticas de coluna única para várias colunas, execute CREATE STATISTICS para cada uma das colunas.
• Não há suporte para estatísticas de várias colunas.

Consultar dados particionados

Aplica-se a Instância Gerenciada e Azure Synapse Analytics.

Quando os dados são organizados em pastas ou arquivos (também chamados de partições), use eliminação de partição para consultar apenas pastas e arquivos específicos. A eliminação de partição reduz o número de arquivos e a quantidade de dados que a consulta precisa ler e processar, resultando em melhor desempenho.

Para eliminar partições da execução da consulta, use a função de metadados filepath() na cláusula WHERE da consulta.

Primeiro, crie uma fonte de dados externos:

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

O exemplo a seguir consulta arquivos de dados de Táxis amarelos em NYC relativos somente aos últimos três meses de 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;

Se os dados armazenados não forem particionados, considere particioná-los para aprimorar o desempenho da consulta.

Se você estiver usando tabelas externas, e houver suporte para as funções filepath() e filename(), mas não na cláusula WHERE. Você ainda pode filtrar por filename ou filepath se usá-los em colunas computadas. O exemplo a seguir demonstra este:

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); 

Se os dados armazenados não forem particionados, considere particioná-los para aprimorar o desempenho da consulta.

Enviar por push de computação para Hadoop

Aplicável a: SQL Server 2016 (13.x), SQL Server 2017 (14.x) e SQL Server 2019 (15.x) apenas.

O PolyBase envia alguns cálculos para a fonte externa a fim de otimizar a consulta geral. O otimizador de consulta toma a decisão baseada em custo de enviar por push a computação para o Hadoop, caso isso aprimore o desempenho de consulta. O otimizador de consulta usa as estatísticas nas tabelas externas para tomar a decisão baseada em custo. O envio por push do cálculo cria trabalhos MapReduce e aproveita os recursos computacionais distribuídos do Hadoop. Para obter mais informações, consulte Computações de pushdown no PolyBase.

Escalar recursos de computação

Aplicável a: SQL Server 2016 (13.x), SQL Server 2017 (14.x) e SQL Server 2019 (15.x) apenas.

Para melhorar o desempenho da consulta, é possível usar os grupos de escala horizontal do PolyBasedo SQL Server. Isso permite a transferência de dados em paralelo entre as instâncias do SQL Server e os nós do Hadoop, além de adicionar recursos de computação para operação em dados externos.

Conteúdo relacionado

• Perguntas frequentes no PolyBase
• Virtualização de dados com o PolyBase no SQL Server