Queries do Dia a Dia: Rotina para criar um Histórico de Utilização de Índices
Olá Pessoal,
Muitos de vocês já devem ter visto que a DMV sys.dm_db_index_usage_stats retorna a utilização dos índices da nossa base de dados.
Quem já assistiu minha palestra Me tornei um DBA, o que devo aprender para ontem?, ou participou do meu Treinamento de Tarefas do Dia a Dia de um DBA, já me viu falando dessa DMV.
Essa DMV é SENSACIONAL!!!
Com essa informação, podemos excluir aqueles índices criados há 10 anos e que ninguém tinha coragem de excluir com medo de impactar no ambiente.
Agora vocês sabem se o índice é utilizado e decidem se podem excluir ou não.
Para excluir um índice você tem que monitorar PELO MENOS 1 mês se esse índice está sendo utilizado para algo importante.
O problema é que essa DMV é reiniciada toda vez que o SQL Server é reiniciado.
E tem mais…
Muitos não sabem, mas no SQL Server 2012 e 2014 ela também é reiniciada quando realizamos um REBUILD no índice. Então, sua rotina de desfragmentação de índices também pode atrapalhar na análise de quantas vezes o índice está sendo utilizado.
Esse problema de restart da dmv na execução do REBUILD foi resolvido no SQL Server 2016 conforme post abaixo:
Como resolver isso e ter certeza da quantidade de vezes que seu índice está sendo utilizado?
R: Criando um histórico de utilização de índices.
Basta abrir o arquivo abaixo na base que deseja criar esse log e executar:
O script vai criar as tabelas abaixo:
- BaseDados
- Tabela
- Servidor
- Historico_Utilizacao_Indices
A view para facilitar a visualização das informações:
- vwHistorico_Utilizacao_Indice
E a procedure que fará a carga dos dados nas Tabelas:
- stpCarga_Utilizacao_Indice
Depois de executar o script, basta criar um job para executar a procedure stpCarga_Utilizacao_Indice diariamente.
Para visualização, criei uma tabela e fiz um insert de 1000 linhas:
CREATE TABLE Teste_Utilizacao_Indice(
Cod INT IDENTITY PRIMARY key,
Dt_Log DATETIME )
INSERT INTO Teste_Utilizacao_Indice(Dt_log)
SELECT GETDATE()
GO 1000
Em seguida executei a procedure de carga:
EXEC stpCarga_Utilizacao_Indice
O resultado pode ser visto na view abaixo:
Você tem a data do log, o Servidor, a Base, a Tabela, o índice, a quantidade de Seek, Scans e Lookup, se o índice já teve atualização e a última vez que esse índice foi utilizado.
Com isso, você consegue monitorar a utilização dos índices nos últimos 30 ou 60 dias (Seek+Scans+Lookup) e analizar se esse índice está valendo a pena manter ou se é melhor excluír.
Já utilizei muito esse histórico de utilização de índices para excluir índices em ambientes críticos.
Agora vocês também podem implementar.
Script simples, mas já está aí prontinho para vocês utilizarem.
Gostou dessa dica?
Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.
Aprenda a criar essa e muitas outras rotinas importantes para o seu dia a dia de DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Abraços,
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Vlw Fabricio, bem legal esse script, obrigado por compartilhar.
Obrigado Reginaldo!
Fabricio,
quando executo a stpCarga_Utilizacao_Indice
ocorre o erro: Msg 208, Level 16, State 1, Procedure stpCarga_Utilizacao_Indice, Line 36
Invalid object name ‘Traces.dbo.Servidor’.
INSERT INTO dbo.Tabela(Nm_Tabela)
SELECT DISTINCT A.Nm_Tabela
FROM ##Historico_Utilizacao_Indices A
LEFT JOIN Traces.dbo.Tabela B ON A.Nm_Tabela = B.Nm_Tabela
WHERE B.Nm_Tabela IS null
Obrigado.
Olá Alexandre,
Fiz um teste e aparentemente o script está correto. Verifica se a tabela “Servidor” está criada na database. Caso não esteja, utilize o código abaixo para criá-la e teste novamente.
CREATE TABLE [dbo].[Servidor](
[Id_Servidor] [int] IDENTITY(1,1) NOT NULL,
[Nm_Servidor] [varchar](50) NOT NULL,
CONSTRAINT [PK_Servidor] PRIMARY KEY CLUSTERED
(
[Id_Servidor] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Abraço.
Existe um erro na consulta de criação dos objetos quando defini Tracers.dbo.Servidor. Quando ajustei a consulta removendo o LEFT JOIN e Tracers a consulta funcionou tranquilamente. Uma pergunta grande Fabricio, primeiramente parabéns. A execução você roda diariamente por qual motivo, pois disse que geralmente analisa as informação do último acesso entre os intervalos de 30 – 60 dias? Abraços!