Na primeira parte desse artigo você viu que criar índices é um trabalho bem fácil, mas saber a quantidade e qual tipo exato de índice que você deve criar na sua tabela, não é tão fácil assim. Se você não leu a primeira parte, sugiro fortemente que o faça clicando aqui.
Fill Factor
Tão importante quanto saber a hora de criar um índice primário ou um índice secundário, é saber qual o Fill Factor você deve aplicar no seu índice. Mas o que é o Fill Factor?
Imagine que você tenha uma prateleira com vários livros organizados pelo titulo em ordem alfabética. Se você colocar todos os livros em seqüência, sem deixar um espaço entre eles, quando precisar inserir um novo livro, terá que afastar todos os adjacentes a ele. Mas se você é bem organizado e deixa um espaço de dois livros a cada 10 livros, você terá uma folga quando precisar adicionar um novo livro, pois não será necessário reorganizar toda a prateleira.
Isso é o Fill Factor, é o espaço que você deixa entre os livros, mas no caso dos índices, é a porcentagem de preenchimento de uma página de índice. Traduzindo, se você especificar um Fill Factor de 80% (que é o padrão quando você cria um índice sem declarar o Fill Factor) o SQL Server irá preencher 80% de cada página com os dados referentes à indexação e os outros 20% ficarão disponíveis para futuras inserções ou atualizações.
Um Fill Factor alto como, por exemplo, 100% é excelente para leitura de dados, pois se em um índice padrão o SQL Server precisa percorrer 1.000 páginas para achar um determinado dado, neste índice com 100% de Fill Factor ele percorrerá apenas 800 páginas, já que um número menor de páginas serão necessárias para compor o índice. Porém se você precisar fazer um update ou insert neste índice, você sofrerá com o famoso Page Split
Page Split
Quando o SQL Server tenta inserir ou atualizar uma linha em uma página de índice cheia, aproximadamente metade da página é movida para uma nova página de índice, gerando assim um maior consumo de I/O de Disco. Esta ação, conhecida como Page Split, além de gastar mais recursos, pode fragmentar os dados dos índices, degradando a performance de todas as consultas dependentes deles. Quando você tem muitas operações de Page Split no seu banco de dados, é necessário fazer uma reindexação da base de dados, pois este processo recria os índices, reorganizando-os e melhorando o seu desempenho.
Encontrando o meio termo:
Agora sim, com base em todas essas informações, temos condições suficientes para analisar e encontrar o equilibro para a criação de índices em nossa tabela. Resumindo, você precisará obter as seguintes informações sobre sua tabela para criar os índices necessários:
Quais são os campos mais consultados?
A tabela é mais consultada ou mais atualizada?
Qual o fluxo de dados da minha tabela?
Com essas três perguntas mágicas, podemos definir quais, quantos e como criaremos nossos índices:
Quais índices devo criar?
Identifique a coluna que determina cada linha, de preferência uma coluna que tenha a unicidade garantida, como por exemplo, CPF, ID, RG+UF, etc. Crie um índice primário para essa coluna, pois servirá como base para todas as demais consultas da tabela.
Quantos índices devo criar?
Uma consulta só poderá utilizar um índice por vez em cada tabela, ou seja se você tiver 50 consultas diferentes a serem realizadas em sua tabela, não adianta ter 50 índices, veja quais são as colunas mais utilizadas e qual a combinação de utilização e crie um índice para essas combinações. Por exemplo, se em uma tabela chamada Clientes as consultas mais utilizadas forem:
SELECT CPF, Nome, Idade FROM Clientes
SELECT ID, CPF, Nome FROM ClientesCom certeza vale a pena criar um índice com os campos CPF e NOME. Lembrando que, mesmo se esses forem os campos mais utilizados em sua tabela, e a quantidade de updates/inserts para selects é de 10/1, não vale a pena criar esse índice, pois você tem muito mais atualizações do que inserções.
Como devo criar os índices?
Para responder essa pergunta, além de você ter que saber qual operação é mais requisitada: atualização ou busca, você terá que saber também qual a disponibilidade da sua base de dados para a execução de um reindex. Se sua tabela é 90% consultada e apenas 10% atualizada e é possível executar o reindex semanalmente, você poderá utilizar um Fill Factor de 100%. Mas se sua tabela tem os mesmos 90% de consulta e 10% de atualização e é possível executar o reindex apenas mensalmente, você deverá utilizar um Fill Factor de no máximo 90%, caso contrário haverão muitos Pages Splits na sua base de dados por muito tempo, pois o reindex será executado apenas uma vez por mês.
Mas espere, o que é o Reindex?
Reindex é uma rotina de manutenção de banco de dados que recria todos os indices na base de dados, atualizando-os e organizando-os. Essa manutenção, dependendo do tamanho do banco de dados, poderá levar horas, por isso tem que ser bem planejada. Para mais informações sobre como implantar um reindex, veja este artigo.
Caso de uso:
Certa vez estava desenvolvendo uma consulta que importaria um arquivo de texto e realizaria um tratamento em cima do mesmo. Porém o arquivo era muito grande e a importação dos dados para a minha tabela estava demorando muito. A solução foi criar um índice primário com 1% de Fill Factor (Se você especificar 0%, o Fill Factor será de 100%) para receber a importação e logo após, reindexei a tabela, alterando o Fill Factor para 100%, pois não iria fazer mais alterações na tabela, apenas leitura. Consegui, com essa intervenção, diminuir o tempo da rotina de 4 horas para 25 minutos.
Conclusão:
Em uma tabela só poderá haver um índice primário e vários índices secundários, sabendo-se que quanto maior o numero de índices, mais lento será inserção de dados na tabela. Também é importante lembrar que quanto maior for o Fill Factor melhor a tabela será para leitura e pior para escrita, pois surgirão vários Pages Splits. O inverso também é válido. Portanto use com parcimônia.
Não quero e nem posso criar uma regra para definição de Fill Factor ou para a escolha de indice primário ou secundário, o que eu quero é incentivar você a criar seus próprios índices com o seu próprio critério. Teste, homologue, refaça e aprenda. Não existe um método simples e eficaz. Se você não desenvolve os sistemas, apenas o mantém (como é o meu caso atualmente), é importante estar monitorando o ambiente o tempo todo para saber onde e quando um índice deverá criado, alterado ou removido se for o caso.
Fiquem a vontade para fazer qualquer pergunta ou comentário sobre o tema abordado aqui, basta clicar em Comente!.
Abs,
Afonso Lage.
Nenhum comentário:
Postar um comentário