Nesta série de artigos vamos dar um “mergulho profundo” nas Teorias Probabilísticas (mais conhecido como estatísticas) do SQL Server.

Introdução

Estatísticas são utilizadas pelo otimizador de query para estimar a seletividade das expressões e o tamanho dos resultados intermediário e final da query. Boas estatísticas permitem ao otimizador avaliar com precisão o custo de diferentes planos de pesquisa e escolher planos de alta qualidade.

Conceitualmente estatísticas são objetos que contém informações sobre a distribuição de valores em uma ou mais colunas de uma tabela ou view indexada (essa distribuição pode ser visualizada através de um histograma). O otimizador utiliza esses objetos para estimar a cardinalidade (o número de linhas) do resultado da query. Por exemplo, o otimizador poderia usar a estimativa de cardinalidade para escolher o operador Index Seek ao invés de um Index Scan, melhorando a performance da query.

“Um objeto de estatística contém informações de distribuição de valores de uma ou mais colunas de uma tabela ou view indexada”

“O otimizador de consulta utiliza as estatísticas para estimar a seletividade das expressões e a cardinalidade do result set”

Estatísticas Coletadas pelo SQL 2008

Além de utilizar os objetos de estatísticas, o SQL Server também precisa e mantém informações no nível de tabela para serem utilizadas pelo otimizador de consulta durante a estimativa de custo da query, as quais são armazenadas na tabela sysindexes (consulte a view de compatibilidade sys.sysindexes).

Informações armazenadas na sysindexes utilizadas pelo otimizador de consulta:

Coluna Descrição
ROWS Quantidade de linhas na tabela ou índice, sendo:· indid 0 para heap· indid 1 para índice clustered

· indid >= 2 para índices nonclustered

DPAGES Quantidade de páginas ocupadas pela tabela ou índice

Existe, ainda, uma outra coluna muito importante na sysindexes, que é utilizada pelo SQL Server para determinar quando um objeto de estatística precisa ser atualizado (se as atualizações automáticas estiverem ligadas). Trata-se da coluna ROWMODCTR(falaremos mais a respeito em tópico posterior).

Script sys.sysindexes

SELECT i.id    ObjectId,
t.name  TableName,
i.indid Index_Stat_Id,
i.name  Index_Stat_Name,
i.rowmodctr,
i.rows,
i.dpages
FROM   sysindexes i
JOIN sys.tables t
ON i.id = t.object_id 

image

Auto Stats

O SQL Server possui várias funcionalidades para a manutenção de estatísticas dentro dos bancos de dados, sendo a mais importante a capacidade de criar e atualizar as estatísticas automaticamente. Esta feature, também conhecida como Auto Stats, está habilitada por default e deveria ser mantida assim, vez que é a melhor prática para a maioria dos cenários.

Funcionalidades para manutenção

Sys.stats Lista objetos de estatísticas para as tabelas do banco
Sys.Stats_columns Lista as colunas que compõem as estatísticas
SP_helpstats Lista objetos de estatísticas para uma determinada tabela
Sys.sysindexes View de compatibilidade que lista índices e objetos de estatísticas
Sys.indexes View de catálogo que lista os índices
Dbcc show_statistics Exibe informações descritivas e detalhadas para um determinado objeto de estatística
ALTER DATABASE AUTO_CREATE_STATISTICS Habilita / desabilita a criação automática no banco
ALTER DATABASE AUTO_UPDATE_STATISTICS Habilita / desabilita a atualização automática no banco
SP_AUTOSTATS Habilita / desabilita a atualização automática para uma tabela, índice, objeto de estatística ou view indexada
SP_CREATESTATS Cria manualmente estatísticas para todas as colunas de todas as tabelas do banco
SP_UPDATESTATS Atualiza manualmente todas as estatísticas existentes no banco
CREATE STATISTICS Cria manualmente um objeto de estatística
DROP STATISTICS Dropa um objeto de estatística
UPDATE STATISTICS Atualiza manualmente um objeto de estatística

Criação de Estatísticas

Existem diferentes maneiras de criar estatísticas no SQL Server. Se o Auto Stats está habilitado então o SQL Server criará automaticamente, quando precisar, estatísticas sobre colunas individuais de tabelas e/ou views indexadas. Isso ocorre quando uma instrução SELECT, DELETE, UPDATE ou MERGE utiliza alguma coluna como parte de predicado (WHERE ou JOIN) ou em expressões com DISTINCT. Se a coluna utilizada ainda não possui estatística, então o SQL Server criará automaticamente um objeto de estatística para a mesma.

Nota: no cenário de criação automática, explicado acima, o componente do SQL Server responsável por criar estatísticas é o query-processor (processador de query).

Vejamos um exemplo:

USE tempdb
go

— Cria uma tabela para demonstração
CREATE TABLE dbo.pessoa
(
id   INT NOT NULL,
nome VARCHAR(50) NOT NULL,
sexo CHAR(1),
)
go

— insere alguns registros
INSERT INTO dbo.pessoa
VALUES      (1,
‘Ana Maria da Silva’,
‘F’),
(2,
‘Joana Ferreira’,
‘F’),
(3,
‘Cleber da Silva’,
‘M’);

— faz uma pesquisa
SELECT *
FROM   dbo.pessoa
WHERE  sexo = ‘F’

— checa se criou estatística para a tabela pessoa 
EXEC Sp_helpstats
‘dbo.Pessoa’,
‘all’

image

Note que foi criado automaticamente para a coluna Sexo um objeto de estatística. A query abaixo apresenta maiores detalhes sobre a estatística recém criada. Note a coluna AUTO_CREATED. Esta coluna é um bit que indica se a estatística foi criada automaticamente pelo processador de query.

— conferindo a estatística criada automaticamente pelo SQL Server
SELECT t.name                            AS [Table Name],
s.name                            AS [Stat Name],
stats_id                          [Stat Id],
Stats_date(s.object_id, stats_id) AS [Last Updated],
s.auto_created,
s.user_created,
s.has_filter
FROM   sys.stats s
JOIN sys.tables t
ON s.object_id = t.object_id
WHERE  t.name = ‘pessoa’ 

image

A criação de objetos de estatísticas também pode ser feita através das instruções CREATE STATISTICS (conhecida como criação manual ou explícita)e através de CREATE INDEX (conhecida como criação implícita). Nestes casos, é possível criar um único objeto de estatística que faça referência a várias colunas, respeitando-se o limite máximo de 32 colunas por objeto de estatística. A instrução CREATE INDEX, além de criar um índice, implicitamente também cria um objeto de estatística. Se o índice possui um predicado, ou seja, se for um índice filtrado, então a estatística também será filtrada.

Script criação manual de estatística (criação explícita)

— Criando manualmente um objeto de estatística
— apontando para duas colunas ao mesmo tempo
CREATE STATISTICS pessoa_nome_sexo ON dbo.pessoa (nome, sexo);

— conferindo a estatística criada manualmente pelo usuário
SELECT t.name                            AS [Table Name],
s.name                            AS [Stat Name],
stats_id                          [Stat Id],
Stats_date(s.object_id, stats_id) AS [Last Updated],
s.auto_created,
s.user_created,
s.has_filter
FROM   sys.stats s
JOIN sys.tables t
ON s.object_id = t.object_id
WHERE  s.name = ‘Pessoa_Nome_Sexo’;

image

Note que a coluna AUTO_CREATED indica que a estatística não foi automaticamente criada pelo processador de query. Entretanto, a coluna USER_CREATED indica que a estatística foi criada manualmente pelo usuário. Essa coluna sempre terá valor igual a 1 quando a estatística é criada explicitamente pelo usuário através dos comandos CREATE STATISTICS e SP_CREATESTATS.

Script criando implicitamente uma estatística como conseqüência da criação de índice

— Cria índice para a coluna Nome
CREATE NONCLUSTERED INDEX pessoa_nome
ON dbo.pessoa (nome ASC);

— conferindo a estatística criada implicitamente por conta do índice
SELECT t.name                            AS [Table Name],
s.name                            AS [Stat Name],
stats_id                          [Stat Id],
Stats_date(s.object_id, stats_id) AS [Last Updated],
s.auto_created,
s.user_created,
s.has_filter
FROM   sys.stats s
JOIN sys.tables t
ON s.object_id = t.object_id
WHERE  s.name = ‘Pessoa_Nome’;

image

Perceba que as colunas AUTO_CREATED e USER_CREATEDestão, ambas, com valor igual a 0, indicando que a estatística não foi criada automaticamente pelo processador de query e nem explicitamente pelo usuário.

Para finalizar vamos criar uma estatística filtrada. Para tanto, vamos fazer uma criação implícita a partir da criação de um índice filtrado.

— Cria índice filtrado para a coluna Nome – somente Masculinos
CREATE NONCLUSTERED INDEX Pessoa_NomeM
ON dbo.Pessoa (Nome ASC)
WHERE Sexo = ‘M’;

— Cria índice filtrado para a coluna Nome – somente Masculinos
CREATE NONCLUSTERED INDEX Pessoa_NomeM
ON dbo.Pessoa (Nome ASC)
WHERE Sexo = ‘M’;
image

Perceba que a coluna HAS_FILTERtem valor 1, indicando que é uma estatística filtrada.

Recapitulando

Dentro do SQL Server existem três maneiras para a criação de estatísticas:

Automática: quando criada automaticamente pelo query processor

Explícita: quando criada explicitamente pelo usuário (CREATE STATISTICS)

Implícita: quando criada como decorrência da criação de índices

No próximo artigo desta série vou discorrer sobre atualização automática e manual de estatísticas. Até!