Guardando o histórico de uso dos índices

Olá Pessoal,

Hoje vou colocar aqui umas das consultas que utilizo bastante nos clientes quando preciso de um histórico de utilização dos índices e a quantidade de utilização de scans, seeks e lookups já realizados.

Primeiramente precisamos de uma tabela onde vamos salvar estes históricos, então vamos criá-lá.

CREATE TABLE dbo.Historico_Uso_Indices(
Database varchar(128) NOT NULL,
table_name varchar(100) NOT NULL,
user_seeks int NULL,
user_scans int NULL,
user_lookups int NULL,
TOTAL_LEITURAS int NOT NULL,
last_user_seek datetime  NULL,
last_user_scan datetime  NULL,
last_user_lookup datetime  NULL,
NOME_INDEX varchar(100) NOT NULL,
DATA_REGISTRO datetime NOT NULL)

Temos a nossa tabela criada o proximo passo é criar a consulta que irá popular essa tabela. A DMV principal aqui será a sys.dm_db_index_usage_stats. Segundo BOL a definição desta DMV é:

Returns counts of different types of index operations and the time each type of operation was last performed.

Como viram a intenção aqui é retornar alguns contadores referentes aos índices do meu banco de dados. Juntamente com essa DMV, foram utilizadas também algumas outras DMV’s somente para deixar mais claro o que eram determinadas informações. Abaixo segue então o segundo passo do nosso script.

insert into Historico_Uso_Indices
select
DB_NAME(database_id) as DB, ob.name as TABLE_NAME,
user_seeks,user_scans,user_lookups,(user_seeks+user_scans+user_lookups) as TOTAL_LEITURAS,
last_user_seek,last_user_scan,last_user_lookup,sx.name as NOME_INDEX,GETDATE() AS DATA_REGISTRO
from sys.dm_db_index_usage_stats as st
join sys.indexes as sx
on st.object_id = sx.object_id and st.index_id = sx.index_id
join sys.objects as ob
on sx.object_id = ob.object_id
where DB_NAME(database_id) not in (‘ReportServerTempDB’,’ReportServer’)
and database_id = DB_ID() — Remova esta linha para pegar de todas as bases de dados da instancia

Como vocês podem ver, ja estou inserindo as informações recuperadas apartir dessa minha consulta. Se desejarem pegar esses dados de todas as bases da instancia então é só retirar a ultima linha do script.

No final disso tudo existe uma terçeira consulta onde ela faz algumas validações, para me retornar aquilo que eu realmente preciso, que são as quantidades de scans, seeks e lookups realizados em X dias.

select a.[Database],a.table_name,a.NOME_INDEX,
DATEDIFF(DAY,a.DATA_REGISTRO,b.DATA_REGISTRO ) as Intervalo_Dias,
SUM((b.user_scans – a.user_scans)) as Diff_Scan,
SUM((b.user_lookups – a.user_lookups)) as Diff_Lookup,
SUM((b.user_seeks – a.user_seeks)) as Diff_Seek
from Historico_Uso_Indices as a
join Historico_Uso_Indices as b
on a.[Database] = b.[Database]
and a.table_name = b.table_name
and a.NOME_INDEX = b.NOME_INDEX
and a.user_seeks < b.user_seeks
and a.user_lookups < b.user_lookups
and a.user_scans < b.user_scans
group by a.[Database],a.table_name,a.NOME_INDEX,
DATEDIFF(DAY,a.DATA_REGISTRO,b.DATA_REGISTRO);

Digamos que eu executei o segundo script ontem (28/09), e hoje eu executei novamente a terceira consulta vai me retornar a diferença de acessos do tipo scans, seeks e lookups entre esse dia. Essa informação é bem valiosa para mim, pois eu consigo identificar quais foram os índices mais acessados, e quais os métodos de pesquisa foram os mais realizados.

Se você quiser, você ainda pode realizar essa consulta:

select database,table_name, nome_index, total_leituras from Historico_Uso_Indice
order by total_leituras desc

E analisar quais foram os índices mais acessados do seu banco de dados.

Abaixo segue uma imagem do resultado a consulta do terceiro script.

Imagem02

 

Pessoal por hoje é isso, espero que seja esses scripts sejam úteis para vocês também.

Marcos Freccia
@SQLFreccia
MCTS SQL Server 2008

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em junho 29, 2011, em Administração, VirtualPass e marcado como , , , , . Adicione o link aos favoritos. 2 Comentários.

  1. Antonio Augusto

    boa… já coloquei em produção….

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: