Atenção: esse artigo tem um vídeo complementar. Clique e assista!
Nesse artigo vamos demonstrar como avaliar a utilização e performance de índices no SQL Server 2005 e 2008 utilizando as DMVs (Dynamic Management Views) e DMFs (Dynamic Management Functions). Dentre os tópicos abordados em nossa análise sobre índices, teremos: fragmentação, espaço utilizado, Missing Index e consultas que nos permitirão elencar e entender detalhadamente se os índices estão sendo utilizados de forma eficaz ou se estão apenas ocupando espaço no banco de dados.
Para que serve
Este artigo visa auxiliar administradores de banco de dados e desenvolvedores a identificarem como de fato os índices estão sendo utilizados pelo banco de dados, e com base na análise realizada avaliar se determinado índice deve ser excluído, alterado, reorganizado, reindexado ou se existe a necessidade de novos índices para contemplar consultas realizadas pela aplicação.
Em que situação o tema é útil?
Este tema é útil para profissionais que desejam ter uma visão ampla dos recursos disponíveis no SQL Server 2005 e 2008 que podem ser utilizados para realizar uma análise técnica detalhada sobre a utilização de índices no banco de dados.
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 ainda temos mais quatro opções, divididas entre funções e visões, que podemos utilizar em conjunto com as DMVs e DMFs citadas acima, para obter informações sobre índices que o SQL Server sugere a criação para aumento de performance em consultas realizadas na base de dados. Estes índices são conhecidos como missing index.
O SQL Server sugere um novo índice sempre que uma consulta não encontrar um índice que a contemple e o mesmo interpretar que um índice, com as características necessárias para contemplar esta consulta, aumentaria sua performance.
Sempre que o SQL Server interpretar que um novo índice beneficiaria a performance das consultas realizadas, ele automaticamente irá inserir um registro contendo as informações necessárias para a criação deste novo índice nas seguintes DMVs e DMFs:
• sys.dm_db_missing_index_details: Esta DMV sugere índices que poderiam ser criados para melhorar a performance em consultas realizadas em determinadas tabelas;
• ...
