Fala pessoal,

Algum tempo atrás, compartilhei uma experiência que tive no Blog do Fabrício Catae (Blog|Twitter), mas também resolvi deixar registrado por aqui.

Muitos de vocês já utilizaram a dmv sys.dm_db_index_usage_stats para verificar a utilização e atualização dos índices de uma tabela. Também sabemos que essa dmv tem seus dados reiniciados quando o serviço do SQL Server é reiniciado. Assim, como os servidores do meu ambiente de banco de dados possuem uma atualização mensal de segurança, os servidores são reiniciados mensalmente e os dados dessa dmv seriam perdidos. Ou seja, eu só teria essas valiosas informações sobre os índices durante o período de um mês.

Para resolver esse problema, criei uma tabela que armazena diariamente a utilização dos índices. Com isso, posso analisar durante um período muito grande, a utilização dos meus índices antes de excluí-los. Eu já possuo mais de 1 ano de baseline já que o espaço ocupado por essas informações é pequeno.

Para quem quiser possuir um histórico dessas informações, o script abaixo cria uma tabela de histórico e insere as informações de utilização dos índices nessa tabela.

CREATE TABLE [dbo].[Historico_Utilizacao_Indices](
[Id_Historico_Utilizacao_Indices] [int] IDENTITY(1,1) NOT NULL,
[Dt_Historico] [datetime] NULL,
[Nm_Servidor] [varchar](30) NULL,
[Nm_Database] [varchar](30) NULL,
[Nm_Tabela] [varchar](50) NULL,
[Nm_Indice] [varchar](50) NULL,
[User_Seeks] [int] NULL,
[User_Scans] [int] NULL,
[User_Lookups] [int] NULL,
[User_Updates] [int] NULL,
[Ultimo_Acesso] [datetime] NULL )

INSERT INTO Historico_Utilizacao_Indices(Dt_Historico, Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice,  User_Seeks, User_Scans,User_Lookups, User_Updates, Ultimo_acesso)
SELECT getdate(), @@SERVERNAME, db_name(db_Id()), o.Name, i.name, s.user_seeks,s.user_scans,s.user_lookups, s.user_Updates,  isnull(s.last_user_seek,isnull(s.last_user_scan,s.last_User_Lookup)) Ultimo_acesso
FROM sys.dm_db_index_usage_stats s
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
join sys.sysobjects o on i.object_id = o.id
WHERE s.database_id = db_id()
ORDER BY o.Name, i.name, s.index_id

Para armazenar as informações, criei um job que roda essa query de INSERT para cada database que eu defini guardar os históricos.

Assim, eu utilizo essa tabela de histórico para analisar uma possível exclusão dos índices que são pouco utilizados ou que não são utilizados. Agora, compartilhando a experiência que tive, eu possuo uma tabela com muitas consultas e alterações que armazena 50 milhões de registros. Nessa tabela, tenho um índice em um campo chamado Fl_Situacao que pode possuir os valores 0, 1, 2, 3 ou 4.

Sempre acompanhei esse índice e verifiquei que tinha algumas utilizações somente no inicio do mês. Um certo dia, resolvi excluir esse índice seguindo o raciocínio de que o índice era pouco seletivo, a tabela é muito grande e o índice era pouco utilizado, não valendo a pena o custo de manutenção do mesmo. Após excluir o índice, acompanhando meu trace com as querys que demoram mais de 3 segundos, verifiquei que nenhuma query apresentou problema de lentidão.

Show de bola, diminuí uma operação de manutenção de um índice em uma tabela muito utilizada.

Entretanto, no inicio do mês, existia uma query com uma condição “where Fl_Situacao = 2” dentre outras restrições. Quando essa query rodou sem o índice que eu excluí, a mesma fez um clustered index scan nessa tabela, me causando um grande problema de lentidão no banco de dados. Isso aconteceu pois, dos 50 milhões de registros existentes na tabela, apenas 1.000 registros possuíam o campo Fl_Situacao = 2, o que tornava o índice existente nessa coluna extremamente eficiente para essa query.

Resultado, como não dava para alterar a consulta, tive que recriar o índice na mesma noite.

Mais uma vez eu digo, vivendo e aprendendo!!! Meu maior aprendizado está no meu dia a dia de trabalho.

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.

Confira mais experiências do Dia a Dia de um 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