Blog – Fabiano Neves Amorim

——————————

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)

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 – Spool

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”)

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.

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.

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.

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.

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

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

 

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

Hoje um colega aqui da empresa, precisava de uma função que
retorne a quantidades que um caracter aparece em uma determinada string.

Eu desconheço uma função do SQL que faça isso, portanto sugeri
que ele criasse uma user function para fazer essa validação.

Ai é que ta, sabemos que user functions podem dar uma boa
dor de cabeça pela sua péssima performance quando utilizadas em consultas que
retornam uma quantidade razoável de registros. Então, parei para pensar melhor para
ver qual seria a melhor maneira de fazer essa function.

Creio que a idéia inicial de todos seria fazer um loop na string,
correndo por todos os caracteres somando a quantidade de vezes em que o
caracter aparece na string. Algo mais ou menos assim:

DECLARE @Str VarChar(200),

       
@Caracter_A_Procurar VarChar(200),

        @i Int,

       
@Qtde_Caracter Int;

SET @Str = ‘Um teste para validar
quantos caracteres existem nesta String’

SET @Caracter_A_Procurar = ‘a’

SET @i = 0

SET @Qtde_Caracter = 0

WHILE @i <= LEN(@Str)

BEGIN

    IF SUBSTRING(@Str, @i, 1) = @Caracter_A_Procurar

        SET @Qtde_Caracter =
@Qtde_Caracter + 1

    SET @i = @i + 1

END

SELECT @Qtde_Caracter

Ok, o código acima funciona, mas pensando bem, existe outra
maneira bem mais eficiente de fazer esta validação. Que tal assim:

DECLARE @Str VarChar(200),

       
@Caracter_A_Procurar VarChar(200)

SET @Str = ‘Um teste para validar
quantos caracteres existem nesta String’

SET @Caracter_A_Procurar = ‘a’

SELECT LEN(@Str) – LEN(REPLACE(@Str,
@Caracter_A_Procurar, ”))

O código acima pega a string e faz um replace do caracter “a”
por nada, e depois verifica a quantidade de caracteres da string sem o “a” e
subtrai pela quantidade de caracteres original. O resultado será exatamente a
quantidade de vezes em que “a” aparece na string.

Fica ai a dica…

Para quem não leu o 1º Post, segue o link.

Quiz – Porque o SQL não utiliza meu indice?

É isso ai pessoal, vamos entender melhor porque o SQL não pode confiar no índice para retornar nossas informações.

Para entender melhor este caso, temos que pensar como se fossemos o Otimizador de consultas.

Seguindo nessa lógica, vamos lá.

Lembrando que o papel do Otimizador é de calcular qual é a maneira mais rápida de retornar os dados de uma determinada consulta, baseado nas informações que ele tem em relação ao schema da tabela, estatísticas, e recursos de hardware disponíveis no momento da execução.

Bom, no caso de nossa tabela, o Otimizador sabe que ele tem apenas 2 maneiras de acessar os dados, são elas: Utilizar o Índice Cluster, ou utilizar o Índice NonCluster. Os dois índices tem todos os dados necessários para retornar a consulta gravados em suas páginas.

O ponto chave aqui é, qual é o melhor índice o Cluster que esta ordenado por ID, ou o NonCluster que esta ordenado por Nome1 e Valor1.

De primeira vista, podemos olhar o nonclustered e dizer que ele sem dúvida é a melhor maneira de acessar os dados, já que ele está ordenado por Nome1 e Valor1, que são exatamente as colunas utilizadas na clausula WHERE da consulta. Porém, existe um fator determinante para que esta escolha não seja viável, a clausula ORDER BY que está solicitando que os dados sejam retornados pela coluna ID.

Bom mas você pode estar pensando, ora, a coluna ID pertence ao índice cluster, então porque não pegar ela e retornar os dados? A resposta é: – Porque o SQL não pode confiar que os dados estarão na ordem correta. E neste caso ele teria ler os dados do índice e depois fazer um SORT pela coluna ID. Isso pode ser comprovado caso nós forcemos o uso do índice. Vamos ver o como ficaria o plano de execução.

SELECT ID

  FROM TabTeste WITH(INDEX = ix_TesteSem_Include)

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Como podemos observar, ao forçar o uso do Índice o SQL gera um operador de SORT onde este processo representa 82% de custo, quase que o total da consulta.

Ok, entendido que o SQL não pode usar o índice noncluster, porque neste caso ele teria que ordenar o resultado por ID, mas, porque ele não pode confiar no índice? Os dados não estão no Índice?

O que acontece é que o índice está ordenado por Nome1,Valor1 e ID, e fazendo esta leitura o SQL não pode confiar que os registros retornados estarão na ordem correta. Perai, ficou meio confuso né? Vamos fazer o seguinte, vamos ver na prática, acho que vai ficar mais fácil de entendermos.

Vamos ver como o índice está ordenado fisicamente nas páginas de dados.

Primeiro temos que pegar o valor Hexadecimal que diz qual é a primeira página de dados do índice.

select * from sysindexes

where id = Object_id(‘TabTeste’)

O valor da coluna root contem o ponteiro para a primeira página raiz do índice.

Vamos converter o valor Hexadecimal para um Inteiro. Basta reajustar a ordem dos valores e usar a função CAST do próprio SQL.

select CAST(0x040D AS INT)

Ok, então sabemos que a página raiz do índice Nonclustered é o 1037, vamos ver como os dados estão armazenados nesta página? Para isso vamos usar o DBCC PAGE. Os parâmetros passados são,

2 = ID do banco de dados, como estou no TempDB o valor será sempre 2. Caso você esteja logado em outro banco pode pegar usando select DB_ID(DB_name())

1 = Arquivo físico onde a página está armazenada, no Hexadecimal os 4 últimos valores irão dizer qual é o arquivo, no nosso exemplo foi retornado 0100 fazendo a reorganização dos valores teríamos 0001, portanto 1.

1037 = Página onde os dados estão localizados

3 = Forma de visualização dos dados da página. Pode ser 1, 2 ou 3. Como quero ver os dados em uma grade usei o 3.

DBCC TRACEON(3604)

GO

DBCC PAGE(2,1,1037,3)

Ok estes são os dados da página nível raiz, vamos descer os níveis, para ver os dados… só lembrando… A arquitetura do índice é…

Na coluna ChildPageID temos qual é a página filho do valor atual, vamos analisar a primeira página de dados que é a  PageID 1034. Nesta página teremos os dados do índice, e poderemos aplicar o filtro do where.

DBCC PAGE(2,1,1034,3)

.

.

.

Repare que temos o valores dos dados gravados no índice, e eles estão corretamente ordenados por Nome1, Valor1, e ID que é a chave do índice Cluster. Se você continuar a ler os valores das páginas 1036 e 1038 verá que a tabela inteira estará armazenada e ordenada conforme requerimento de nossa consulta(Nome1 e Valor1 para o filtro, e ID para ordenação).

Vamos ver o final do índice, que é a página 1038.

DBCC PAGE(2,1,1038,3)

Sendo assim podemos facilmente visualizar que para a consulta abaixo o SQL poderia simplesmente ler os dados na ordem em que os valores estão sendo apresentados, que ele iria retornar tudo certo, inclusive os dados já estão ordenados por ID. Na teoria, não precisa do SORT, gerado quando forçamos o uso do índice.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Porque então não fazer isso? Votando a pensar como o Otimizador, me responda uma coisa, baseado nas informações que você tem, schema, estatísticas.. Você realmente pode confirmar que SEMPRE os dados estarão conforme nossa visualização? Sim? Talvez? Não sei? …

Caso o SQL pense que o índice já está ordenado corretamente e não gere o SORT, O que aconteceria se eu por ex, incluísse uma linha com os seguintes  valores?

SET IDENTITY_INSERT TabTeste ON

INSERT INTO TabTeste(ID, Nome1, Valor1) VALUES(-1, ‘aaaa’, 1)

SET IDENTITY_INSERT TabTeste OFF

Para ver o dano, vamos rodar a consulta mas sem o ORDER BY, apenas para tentar forçar que o SQL use o Índice, ou seja, você quer trazer os dados pela ordem em que eles estão armazenados no índice,

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

Como podemos observar os dados não foram retornados na ordem esperada, ou seja, ordenados por ID, se o SQL ler os dados do índice ele terá que gerar um sort para reordenar os valores por ID.

Vamos ver como os dados ficaram na última página do índice.

DBCC PAGE(2,1,1038,3)

Podemos ver que, a tabela é ordenada Nome1 e Valor1, como o Valor1 que foi inserido é “1” então ele deve vir depois do ultimo “0”, mas o ID “-1” é menor que “1000”. Seguindo a ordem, a regra é primeiro por Nome1 e depois por Valor1, e não por Nome1 e depois por ID.

Fiz essa inclusão para mostrar que o SORT é necessário já que no índice nonclustered a coluna ID não está ordenada conforme desejamos.

Se alterarmos a consulta para trazer apenas um Nome1 e apenas 1 Valor1, então o índice é utilizado, e não é mais necessário o SORT, pois o Otimizador já sabe que ele pode confiar que para os valores nome1 = ‘aaaa’ e valor1 = 0, os registros estarão ordenados corretamente por ID.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 = 0

 ORDER BY ID

Com isso podemos aprender que o Otimizador pode optar por, evitar fazer um SORT mesmo que isso cause uma não utilização de um índice. Operações de SORT acontecem no TEMPDB e são extremamente pesadas.

Ao criar um índice utilizando a opção Include, o SQL Server grava os valores do INCLUDE depois das chaves do índice, ou seja, trocamos a ordem de Nome1, Valor1, ID por Nome1, ID, Valor. Fazendo isso o SQL pode ter certeza de que os valores estarão ordenados por ID, já que o filtro no Nome1 é uma constante ou seja, é um valor único.

CREATE NONCLUSTERED INDEX ix_Teste_Include ON TabTeste(Nome1) INCLUDE(Valor1)

GO

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Repare que para a consulta que utiliza o índice com Include, o valor passado como SeekPredicate para o Índice foi o Nome1 = ‘aaaa’, já o Valor1 <= 10 é passado como Predicate, ou seja, ele não será utilizado para fazer o seek no índice. Depois eu o seek por Nome1 for feito, será aplicado um filtro neste resultado para que apenas as linhas onde Valor1 <= 10 sejam retornadas.

O Gustavo Maia fez uma analise bem interessante em relação a estes testes, segue o link para o doc que ele escreveu.

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/Gustavo%20Maia%20-%20Desafio.doc

Bom pessoal, semana que vem estou de férias, portanto que Deus Abençoe a todos e tenham um Feliz Natal.

Abraço.

Afe, cada uma viu… a WTF da vez veio novamente do forum MSDN.

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

Eu fico feliz quando vejo isso, é sinal que vou ter emprego garantido por um bom tempo.

Desta vez tem de tudo, código comentado no meio do SQL, SubQueries totalmente desnecessárias… (Campo =1 OR Campo IS NULL)… Bom… veja com os próprios olhos…

UPDATE CIRCUITO SET
    cod_coord_implantacao_cir  = (SELECT cod_loc FROM localidade WHERE cnl_loc=cod_coordenador_implantacao),
    cod_locA_cir    = (SELECT cod_loc FROM localidade WHERE cnl_loc=sigla_localidade_ponta_a),
    cod_locB_cir    = (SELECT cod_loc FROM localidade WHERE cnl_loc=sigla_localidade_ponta_b),
    cod_orgven_cir   = (SELECT cod_orgven FROM Orgao_Vendas WHERE sigla_orgven=cod_coordenador_venda),
    conta_corrente_usr_cir   = cod_conta_corrente,
/*    contrato_cir   = ISNULL(cod_contrato_usuario_circuito,contrato_cir), */
    contrato_especial_cir     = cod_contrato_especial,
    data_contrato_cir  = ISNULL(data_contrato,data_contrato_cir),
    data_cadastro_cir  = data_entrada_contrato,
    data_prazo_entrega_cir      = case  when data_prazo_entrega_cir is null then data_previsao_ativacao
     when data_prazo_entrega_cir <> cast(data_previsao_ativacao as datetime) then data_previsao_ativacao
      else data_prazo_entrega_cir
      end,
    data_prazo_entrega_original_cir =  case when final_stat = 1 then null
     else
         case when data_prazo_entrega_cir is not null
      and data_prazo_entrega_cir < cast(data_previsao_ativacao as datetime)
      and data_prazo_entrega_original_cir is null then data_prazo_entrega_cir
         else data_prazo_entrega_original_cir
         end
     end,
    data_primeiro_reaprazamento_cir =  case when final_stat = 1 then null
     else
         case when data_prazo_entrega_cir is not null
      and data_prazo_entrega_cir < cast(data_previsao_ativacao as datetime)
      and data_prazo_entrega_original_cir is not null
      and data_primeiro_reaprazamento_cir is null then data_prazo_entrega_cir
         else data_primeiro_reaprazamento_cir
         end
     end,
    data_alt_data_prazo_entrega_cir = case when final_stat = 1 then null
     else data_alt_data_prazo_entrega_cir
     end,
    resp_alt_data_prazo_entrega_cir = case when final_stat = 1 then null
     else resp_alt_data_prazo_entrega_cir
     end,
    contrato_cir  = cod_contrato_usuario_circuito,
    cod_pendA_cir   =  (SELECT cod_pend FROM Pendencia WHERE sigla_pend=cod_pendencia_ponta_a),
    cod_pendB_cir   =  (SELECT cod_pend FROM Pendencia WHERE sigla_pend=cod_pendencia_ponta_b),
    cod_pendpri_cir   =  (SELECT cod_pendpri FROM Pendencia_Prioritaria WHERE cod_origem_pendpri=cod_pendencia_prioritaria),
    cod_ofen_cir    =  (SELECT cod_ofen FROM Ofensor WHERE sigla_ofen=cod_ofensor_circuito),
    cod_veloc_cir   =  (SELECT cod_veloc FROM Velocidade WHERE valor_veloc=cod_velocidade),
    data_disp_tec_cir   = DATA_DISPONIBILIDADE_TECNICA,
    data_tec_sem_previsao_cir  = DATA_INVIABILIDADE_TECNICA,
    cod_provA_cir   = (SELECT cod_prov FROM Provedor WHERE cod_prov=COD_PROVEDOR_PONTA_A),
    num_pedido_acessoA_cir  = NUM_PEDIDO_ACESSO_PONTA_A,
    data_pedido_acessoA_cir  = DATA_PEDIDO_ACESSO_PONTA_A,
    cod_tipacesA_cir  = (SELECT cod_tipaces FROM Tipo_Acesso WHERE sigla_tipaces=TIPO_ACESSO_PONTA_A),
    num_acessoA1_cir   = NUM_ACESSO_PONTA_A,
    data_aceiteA_cir   = DATA_ACEITE_PONTA_A,
    enderecoA_cir   = DESCR_ENDERECO_PONTA_A,
    cod_provB_cir   = (SELECT cod_prov FROM Provedor WHERE cod_prov=COD_PROVEDOR_PONTA_B),
    num_pedido_acessoB_cir  = NUM_PEDIDO_ACESSO_PONTA_B,
    data_pedido_acessoB_cir  = DATA_PEDIDO_ACESSO_PONTA_B,
    cod_tipacesB_cir  = (SELECT cod_tipaces FROM Tipo_Acesso WHERE sigla_tipaces=TIPO_ACESSO_PONTA_B),
    num_acessoB1_cir   = NUM_ACESSO_PONTA_B,
    data_aceiteB_cir   = DATA_ACEITE_PONTA_B,
    enderecoB_cir   = DESCR_ENDERECO_PONTA_B,
    terminal_cir    = COD_NUM_TERMINAL,
    data_alocacao_cir   = DATA_FIN_ALOCACAO,
    data_configuracao_cir  = DATA_FIN_CONFIGURACAO,
    indic_reaprov_facilA_cir  = INDIC_REAPR_ACESSO_PONTA_A,
    indic_reaprov_facilB_cir  = INDIC_REAPR_ACESSO_PONTA_B,
    indic_situacao_pendencia_cir  = INDIC_SITUACAO_PENDENCIA,
    indic_tipo_pendencia_cir  = INDIC_TIPO_PENDENCIA,
    cod_agente_execa_cir = (case when final_stat = 1 then null
            else cod_agente_execa_cir
                         end),

 

    cod_agente_execb_cir = (case when final_stat = 1 then null
            else cod_agente_execb_cir
                         end),

 

    cod_agente_coord_cir = (case when final_stat = 1 then null
            else cod_agente_coord_cir
                         end),

 

    cod_acao_cir   = (CASE indic_tipo_pendencia
          WHEN 1 THEN 1
      WHEN 5 THEN 1
      WHEN 2 THEN CASE indic_acao
        WHEN ‘F’ THEN 2
        WHEN ‘V’ THEN 3
        WHEN ‘E’ THEN 4
        ELSE 6
       END
    END),
/*
    cod_stat_cir   = (CASE indic_situacao_pendencia
     WHEN 1 THEN
      CASE
       WHEN ((cod_tiporgven_orgven=2)
       AND (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>30)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
       WHEN ((cod_tiporgven_orgven=1)
       AND (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>45)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
      ELSE
       1 — PENDENTE
         END
     WHEN 2 THEN 3 — DISPONIBILIZADO
     WHEN 3 THEN 2 — INVIAVEL
     WHEN 4 THEN 9 — INTERROMPIDO
     WHEN 5 THEN 14 — ATIVADO COMERCIAL
        END),
*/
    cod_stat_cir   = (CASE indic_situacao_pendencia
     WHEN 1 THEN
      CASE
       WHEN ( (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>30)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
      ELSE
       1 — PENDENTE
         END
     WHEN 2 THEN 3 — DISPONIBILIZADO
     WHEN 3 THEN 2 — INVIAVEL
     WHEN 4 THEN 9 — INTERROMPIDO
     WHEN 5 THEN 14 — ATIVADO COMERCIAL
        END),
   cod_veloc_anterior_cir= (SELECT cod_veloc FROM Velocidade WHERE valor_veloc=veloc_anterior),
   indic_tipo_servico= CP.indic_tipo_servico,
   cod_degrau= CP.cod_degrau,
   data_aditivo_cir=CP.data_aditivo_cir,
   indic_tipo_cliente_cir = CP.indic_tipo_cliente_cir,
   migrado_cir = CP.migrado,
   sinergia_novo = CP.sinergia_novo,
   backbone_acesso = CP.backbone_acesso,
   proposta_claro = CP.proposta_claro,
   designacao_associada = CP.designacao_associada,
   solicitacao_associada = CP.solicitacao_associada,
   designacao_claro = CP.designacao_claro,
   projeto_claro = CP.projeto_claro,
   data_inicio_faturamento_claro = CP.data_inicio_faturamento_claro
    FROM Circuito_Pendentes CP, status
    WHERE (ots_ativacao_vsat_cir=ots_ativacao_vsat)
    and (cod_stat = cod_stat_cir)
    and (cod_servico=300)
    and (cod_ser_cir=300)
    and (fonte_cir=0)
    and (cod_acao_cir  <> 7 or cod_acao_cir is null)  –Ação Desativar