Arquivo
SQL Server 2008 Books Online (23 January 2009)
Saiu uma atualização do Books Online SQL 2008. Eu já estou baixando e você?
——————————
SQL Server 2008 Books Online (January 2009)
New and Updated Topics in SQL Server 2008 Books Online (23 January 2009)
The following technologies and categories have had new topics added or existing topics updated with significant changes in the 23 January 2009 release of SQL Server 2008 Books Online.
Getting Started: New and Updated Topics (23 January 2009)
Integration Services: New and Updated Topics (23 January 2009)
Database Engine: New and Updated Topics (23 January 2009)
Reporting Services: New and Updated Topics (23 January 2009)
07 Coisas que você precisa saber em relação “Statistics e Optimizer”
1. Selectivity:
a. Podemos definir seletividade como: O grau de um valor dentro de um determinado grupo, por exemplo, os jogadores da seleção de futebol são selecionados dentre milhares de opções que um técnico tem.
b. Aplicando este conceito a banco de dados teríamos, em uma tabela de Clientes a coluna Tipo de Pessoa, pode ser J(Jurídica) ou F(Física), podemos dizer que o grau de seletividade é de baixo, pois só há duas variantes do valor dentre um todo. Por outro lado, uma coluna CPJ ou CNPJ será altamente seletiva, pois irá retornar apenas um registro dentre toda a tabela.
2. Density:
a. O termo densidade vem da física, e é calculado por pela massa dividia por volume, uma representação matemática seria como na figura abaixo. Por exemplo, estamos um pouco mais acostumados a ouvir o termo densidade geográfica. A densidade geográfica do Brasil é calculada pela quantidade de habitantes dividida pelo tamanho da área geográfica, o que seria 187.000.000 dividido por 8.514.215,3 km2 que é igual a 21,96 habitantes por km2.
b. No SQL Server podemos traduzir isso para: Quanto mais densa for uma coluna, mais registros ela retorna. Repare que é exatamente o contrário de seletividade, que quando mais seletiva for uma coluna menos registros ela retorna.
c. Calculo matemático de densidade:
Onde D = Densidade, M = Massa e V é igual ao Volume.
d. Para calcular a densidade de uma coluna você pode executar a consulta abaixo.
SELECT (1.0 / Count(Distinct <Column>))
3. Cardinality:
a. É utilizada para medir o número de linhas que satisfaçam uma condição. Por exemplo, imagine uma tabela com 500 linhas e uma consulta com where Nome = ‘Joao’, o SQL vai na estatística e lê no histograma que o valor ‘Joao’ representa 5% da tabela, portanto a cardinalidade é de (5% * 500) = 25.
b. Nos planos de execução podemos considerar a cardinality como a “estimated number of rows” apresentado nos operadores. Não preciso dizer que uma má interpretação na cardinality pode gerar um plano ineficiente, certo?
4. SARG:
a. SARG(Search Arguments) são condições de sua consulta. O valor que aparece no WHERE = “…” esse 3 pontinhos é o valor de pesquisa. Com base nesta informação o otimizador vai estimar quantas linhas serão retornadas e qual plano deverá utilizar para acessar esses dados. Ou seja, com base no argumento de consulta(SARG) informado o SQL analisa a selectivity, density e cardinality para gerar o plano.
5. O que acontece quando o SQL não consegue estimar a cardinalidade de um valor(SARG) ?
a. A cardinalidade e seletividade de um valor são informações essenciais para otimizador decidir qual plano usar. Sempre que possível durante a fase de criação do plano de execução(otimização), o SQL tenta executar transformar seus argumentos de consulta em constantes. Por exemplo, durante a fase de otimização, ele troca um “WHERE Valor = 1 + 1” por “WHERE Valor = 2“, e com base no “2” ele tenta ler nas estatísticas da tabela os dados necessários para ele tomar a decisão de qual plano gerar.
b. O otimizador não consegue identificar(leia-se sniff) o valor de um argumento de consulta por vários motivos, vou destacar alguns:
i. Quando na constante é utilizado uma função que seja diferente das listadas abaixo:
1. Lower, Upper, RTrim, Datediff, Dateadd, Datepart, Substring, Charindex, Len, SUser_SName, IsNull, GetDate, GetUTCDate;
2. Exemplos:
1. “WHERE <coluna> = ABS(-1000)”, para que o SQL consiga utilizar o argumento de consulta, você poderia trocar o comando por “WHERE <coluna> = -1000 * -1”
2. “WHERE <coluna> = REPLACE(‘xx8888888’, ‘xx’, ’88’)”: Não consegue gerar a cardinalidade.
3. “WHERE <coluna> = LEN(88888888)”: Consegue gerar cardinalidade normalmente.
ii. Quando utilizado variáveis locais. Por exemplo:
DECLARE @I Int
SELECT * FROM <tabela>
WHERE <Coluna> = @I
iii. Quando utilizado scalar-valued user-defined functions.
SELECT * FROM <tabela>
WHERE <coluna> = dbo.fn_FormataTexto(‘Texto’)
iv. Quando é utilizado um operador de diferença, por exemplo o <>, o argumento de consulta não é valido.
v. Consultas do tipo “WHERE <coluna> = (select <coluna> from <tabela>)”, não permitem que o argumento seja utilizado, pois o resultado da subquery não é conhecido no momento da geração do plano.
c. Quando o otimizador não consegue identificar qual é a cardinalidade de um valor, ele usa o que chamamos de Magic Density(haha eu adoro esse nome), também conhecido por “Guess”, resumindo, ele tenta “adivinhar” quantas linhas serão retornadas. Para isso ele usa a densidade da coluna ou então ele usa um percentual fixo. Vejamos alguns exemplos de como ele faz esta “mágica”.
d. A partir do SQL Server 2000 funciona assim:
1. — Para =, Usa COUNT(*) * Density da coluna ou seja, COUNT(*) * (1.0 / (COUNT (DISTINCT <coluna>)))
2. SELECT *
3. FROM <tabela>
4. WHERE <coluna> = @I
5. — Para BETWEEN, Usa 9% ou seja, (COUNT(*) * 9.0) / 100
6. SELECT *
7. FROM <tabela>
8. WHERE <coluna> BETWEEN @I AND @X
9. — Para >, >=, < e <=, Usa 30% ou seja, (COUNT(*) * 30.0) / 100
10.SELECT *
11. FROM <tabela>
12. WHERE <coluna> “>, >=, < e <=” @I
e. Importante: No SQL Server 2000 tem um detalhe que é um pouco diferente, os percentuais utilizados(9% e 30%) não mudam, porem ao utilizar o between o otimizador consegue identificar um comportamento bem interessante, coisa que a partir do SQL2005 foi alterado e ao meu ver está errado, deveria voltar como era no 2000. No SQL 2000, se eu escrever uma consulta assim:
1. SELECT *
2. FROM <tabela>
3. WHERE <coluna> BETWEEN @I AND @I
Repare que a consulta utiliza a variável @I duas vezes, ou seja este between é a mesma coisa que um WHERE <coluna> = @I. Neste caso o SQL 2000 gera a seguinte instrução de argumento:
OBJECT:([dbo].[<tabela>].[PK_…]), WHERE:([ <tabela>].[<coluna>]=[@I])
Já o nosso querido SQL 2005 e 2008 geram o seguinte;
OBJECT:([dbo].[<tabela>].[PK_…]), WHERE:( [dbo].[ <tabela>].[<coluna>]>=[@I] AND [dbo].[ <tabela>].[<coluna>]<=[@I])
Pois é, ele não é esperto o suficiente para identificar que “BETWEEN @I AND @I” é igual a “= @I”. Outra coisa interessante é que se você rodar a consulta no SQL 2005\2008 utilizando o hint OPTION(RECOMPILE) ele gera o plano corretamente, igual ao SQL 2000.
6. RowModCtr:
a. Existe uma coluna na tabela sysindexes do SQL 2005 ou sys.sysindexes para posteriores, onde podemos observar quantas alterações ocorreram em uma determinada coluna deste a última atualização das estatísticas.
b. Sempre que acontece um INSERT, DELETE ou UPDATE envolvendo a coluna da estatística, o SQL Server atualiza este valor.
c. Quando as estatísticas são atualizadas, esta coluna é zerada.
d. Caso você queira ter mais controle sobre quando disparar um UPDATE STATISTICS, você pode utilizar esta coluna para ver quantas alterações ocorreram na tabela, e decidir se deve disparar uma atualização ou não.
7. Quando um auto update statistics é disparado?
a. Sempre que uma coluna que pertence a uma estatística alcança um número suficiente de modificações, o SQL Server dispara o auto update statistics para manter os dados atualizados. Este controle funciona assim:
i. Se a quantidade de linhas da tabela for menor que 6 e for uma tabela temporária(#), atualiza as estatísticas a cada 6 modificações na tabela.
ii. Se a quantidade de linhas da tabela for maior que 6, porem menor ou igual a 500, atualiza as estatísticas a cada 500 modificações na tabela.
iii. Se a quantidade de linhas da tabela for maior que 500, atualiza as estatísticas a cada (500 + 20% da tabela) modificações na tabela. Portanto, uma tabela com 1000 linhas, só sofreria uma atualização automática quando a rowmodctr atingisse 700 modificações.
iv. Só lembrando que variáveis do tipo table, não tem estatisticas portanto elas não geram um auto update.
b. Pergunta, tem como visualizar quando o auto update é disparado?
i. Tem várias formas, segue algumas:
1. Se a coluna rowmodctr estiver zerada isso significa que o auto update acabou de ser executado.
2. No profiler selecione as colunas SP:StmtCompleted e SP:StmtStarting que quando o UPDATE for disparado você verá o código utilizado pelo SQL para pegar as informações da coluna. Verá algo mais ou menos parecido com o código abaixo:
SELECT StatMan([SC0])
FROM (SELECT TOP 100 PERCENT <coluna> AS [SC0]
FROM [dbo].<tabela> WITH (READUNCOMMITTED)
ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL
3. Você pode habilitar o traceflag 205 para fazer com que o SQL grave uma informação no arquivo de error log.
4. O traceflag 8721 também grava uma informação no error log.
Quer mais? Boa Leitura:
Troubleshooting Poor Query Performance: Cardinality Estimation
Statistical maintenance functionality (autostats) in SQL Server
INF: Search Arguments That Determine Distribution Page Usage
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Use Parameters or Literals for Query Inputs
How to update statistics for large databases
How It Works: Statistics Sampling for BLOB data
Support WebCast: Effective Indexing and Statistics with SQL 2000
Operador do dia – Nonclustered Index Spool
![clip_image001[3]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0015b35d5b25d.gif "clip_image001[3]"){kind=small}
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 – Eager Spool e “Halloween Problem”
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:
![clip_image002[3]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0025b35d5b45d.png "clip_image002[3]")
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:
![clip_image003[3]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0035b35d5b65d.png "clip_image003[3]")
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.
![clip_image004[3]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0045b35d5b35d.png "clip_image004[3]")
![clip_image005[3]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0055b35d5b35d.png "clip_image005[3]")
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:
- Valor = 1, rebind ocorre já que é a primeira vez que o operador é chamado. Calcula o valor e joga no cache do Spool.
- Valor = 1, rewind ocorre já que o valor para 1 já foi calculado e está em cache.
- 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.
- 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.
Recovery Model Bulk-logged
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.”
“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).”
Operador do dia – Lazy Spool
Lazy Spool
Continuando a falar sobre os operadores de Spool, o próximo da lista é o Lazy Spool.
Para melhor entendimento do assunto, caso não tenha lido os outros posts onde falei sobre Spool, recomendo que leia.
Operador do dia – Eager Spool e “Halloween Problem”
O Lazy Spool é bem parecido com o Eager Spool, porém a grande diferença entre eles é que o Lazy lê os dados conforme as linhas são requisitadas, a cada acesso ao operador, ele lê apenas uma linha, diferentemente do Eager que logo no primeiro acesso lê todos os dados.
Para ilustrar o uso do Lazy 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 = 1
WHILE @I < 3
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 Lazy Spool, criei uma consulta que retorna todos os pedidos que tiveram venda maior que a média de venda,
comparado a todas as vendas para o mesmo cliente.
SELECT Ped1.Cliente, Ped1.Valor
FROM Pedido Ped1
WHERE Ped1.Valor < (SELECT AVG(Ped2.Valor)
FROM Pedido Ped2
WHERE Ped2.Cliente = Ped1.Cliente)
Antes de ver o plano vamos entender melhor a consulta.
A SubConsulta retorna a média de venda (AVG(Ped2.Valor)) de um determinado cliente 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:
|–Nested Loops(Inner Join)
|–Table Spool
| |–Segment
| |–Sort(ORDER BY:([Ped1].[Cliente] ASC))
| |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Pedido].[PK__Pedido__6E01572D] AS [Ped1]))
|–Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[Pedido].[Valor] as [Ped1].[Valor]<[Expr1004]))
|–Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013]/CONVERT_IMPLICIT(numeric(19,0),[Expr1012],0) END))
| |–Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1013]=SUM([tempdb].[dbo].[Pedido].[Valor] as [Ped1].[Valor])))
| |–Table Spool
|–Table Spool
Podemos observar que o operador de Spool aparece 3 vezes no plano de execução, isso não significa que existem 3 tabelas temporárias, na verdade os 3 compartilham a mesma tabela temporária, isso pode ser observado analisando os hints exibidos pelos operadores.
Repare que o primeiro Spool tem o Node ID 2, e os outros operadores fazem referencia ao Primary Node 2.
Vou tentar detalhar este plano de execução o máximo possível, para que fique um pouco mais fácil de entender o que está acontecendo.
O primeiro passo do plano é ler os dados envolvidos na consulta e agrupá-los por cliente.
Primeiro o operador de clustered index scan está lendo os dados das colunas clientes e valor.
Input() do operador é:
Output() = Colunas Cliente e Valor.
Ao receber o Output() do clustered index scan, o operador de Sort ordena o resultado pela coluna cliente.
Input() = Output() anterior a ele(clustered index scan)
Output() = Dados ordenados por Cliente
O operador Segment quebra os dados em vários grupos, neste caso, ele recebe os dados ordenados por cliente, e quebra os dados em grupos que compartilham o mesmo cliente. O primeiro grupo serão os dados que compartilham o Cliente “0”. Como os dados estão ordenados por cliente, ele só precisa ir lendo as linhas até encontrar um valor diferente do atual(“0”). Quando isso acontece ele passa seu output() para o próximo operador, e espera o processo voltar até ele para que ele calcule o próximo grupo de dados até terminar sua leitura.
Input() = Output() anterior a ele(Sort)
Output() = Grupo de dados (primeiro grupo “Cliente = 0”)
![clip_image017[1]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0175b15d5b25d.jpg "clip_image017[1]")
Aqui vemos o Table Spool trabalhando como Lazy, ou seja, lê os dados conforme requisição. Ele cria uma tabela temporária no tempdb armazenando os dados que contem o mesmo grupo de Cliente.
Input() = Resultado do Segment
Output() = Tabela com os dados armazenados no tempdb
Faz o join entre a tabela do from com a tabela do join. Neste caso, para cada linha do Table Spool faz um scan nas linhas do próximo join retornando as linhas que satisfaçam o join.
Vamos passar para o segundo passo do plano. Onde o SQL vai calcula a média de venda de um determinado cliente.
![clip_image021[1]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0215b15d5b25d.jpg "clip_image021[1]"){kind=small}
Lê os dados atuais do Spool, e passa o resultado para o aggregate calcular a média. Lembrando que os registros do Spool são exatamente o grupo de clientes “0”.
O Stream Aggregate lê os dados do Spool e calcula a média da coluna valor, retornando apenas 1 registro como parâmetro de saída.
Converte o resultado da aggregação no tipo Numeric para fazer o join com o valor do Spool.
![clip_image023[1]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0235b15d5b25d.jpg "clip_image023[1]"){kind=small}
Faz o join entre a tabela do from com a tabela do join. Neste caso, para cada valor computado, faz um scan no table spool retornando as linhas que satisfaçam o join, ou seja, onde o Valor do Table Spool for maior que o valor retornado pela agregação.
![clip_image021[2]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0215b25d5b25d.jpg "clip_image021[2]"){kind=small}
O ultimo table spool é utilizado pelo Nested Loops para fazer o join com o valor do stream aggregate.
Ao terminar este processo o operador de segment é executado novamente, desta vez repetindo todo o processo passando para o spool o próximo grupo de dados.
Ao utilizar o Spool o SQL Server só precisa ler a tabela Pedidos uma única vez, todos os acessos posteriores a primeira leitura são efetuados no spool, que por sua vez só guarda os valores de um segmento por vez.
Vou tentar ilustrar o que aconteceu aqui:
1 – O Nested Loop lê a primeira linha no Table Spool. Faz um scan com todos os clientes que resultaram do 2 Nested Loop, que irá retornar as linhas onde o valor é maior que a média.
2 – Executa o segundo join. Verifica quais as linhas que tem o valor maior que a média.
3 – Calcula a média para o cliente atual(0).
Para o resultado da média, verifica quais as linhas que satisfaçam o join. Por ex: 1275.91 é maior que 362.78? Caso seja verdadeiro então retorna o Cliente e o Valor, senão passa para o próximo registro do table spool. Repete este processo até terminar a leitura de todos os registros do spool.
4 – Faz um Join com o resultado do 2 Nested Loop.
5 – Chama o segment que limpa o Spool e passa para o próximo grupo de dados, iniciando novamente o processo 1.
Pessoal, vimos o uso do Lazy Spool para trabalhar com joins, evitando acessar uma tabela várias vezes, otimizando o processo de execução da consulta.
Ainda falta vermos os RowCount Spool e o Nonclustered Index Spool. Fique de olho.
Abraço.
Interview Question
Hi Folks
Recentemente li um post bem interessante onde o autor citava 10 perguntas que ele faz em uma entrevista para contratação de um DBA Senior, seja ele desenvolvedor ou administrador.
A primeira pergunta era mais focada a desenvolvimento e, como é minha praia, achei bem interessante e vou compartilhar minha opinião com vocês.
É mais ou menos assim:
Escreva um código onde você irá printar números de 1 a 100, quando o número for múltiplo de 3 você irá escrever ”Fizz”, quando o número for múltiplo de 5 você irá escrever “Buzz” e quando o número for múltiplo de 3 e 5 escreva “FizzBuzz”.
Existem várias maneiras de escrever este código, a intenção não é ver qual é o melhor código, mas sim apurar a lógica de programação e agilidade em lidar com problemas em tempo hábil.
Um desenvolvedor que se preze não deve levar mais de 10 minutos para escrever um código desses, e se for capaz tente escrever sem compilar o código, apenas escreva o comando e depois de terminar ai sim compile. Não tem problema se der algum erro na primeira compilação, mas se você conseguir fazer o código funcionar sem erro na primeira execução, isso mostra que sua lógica e prática de desenvolvimento realmente existem, e são dignas de serem reconhecidas como “Senior”. Mas lembre-se, o mais importante é fazer com que o código funcione.
Eu é claro, escrevi o código e, confesso que da primeira vez que compilei não deu erro, mas a lógica estava errada devido a uma falta de atenção minha, assim que compilei vi o erro corrigi e rodei de novo com sucesso. Fica a dica, não faça como eu, escreva o código e depois revise ele para ver se não ficou algum GAP pra traz.
Meu código está aqui, não baixe antes de tentar escrever o seu código, não minta para você mesmo.
http://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/FizzBuzzProblem.sql
Operador do dia – Eager Spool e “Halloween Problem”
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.
![clip_image001[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0015b135d5b25d.png "clip_image001[13]")
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.
![clip_image002[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0025b135d5b75d.png "clip_image002[13]")
|–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.
![clip_image003[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0035b135d5b25d.png "clip_image003[13]")
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.
![clip_image004[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0045b135d5b25d.png "clip_image004[13]")
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
![clip_image005[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0055b135d5b45d.png "clip_image005[13]")
|–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:
![clip_image006[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0065b135d5b25d.png "clip_image006[13]")
A primeira linha é o ID 763, o SQL atualiza o ID 763 e os registros ficam assim:
![clip_image007[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0075b135d5b25d.png "clip_image007[13]")
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?:
![clip_image008[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0085b135d5b25d.png "clip_image008[13]")
Vamos lá de novo, pega a primeira linha(ID = 763). Atualiza o valor com 10%, os dados ficam assim:
![clip_image009[13]](https://blogfabiano.com/wp-content/uploads/2009/01/clip_image0095b135d5b25d.png "clip_image009[13]")
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
Posts – Performance
Pessoal, para quem não acompanha este blog desde o Início, segue alguns posts, relacionados a performance que talvez sejam útil para vocês.
10 pontos que devem ser observados quanto a performance de uma consulta partes 1,2,3,4
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!490.entry
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!491.entry
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!545.entry
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!546.entry
Data Types
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!181.entry
Boas práticas na definição de Primary Keys
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!318.entry
Statisticas e Plano de Execução
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!160.entry
O que é melhor? Um Index Seek ou Clustered Index Scan?
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!693.entry
Operador do dia – Assert
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!1153.entry
Operador do Dia – Bookmark Lookup
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!1160.entry
Operador do Dia – Compute Scalar
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!1182.entry
Operador do dia – Concatenation
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!1190.entry
Operador do dia – Spool
http://fabianosqlserver.spaces.live.com/blog/cns!52EFF7477E74CAA6!1300.entry
Quiz – Porque o SQL não utiliza meu indice?
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!1197.entry
Resposta – Quiz SQL vs Indice
http://fabianosqlserver.spaces.live.com/blog/cns!52EFF7477E74CAA6!1266.entry
Dissecting SQL Server Execution Plans
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!782.entry
Scalar Functions e Analysis Services – Named Calculation
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!838.entry
Indice / Lookup / Recompile
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!230.entry
Performance – Caso Real – Parte1
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!247.entry
Performance – Caso Real – Parte2
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!250.entry
Why Triggers are Bad
http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!486.entry
Operador do dia – Spool
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.
Frase do dia
"Sempre e nunca são duas palavras que você sempre deve lembrar de nunca utilizar." Anonimo.
Fabiano Amorim
SQL Server MVP
Meu livro, Complete ShowPlan Operators
Livro, SQL Server além do conceito
Livro, Diálogos ao vento
Livro, Diálogos com D
Blog - Fabiano Neves Amorim 