sys.dm_db_index_usage_stats

Explicando de uma forma simples, pode-se dizer que esta DMV devolve informação sobre os valores dos contadores dos varios tipos de operações realizadas sobre os índices, bem como, a data/hora da realização da última operação de cada tipo.

Assim se pretender saber quais os índices que não estão a ser usados, ou quais deverão ser avaliados em termos de continuarem a existir ou não (devido à sobrecarga ao nível user_updates ser bastante mais elevada que ao nível das user_seeks, user_scans, ou mesmo user_lookups), esta é a DMV correcta !

Ah ! Mas existe um pequeno problema !

Como é do conhecimento público, sempre que o engine do SQL Server é re-inicializado, perdem-se todos os dados históricos.

A questão coloca-se de forma simples. Para que seja possível efectuar uma correcta avaliação da utilização dos índices, numa BD específica, os resultados da DMV sys.dm_db_index_usage_stats deverão ser armazenados numa tabela para uma análise posterior mais cuidada.

Como implementar uma solução para esta questão ?

1) Criando uma BD onde se proceda ao armazenamento de toda a informação relacionada com a Administração das BDs (se não tiver já uma !). Neste exemplo vamos utilizar uma BD com o nome [Performance]

2) Criando uma tabela onde se armazene toda a informação devolvida pela DMV sys.dm_db_index_usage_stats . Neste exemplo vamos utilizar uma tabela com o nome: [IndexUsage] , e com um extra de mais 4 campos, nomeadamente:

a. [TimeStamp] – Date/time da última recolha informação

b. [LastRestart] – Date/time do ultimo re-start ao Engine

c. [NodeName] – Útil quando se está a trabalhar num cluster

d. [DatabaseName] - Nome DB.

[Script: 01.Create.Table.IndexUsage.sql]

3) Criando uma Procedure que efectue a recolha da informação para dentro da tabela [IndexUsage] da referida BD. [Script: 02.Procedure.sp_IndexUsageComplete.sql]

4) Criando um JOB para realizar a Recolha de Informação sobre utilização dos índices [Schedule: Daily: 23:50] [Script: 03.JOB.sql – SFF adicionar o conteudo deste script ao referido JOB ! Este script não cria o JOB !!!]

Ok ! Nesta fase já conseguiu pôr tudo a funcionar e deseja realizar uma análise usando toda a informação que recolheu ?

Para realizar a referida análise, poderá recorrer à seguinte query:

/* BEGIN QUERY */

select *

from Performance.dbo.IndexUsage

where timestamp like

(select MAX(timestamp) from Performance .dbo.IndexUsage)    -- Date/time of last collect job

and (user_seeks =0 and user_scans = 0)

and IndexTypeDesc not like 'HEAP'   -- Exclude tables with no PKs and no cluster indexes

and IndexName not like 'PK_%'

--and DatabaseName like 'DatabaseYouWantToCheck'

order by user_updates desc

/* END QUERY */

Ok ! Vamos gastar uns minutos a efectuar algumas reflexões:

i) Possui neste momento informação recolhida ao longo de um periodo considerável (sem re-starts no referido periodo) – diga-se que possui informação dos últimos 3 meses (pode variar em cada cenário !!!)

ii) Voçê possui um bom nível de conhecimento sobre os requisitos do negócio da empresa. Isto significa que deverá estar consciente, por exemplo, se todos os relatórios que são gerados para suportar o referido negócio, foram executados no referido periodo (no qual se efectuou a recolha de informação).

Não deverá ser tomada qualquer tipo de decisão ao nível da eliminação de indices, sem saber se os mesmos irão ser futuramente usados, ao nível de por exemplo, um relatório de fim de ano !

Nota: Se no seu ambiente de testes, identificar informação que não esteja a aparecer sobre um determinado indice que tem a certeza que existe, basta que efectue algumas queries sobre a referida tabela, em que sejam incluidos os campos que compõem o referido índice !!!

Lembre-se, não tome qualquer tipo de decisão sem a devida informação !