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.

Pessoal vamos deixar uma coisa bem clara.

O Recovery Model Bulk-Logged NÃO é o recovery que grava menos LOG.

Por se chamar Bulk-Loogger isso não significa que ele só grava operações de bulk. Pelo contrário na verdade isso significa que ele não grava operações de bulk.

Portanto para ambientes de desenvolvimento, testes ou mesmo bases OLAP, onde não é necessário fica fazendo backup para voltar a um determinado tempo. O Ideal será utilizar o recovery Simple.

Eu já ouvi várias vezes, dizerem que o Bulk é melhor que o Simple.

Veja bem, o Bulk-Logged é semelhante ao Full, a diferença entre eles é que o Bulk não grava operações de Bulk como, SELECT INTO, BCP, BULK INSERT,CREATE INDEX e operações com os tipos de dados texto e ntext.

Segue alguns links que comprovam o que eu escrevi:

http://msdn.microsoft.com/pt-br/library/ms186229.aspx

“Geralmente, o modelo de recuperação bulk-logged é semelhante ao modelo de recuperação completa e as informações descritas para o modelo de recuperação completa também se aplicam a ambos.”

http://www.devmedia.com.br/articles/viewcomp.asp?comp=6458&hl=

“Este modelo registra muito mais informação no log de transações do que o modelo SIMPLE. A única informação não registrada são operações de volume como: SELECT INTO, BCP, BULK INSERT,CREATE INDEX e operações com os tipos de dados texto e ntext.”

http://blogs.msdn.com/luti/archive/2007/03/29/log-sequence-number-lsn-e-a-tabela-backupset-backup-restore-parte-1-de.aspx

“Quando o banco de dados está com o recovery model full ou Bulk-logged, todas as transações processadas pelo SQL Server são registradas no log de transação (arquivos .ldf), e cada instrução executada recebe um número de seqüência (LSN).”

Spool

Eager Spool

Lazy Spool

RowCount Spool

Table Spool

Nonclustered Index Spool

Pessoal como podemos ver o operador de hoje é o Spool, e ele tem várias funções dentro dos planos de execução. O conceito pode ser utilizado em várias tarefas.

Basicamente falando, ele pega um resultado intermediário e armazena este resultado no TempDB. Este processo é feito sempre que o resultado armazenado é muito complexo para ser calculado, para estes casos, ele faz o calculo apenas uma vez, e depois armazena o resultado para futuras consultas.

Os operadores de Spool sempre são utilizados em conjunto com outros operadores. Como eles armazenam um valor, eles precisam receber este valor para ser armazenado. E este valor futuramente pode ser lido por um select. Por exemplo, o spool pode ser utilizado junto com um clustered índex scan, onde ele armazena as linhas lidas pelo scan.

Uma simples representação gráfica do que eu disse seria um plano mais ou menos assim.

<— <— 

Figura 1

No exemplo da figura 1 a ordem seria: Clustered Index Scan passaria os dados para o Spool e o Select lê os dados diretamente do Spool.

Vamos ver cada tipo de spool individualmente começando com o Eager Spool .

Bom, a palavra Eager significa ansioso, ou ávido em inglês. O papel do Eager Spool é pegar todas as linhas recebidas por um outro operador e armazenar estas linhas no tempdb.

Caso o operador que passou os dados para o Spool não faz nenhum calculo(por exemplo, um nested loop), então o spool pode ser utilizado pelo próximo operador para ler os dados. Mas caso os dados tenham que ser calculados(por exemplo, um stream aggregate) então o Spool terá que chamar o operador novamente para calcular os dados e retornar para ele.

O operador é “Eager” porque assim que o operador anterior a ele(operador de select no exemplo da figura 1), requer as suas linhas, ele vai no operador seguinte a ele(operador de clustered índex scan no exemplo da figura 1) e já pega todas as linhas do clustered índex scan, e não uma linha por vez(conforme requisição).

Com base nisso, podemos dizer que o Eager Spool é um operador do tipo blocking. Opa eu ainda não falei sobre a diferença entre operadores blocking e nonblocking?

Ok vamos abrir um enorme parênteses aqui.

(

Existem duas categorias de operadores: os operadores “nonblocking” e os operadores “blocking” ou “stop-and-go”:

Os operadores nonblocking são aqueles que lêem as linhas do input e produzem o output ao mesmo tempo. No método conhecido por GetRow(), conforme a leitura das linhas vão acontecendo, o operador executa sua tarefa e já retorna o output para o próximo operador fazer seu trabalho. O operador de Nested Loop é um bom exemplo desse comportamento. Assim que a primeira linha é recebida (GetRow()) o SQL faz o Join com a tabela do outer, e caso o relacionamento seja verdadeiro a linha é retornada no output. Este processo se repete até o fim da leitura na tabela do Join;
Já os operadores “blocking” necessitam primeiro ler todas as linhas (normalmente no método Open()) do input para depois retorná-las para o output. Um exemplo clássico de operadores blocking é o Sort, que necessita primeiro ler todas as linhas do input para fazer a ordenação e, por fim, retornar as linhas ordenadas.

)

Pessoal comecei a escrever os códigos para simular os “Spool”s, e percebi que isso seria um pouco mais difícil do que eu imaginava. Criar as tabelas, e os selects, forçando determinada situação é bem complicado e requer uma boa imaginação.

Ao invés de esperar eu terminar os scripts para colocar o post, vou postar somente isso, e quando eu terminar os scripts, continuo a escrita, já vi que esse vai ser um operador que vai dar bastante o que falar, ou melhor, escrever.

Portanto, Continua….

Abraço.

Senhores, Não grite com seu Hardware. Isso pode causar queda de desempenho de seus HDs. L

Pois é, … Inacreditável mas se ver o vídeo do link verá que é real.

Conclusão, certifique-se de que seu rack não está vibrando por algum motivo, isso pode ser um problema.

Fico pensando, o que se passa na cabeça do sujeito que faz uma descoberta dessas. J

http://blogs.sun.com/brendan/entry/unusual_disk_latency

The following screenshot shows two spikes of unusually high disk I/O latency during a streaming write test:

This screenshot is from Analytics on the 7410. The issue is not with the 7410, it’s with disk drives in general. The disk latency here is also not suffered by the client applications, as this is ZFS asynchronously flushing write data to disk. Still, it was great to see how easily Analytics could identify this latency, and interesting to see what the cause was.

See this video for the bizarre explanation:

Don’t try this yourself…

Muito bom, vale a pena deixar o áudio ligado enquanto trabalha.

Mundo Coorporativo com Herodoto Barbeiro na CBN

Como falar em público
Entrevista com JB Oliveira, jornalista

Uso e origem das palavras, estrangeiras ou não, no nosso dia-a-dia
Max Gehringer, escritor e executivo

Como diferenciar a carreira profissional?
Entrevista com Luciano Salamacha, professor da FGV e consultor de empresas

Desmitifique as fantasias que existem em relação à vida corporativa
Luli Radfaher, professor da USP e autor do livro A arte da guera para quem mexeu no queijo do pai rico

O ranking das marcas mais valiosas do mundo e seus efeitos no mundo dos negócios
Entrevista com Alejando Piñedo, diretor da Interbrands do Brasil

Como despertar o talento em cada um
Entrevista com Paulo Araújo, consultor e palestrante

Desmitifique as fantasias que existem em relação à vida corporativa
Luli Radfaher, professor da USP e autor do livro A arte da guera para quem mexeu no queijo do pai rico

A Amazon publicou um link para pré-venda do novo livro da Delaney(co-Autores Paul Randal, Kimberly Trip, Conor…) que está no forno e com previsão de entrega para Fevereiro de 2008.

http://www.amazon.com/dp/0735626243/ 

Estou aguardando ansiosamente…

Fiz uma
recomendação de um livro na thread abaixo no Forum do MSDN,

 

http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3983673&SiteID=21

 

Quero aproveitar
para deixar o link aqui tbm para quem se interessar.

 

“Dissecting
SQL Server Execution Plans” do Grant Fritchey.

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/Art%20of%20High%20Performance%20SQL%20Code%20by%20Grant%20Fritchey.pdf

Depois vou postar as minhas centenas de fotos e videos sobre o Community Zone, mais essa foto que o Diego postou no blog dele ficou show, … Galera do mal essa eim… É uma honra pra mim pousar do lado desses feras…

Valeu Diego.

Citação

Gustavo, Luciano, Laercio, Diego, Junior Galvão e Fabiano

Viu galera, reparam uma coisa? o "01" é o 1º a aparecer na foto coincidência não?….hahaha…

Estarei apresentando uma WebCast(Treinamento Online) amanhã na quarta-feira, 26 de agosto de 2008.

Segue o resumo do que será discutido na WebCast e o link para inscrição do evento..

—————————————————————————————————————————————-

Visão Geral do Evento

Palestrante: Fabiano Amorim.

SQL Server Management Objects (SMO) é uma biblioteca de objetos do .NET Framework que permite gerenciar e criar objetos em um servidor SQL Server. Veja neste WebCast como usar os componentes SMO para criar tarefas de manutenção de banco de dados a partir de uma aplicação WindowsForms. Veremos também o uso do SQL Server Management Studio Express que é ferramenta gratuita de gerenciamento do SQL Server Express.

https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032387063&EventCategory=4&culture=pt-BR&CountryCode=BR

Conto com a presença de todos.

Update

Os arquivos utilizados na WebCast podem ser baixados usando os links abaixo.

 http://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/ExemploSMO.ziphttp://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/MSDNWebcast-ManipulandooSQLExpresscomSMOeManagementStudioExpress.pptx

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/MSDN%20Webcast%20-%20Manipulando%20o%20SQL%20Express%20com%20SMO%20e%20Management%20Studio%20Express.pptx

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/ExemploSMO.zip