Ative o modo turbo nas funções de janela

No processo de melhora no desempenho de motores de automóveis uma das opções é acrescentar turbina que force a entrada de mais ar para as câmaras de combustão; é o turbocompressor. A partir do SQL Server 2016 é possível “instalar uma turbina”, melhorando o desempenho das funções de janela.

1. Introdução

Em artigo anterior, Otimização de funções de janela, foi detalhado o método “índice POC” para otimização de códigos SQL que contenham funções de janela. Neste artigo será descrito outro método, que denomino de “modo turbo”. Para melhor aproveitamento do conteúdo deste artigo recomenda-se a leitura prévia dos capítulos 1 e 2 do artigo anteriormente mencionado. Conhecimento básico de análise de planos de execução facilita compreender algumas partes deste artigo; se estiver em dúvidas sobre este assunto sugiro a leitura do artigo O Plano Perfeito.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

2. Modo “turbo”

2.1. Modo de processamento

Até a versão 2008 R2 do SQL Server, inclusive, os operadores do plano de execução processavam uma linha por vez (row mode execution), com as funções internas acionadas no operador aplicadas linha a linha. No artigo O Plano Perfeito é explicado que os operadores se classificam em tipos streaming e blocking. O operador do tipo streaming provê um fluxo contínuo de dados, isto é, à medida que lê dados do(s) operador(es) que o precedem também envia dados para o operador seguinte. Já os operadores do tipo blocking leem todos os dados, os processam e somente após enviam os dados para o operador seguinte. Há também os operadores semi-blocking. Como exemplos, Lazy Spool é um operador do tipo streaming; já o Eager Spool é do tipo blocking.

Com a implementação de índice columnstore no SQL Server 2012, um segundo modo de processamento interno passou a ser utilizado em algumas situações, que é o processamento em lote de linhas (batch mode execution), onde o lote é um conjunto de vetores e onde cada vetor representa uma coluna a ser processada no operador. O tamanho de cada lote é de 64 kB, com até 900 elementos (linhas) por vetor.

Na versão 2012 do SQL Server havia pouquíssimos operadores com a possibilidade de operar nesse novo modo de processamento, sendo que na versão 2014 em outros operadores foi implementada a possibilidade de operar nesse modo. Já na versão 2016 do SQL Server foi introduzido o operador Window Aggregate, que processa cálculos em janela mais rapidamente. A definição desse novo operador é “Computes values of functions across the windows defined by the OVER clause. Input rowset is suitably sorted stream”. Outra alteração foi em relação ao operador Sort, que passou a dispor do modo de processamento em lote.

Nas versões 2016 e 2017 do SQL Server o requisito para que o processamento em lote entre em ação é a existência de ao menos um índice columnstore em uma das tabelas que façam parte da consulta SQL. Como fazer então para tirar vantagem desse novo modo de operação se não houver nenhum índice columnstore nas tabelas? No artigo What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016 é ensinado um artifício que consiste em criar um índice columnstore vazio, ao definir filtro que retorne sempre um resultado falso.

2.2. Caso “Calcular total de cada pedido”

O primeiro caso que vamos analisar no modo turbo é o que calcula o total de cada pedido:

-- código #2.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT ID_Pedido as [N. pedido],
       convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Item as [Item], ID_Produto as Produto, Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by ID_Pedido) as [Valor pedido]
  from dbo.tbPedido
  order by ID_Pedido, ID_Item;

Original. O plano de execução do código #2.1 foi o seguinte:

p041_codigo #2.1 - plano

Índice columnstore vazio. Considerando-se a tabela tbPedido uma forma de gerar o índice columnstore vazio é a seguinte:

-- código #2.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE nonclustered columnstore INDEX I5_tbPedido  
     on dbo.tbPedido (ID_Pedido)
     where ID_Pedido = 1 and ID_Pedido = 2;

É um índice non clustered que existe mas ao mesmo tempo não existe pois não ocupa espaço físico!

Teste 1. Após gerar o índice columnstore I5_tbPedido vazio foi rodado novamente o código #2.1 e eis como ficou o plano de execução:

p041_codigo #2.1 pos #4.1 - plano

                     Na figura acima os operadores que estão delimitados pela linha amarela tiveram processamento em lote. Essa informação pode ser obtida ao analisar o valor da propriedade Actual Execution Mode, conforme se observa nas propriedades do operador Sort:p041_codigo #2.1 pos #4.1 - plano - sort

Observe também a presença da propriedade Actual Number of Batches, que informa o número de lotes que foram processados. Se dividirmos o número de linhas (121.317) pelo número de lotes (135) obtemos 899, cujo valor é coerente com o informado anteriormente: “O tamanho de cada lote é de 64 kB, com até 900 elementos (linhas) por vetor”.

Teste 2. Uma forma de eliminar o operador Sort que aparece no plano de execução de “Teste 1” é gerar o seguinte índice:

-- código #2.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE unique nonclustered INDEX I2_tbPedido
     on dbo.tbPedido (ID_Pedido, ID_Item)
     include (Valor_Item, Data_Pedido,
              ID_Produto, Qtd, [Preço_Unitário]);

Ao gerar índice de cobertura pelas colunas ID_Pedido e ID_Item, que são as utilizadas no operador Sort, e a seguir rodar novamente o código #2.1 obtemos o seguinte plano de execução:

p041_codigo #2.1 pos #4.1 pos #3.1 - plano

Temos um plano de execução mais enxuto, sem a presença do operador Sort e efetuando a leitura no índice I2_tbPedido, que é índice de cobertura.

Comparação. As estatísticas de I/O e de processamento das 3 situações foram coletadas e reunidas em um único quadro, abaixo:

p041_figura 4.2.1

Na comparação entre as colunas “original” e “teste 1” percebe-se redução drástica no custo estimado e também a não utilização do banco de dados tempDB para armazenamento temporário. A diferença de tempo decorrido não foi significativa, provavelmente por causa da reduzida quantidade de linhas da tabela tbPedido (cerca de 120 mil linhas).

Na comparação entre as colunas “original” e “teste 2” percebe-se redução maior no custo estimado e também no tempo de CPU e a não utilização do banco de dados tempDB para armazenamento temporário.

2.3. Caso “Relatório completo”

E qual foi o impacto do modo turbo na emissão completa do relatório, descrita no caso “Relatório completo” do artigo Otimização de funções de janela? Segue o código SQL do item 2.4 daquele artigo:

-- código #2.4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

--
DROP if exists INDEX I5_tbPedido on dbo.tbPedido;
DROP if exists INDEX I2_tbPedido on dbo.tbPedido;

--
SELECT convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Pedido as [N. pedido], ID_Item as [Item],
       ID_Produto as Produto, Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by ID_Pedido
                              order by ID_Item
                              rows unbounded preceding) as [Soma acumulada item do pedido],
       sum (Valor_Item) over (partition by ID_Pedido) as [Valor pedido],
       sum (Valor_Item) over (partition by Data_Pedido) as [Valor total dia]
  from dbo.tbPedido
  order by Data_Pedido, ID_Pedido, ID_Item;

Original. O plano de execução do código #2.4 foi o seguinte:

p041_codigo #2.4 - plano

Teste 1. Após gerar o índice columnstore I5_tbPedido vazio (código #2.2) foi rodado novamente o código #2.4 e eis como ficou o plano de execução:

p041_codigo #2.4 pos #4.1 - plano

Ficou bem mais simples, embora isso não signifique que seja mais eficiente. Observe a presença de dois operadores de ordenação.

Teste 2. Ao gerar índices adicionais teremos algum ganho no modo turbo? Para gerar esse índice rodamos o seguinte código SQL

-- código #2.5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- totalizações 1 e 2
CREATE unique nonclustered INDEX I3_tbPedido
     on dbo.tb Pedido (ID_Pedido, ID_Item)
     include (Valor_Item, Data_Pedido,
              ID_Produto, Qtd, [Preço_Unitário]);

-- totalização 3
CREATE unique nonclustered INDEX I4_tbPedido
     on dbo.tbPedido (Data_Pedido, ID_Pedido, ID_Item)
     include (Valor_Item, ID_Produto, Qtd, [Preço_Unitário]);

e a seguir novamente o código #2.4, obtendo-se então o seguinte plano de execução:

p041_codigo #2.4 pos #4.1 pos #3.5 - plano

Um dos operadores Sort foi eliminado pois a leitura passou a ser pelo índice I3_tbPedido, que fornece as linhas já ordenadas pelas colunas ID_Pedido e ID_Item. Quanto ao índice I4_tbPedido, este foi ignorado. Ou seja, ele não é necessário para este contexto.

Comparação. As estatísticas de I/O e de processamento das 3 situações foram coletadas e reunidas em um único quadro, abaixo:

p041_figura 4.3.1

Ao comparar os valores das colunas “original” com “teste 1” percebe-se que o modo turbo reduziu em muito o tempo de utilização de processador. Já na comparação entre os testes 1 e 2 não se percebe muita diferença. Ou seja, no contexto dos testes não foram necessários os índices I3_tbPedido e I4_tbPedido para agilizar o processamento da consulta; bastou o índice columnstore vazio.

3. Finalizando…

3.1. Observações

Neste artigo foi descrita uma das técnicas para otimização de funções de janelas; se há vantagem ou não na utilização delas depende do contexto, mas sempre é bom ter trunfo na manga para utilizar quando possível.

Cabe repetir que os testes foram efetuados com o banco de dados configurado com maxdop 1 para gerar planos de execução sem paralelismo, o que facilita a apresentação e explicação dos mesmos. Entretanto, em uma situação de otimização deve-se avaliar vários contextos.

Ao longo do artigo foram apresentados vários planos de execução para demonstrar o impacto das opções apresentadas. Cada plano de execução possui uma riqueza de detalhes quando se consulta as propriedades de cada um dos operadores. Infelizmente não foi possível descrever tudo o que foi observado durante os testes, pois tornaria este artigo longo.

3.2. Material de estudos

3.2.1. Documentação

3.2.2. Artigos e vídeos

3.2.3. Livros


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Otimização de funções de janela

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s