Blog – Fabiano Neves Amorim

Para falar sobre os operadores de Spool, criei uma tabela chamada Funcionarios. Segue o script para criação e carga dos dados.

USE TempDB

GO

SET NOCOUNT ON

IF OBJECT_ID(‘Funcionarios’) IS NOT NULL

DROP TABLE Funcionarios

GO

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

Nome VarChar(30),

Salario Numeric(18,2));

DECLARE @I SmallInt

SET @I = 0

WHILE @I < 1000

BEGIN

INSERT INTO Funcionarios(Nome, Salario)

SELECT ‘Fabiano’, ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 500000.0)))

SET @I = @I + 1

END

Segue um exemplo dos dados da tabela.

Ok, agora que estamos com os scripts compilado, podemos voltar ao assunto, paramos no Eager Spool.

Se você não leu a 1º parte deste artigo, leia antes para entender como o Eager Spool funciona, leia também sobre as duas categorias de operadores, os blocking e nonblocking.

Para ilustrar o funcionamento do Eager Spool, vamos voltar no tempo para uma época onde eu nem pensava em ter nascido, mas já existiam uns caras trabalhando e muito, com banco de dados.

Era Halloween, a noite estava fria no penoso inverno(sei lá se era inverno :-), inclui para dar um ar de drama) de 1976, crianças pediam “tricks and treats” nas casas. Enquanto isso em um lugar não muito distante dali, foi rodado um update em um banco de dados, para atualizar em 10% o salário de todos funcionários que ganhavam menos de $ 25.000,00.

Assim começa a história de um problema conhecido por “Halloween Problem”, engenheiros da IBM foram os primeiros a encontrar o problema, e a partir de então, ao longo dos anos, vários banco de dados sofreram com problemas semelhantes, inclusive nosso querido SQL Server como pode ser percebido aqui.

O problema consiste em utilizar um índice nonclustered para atualizar uma determinada coluna, e pode ocorrer de uma mesma linha ser atualizada várias vezes.

Todo UPDATE é executado em 2 etapas, a 1º é a fase de leitura, e a 2º é a fase de atualização. Por exemplo.

UPDATE Funcionarios SET Salario = 0

WHERE ID = 10

Para a consulta acima, a primeira fase é localizar quais os registros que necessitam ser atualizados, e depois é necessário atualizar estes registros. Caso exista algum índice sobre a coluna que está sendo modificada(Salario) então este índice também precisa ser atualizado.

O que acontece é que em algumas consultas, durante a fase de leitura, se SQL optar por ler os dados utilizando um índice, o valor deste índice pode mudar conforme a 2º fase for sendo executada. Vamos ver alguns exemplos, simulando a mesma consulta mencionada no problema encontrado pela IBM.

Digamos que eu queira dar 10% de aumento de salário para todos os funcionários que ganham menos de R$2000,00 reais. E rode a seguinte consulta,

UPDATE Funcionarios SET Salario = Salario * 1.1

FROM Funcionarios

WHERE Salario < 2000

Temos o seguinte plano de execução.

|–Clustered Index Update(OBJECT:([dbo].[Funcionarios].[PK__Funciona__3214EC27656B779F]), OBJECT:([dbo].[Funcionarios].[ix_Salario]), SET:([dbo].[Funcionarios].[Salario] = [Expr1003]))

|–Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))

|–Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1007] THEN (0) ELSE (1) END))

|–Top(ROWCOUNT est 0)

|–Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(numeric(18,2),[dbo].[Funcionarios].[Salario]*(1.1),0), [Expr1007]=CASE WHEN [dbo].[Funcionarios].[Salario] = CONVERT_IMPLICIT(numeric(18,2),[dbo].[Funcionarios].[Salario]*(1.1),0) THEN (1) ELSE (0) END))

|–Clustered Index Scan(OBJECT:([dbo].[Funcionarios].[PK__Funciona__3214EC27656B779F]), WHERE:([dbo].[Funcionarios].[Salario]<(2000.00)) ORDERED)

Por enquanto, vamos nos atentar apenas para as fases que eu mencionei. No Clustered Index Scan, vemos que o SQL seleciona as linhas que serão atualizadas, e o Clustered Index Update atualiza as linhas nos índices PK… e no ix_Salario.

Bom, seguindo esta lógica, vamos desenhar um pouco como o processo vai funcionar.

Sabemos que o índice cluster está ordenado por ID, e é com base nele que o SQL está pegando quais as linhas serão atualizadas, temos o seguinte.

A primeira linha que é selecionada é o ID = 3,

Podemos traduzir estas etapas por:

· Seleciona a primeira linha

· Atualiza a coluna salário com o valor atual * 1.1

Depois desta atualização teríamos o seguinte.

Repare que o valor do salário ganhou um aumento de 10%. Continuando, ao selecionar a próxima linha o ID será igual a 5, e este processo irá continuar até o fim da tabela.

Até aqui, vemos que utilizar um índice cluster não gera nenhum erro, se o SQL continuar nesta lógica até o fim do processo, todas as linhas serão atualizadas corretamente. Mas e se ele utilizar o índice nonclustered ix_salario para ler os dados(1º fase do update)?.

Vamos usar a mesma ilustração que utilizamos acima. Mas desta vez vamos forçar o uso do índice ix_salario.

UPDATE Funcionarios SET Salario = Salario * 1.1

FROM Funcionarios WITH(INDEX=ix_Salario)

WHERE Salario < 2000

|–Clustered Index Update(OBJECT:([dbo].[Funcionarios].[PK__Funciona__3214EC27656B779F]), OBJECT:([dbo].[Funcionarios].[ix_Salario]), SET:([dbo].[Funcionarios].[Salario] = [Expr1003]))

|–Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))

|–Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1007] THEN (0) ELSE (1) END))

|–Top(ROWCOUNT est 0)

|–Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(numeric(18,2),[dbo].[Funcionarios].[Salario]*(1.1),0), [Expr1007]=CASE WHEN [dbo].[Funcionarios].[Salario] = CONVERT_IMPLICIT(numeric(18,2),[dbo].[Funcionarios].[Salario]*(1.1),0) THEN (1) ELSE (0) END))

|–Table Spool

|–Index Seek(OBJECT:([dbo].[Funcionarios].[ix_Salario]), SEEK:([dbo].[Funcionarios].[Salario] < (2000.00)) ORDERED FORWARD)

Repare que desta vez depois de ler os dados do índice ix_salario, o SQL utilizou o operador de blocking de Eager Spool. Como eu mencionei no 1º post, assim que são chamados pela primeira vez, os operadores de blocking leem(com a reforma ficou assim né? sem o ^) todos os dados disponíveis e depois passam para o próximo passo. No nosso exemplo o Eager Spool grava os dados do índice em uma tabela temporária, e posteriormente os updates não leem mais os dados do ix_salario, e sim do Eager Spool.

Bom, você deve estar se perguntando, mas e o problema do halloween cadê? Vamos chegar lá agora.

Se o SQL não tivesse utilizado o Eager Spool para ler os dados, ele iria ler a primeira linha do índice ix_salario, atualizaria o valor de salário com 10% e depois iria selecionar a outra linha e etc… Sabemos que o ix_salario está ordenado pela coluna salário. Vamos ilustrar.

Os dados no índice estão assim:

A primeira linha é o ID 763, o SQL atualiza o ID 763 e os registros ficam assim:

Passa para o próximo ID. Que é o 468.

Este problema não foi encontrado antes, porque ele não acontece sempre. Neste caso não deu o problema, mas me diga uma coisa. E se os dados estivessem assim?:

Vamos lá de novo, pega a primeira linha(ID = 763). Atualiza o valor com 10%, os dados ficam assim:

Pega a próxima linha, que é a? WTF? É o ID 763 de novo? Yes, .. vamos com calma, porque raios o ID 763 foi parar na 2º linha? Ele não era o primeiro registro?

Sim, ele era, mas ao atualizar o valor do salário em 10%, o SQL atualizou inclusive o ix_salario(veja no plano, que o clustered índex update, atualiza a PK e o ix_salario), o que fez com que os dados fossem reposicionados no índice. O índice tem que manter os dados ordenados fisicamente. Assim que algum valor muda, ele reposiciona este valor na arvore binária. Como a leitura está sendo feita em com base no índice, e ele esta sofrendo uma atualização, este problema pode claramente ocorrer.

Conclusão o funcionário com ID = 763 teria o salário aumentado em 20%(um, até que pode ser uma boa, já que ele chama Fabiano :-)). No problema da IBM eles disseram que ao final da consulta todos os funcionários estavam ganhando $25.000,00. E que daí foram tentar entender o que havia acontecido.

O time de desenvolvimento do Query Processor, utiliza os operadores de blocking para garantir que os dados serão lidos serão os mesmos independente de posteriormente eles sofrerem uma atualização. No nosso exemplo o SQL utilizou o Eager Spool, e quando ele vai ler os dados pela segunda vez, ele não vai no ix_salario, e sim no Spool, que guardou os dados no TempDB. O spool fez uma cópia dos registros do indice ix_salario no tempdb.

Bom, vimos que o Eager Spool pode ser utilizado para evitar o problema conhecido por “Halloween problem”, mas ele também pode ser utilizado em suas consultas para quando o Query Optimizer achar que compensa criar uma cópia dos dados ao invés te ter que sempre calcular os dados para ler qual será o próximo registro.

Fique ligado para saber mais sobre os próximos tipos de Spool.

Deixo alguns links muito bons, que falam sobre o “Halloween Problem”.

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

http://it.toolbox.com/blogs/db2luw/what-is-the-halloween-problem-in-databases-12618

http://blogs.msdn.com/craigfr/archive/2008/02/27/halloween-protection.aspx