Blog – Fabiano Neves Amorim

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 km² que é igual a 21,96 habitantes por km².

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: Constant Folding and Expression Evaluation During Cardinality Estimation

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

I Smell a Parameter!

How to update statistics for large databases

How It Works: Statistics Sampling for BLOB data

Index Statistics

Support WebCast: Effective Indexing and Statistics with SQL 2000