Blog – Fabiano Neves Amorim

Recentemente vi uma pergunta bem interessante, e vou publicar aqui também, pode ser que você já tenha visto, portanto se esse for o caso não estrague a brincadeira com a solução nos comentários ok? J Valeu.

Vamos lá, vou colocar um script e fazer uma pergunta e o leitor que conseguir me dar a melhor resposta ganhará um.. ummm….. super parabéns….

Eu irei analisar as respostas e publicar o nome do Leitor aqui no Blog, ao responder envie um e-mail para mim no fabiano_amorim( at )bol.com.br com o maior número de detalhes que você puder levantar em relação a questão abaixo.

Vamos lá,

O problema é o seguinte, eu tenho um índice em uma tabela e o SQL não o utiliza, porém se eu trocar a coluna do índice e colocar na clausula INCLUDE ele passa a usar o índice, o que eu quero saber é o porque deste comportamento.

USE TEMPDB

GO

DROP TABLE TABTeste

CREATE TABLE TabTeste(ID     Int Identity(1,1) Primary Key,

                      Nome1  VarChar(4) NOT NULL,

                      Valor1 Int NOT NULL)

GO

DECLARE @i INT

SET @i = 0

WHILE (@i < 1000)

BEGIN

    INSERT INTO TabTeste(Nome1, Valor1)

    VALUES(‘aaaa’, 0) 

    SET @i = @i + 1

END;

GO

Na consulta abaixo o SQL tem todos os valores da consulta no índice, já que a coluna ID é o índice cluster, portanto ele faz parte do índice nonclustered.

Então porque o SQL não utiliza o índice por Nome1 e Valor1? Neste caso o SQL não deveria utilizar o índice?

CREATE NONCLUSTERED INDEX ix_TesteSem_Include ON TabTeste(Nome1, Valor1)

GO

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Se eu recriar o índice, mas desta vez incluindo a coluna Valor1 como INCLUDE ele passa a utilizar o índice, porque?

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

Muito boa pergunta eim!

Relembrando respondam no meu e-mail, em breve estarei publicando a resposta…

  Concatenation

O operador de hoje é o concatenetion, seu comportamento é bem simples, ele recebe vários inputs, concatena tudo e retorna cada linha lida. Ele pode ser visualizado quando utilizado o comando UNION ALL.

É um exemplo clássico de operadores que recebem mais de um input, e tem mais de um filho.

Antes de iniciar a falar sobre ele vamos entender alguns pontos importantes em relação aos operadores dos Planos de Execução.

Todos os operadores executam os mesmos comandos de Init(), GetNext() e Close(), e estes operadores podem receber um ou mais filhos como input, vamos ver alguns exemplos.

Script para criação de tabela de teste.

USE tempdb

GO

CREATE TABLE TABTeste(ID   Int Identity(1,1) PRIMARY KEY,

                      Nome VarChar(250)      DEFAULT NewID())

GO

SET NOCOUNT ON

GO

INSERT INTO TABTeste DEFAULT VALUES

GO 10000

O comando acima cria uma tabela e inclui 10000 registros.

SELECT *

  FROM TABTeste a

 INNER JOIN TABTeste b

    ON a.ID = b.ID

Plano de execução estimado:

SELECT *     FROM TABTeste a   INNER JOIN TABTeste b      ON a.ID = b.ID

  |–Merge Join(Inner Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[TABTeste].[ID] as [b].[ID]=[tempdb].[dbo].[TABTeste].[ID] as [a].[ID]))

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD] AS [b]), ORDERED FORWARD)

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD] AS [a]), ORDERED FORWARD)

Como podemos observar, na consulta acima o SQL utilizou o operador de MERGE para fazer a operação de Join entre as tabelas do select, e neste caso ele está recebendo dois filhos(duas vezes a tabela TABTeste) como input.

O operador de hoje, o Concatenation é um exemplo de operadores que tem mais de um filho como input.

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

SELECT * FROM TABTeste  UNION ALL   SELECT * FROM TABTeste  UNION ALL   SELECT * FROM TABTeste  UNION ALL   SELECT * FROM TABTeste

  |–Concatenation

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD]))

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD]))

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD]))

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC2743869CDD]))

O operador Concatenation recebe o resultado dos scans efetuados pelo Clustered Index Scan e copia as linhas lidas para o Outupt, e repete este processo para todos os Inputs.

O SQL irá executar este processo na ordem que é exibido o
plano, neste caso, o primeiro scan é o acima e o ultimo é o abaixo.

  Compute Scalar

O operador de hoje, é o compute scalar, e ele esta presente na maioria dos planos de execução que vemos.

Como o próprio nome já diz o Compute Scalar efetua um determinado calculo e retorna o valor calculado, este calculo pode ser uma conversão, uma concatenação e outros…

Quase sempre ele é passado despercebido por apresentar um custo mínimo em relação a query, mas ele pode ser um ponto de atenção quando estamos falando de cursores, e loops muito grandes. Principalmente se estiver com problema de CPU.

Por exemplo, utilizar o Compute Scalar para fazer uma simples conversão pode ser tranqüilo, mas execute isso 1 milhão de vezes e verá que se você conseguir evitar este passo terá um ganho de CPU, e por conseqüência terá ganho de tempo.

USE tempdb

GO

CREATE TABLE TABTeste(ID   Int Identity(1,1) PRIMARY KEY,

                      Nome VarChar(250)      DEFAULT NewID())

GO

SET NOCOUNT ON

GO

INSERT INTO TABTeste DEFAULT VALUES

GO 10000

O comando acima cria uma tabela e inclui 10000 registros.

Agora vamos fazer um loop para o SQL passar pelo código 1 milhão de vezes.

DECLARE @I Int

SET @I = 0

WHILE @I < 1000000

BEGIN

  IF EXISTS(SELECT ID FROM TABTeste WHERE ID = @I)

  BEGIN

    PRINT ‘Entrou no IF’

  END

  SET @I = @I + 1;

END

GO

Plano de execução estimado:

 Repare que o SQL Server utilizou o Compute Scalar, vamos olhar melhor no plano em modo texto.

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

        |–Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))

             |–Constant Scan

             |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED FORWARD)

Conforme podemos observar o Compute Scalar foi Utilizado para verificar se o Nested Loop retornou alguma coisa ou não, ou seja, ele está fazendo o papel do IF EXISTS…

O resultado de execução da query e o uso de CPU está na imagem abaixo, gerada pelo Profiler.

Agora vamos alterar o código para remover o uso do Compute Scalar

DECLARE @I Int, @Var Int

SET @I = 0

WHILE @I < 1000000

BEGIN

  SELECT @Var = ID FROM TABTeste WHERE ID = @I

  IF @@ROWCOUNT > 0

  BEGIN

    PRINT ‘Entrou no IF’

  END

  SET @I = @I + 1;

END

GO

Plano de execução estimado:

       |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED FORWARD)

Desta vez o SQL não utilizou o Compute Scalar, vamos ver o resultado no profiler.

Como você pode observar o SQL utilizou menos CPU e terminou a execução em nos tempo. Não estou querendo mostrar qual é a melhor forma de fazer uma validação, apenas que exibir o uso do operador. De qualquer forma, fica ai a dica do uso da @@RowCount. Eu já mudei um código onde tinha uns 30 IF EXISTS dentro de um loop desses e o resultado foi bem satisfatório.

Vamos ver mais do Compute Scalar na pratica.

DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)

SELECT ‘Fabiano’ + ‘ – ‘ + ‘Amorim’ FROM @Tab

SELECT ‘Fabiano’ + ‘ – ‘ + ‘Amorim’ FROM @Tab

  |–Compute Scalar(DEFINE:([Expr1003]=’Fabiano – Amorim’))

       |–Clustered Index Scan(OBJECT:(@Tab))

O plano gerado foi exatamente para fazer a concatenação, simples não?

Agora vamos ver um comportamento bem interessante do Compute Scalar, que mudou a partir do SQL Server 2005.

Imagine o seguinte código:

DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)

DECLARE @ID_Int Integer

SELECT *

  FROM @Tab

 WHERE ID = @ID_Int

Repare que a coluna ID é do tipo SmallInt e a variável @ID_Int é do tipo Integer, ou seja o SQL terá que converter o valor de @ID_Int para poder efetuar a comparação com a coluna ID.

No SQL Server 2000 ele gera o seguinte plano:

SELECT *    FROM @Tab   WHERE ID = @ID_Int

  |–Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))

       |–Compute Scalar(DEFINE:([Expr1002]=Convert([@ID_Int])-1, [Expr1003]=Convert([@ID_Int])+1, [Expr1004]=If (Convert([@ID_Int])-1=NULL) then 0 else 6|If (Convert([@ID_Int])+1=NULL) then 0 else 10))

       |    |–Constant Scan

       |–Clustered Index Seek(OBJECT:(@Tab), SEEK:(@Tab.[ID] > [Expr1002] AND @Tab.[ID] < [Expr1003]),  WHERE:(Convert(@Tab.[ID])=[@ID_Int]) ORDERED FORWARD)

Uau, que trampo eim? Agora vamos ver o que acontece se eu rodar este código no SQL 2005 ou 2008.

Plano de execução no SQL Server 2005

SELECT *    FROM @Tab   WHERE ID = @ID_Int

  |–Clustered Index Seek(OBJECT:(@Tab), SEEK:([ID]=[@ID_Int]) ORDERED FORWARD)

Aaaa, agora sim… Ficou fácil não? Ué mas o SQL não precisa mais converter o valor?

Vamos dar uma olhada melhor nos hints do operador Clustered Index Seek.

O time de desenvolvimento do Query Optimizer mudou um pouco a coisa, agora o SQL utiliza a função “Scalar Operator” para converter o valor de @ID_Int para o tipo da coluna ID.

Interessante…

Por hoje é só, até segunda-feira com o próximo Operador do dia.

O operador de hoje, é um dos mais famosos, e comuns de visualizarmos nos planos de execução.

Eles mudaram bastante conforme o tempo, no SQL 2000 ele se chamava BookMark Lookup, já no SQL 2005 SP2 e 2008 ele é conhecido como Key Lookup. No modo texto do plano de execução ele é apresentado como Index Seek.

 Ícone no SQL Server 2005 e 2008.

 Ícone no SQL Server 2000.

O Key Lookup é utilizado em tabelas que contem um índice Cluster, o SQL utiliza este operador para localizar dados em um índice cluster, dados estes que não pertencem a um determinado índice nonclustered.

Eu escrevi um artigo falando sobre sua funcionalidade e com alguns exemplos no link abaixo.

Exibir artigo…

Eu mencionei que ele é famoso porque a bastante coisa sobre ele na internet.

Quando você encontrar ele em um plano de execução, de cara você sabe que tem uma chance de otimizar a consulta criando um Covered Index, ou seja, criar um índice nonclustered que irá compor todas as colunas do seu select.

Funcionamento:

Vamos usar o seguinte script.

CREATE TABLE TabTeste (ID Int PRIMARY KEY, Nome VarChar(80), SobreNome VarChar(80))

CREATE NONCLUSTERED INDEX ix_Teste ON TABTeste(Nome)

SELECT * FROM TabTeste WITH(index = ix_Teste)

WHERE Nome = ‘Fabiano’

SELECT * FROM TabTeste WITH(index = ix_Teste)  WHERE Nome = ‘Fabiano’

  |–Nested Loops(Inner Join, OUTER REFERENCES:([dbo].[TabTeste].[ID]))

       |–Index Seek(OBJECT:([dbo].[TabTeste].[ix_Teste]), SEEK:([dbo].[TabTeste].[Nome]=’Fabiano’) ORDERED FORWARD)

       |–Clustered Index Seek(OBJECT:([dbo].[TabTeste].[PK__TabTeste__3214EC27131C685F]), SEEK:( [dbo].[TabTeste].[ID]= [dbo].[TabTeste].[ID]) LOOKUP ORDERED FORWARD)

Na consulta acima, o SQL irá utilizar o índice ix_teste para pegar os dados da tabela TabTeste, porém no índice ix_teste eu só tenho os dados das colunas Nome, e ID (todo índice nonclustered também tem a chave do índice cluster), mas na consulta estou pedindo todas as colunas (*).

Neste  caso o SQL irá ler as colunas ID e Nome do índice ix_teste e o valor de ID e Sobrenome do índice Cluster que é a PK. Depois de ler os valores, ele pega os 2 ID e faz um Join para “juntar” qual Sobrenome pertence a qual ID e Nome, por fim retornando os dados para o Select.

Vou tentar falar de 1 operador para cada dia, vamos todos torcer para que eu lembre e consiga ter tempo para isso, vamos lá.

Para não ficar falando por 1 ano sobre os operadores, vou falar apenas daqueles operadores mais comuns, o primeiro é o Assert.

O Assert é utilizado para validar uma determinada condição, ele valida se uma Constraint não foi violada, por exemplo uma contraint Check que define que só pode haver 2 valors, o assert irá validar se o valor que foi passado como entrada está de acordo com a constraint. 

Vamos ver alguns exemplos para entendermos melhor.

Assert validando Check Constraints:

CREATE TABLE Tab1(ID Int IDENTITY(1,1) PRIMARY KEY, Sexo Char(1))

GO

ALTER TABLE TAB1 ADD CONSTRAINT ck_Sexo_M_F CHECK(Sexo IN(‘M’,‘F’))

GO 

INSERT INTO Tab1(Sexo) VALUES(‘X’)

GO

O plano de execução para o insert é o seguinte,

INSERT INTO Tab1(Sexo) VALUES(‘X’)

  |–Assert(WHERE:(CASE WHEN [rd_des_176].[dbo].[Tab1].[Sexo]<>’F’ AND [rd_des_176].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

       |–Table Insert(OBJECT:([rd_des_176].[dbo].[Tab1]), SET:([rd_des_176].[dbo].[Tab1].[Sexo] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@1],0)))

Como podemos observar o plano utiliza o Assert para verificar se o valor que está sendo inserido não viola a contraint,

Se o valor for diferente de ‘F’ e diferente de <> ‘M’ então retorna 0 senão retorna NULL,

O Assert é programado para gerar um erro caso o seu valor de saída seja diferente de NULL, o seja, o valor não é nem ‘F’ e nem ‘M’.

Assert validando Foreign Keys:

ALTER TABLE Tab1 ADD ID_TipoSexo Int

GO

CREATE TABLE Tab2(ID Int IDENTITY(1,1) PRIMARY KEY, Sexo Char(1))

GO

INSERT INTO Tab2(Sexo) VALUES(‘F’)

INSERT INTO Tab2(Sexo) VALUES(‘M’)

INSERT INTO Tab2(Sexo) VALUES(‘I’)

GO

ALTER TABLE TAB1 ADD CONSTRAINT fk_Tab2 FOREIGN KEY (ID_TipoSexo) REFERENCES Tab2(ID)

GO

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES(4, ‘X’) 

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES(4, ‘X’)

  |–Assert(WHERE:(CASE WHEN NOT [Pass1009] AND [Expr1008] IS NULL THEN (0) ELSE NULL END))

       |–Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[Tab1].[ID_TipoSexo] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[Tab1].[ID_TipoSexo]), DEFINE:([Expr1008] = [PROBE VALUE]))

            |–Assert(WHERE:(CASE WHEN [tempdb].[dbo].[Tab1].[Sexo]<>’F’ AND [tempdb].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

            |    |–Clustered Index Insert(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]), SET:([tempdb].[dbo].[Tab1].[ID_TipoSexo] = [@1],[tempdb].[dbo].[Tab1].[Sexo] = [Expr1004],[tempdb].[dbo].[Tab1].[ID] = [Expr1003]))

            |         |–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@2],0)))

            |              |–Compute Scalar(DEFINE:([Expr1003]=getidentity((1856985942),(2),NULL)))

            |                   |–Constant Scan

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Tab2].[PK__Tab2__3214EC27755C58E5]), SEEK:([tempdb].[dbo].[Tab2].[ID]=[tempdb].[dbo].[Tab1].[ID_TipoSexo]) ORDERED FORWARD)

Desta vez podemos ver o operador Assert duas vezes, primeiro validando a Check Constraint, e depois ele pegando o resultado do Join da Tab1 com a Tab2 e utilizando o mesmo controle de 0 continua a execução ou NULL gera erro. O interessante aqui é que o “[Expr1008] IS NULL”, pois se o valor passado para a coluna ID_TipoSexo for NULL então não pode gerar erro, neste caso ele retornaria o 0, e continuaria o processo.

Se você executar o INSERT o SQL irá reclamar do valor do X, se você alterar o X para F, ele irá reclamar do valor do 4, se você trocar o 4 para NULL, 1, 2 ou 3 o insert irá ser executado com sucesso.

Assert validando SubQuery:

O Assert também faz a validação de uma SubQuery por ex, sabemos que uma SubQuery Scalar não pode retornar mais de um valor, mas nem sempre isso acontece, o responsável por validar se a SubQuery Scalar retornou mais de um valor é o Assert.

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), ‘F’)

  INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), ‘F’)

  |–Assert(WHERE:(CASE WHEN NOT [Pass1016] AND [Expr1015] IS NULL THEN (0) ELSE NULL END))

       |–Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[Tab1].[ID_TipoSexo] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[Tab1].[ID_TipoSexo]), DEFINE:([Expr1015] = [PROBE VALUE]))

            |–Assert(WHERE:([Expr1017]))

            |    |–Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [tempdb].[dbo].[Tab1].[Sexo]<>’F’ AND [tempdb].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

            |         |–Clustered Index Insert(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]), SET:([tempdb].[dbo].[Tab1].[ID_TipoSexo] = [Expr1008],[tempdb].[dbo].[Tab1].[Sexo] = [Expr1009],[tempdb].[dbo].[Tab1].[ID] = [Expr1003]))

            |              |–Top(TOP EXPRESSION:((1)))

            |                   |–Compute Scalar(DEFINE:([Expr1008]=[Expr1014], [Expr1009]=’F’))

            |                        |–Nested Loops(Left Outer Join)

            |                             |–Compute Scalar(DEFINE:([Expr1003]=getidentity((1856985942),(2),NULL)))

            |                             |    |–Constant Scan

            |                             |–Assert(WHERE:(CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END))

            |                                  |–Stream Aggregate(DEFINE:([Expr1013]=Count(*), [Expr1014]=ANY([tempdb].[dbo].[Tab1].[ID_TipoSexo])))

            |                                       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]))

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Tab2].[PK__Tab2__3214EC27755C58E5]), SEEK:([tempdb].[dbo].[Tab2].[ID]=[tempdb].[dbo].[Tab1].[ID_TipoSexo]) ORDERED FORWARD)

Na consulta acima, o SQL gerou um Stream Aggregate para calcular quantas linhas a subquery irá retornar, depois o Assert pegou este resultado o fez a sua analise.

É interessante ver como o Query Optimizer é esperto o suficiente para não utilizar operadores quando eles não são necessários, por exemplo:
INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1 WHERE ID = 1), ‘F’)

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT TOP 1 ID_TipoSexo FROM Tab1), ‘F’)

Nas consultas acima, o Query Optimizer é esperto o suficiente para saber que somente uma linha será retornada, portanto não há necessidade de uso do Assert.

É Isso ai pessoal por hoje é só.

Até amahã.