[SQL Server] Desvendando Estatísticas – Parte 1

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:

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 

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

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’

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’ 

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’;

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’;

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’;

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é!

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:

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

IMAGEM – 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

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:

IMAGEM – 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.

IMAGEM – 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)

IMAGEM – 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

IMAGEM – 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

— Cria índice filtrado para a coluna Nome – somente Masculinos

IMAGEM – 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é!