Atenção: esse artigo tem um vídeo complementar. Clique e assista!

A partir da versão 2005 do SQL Server, contamos com novos recursos como DMVs (Dynamic Management Views) e DMFs (Dynamic Management Functions), que, através de metadados, coletados pelo serviço do SQL Server, nos dão uma visão diferenciada sobre como nosso banco e instância estão se comportando.

Neste artigo, utilizaremos as informações disponibilizadas por estes recursos para realizar uma análise detalhada de como nossos índices estão sendo utilizados, e com base nestas informações, identificarmos, por exemplo, quais são os índices mais utilizados, que tipo de manutenção seria mais apropriada para eles, assim como encontrar índices que não estão sendo utilizados de forma adequada pelas consultas.

Índices são utilizados para aumentar o desempenho em operações de leitura no banco de dados. Um índice utilizado corretamente pode melhorar exponencialmente a velocidade com que as consultas são retornadas pelo SGBD e diminuir significativamente a quantidade de I/O em disco.

Esta redução de I/O ocorre porque os dados ao serem indexados passam a utilizar a estrutura criada pelo índice, se limitando a uma busca apenas nas páginas de dados do índice.

Sem um índice, o SQL Server é obrigado a realizar uma leitura completa em todas as páginas de dados referente à tabela em que o dado solicitado está armazenado.

Apesar do índice, se bem definido, aumentar o desempenho em operações de leitura e reduzir I/O, ele também gera um custo considerável em operações de escrita. Esse comportamento ocorre porque o índice deve se manter atualizado.

Para manter o índice atualizado o SQL Server replica, de forma síncrona, todas as alterações (Insert, Update e Delete) realizadas na tabela para o índice. Logo, se uma tabela contém quatro índices, uma única operação de inserção de dados (insert) irá gerar cinco inserções, sendo uma para a tabela e mais uma para cada índice existente.

O mesmo procedimento ocorre em operações que apagam informações (delete). Em operações que atualizam dados (update) apenas índices que contêm as colunas atualizadas serão afetados.

Devido a este processo de atualização dos índices, o tamanho utilizado pelo índice em disco estará sempre em constante crescimento. Dessa forma, quanto maior a quantidade de índices, mais espaço em disco precisamos reservar para seu crescimento.

Uma forma de acompanharmos detalhadamente todos os processos envolvendo índices, desde seu crescimento até a forma com que é utilizado, é utilizando as DMVs e DMFs.

Visões e Funções Dinâmicas para Índices

As DMVs e DMFs são visões e funções carregadas pelo próprio serviço do SQL Server com diversos dados sobre operações realizadas no servidor de banco de dados.

Elas permitem ao administrador do banco uma melhor e mais ampla interpretação das informações sobre armazenamento, manipulação e utilização de seus recursos.

Dentre as DMVs e DMFs disponíveis a partir do SQL Server 2005, existe uma categoria dedicada apenas a análise de informações sobre índices. Essa categoria é chamada de Index Related Dynamic Management Views and Functions, onde contamos com as seguintes visões e funções:

• sys.dm_db_index_operational_stats: Esta DMF retorna informações referentes a atualizações realizadas, como inserção, atualização ou deleção, para cada partição de uma tabela ou índice;

• sys.dm_db_index_usage_stats: Visão que exibe diferentes tipos de contadores de operações realizadas nos índices, como quantidade de acessos realizados pelo usuário, tipos de acesso como seek ou scan e a última vez em que o índice foi utilizado por uma consulta;

• sys.dm_db_index_physical_stats: Função que lista informações sobre espaço utilizado e fragmentação de índices.

Na categoria Index Related Dynamic Management Views and Functions"