Blog – Fabiano Neves Amorim

Nonclustered Index Spool

Continuando a falar sobre os operadores de Spool, o próximo da lista é o Nonclustered Index Spool.

Para melhor entendimento do assunto, caso não tenha lido os outros posts onde falei sobre Spool, recomendo que leia.

Operador do dia – Spool

Operador do dia – Eager Spool e “Halloween Problem”

Operador do Dia – Lazy Spool

Assim como os outros tipos de Spool, o Index Spool pode ser utilizado como Lazy e Eager. Ele é utilizado para agilizar na leitura de uma tabela que não está indexada. Quando o SQL precisa ler um valor que não esta indexado ele pode optar por criar um “índice temporário” utilizando o Spool.

Isso pode agilizar e muito as suas consultas, vamos ver um exemplo prático deste uso.

Para ilustrar o uso do Index Spool criei uma tabela chamada Pedidos. Segue o script para criação e carga dos dados:

IF OBJECT_ID(‘Pedido’) IS NOT NULL

  DROP TABLE Pedido

GO

CREATE TABLE Pedido (ID         Int IDENTITY(1,1) PRIMARY KEY,

                     Cliente    Int NOT NULL,

                     Vendedor   VarChar(30) NOT NULL,

                     Quantidade SmallInt NOT NULL,

                     Valor      Numeric(18,2) NOT NULL,

                     Data       DateTime NOT NULL)

 

DECLARE @I SmallInt

SET @I = 0

 

WHILE @I < 50

BEGIN

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Fabiano’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

 

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Amorim’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Coragem’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

  SET @I = @I + 1 

END

SET @I = 0

WHILE @I < 2

BEGIN

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT Cliente, Vendedor, Quantidade, Valor, Data

    FROM Pedido

  SET @I = @I + 1 

END

GO

Exemplo dos dados da tabela:

Para simular o uso do Index Spool, criei uma consulta que retorna todos os pedidos que tiveram venda maior que a média de venda,

comparado a todas as vendas anteriores a data de venda atual.

SELECT *

  FROM Pedido Ped1

 WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor)

                       FROM Pedido AS Ped2

                      WHERE Ped2.Data < Ped1.Data)

Antes de ver o plano vamos entender melhor a consulta.

A SubConsulta retorna a média de venda (AVG(Ped2.Valor)) em uma determinada data da tabela do from(Ped1.Cliente), depois a consulta principal, pega esta média, e retorna as vendas que tiveram o valor maior que ela.

O plano de execução é o seguinte:

 

SELECT *    FROM Pedido Ped1   WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor)                         FROM Pedido AS Ped2                        WHERE Ped2.Data < Ped1.Data)

  |–Filter(WHERE:([tempdb].[dbo].[Pedido].[Valor] as [Ped1].[Valor]>[Expr1004]))

       |–Nested Loops(Inner Join, OUTER REFERENCES:([Ped1].[Data]))

            |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Pedido].[PK__Pedido__3214EC2753C82368] AS [Ped1]))

            |–Index Spool(SEEK:([Ped1].[Data]=[tempdb].[dbo].[Pedido].[Data] as [Ped1].[Data]))

                 |–Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012]/CONVERT_IMPLICIT(numeric(19,0),[Expr1011],0) END))

                      |–Stream Aggregate(DEFINE:([Expr1011]=Count(*), [Expr1012]=SUM([tempdb].[dbo].[Pedido].[Valor] as [Ped2].[Valor])))

                           |–Index Spool(SEEK:([Ped2].[Data] < [tempdb].[dbo].[Pedido].[Data] as [Ped1].[Data]))

                                |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Pedido].[PK__Pedido__3214EC2753C82368] AS [Ped2]))

Este plano é bem interessante, e podemos observar que o SQL optou por usar 2 Index Spool, um como Eager e outro como Lazy. Vemos também que depois do Nested Loops, o SQL utiliza o operador de Filter para selecionar apenas as linhas que satisfaçam a condição no where(WHERE Ped1.Valor > …).

Vamos iniciar a analise mais profunda do plano e, entender alguns conceitos em relação a leitura e calculo dos dados pelos operadores.

O operador de Clustered index Scan, lê os dados da tabela Pedidos e retorna as colunas Valor e Data para o Operador de Eager Index Spool. Com as colunas Data e Valor ordenadas no índice, o SQL utiliza o Index Spool para selecionar todas as linhas onde a Data seja menor que a data da tabela Ped1. Durante a primeira leitura dos dados o Eager Index lê todas as linhas da tabela e grava no índice.

Vemos também que o SQL optou por utilizar o operador de Nested Loops, esta operação faz uma leitura completa da tabela do from, e para cada linha lida ele vai na tabela do join procurar pelo seu valor correspondente. Após a conclusão do Nested Loops, o SQL retorna todas as colunas da tabela Pedidos, mais o valor da média, que é representado pelo valor “Expr1004”. Este valor é o resultado do calculo efetuado pelo Compute Scalar. Isso pode ser confirmado analisando o hint output dos operadores de Nested Loops e Compute Scalar.

Temos então a seguinte regra: Para cada linha do from(Ped1) calcula a média sendo que Ped2.Data. tem que ser menor que Ped1.Data.

Para calcular esta média, o SQL utilizou o Stream Aggregate mais o Compute Scalar.

Dada esta introdução, chegamos onde eu queria.

Quando o SQL Server precisa ler um valor que ele sabe que repete várias vezes, ele pode utilizar o Spool para não precisar calcular um valor que já foi calculado. Por exemplo, digamos que a coluna data tenha um grau de densidade alto, ou seja, os valores duplicam bastante dentro da massa da tabela. Isso irá acarretar em um mesmo calculo mais de uma vez, já que o Nested Loops vai chamar os passos do join para cada linha lida no from. Se o valor passado como condição para o join é igual ao valor que já foi calculado, então não precisa calcular os dados novamente. O ideal neste caso é que o calculo que já foi feito seja reaproveitado.

O operador de Index Spool trabalhando como Lazy é desenhado para otimizar o processo do Join, isso significa que ele é otimizado para prever justamente este caso que citei, um valor que já foi calculado não será calculado novamente, mas ele lê este valor na tabela que esta em cache (tempdb). Portanto para o Spool é importante distinguir entre a execução do join com um novo parâmetro ou um parâmetro que já foi utilizado.

É possível identificar pelo plano de execução, quando o Spool teve que recalcular um valor e quando ele reutilizou um valor já calculado, basta analisar os valores de Rewind e Rebind. Onde:

· Rewind: Definido pela execução do mesmo parâmetro. Um rewind faz com que o Spool leia os dados que estão em cache.

· Rebind: Acontece quando um novo parâmetro é enviado para execução. O rebind faz com que o Spool calcule um novo valor e carregue o resultado para o cache.

Aproveitando que estamos falando sobre os operadores, o rewind e rebind ocorrem no método Init() do operador. Só para deixar mais claro, sempre que um operador é executado ele executa 3 métodos, são eles:

· Init() – Neste método o operador é inicializado e carrega as informações que são necessárias para sua execução. Este método é chamado pelo menos 1 vez, porem dependendo do operador ele pode ser chamado mais de uma vez. No caso dos operadores de spool, sempre que este método é chamado ocorre um rebind ou rewind.

· GetNext() – Lê a primeira ou subsequente linha dos dados. Cada vez que o GetNext() é chamado o SQL lê a linha necessária para executar sua operação.

· Close() – Finaliza o operador e libera os dados utilizados por ele.

Vamos supor uma tabela simples, com alguns registros na seguinte ordem, 1,1,2 e 1. Uma representação do Rewind e Rebind na prática seria algo mais ou menos assim:

1. Valor = 1, rebind ocorre já que é a primeira vez que o operador é chamado. Calcula o valor e joga no cache do Spool.
2. Valor = 1, rewind ocorre já que o valor para 1 já foi calculado e está em cache.
3. Valor = 2, rebind ocorre pois o valor 2 ainda não foi calculado. Calcula o valor e joga no cache do Spool. O spool só consegue gravar um valor por vez, portanto neste caso calculo para o valor 1 é truncado, para dar lugar ao valor do calculo 2.
4. Valor = 1, rebind ocorre já que o valor para 1 não está mais em cache, pois ele foi truncado quando o rebind para o passo 3 ocorreu.

Normalmente os operadores de Spool apagam os dados de sua tabela temporária a cada Rebind, ou seja, sempre que ele precisar calcular um novo valor, antes de iniciar o calculo ele apaga os dados atuais. Quando o valor subseqüente é igual, o Lazy Index Spool consegue identificar que não é necessário fazer um rebind e reutiliza o valor gravado no cache.

Na minha opinião, o Lazy Spool seria mais eficiente se ele não truncasse os dados, sempre que ele precisar fazer um Rebind. Ao manter os dados em cache, ele conseguiria fazer um novo rewind no nosso exemplo(ponto 4), e assim evitaria ter que calcular um valor que ele já calculou.

Eu entrei em contato com o Craig Freedman para dar esta sugestão e confirmar se o SQL realmente truncava o valor a cada rebind, ele confirmou que é isso mesmo, pois o spool só consegue trabalhar com um valor por vez. Portanto, é uma pena.

Lembrando que o Otimizador verifica se é necessário utilizar o Lazy Index baseado no custo da operação, quando a densidade da coluna for muito alta e o custo for spool for menor que refazer o calculo(reler o valor na origem e não na tabela temporária do spool). Por enquanto segue o link para a explicação do Gustavo Maia, estou escrevendo um novo post onde vou falar um pouco mais sobre, como a densidade de uma coluna pode ser visualizada, calcula e quando ela é utilizada.

Fica a seguinte dica, normalmente quando vemos um Index Spool, isso significa que se criarmos um índice pelas colunas que ele utilizou, é bem provável que o Spool suma, e o SQL passe a utilizar o novo índice.

É isso ai galera, falta apenas falar sobre o RowCount Spool e podemos passar para outro operador. Fique de olho, e se tiver sugestão de algum operador, é só postar um comentário.

Abraço.