No primeiro artigo desta série, falei sobre a criação de estatística (automática, explícita e implícita). Agora, vou discorrer sobre o processo de atualização (manual e automática). Apresento detalhes de quando a atualização automática ocorre, mencionando, por exemplo, o custo da recompilação, dentre outras coisas.
Atualização de estatísticas
Um objeto de estatística pode ser atualizado manualmente pelo usuário, através dos comandos SP_UPDATESTATS e/ou UPDATE STATISTICS. Enquanto a procedure SP_UPDATESTATS atualiza todas as estatísticas de um determinado banco de dados, o comando UPDATE STATISTICS atualiza as estatísticas de uma determinada tabela ou, ainda, atualiza pontualmente a estatística de determinado índice. Se o Auto Stats está ativado no banco de dados, que é a configuração default, então o SQL Server irá atualizar as estatísticas automaticamente quando elas forem demandadas pelo otimizador de consulta.
Mas por que estatísticas precisam estar atualizadas? Ora, estatísticas desatualizadas após uma série de INSERTs, UPDATEs e DELETEs na coluna podem não refletir uma distribuição de dados verdadeira. Isso significa que, quando a estatística for requisitada para uso, o otimizador de consulta poderá produzir, como consequência, um plano de execução ruim, impactando na performance de execução da consulta. Manter estatísticas atualizadas é uma das regras de ouro na busca por performance.
Atualização automática
Existem dois modos de atualização automática: o modo síncrono e o modo assíncrono. Por default, o Auto Stats está habilitado e utiliza o modo síncrono. No modo automático de atualizações síncronas, as estatísticas podem ser automaticamente atualizadas na fase de otimização (geração de um plano de execução) ou na fase de execução de um plano compilado (plano que já existia em cache).
- Fase de otimização
Quando o otimizador recebe uma query, ele verifica se existe um plano em cache que seja compatível com a instrução recebida. Caso não exista, então ele fará a otimização, construindo um plano adequado, compilando-o e gravando-o no plan cache. No momento da otimização, se o otimizador precisar de um objeto de estatística em particular, o mesmo será atualizado, caso não esteja. Isso garante que a query será compilada com estatísticas atualizadas, ou seja, antes de compilar a query, a estatística sofre atualização. No modo assíncrono, a otimização dispara a atualização do objeto de estatística, caso ele esteja desatualizado – mas não aguarda a conclusão do processo de atualização, compilando a query e utilizando as estatísticas antigas. Isso pode ser interessante para work loads com pequenas queries e tabelas muito grandes.
- Fase de execução de plano compilado
Quando o otimizador encontra no plan cache um plano de execução compatível com a instrução recebida, ele simplesmente o encaminha para o próximo componente: query executor. Este, por sua vez, antes de executar efetivamente a instrução, precisa checar se as estatísticas das quais o plano em cache depende estão atualizadas ou não. Caso uma ou mais estatísticas estejam desatualizadas, então o plano é removido do cache, as estatísticas são atualizadas e o plano é recompilado. Na sequência, a consulta é executada pelo query executor. Atualizações de estatísticas nesta fase provocam recompilação de queries.
O tempo gasto para recompilar também representa custo para a performance, especificamente quando há alta quantidade de recompilação no ambiente. Dessa forma, existe um trade off entre manter estatísticas extremamente atualizadas versus o tempo de recompilação. Em outras palavras, o benefício de utilizar planos de execução com estatísticas atualizadas versus o custo de recompilação. Isso precisa ser balanceado pelo DBA no momento em que for implantar uma rotina de atualização manual de estatísticas. A questão é: qual a frequência ideal para a atualização manual?
No próximo tópico, explicaremos o porquê de se adotar uma rotina de atualização manual de estatísticas, bem como sugestão para estimar frequência de atualização.
Por que complementar as atualizações automáticas?
Quando se fala em atualização de estatísticas, não é recomendável confiar exclusivamente na opção AUTO_UPDATE_STATISTICS dos bancos de dados. Na verdade, o período que você vai dar entre as atualizações depende da volatilidade dos seus dados. Dessa forma, é necessário analisar os dados para definir uma frequência de atualização. Uma dica que pode servir como referência geral é analisar a porcentagem de mudanças que a base sofre por dia. Se a tabela sobre de 5 a 10%/dia, então recomendo atualizar as estatísticas diariamente. Se a tabela sobre de 5 a 10%/semana, então recomendo atualizar as estatísticas semanalmente (leia a respeito aqui).
Como regra geral, as estatísticas são atualizadas automaticamente pelo SQL Server quando cerca de 20% dos dados sofrem mudanças (+ um mínimo de 500 linhas). Se você tem uma tabela com 100 milhões de linhas, então o SQL atualizará as estatísticas quando 20.500 linhas (um pouquinho mais de 20%) sofrerem mudanças. Se você tem uma tabela de 10 mil linhas, a atualização acontecerá quando 2.500 linhas sofrerem mudanças. Atente para a quantidade de mudanças que precisou acontecer antes de as estatísticas serem atualizadas. Quanto maior a tabela, maior será a demora para acontecer a atualização. Nesse ponto, podemos concluir que criar uma rotina para atualizar as estatísticas pode contribuir, e muito, na diminuição desse intervalo de defasagem.
Internamente, o SQL não fará a atualização dessas estatísticas imediatamente após a margem mínima de mudanças ter sido alcançada; ao invés disso, as estatísticas atuais serão invalidadas e só serão de fato atualizadas quando o otimizado necessitar de tais estatísticas para atender alguma demanda de usuário.
Mas o que significa “20%” de mudanças dos dados? Isso é baseado em atualizações na coluna ou em inserção de novos registros?
A partir do SQL 2005, “20%” significa “20%” de mudanças na coluna, ou seja, as mudanças não são analisadas pela linha da tabela, mas individualmente por coluna. Assim, o SQL 2005/ 2008 cria um contador de modificações para a coluna, de modo a identificar quando a estatística da coluna deverá ser invalidada. Isso significa alterar, incluir ou deletar valores na coluna (INSERT, UPDATE, DELETE). Para fazer tal controle, o SQL Server utiliza uma tabela interna de sistema, não documentada, chamada “sys.sysrscols” (no SQL 2008). É possível consultar essa tabela somente a partir de uma conexão DAC (utilize SQLCMD –A).
Estatísticas são automaticamente marcadas como inválidas quando as mudanças atingem 20% + 500; elas são atualizadas quando demandadas pelo otimizador.
Como rastrear as mudanças para uma coluna que possui estatística
Utilize a coluna rowmodctr da sys.sysindexes. Ela acumula inserts, updates e deletes desde a última atualização da estatística da coluna. Valores maiores que zero indicam que a estatística sofreu mudanças e, portanto, precisa passar por atualização. Mais adiante, veremos um exemplo de como utilizar a coluna rowmodctr.
Quando as estatísticas são consideradas desatualizadas e quando são atualizadas
- o tamanho da tabela saiu de 0 para > 0 linhas
Quando a estatística foi originalmente criada, a tabela não tinha nenhum registro. Assim, foi criado um objeto de estatística vazio. Isso normalmente acontece quando, no momento da criação de uma tabela, criamos uma primary key ou uma unique key, provocando a criação implícita de estatística (já que o SQL Server cria automaticamente índices para primary key e unique key). Em seguida, após a inserção de alguns registros na tabela, é realizada uma consulta que utiliza o objeto de estatística que até o momento estava vazio.
Pronto: o SQL Server fará a atualização automática porque a estatística se enquadra no Teste 1.
Exemplo:
use TempDB
go
-- Criando uma tabela Vendedor
if OBJECT_ID('dbo.Vendedor') IS NOT NULL
drop table dbo.Vendedor;
create table dbo.Vendedor
( Id int identity not null constraint pk_vendedor primary key,
Nome varchar(50) not null default 'aaaaaaaaaaaa',
Sexo char(1) not null default 'm',
)
go
-- Foi criada uma estatística que ainda não sofreu atualização
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 = 'Vendedor'
/* O comando dbcc show_statistics nos diz que a estatística está vazia. Essa estatística está vazia porque quando a mesma foi criada a tabela estava vazia.
Note que utilizamos a opção with stat_header para exibir apenas o cabeçalho do objeto de estatística */
dbcc show_statistics('Vendedor', 'PK_Vendedor') with stat_header;
-- fazendo apenas um insert de registro
insert dbo.Vendedor default values;
-- uma pesquisa, neste momento, na coluna Id da tabela Vendedor
-- provocará a atualização da estatística
select * from dbo.Vendedor where id = 1;
-- conferindo a atualização
dbcc show_statistics('Vendedor', 'PK_Vendedor') with stat_header;
- Quando acontecer 20% de mudanças + 500
A atualização acontecerá quando, desde a última vez que a estatística foi atualizada, o contador de mudanças atingir 20% + 500.
Exemplo:
use TempDB
go
-- Recriando a tabela Vendedor
if OBJECT_ID('dbo.Vendedor') IS NOT NULL
drop table dbo.Vendedor;
create table dbo.Vendedor
( Id int identity not null constraint pk_vendedor primary key,
Nome varchar(50) not null
)
Go
-- inserindo 10 registros na tabela, de forma aleatória
INSERT INTO dbo.Vendedor
SELECT TOP 10 p.Nome
FROM DBAuxiliar.dbo.Nomes p
ORDER BY NEWID()
-- fazendo uma consulta pela coluna Nome e provocando, assim,
-- criação automática de estatística
select * from dbo.Vendedor v
where v.Nome LIKE 'MARIA%'
-- Foi criada uma estatística para a coluna nome
-- chamada _WA_Sys_00000002_145C0A3F
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 = 'Vendedor'
/* O comando dbcc show_statistics mostra na coluna ROWS a qtde de linhas
que existia na tabela quando a estatística foi criada ou atualizada, isto é, 10 linhas */
dbcc show_statistics('Vendedor', _WA_Sys_00000002_145C0A3F)
with stat_header;
-- A coluna rowmodctr da view de compatibilidade sysindexes
-- indica a quantidade de mudanças (insert, update, delete) desde
-- a última atualização. Neste caso, 0 (zero)
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
where t.name = 'Vendedor'
and i.indid = 2
-- inserindo aleatoriamente 400 novos registros
INSERT INTO dbo.Vendedor
SELECT TOP 400 p.NOME
FROM Sismac.dbo.PESSOAS p
ORDER BY NEWID()
-- Verificando a coluna rowmodctr da sysindexes: 400 mudanças
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
where t.name = 'Vendedor'
and i.indid = 2
-- A estatística ainda não foi atualizada
dbcc show_statistics('Vendedor', _WA_Sys_00000002_145C0A3F)
with stat_header;
Quando a estatística acima será atualizada? Quando atingir 20% de mudanças + 500. Os 20% são calculados sobre a quantidade de linhas que existia quando a estatística foi atualizada pela última vez. No exemplo anterior, foram 10 linhas. Assim, temos: 10 * 20% = 2. Soma-se ao número 2 o valor de 500 modificações. Dessa forma, será necessário pelo menos o total de 502 modificações na coluna para que a estatística seja atualizada. Como já ocorreram 400 modificações, restam apenas 102. Uma fórmula que podemos usar para estimar quando acontecerá a próxima atualização de estatística é:
(Qtde da última atualização * 0.20 + 500) – Qtde de modificações já registradas
Vamos continuar explorando o exemplo anterior, evoluindo-o.
-- Excluindo 110 registros da tabela vendedor
-- O objetivo é incrementar o contador de modificações na sysindexes
delete top (110) dbo.Vendedor
-- Verificando a coluna rowmodctr da view de compatibilidade sysindexes
-- contador de modificações registra 510
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
where t.name = 'Vendedor'
and i.indid = 2
-- Fazendo uma nova consulta pela coluna Nome da tabela Vendedor,
-- obrigando o query processor a atualizar a estatística
select * from dbo.Vendedor v
where v.Nome LIKE CARLA%'
-- A estatística foi atualizada
dbcc show_statistics('Vendedor', _WA_Sys_00000002_145C0A3F)
with stat_header;
Nota
Mesmo atingindo os 20% + 500, o query executor poderá não atualizar as estatísticas, dependendo da distribuição de valores no respectivo objeto de estatística e do valor informado no predicado da consulta.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?