Consultoria DBA SQL Server

Como encontrar as estatísticas que estão desatualizadas no SQL Server.

Problema: Preciso descobrir quais são os objetos que estão com as estatísticas desatualizadas no banco de dados nas últimas 24 horas e se possível objetos que sofreram muitas alterações.

Solução: Para descobrir quais objetos estão com as estatísticas desatualizadas no banco de dados, você pode buscar essa informação na sys.sysindexes e sys.tables. A princípio a minha recomendação é que você mantenha a atualização das estatísticas automática no banco de dados habilitado. Para saber se está bloqueado ou não, basta clicar com o botão direito no banco de dados e selecione propriedades na aba options você encontrará essa informação como está na imagem abaixo:

Auto Statistics True

Agora vamos ao que interessa, afinal quais são as estatísticas que estão desatualizadas na minha base de dados? Execute o script abaixo:

DECLARE @horas int
DECLARE @linhas_alteradas int
DECLARE @comando_update nvarchar(300);

SET @horas=24
SET @linhas_alteradas=1000

--Update all the outdated statistics
DECLARE cursor_estatisticas CURSOR FOR
SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@horas,GETDATE()) 
AND rowmodctr>=@linhas_alteradas 
AND id IN (SELECT object_id FROM sys.tables)
 
OPEN cursor_estatisticas;
FETCH NEXT FROM cursor_estatisticas INTO @comando_update;
 
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 /*Descomente o comando abaixo para executar */
 --EXECUTE (@comando_update);
 PRINT @comando_update;
 
 FETCH NEXT FROM cursor_estatisticas INTO @comando_update;
 END;
 
CLOSE cursor_estatisticas;
DEALLOCATE cursor_estatisticas;
GO

Como vocês podem ver foi criado um cursor que verifica quais são as estatísticas que estão desatualizadas utilizando dois filtros quantidade de linhas alteradas no parâmetro @linhas_alteradas e últimas horas @horas. Neste caso em específico eu listei todas as estatísticas que estão desatualizadas nas últimas 24 horas e em tabelas que sofreram mais de 1000 atualizações. Fique a vontade para alterar esses filtros.
Verificar isso base por base pode ser cansativo não é mesmo?

O que acha de verificar todas as estatísticas desatualizadas em todos os bancos de dados?

Veja no script a seguir:

DECLARE @command varchar(4000) 
SELECT @command = '
DECLARE @horas int
DECLARE @linhas_alteradas int
DECLARE @comando_update nvarchar(400);

SET @horas=24
SET @linhas_alteradas=1000
-- Descomentar para filtrar as bases do sistema 
--IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

USE ? SELECT ''USE ?' + ' UPDATE STATISTICS '' + SCHEMA_NAME() + ''.'' + +OBJECT_NAME(id)+'' ''+name
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@horas,GETDATE()) 
AND rowmodctr>=@linhas_alteradas 
AND id IN (SELECT object_id FROM sys.tables)'
--PRINT @command
EXEC sp_MSforeachdb @command

Eu tirei o cursor e utilizei o select usando a procedure sp_MSforeachdb para executar em todos os bancos de dados.