1:  CREATE PROCEDURE st_proc @Valor Int

   2:  AS

   3:  SELECT *

   4:  FROM TabTeste

   5:  WHERE Valor <= @Valor

Ao executar esta proc, se o valor passado para a variável @Valor for altamente seletivo, ou seja, irá fazer com que a consulta retorne poucas linhas, então é bem provável que o SQL utilize um possível índice na coluna valor e depois faça um bookmark para ler os dados que não estão no índice.

Bom, mas em tempo de compilação(geração do plano) da proc, como o SQL sabe se o valor é bastante seletivo ou não?. Ele lê (sniff) o valor de @Valor e usa este valor para ver a seletividade e cardinalidade nas estatísticas do índice.

Tendo o valor para ser analisado nas estatísticas o SQL pode gerar o plano mais adequado conforme este valor.

Quando o Sniffing não funciona 1

Vamos imaginar a seguinte proc:

   1:  CREATE PROCEDURE st_proc @Valor Int

   2:  AS

   3:  DECLARE @Variavel_Auxiliar Int

   4:  SET @Variavel_Auxiliar = @Valor;

   5:  SELECT *

   6:  FROM TabTeste

   7:  WHERE Valor <= @Variavel_Auxiliar

Quando utilizamos variáveis auxiliares nas procs, em tempo de compilação, o SQL não consegue estimar qual será o valor da variável @Variavel_Auxiliar, portanto ele não tem como fazer as estimativas necessárias para decidir qual plano gerar. No tópico 5 deste post, eu já falei o que o SQL faz quando ele não consegue estimar a cardinalidade de um valor.

Se ele não consegue estimar ele vai, “chutar”, neste caso(sinal de <=) a estimativa será de 30% do tamanho da tabela. E posso te garantir que com uma estimativa de 30% da tabela, com certeza o SQL não vai gerar um BookMark, pois com esta estimativa é mais performático fazer um Scan. Mesmo que este Scan seja para ler apenas 1 linha. Lembre-se que o SQL não sabe que é só 1 linha que será retornada, para ele, será retornada 30% da tabela.

Quando o Sniffing não funciona 2

Vamos imaginar a seguinte proc,

   1:  CREATE PROCEDURE st_proc @Valor Int

   2:  AS

   3:  IF @Valor = 0

   4:  SET @Valor = 10;

   5:  SELECT *

   6:  FROM TabTeste

   7:  WHERE Valor <= @Valor

Este foi o problema que aconteceu com nosso cliente. Havia uma procedure onde o valor do parâmetro de entrada era alterado durante a execução da proc. Se a chamada da procedure fosse @Valor = 0, então o valor seria alterado para 10 fazendo com que a estimativa inicial utilizada pelo SQL Server ficasse incorreta.

Independente de o valor do parâmetro de entrada ser alterado, para a compilação da procedure, o SQL vai utilizar o valor recebido inicialmente, ou seja, o valor informado na execução da procedure. Se eu passar o valor 0, o SQL vai usar este valor para estimar a cardinalidade da coluna. Digamos que esta consulta resulta em uma estimativa de retorno de apenas 1 linha, e o SQL decide utilizar um índice pela coluna valor mais um bookmark. Porem com a alteração do parâmetro a consulta passará a retornar 1000 linhas. Neste cenário teríamos um péssimo plano.

Exemplificando

Para exemplificar os problemas mencionados acima, criei uma tabela chamada TabTeste, com um índice nonclustered na coluna Valor e uma proc que faz alguns selects nesta tabela.

Nesta proc, temos os 3 casos mencionados acima, onde a 1º consulta faz a estimativa correta, a segunda consulta utiliza a variável auxiliar e por fim uma consulta que faz um select utilizando a variável após sofrer uma alteração.

   1:  USE TEMPDB

   2:  GO

   3:  SET NOCOUNT ON;

   4:   

   5:  IF OBJECT_ID('tempdb.dbo.TabTeste') IS NOT NULL

   6:    DROP TABLE TabTeste

   7:  GO

   8:  IF OBJECT_ID('tempdb.dbo.st_Proc_Teste') IS NOT NULL

   9:    DROP PROC st_Proc_Teste

  10:  GO

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

  12:                                            Nome VarChar(200) NOT NULL,

  13:                                            Valor Int NOT NULL)

  14:  GO

  15:  DECLARE @i INT

  16:  SET @i = 0 

  17:  WHILE (@i < 50000)

  18:  BEGIN

  19:    INSERT INTO TabTeste(Nome, Valor)

  20:    VALUES(NEWID(), ABS(CHECKSUM(NEWID()) / 1000000) + 1)

  21:    SET @i = @i + 1 

  22:  END;

  23:  GO

  24:  INSERT INTO TabTeste(Nome, Valor) VALUES(NEWID(), 0)

  25:  INSERT INTO TabTeste(Nome, Valor) VALUES(NEWID(), 0)

  26:  INSERT INTO TabTeste(Nome, Valor) VALUES(NEWID(), 0)

  27:  GO

  28:  CREATE NONCLUSTERED INDEX IX_Index ON TabTeste(Valor);

  29:  GO

  30:  CREATE PROCEDURE dbo.st_Proc_Teste @Valor Int

  31:  AS

  32:  BEGIN

  33:    DECLARE @Variavel_Auxiliar Int

  34:    SELECT @Variavel_Auxiliar = @Valor;

  35:    -- Variável original sem alterar

  36:    SELECT *

  37:    FROM TabTeste

  38:    WHERE Valor <= @Valor

  39:    -- Variável auxiliar

  40:    SELECT *

  41:    FROM TabTeste

  42:    WHERE Valor <= @Variavel_Auxiliar

  43:    IF @Valor = 0

  44:    SET @Valor = 10;

  45:    -- Variável original alterada

  46:    SELECT *

  47:    FROM TabTeste

  48:    WHERE Valor <= @Valor

  49:  END

Vamos executar a proc e visualizar os dados retornados.

EXEC dbo.st_Proc_Teste @Valor = 0

Para as consulta 1 e 2 podemos observar que são retornadas apenas 3 linhas, já que na tabela TabTeste só existem 3 valores onde o valor da coluna “Valor” seja menor ou igual a 0. Já na terceira consulta, o valor utilizado no where foi o valor “10”, já que ele foi alterado em tempo de execução. Neste caso várias linhas serão retornadas. Não apenas 3.

Para as consultas 1 e 2, com certeza fazer utilizar o índice nonclustered e fazer um Bookmark é a melhor opção de acesso aos dados, já para a 3º consulta é bem provável que o ideal seria fazer um Scan já que vários registros serão retornados.

Vamos ver o que aconteceu:

Figura - 1º Plano

Podemos observar que a estimativa de quantidade de linhas que seriam retornadas foi precisa, já foi estimado 3 e o número atual de linhas retornadas também foi 3. Aqui podemos ver que o Sniff foi de grande valor.

Figura - 2º Plano

Aqui já podemos ver que o SQL não utilizou o índice, e sim gerou um Clustered Index Scan, mas porque ele não utilizou o Índice? Bom, a resposta está facil, ele gerou uma estimativa incorreta. Como utilizamos a variável auxiliar o SQL estimou que 30% da tabela ou seja, 15000 linhas seriam retornadas, e para retornar 15 mil linhas compensaria fazer o Scan. Mas podemos observar que a quantidade de linhas atuais é de apenas 3.

Figura - 3º Plano

Já o 3º plano utilizou o Índice gerando assim um Idex Seek, mas perai. Quantas linhas ele estimou? Apenas 3. E quantas foram retornadas? 226. Com certeza neste caso seria melhor ele gerar um Scan do que fazer o Seek + Bookmark.

Ora, mas porque ele gerou o bookmark, isso porque para fazer a estimativa, ele utilizou o valor passado no parâmetro de entrada.

Statistics IO

Para comprovar que o 2º e 3º plano geraram planos incorretos, basta analisarmos a quantidade de IOs gerados para retornar os dados de cada consulta.

Nas consultas 2 e 3, o número de IOs foi bem alto. O número de IOs efetuados pela consulta 2 deveria ser igual ao número da consulta 1, já que ambas retornam os mesmo registros.

Para confirmar se na consulta 3 realmente compensava fazer um Scan ao invéz de um Seek + BookMark, podemos simplesmente olhar quantos IOs foram necessários para fazer um Scan na tabela(consulta 2).

Visualizando os valores utilizados pelos parâmetros

Para confirmar quais valores foram utilizados nas consultas, podemos analisar a propriedade ParameterList nas propriedadeos do plano. Para isso, clique com o botão do lado direito do mouse no operador de select no plano de execução, e escolha a opção “Properties”, depois veja a propriedade “Parameter List“.

Propriedades - 1º Consulta

O valor utilizado no momento de compilação foi o mesmo utilizado no momento de RunTime.

Propriedades - 2º Consulta

O QO não conseguiu utilizar nenhum valor, portanto ele não aparece. Neste caso ele utilizou os 30%.

Propriedades - 3º Consulta

O valor utilizado no momento de compilação foi “0” porem no mento de RunTime o valor mudou para “10”.

Conclusão

Bom galera, fica a dica de quando, como e porque estes problemas são causados. Depois de entendido o problema resolve-los fica bem mais facil.

Abraço.

Galera, fiz as provas do programa de profissional 5 estrelas de SQL Server no TechNet. Fiz o update do meu transcript para conseguir a 5º estrela, já que é necessário provar que você fez o exame 70-431 para obter a 5º estrela. Daqui a 72 horas úteis(nem sei quanto da isso em dias...) a 5º estrela deve aparecer no meu nome.

Segue minhas considerações em relação ao programa.

Bom, a uns 9 anos atrás quando eu ainda estava na fase pesada de estudos de informática, pra mim o programa foi bem interessante, cheguei a fazer várias provas de Segurança, ASP, C#, VB, .NET e etc... Na época isso foi legal e tals, e até que serviu bastante pra me ajudar a estudar sobre as tecnologias MS.

Mas hoje, penso que o programa deveria ser diferente. Se eu parar pra pensar, pra mim, para o que servem estas estrelas?

Acho que pra nada, na melhor das hipóteses para mim serviu como passatempo. Creio que a MS deveria ter um programa de incentivo para profissionais qualificados com 5 estrelas. Existem várias formas de fazer isso, seja divulgando melhor o nome deles, dar prêmios, descontos de softwares, voucher para certificação, convites para eventos, e por ai vai.. pensar em como fazer isso não problema meu é problema deles.. e eles tem pessoal de Marketing muito melhores do que eu para pensar nisso...

Considerações em ao material de estudo.

Os materiais são bem interessantes, mas é pouca coisa. O MSDN e TechNet tem MUITA coisa sobre SQL Server, portanto seria mais um “juntar” tudo e disponibilizar como material. Não precisa criar muita coisa nova, quase tudo já existe.

Por falar em juntar, na minha opinião ter tanta coisa em lugar separados é um grande problema, se é que podemos chamar isso de problema. Não tenho palavras para agradecer, todo o conteúdo que a MS disponibiliza para os profissionais que trabalham com seus softwares mas, isso não esta organizado. Nem um pouco organizado, tem coisa pra todo lado: MSDN, Technet, Support, Blogs, WebCasts, ScreenCasts, PodCasts, RadioCasts, Chats ... se eu fosse listar todos os sites que tenho em favoritos relacionados a estudos daria mais de 200... Ei estou falando só sobre SQL Server, imagina se pegarmos os conteúdos de Win... o dia em que eu tiver tempo vou atualizar minha lista de Blogs e vocês vão entender melhor o que eu estou dizendo.

Considerações em relação as provas.

Para quem fez o exame 70-431 e tem o livro de treinamento, as perguntas chegam a dar vergonha. Simplesmente 80 % das perguntas são exatamente as mesmas feitas no livro. Na minha opinião o famoso ctrl-c ctrl-v entrou em ação. O que é uma pena de ser ver. Aproveitando o pena, outra coisa chata é o título da página de ranking completo, “Untitled Page” sensacional.

Algumas perguntas estão escritas parte em inglês parte em português, por exemplo, pergunta em português e resposta em inglês. Como as perguntas foram traduzidas do livro, algumas foram até traduzidas incorretamente.

Eu não gosto disso. Na minha opinião ou é tudo em inglês ou tudo em português, ok, eu sei que tem algumas palavras ou mesmo expressões que é melhor deixar em inglês, mas como eu disse, isso são “algumas”.

Isso me lembra alguns projetos que trabalhei que envolviam empresas multinacionais. Comumente recebo documentos escritos metade em inglês e a outra metade em espanhol, e as vezes os dois misturados, isso sem contar com os comentários em português.

A sim é uma multinacional, então tudo bem, tudo bem nada. Pra mim é falta de organização, escolhe um idioma e todos tem que usar o mesmo e ponto.

Conteúdo para profissionais que finalizaram as 5º estrelas.

Ok, ganhei a 5º estrela, e agora?

O MSDN e TechNet dão muito valor aos profissionais que estão iniciando, não estou dizendo que isso é ruim, eu me lembro muito bem que um dia eu precisei desse valor. Mas para quem já tem um pouco de experiência com SQL Server, não temos muita coisa avançada. Quantas WebCasts nível 300,400 você já assistiu? Ok talvez algumas, mas com certeza nenhuma em português. Quantos artigos, ou programas são disponibilizados com um conteúdo mais focado para profissionais já certificados, pouquíssimos(no momento não me lembro de nenhum).

Ok, eu sei que alguns profissionais MS estão empenhados em ajudar o conteúdo disponibilizado para a comunidade, eu realmente admiro essa ação, mas sei que sozinhos, conseguem muito pouco.

Sinceramente, eu ADORO quando leio um texto e ao terminar de ler não tenho a menor idéia do que li J. Assim eu leio denovo, e denovo... até entender. Esses tipo de conteúdo que eu quero ver. Quer alguns exemplos?

A look at Virtual Address Space - VAS

How It Works: SQL Server Page Allocations

How It Works: SQLIOSim – Checksums

Analisando o trace flag 1118

Conclusão

Bom galera, a pergunta que fica é: Vale a pena obter as 5 estrelas?

Na minha opinião a resposta é sim. Mas eu realmente gostaria que um dia este meu “sim” soasse como “Cara essa você não pode perder”.

Abraço.

Saldo final do dia 16/02/2009:

Caixa de Entrada            =  0 itens

Itens Enviados no dia   =  128

Itens lidos e Excluídos =  185

Conclusão;

Caraca hoje foi um dia daqueles, já percebi que ia ser assim pois logo quando cheguei e abri o Outlook, vi que no sábado e domingo eu havia recebido 53 e-mails L.

Graças a Deus que no final tudo acabou bem J! Pronto para amanhã voltar a batalha.

Saiu uma atualização do Books Online SQL 2008. Eu já estou baixando e você?