Considerações de desempenho no PolyBase para SQL Server

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores no Windows SQL Server 2017 (14.x) e versões posteriores no Linux Azure Synapse Analytics

No PolyBase para SQL Server, não há limite rígido 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 forma como os dados são organizados e da complexidade das consultas e associações.

Este artigo aborda tópicos e orientações importantes sobre 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 rápido ela poderá executar consultas. O otimizador de consulta do mecanismo SQL é um otimizador baseado em 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, escolhe o plano que executa mais rápido.

Criação automática de estatísticas

A partir do SQL Server 2022, o Mecanismo de Banco de Dados analisa consultas de usuários de entrada em busca de estatísticas ausentes. Se as estatísticas estiverem ausentes, o otimizador de consulta criará automaticamente estatísticas em colunas individuais no predicado de consulta ou na condição de junção para melhorar as estimativas de cardinalidade para o plano de consulta. A criação automática de estatísticas é feita de forma síncrona para que você possa 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 dos arquivos de destino.

Criar estatísticas manuais do OPENROWSET

Podem ser criadas estatísticas de coluna única para o caminho OPENROWSET ao usar o sys.sp_create_openrowset_statistics procedimento armazenado, passando a consulta select com uma única coluna como 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 conjunto de dados para criar estatísticas. Opcionalmente, você pode especificar o tamanho da amostra como uma porcentagem usando as opções 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 OPENROWSET.

Para atualizar as estatísticas existentes, solte-as primeiro usando o sys.sp_drop_openrowset_statistics procedimento armazenado e, em seguida, recrie-as usando o 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 de tabelas externas manualmente

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

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

As WITH opções 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 com várias colunas.

Consultar dados particionados

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

Quando os dados são organizados em pastas ou arquivos (também chamados de partições), use a eliminação de partições para consultar apenas pastas e arquivos específicos. A eliminação de partições 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 filepath() de metadados na WHERE cláusula da consulta.

Primeiro, crie uma fonte de dados externa:

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

A consulta de exemplo a seguir lê os arquivos de dados do NYC Yellow Taxi somente para os ú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 estiverem particionados, considere particioná-los para melhorar o desempenho da consulta.

Se estiver a usar tabelas externas, as funções filepath() e filename() são suportadas, mas não na cláusula WHERE. Você ainda pode filtrar por filename ou filepath se os usar em colunas calculadas. O exemplo a seguir demonstra isso:

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 estiverem particionados, considere particioná-los para melhorar o desempenho da consulta.

Enviar processamento para o Hadoop

Aplica-se 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 para otimizar a consulta geral. O otimizador de consulta toma uma decisão baseada em custos para enviar a computação para o Hadoop, se isso melhorar o desempenho da consulta. O otimizador de consulta usa estatísticas em tabelas externas para tomar a decisão baseada em custos. O impulso do processamento cria tarefas MapReduce e aproveita os recursos computacionais distribuídos do Hadoop. Para obter mais informações, consulte Cálculos em Pushdown no PolyBase.

Dimensionar recursos de computação

Aplica-se a SQL Server 2016 (13.x), SQL Server 2017 (14.x) e SQL Server 2019 (15.x) apenas

Para melhorar o desempenho da consulta, você pode usar grupos de expansão do SQL Server PolyBase. Isso permite a transferência paralela de dados entre instâncias do SQL Server e nós Hadoop e adiciona recursos de computação para operar nos dados externos.

Conteúdo relacionado

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