Neste último artigo da série, vou abordar a estrutura interna de um objeto de estatística, dedicando maior atenção ao histograma. Apresento os conceitos de densidade e seletividade, finalizando o artigo com um tópico muito interessante: como o QO utiliza o histograma de uma estatística. Boa leitura.

Entendendo a estrutura de um objeto de estatística

Um objeto de estatística é internamente estruturado em três partes: cabeçalho, vetor de densidade e histograma.

• Para uma visualização completa de um objeto de estatística execute: DBCC show_statistics(‘nome da tabela ou view’, ‘nome da estatística’);
• Para visualização individual de cada parte execute: Cabeçalho DBCC show_statistics(‘nome da tabela ou view’, ‘nome da estatística’) WITH stat_header;
• Vetor de densidade DBCC show_statistics(‘nome da tabela ou view’, ‘nome da estatística’) WITH density_vector;
• Histograma DBCC show_statistics(‘nome da tabela ou view’, ‘nome da estatística’) WITH histogram.

Histograma

O histograma retrata a distribuição de valores da coluna que o objeto de estatística referencia, medindo a frequência de distinção desses valores. Essa informação é essencial para o Query Optimizer (QO) estimar a seletividade e a cardinalidade no momento da geração de um Execution Plan (falaremos mais a respeito em tópico posterior).

O histograma é construído como um conjunto de até 200 passos, sendo que para cada passo é capturado e gravado um valor chave (RANGE_HI_KEY), que passa a funcionar como ponto de starting daquela etapa. Dentro de cada passo, são gravadas informações quantitativas referentes ao intervalo do passo em questão, uma vez que, de forma resumida, um histograma nada mais é do que um conjunto de valores chaves e informações de intervalos entre esses valores.

Para construir o histograma, o SQL Server captura e utiliza uma amostra dos dados da coluna, que ele automaticamente avalia como amostra mais significativa dos dados. Podemos influenciar em como o SQL Server coleta essa amostra, definindo se ele deverá varrer a tabela inteira para produzir a amostra ou simplesmente utilizar um percentual específico (falaremos mais a respeito em tópico posterior). A amostra coletada é então ordenada, permitindo a montagem dos passos.

Vejamos um exemplo com base numa simples tabela de pessoas contendo 500 linhas:

USE tempdb
GO

– cria a tabela pessoa
CREATE TABLE dbo.stspessoa
(
codigo INT NOT NULL PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
sexo VARCHAR(13) NULL
)
GO

– inserindo 500 registros a partir da tabela person.person do ADW2012
INSERT dbo.stspessoa
SELECT TOP 500 p.businessentityid, p.firstname + ‘ ‘ + p.lastname, NULL
FROM adventureworks2012.person.person p
GO

– confere
SELECT * FROM dbo.stspessoa p
ORDER BY p.nome

– cria explicitamente o objeto de estatística
CREATE STATISTICS st_pessoa_nome ON dbo.stspessoa (nome);

– visualizando o histograma da estatística recém criada
DBCC show_statistics(’stspessoa’, st_pessoa_nome) WITH histogram;

O passo 1 tem o valor chave Aaron Adams (coluna RANGE_HI_KEY); o passo 2 tem Abigail Alexander; o passo 3 tem Adam Allen; e assim sucessivamente.

Entre o passo 1 e o 2 (Aaron Adams e Abigail Alexander), existe um intervalo contendo dois registros, conforme coluna RANGE_ROWS do passo 2. Entre passo 2 e 3 (Abigail Alexander e Adam Allen), existe um intervalo contendo um registro, conforme coluna RANGE_ROWS do passo 3. Para visualizarmos esses intervalos, vou fazer um SELECT na tabela stsPessoa e colar abaixo:

Atente para o detalhe de que o passo 1 tem RANGE_ROWS = 0, o passo 2 RANGE_ROWS = 2, o passo 3 RANGE_ROWS = 1 etc. Por definição, o passo 1 sempre terá RANGE_ROWS = 0, pois não existe intervalo anterior a ele.

Voltando nossa atenção à figura do histograma, temos a próxima coluna – EQ_ROWS -, a qual contém a quantidade de valores exatamente iguais aos da chave. Quantidade 1 indica que não há valores duplicados para a chave. A coluna DISTINCT_RANGE_ROWS indica a quantidade de valores distintos dentro do intervalo. Quanto mais valores distintos existirem no decorrer do histograma, maior será a seletividade da coluna. O ideal é que o valor de DISTINCT_RANGE_ROWS seja igual ao de RANGE_ROWS, o que é muito difícil em colunas que guardam nome de pessoas ou produtos, por exemplo. Note que, na figura do histograma, mostrada mais acima, não existe repetição. A coluna AVG_RANGE_ROWS contém a média de valores repetidos dentro do intervalo (fórmula RANGE_ROWS / DISTINCT_RANGE_ROWS). No histograma de exemplo, temos a média 1 dentro de cada intervalo, comprovando que não há repetição.

Resumindo:

RANGE_HI_KEY	Valor que traça o limite superior de uma etapa do histograma. É o valor chave do passo.
RANGE_ROWS	Número de linhas dentro de um intervalo. Os valores são maiores que o valor chave do passo anterior, e menores que o valor chave do passo corrente.
EQ_ROWS	Número de linhas que tem valor igual ao valor chave do passo (RANGE_HI_KEY).
DISTINCT_RANGE_ROWS	Número de valores distintos dentro de um intervalo, excluindo-se o valor chave do passo anterior e o valor chave do passo corrente.
AVG_RANGE_ROWS	Número médio de valores duplicados dentro do intervalo. A média é calculada da seguinte forma: RANGE_ROWS / DISTINCT_RANGE_ROWS

Vejamos agora um exemplo usando uma tabela que guarda números e datas de pedidos de vendas, contendo repetição nas datas.

USE tempdb
GO

– Cria a tabela que guardará os dados dos pedidos
CREATE TABLE dbo.stspedidos
(
idpedido INT NOT NULL PRIMARY KEY,
dtpedido DATETIME NOT NULL
)

– insere 5 mil linhas
INSERT dbo.stspedidos
SELECT TOP 5000 h.salesorderid, h.orderdate
FROM adventureworks2012.sales.salesorderheader h

– cria a estatística
CREATE STATISTICS st_pedidos_data ON dbo.stspedidos (dtpedido);

– exibe o histograma
DBCC show_statistics(’stsPedidos’, st_pedidos_data) WITH histogram;

Veja que entre os passos 3 e 4 existe um intervalo com treze linhas (ou treze valores) e que, dentro desse mesmo intervalo, existem apenas quatro valores distintos, com uma média de repetição de 3,25. Note que no passo 4 existem sete linhas com valores iguais ao da chave, ou seja, existem sete valores iguais a “2005-07-14 00:00:00.000”.

Cabeçalho

Vamos analisar agora o cabeçalho da estatística “ST_Pedidos_Data”.

• Exibe apenas o cabeçalho do objeto de estatística
  DBCC show_statistics(’stsPedidos’, st_pedidos_data) WITH stat_header;

Name	Nome do objeto de estatística.
Updated	Data e hora em que a estatística foi atualizada pela última vez. É possível obter essa informação utilizando também a função STATS_DATE.
Rows	Número total de linhas que existia na tabela quando a estatística foi atualizada pela última vez. Se for uma estatística filtrada, então o número de linhas poderá ser menor que o número de linhas da tabela.
Rows Sampled	Número total de linhas da amostra utilizada para calcular a estatística. Quando Rows Sample é menor que Rows, então o histograma e a densidade são resultados de estimativa baseada na amostragem de um conjunto de linhas. Nota: desde a versão 2005, o SQL Server utiliza o tamanho mínimo de 8 MB ou o tamanho da tabela, se menor, para fazer a amostragem. No nosso exemplo, a tabela é bem pequena, de modo que o SQL utilizou a tabela inteira para fazer a amostragem e calcular a estatística.
Steps	Número de passos no histograma, sendo respeitado o limite de 200.
Density	Este valor de densidade não é utilizado pelo otimizador de consulta e é exibido apenas para compatibilidade com versões anteriores ao SQL Server 2008.
Average Key Length	Tamanho médio em bytes dos valores chaves do histograma (coluna RANGE_HI_KEY).
String Index	Indica (YES ou NO) se o objeto de estatística contém o que é chamado de Estatísticas Sumárias de Caracteres, o que melhora a estimativa de cardinalidade para queries que utilizam o operador LIKE como, por exemplo, WHERE Nome LIKE ‘FERNANDA%’. Estatísticas sumárias de caracteres são armazenadas separadamente do histograma e são criadas para colunas do tipo char, varchar etc.
Filter Expression	Se a estatística for uma estatística filtrada, então será exibida a expressão (predicado) referente ao filtro. Para estatísticas não filtradas será exibido NULL.
Unfiltered Rows	Número total de linhas que existia na tabela sem a aplicação do Filter Expression. Se Filter Expression é nulo, então Unfiltered Rows é igual a Rows.

Vejamos agora o cabeçalho de uma estatística filtrada:

Note que a tabela tem ao todo 58 linhas, conforme Unfiltered Rows. A coluna Rows, por sua vez, exibe a quantidade total de linhas na tabela que atendam ao predicado definido em Filter Expression. Repare, ainda, que o tamanho da amostra é igual ao tamanho da tabela filtrada, isto é, Rows Sampled = Rows.

O Query Optimizer e as estatísticas

Quando e como o otimizador de consultas utiliza as estatísticas? É muito raro o cenário em que o QO não utiliza um objeto de estatística, mesmo até em queries triviais sem cláusula WHERE ou JOIN. Um cenário típico é uma tabela Heap, sem qualquer tipo de índice nonclustered, e que nunca sofreu algum SELECT contendo predicado. Exemplo:

USE tempdb
GO

SET nocount ON
GO

CREATE TABLE dbo.numeros ( numero INT IDENTITY )
GO

INSERT numeros DEFAULT VALUES
GO 300

– Pressionando Ctrl + L => Estimated Execution Plan
SELECT * FROM dbo.numeros

Veja que o QO informou corretamente a quantidade de linhas para a versão estimada do plano de execução, sendo que nesse exemplo a tabela não possui nenhum objeto de estatística. Nesse cenário, o QO buscou a quantidade de linhas da tabela através da view sys.sysindexes, coluna Rows.

SELECT indid, ROWS FROM sys.sysindexes i
WHERE i.id = Object_id(‘Numeros’) AND i.indid = 0

Vamos reconstruir o exemplo, adicionando manualmente uma estatística.

• Criando a estatística
  CREATE STATISTICS st_numero ON dbo.numeros (numero)
• A tabela tinha 300 registros. Vou apagar todos
  DELETE dbo.numeros
• Exibindo o cabeçalho da estatísticas
  DBCC show_statistics(‘numeros’, st_numero) WITH stat_header

Repare que a estatística está desatualizada. Isso aconteceu porque eu criei a estatística antes de deletar os registros da tabela. Vejamos agora o plano estimado para a consulta trivial:

• Pressionando Ctrl + L => Estimated Execution Plan
  SELECT * FROM dbo.numeros

O QO utilizou as informações do cabeçalho da estatística ST_Numero para estimar o tamanho do result set e, infelizmente, o plano estimado não será igual ao plano atual. Esse exemplo comprova que mesmo para queries triviais o QO poderá usar objetos de estatísticas. Vejamos o plano atual para comparação.

Atente para a operação Table Scan: existe divergência entre o Actual Number of Rows e Estimated Number of Rows, exigindo atualização da estatística.

Além de usar uma estatística para estimar o tamanho do result set (o que é conhecido como cardinalidade), o QO também utiliza para estimar tamanhos intermediários, quando a query tem vários operadores no plano de execução, e, sobretudo, para estimar a seletividade, quando a query possui algum predicado. Talvez você já tenha se perguntado: por que o otimizador não usou o índice X da tabela? A resposta para essa questão é: depende da seletividade da sua consulta.

A seletividade está ligada diretamente à quantidade de valores distintos dentro de uma coluna. Quanto mais valores distintos, mais seletiva é a coluna. Para calcular a seletividade de determinada coluna, aplique a seguinte fórmula:

Seletividade = quantidade de valores distintos da coluna/quantidade de valores da coluna

O valor máximo para a seletividade é 1. Quanto mais próximo de 1, melhor. Esse tipo de análise de seletividade é importante quando estamos criando índices. Uma outra maneira de analisar a seletividade é verificar a quantidade de registros a serem retornados para um dado predicado.

USE adventureworks
GO

• A tabela sales.SalesOrderHeader tem 43659 linhas
• Esse select retorna o total de 12 linhas
  SELECT COUNT(*) FROM sales.salesorderheader o WHERE o.customerid = 676
• 12 representa quantos % sobre o total de linhas de SalesOrderHeader
  SELECT 12.0 / 43659.0
• Resposta = 0.00027485% -> está bem abaixo de 1%

Veja que a seletividade para o predicado “o.CustomerID = 676” é alta porque retorna apenas uma pequena quantidade de registros, ou seja, retorna 12 linhas sobre o universo de 43659. Em resumo: quanto menos registros, melhor.

Agora vamos executar essa mesma consulta, usando algumas colunas em vez de COUNT(*) e então verificar o plano de execução:

SELECT o.salesorderid, o.customerid
FROM sales.salesorderheader o
WHERE o.customerid = 676

Veja que o QO fez um Seek, utilizando o índice IX_SalesOrderHeader_CustomerID existente na tabela Sales.SalesOrderHeader.

Densidade, por sua vez, é o oposto da seletividade: quanto maior a densidade, mais registros serão retornados para determinado predicado, o que significa menor seletividade. Portanto, alta densidade significa baixa seletividade e vice-versa.

O Query Optimizer e o histograma

Para entendermos como o QO utiliza o histograma para suas estimativas, vamos usar o exemplo em que criamos a tabela stsPedidos, de 5 mil linhas. Para facilitar, estou exibindo a imagem do histograma dessa tabela novamente.

•  Exemplo 1: Usando o EQ_ROWS
  SELECT * FROM dbo.stspedidos s
  WHERE s.dtpedido = ‘2005-07-09 00:00:00.000?
  OPTION (RECOMPILE)

Na pesquisa do exemplo 1, utilizamos, na cláusula WHERE, uma data que é exatamente o valor chave do passo 3 do histograma – 09/07/2005. Como podemos ver, o QO estimou 6 linhas para a consulta, o que é exatamente igual à coluna EQ_ROWS.

• Exemplo 2: EQ_ROWS + RANGE_ROWS
  SELECT * FROM dbo.stspedidos s
  WHERE s.dtpedido BETWEEN ‘2005-07-09 00:00:00.000? AND ‘2005-07-19 00:00:00.000?
  OPTION (RECOMPILE)

No exemplo 2, utilizamos um range de datas entre 09/07/2005 e 19/07/2005, perfazendo 48 linhas. Note que as datas inicial e final são valores chaves. Para estimar a cardinalidade, o QO realizou a seguinte operação: 6 + 13 + 7 + 14 + 8.

Atente para o detalhe de que a soma começou a partir da coluna EQ_ROWS = 6 do passo 3.

• Exemplo 3: AVG_RANGE_ROWS
  SELECT * FROM dbo.stspedidos s
  WHERE s.dtpedido = ‘20050705?
  OPTION (RECOMPILE)

Nesse último exemplo, fizemos um SELECT utilizando um valor que não está no histograma, ou seja, não é um valor chave. Dessa forma, o QO estimou a cardinalidade utilizando o valor “3,4” da coluna AVG_RANGE_ROWS do passo 3. O valor “3,4” representa a quantidade média de linhas entre os valores chaves 03/07/2005 e 09/07/2005.

Considerações finais

Existem, ainda, outras questões importantes sobre Estatísticas que não foram abordadas nesta série, como por exemplo: o tamanho da amostragem utilizada num processo de criação ou atualização, debatendo quando devemos usar o tamanho default ou fazer um scan na tabela inteira; benefícios ao utilizarmos estatísticas filtradas; criação manual de objetos de estatísticas referenciando mais de uma coluna, objetivando acelerar a performance das consultas; analisando e entendendo as recomendações do DTA para a criação de determinadas estatísticas etc. Enfim, o assunto Estatísticas é algo que merece mais atenção por parte dos profissionais de banco, já que trata-se de um objeto crucial para o otimizador gerar os planos de execução.

Até o próximo artigo!