Utilizando índices na otimização de queries no SQL Server

Criar um índice eficiente não é uma tarefa simples: requer conhecimento das queries em execução e dos diferentes tipos de índice disponíveis. A finalidade desse artigo é explicar como funcionam os índices e sua adaptabilidade as queries no universo do SQL Server 2000.

Estrutura interna de um índice

Índices são estruturas que possuem algoritmos otimizados para acessar dados. Assim como nas tabelas, páginas de índices também ocupam espaço físico. O corpo de um índice é formado pelas colunas da tabela cujos dados se deseja classificar seguido de uma referência conhecida como “ponteiro”, que serve para localizar a chave na página de dados da tabela. Existe também um tipo de índice especial que não utiliza ponteiros - conhecidos por cluster - que será detalhado adiante.

Índices no SQL Server 2000 são construídos sobre estruturas denominadas árvores balanceadas (=”B-Tree”), cujo desenho lembra o esqueleto de uma pirâmide. A idéia desse algoritmo é fornecer um modelo de pesquisa que agilize o processo de busca, efetuando um número reduzido de leituras nas páginas do índice para que se obtenha a localização da chave pesquisada. Quando procuramos por determinada palavra num livro, localizamos a(s) página(s) desejada(s) através de uma busca em seu índice. Se fossemos ensinar alguém como procurar a palavra “ADMIN” num livro de SQL Server, provavelmente ensinaríamos alguma coisa assim:

1. Localize o índice remissivo no final do livro;
2. Procure o bloco de palavras que iniciam pela letra “A”;
3. Efetue uma leitura seqüencial nesse bloco até localizar a palavra desejada. As páginas do livro que contêm referências à palavra “ADMIN” encontram-se listadas ao lado da palavra.

A figura 1 ilustra um processo de busca envolvendo a mesma pesquisa acima numa árvore B-Tree de um índice não-cluster. O processo tem início numa página-mestre conhecida como ”root page”, procurando pela maior chave da página cujo valor é menor ou igual à palavra pesquisada. Em nosso exemplo, a primeira palavra cujo código alfabético é menor ou igual à “ADMIN” é “ACESSO”, portanto seguiremos nessa direção até a página de número 2, localizada num nível intermediário conhecido por “non leaf level”. A busca é finalizada no nível folha ou “leaf level page”, onde encontramos a referência para a página de dados onde se localiza a palavra.

Tipos de índice existentes no SQL Server 2000

Existem dois tipos básicos de índice: cluster e não-cluster. Índices cluster impõem uma organização na própria página de dados da tabela, fazendo com que permaneçam classificadas de acordo com a composição de sua chave. Se você executar o comando select * from NorthWind.dbo.Orders irá notar que os pedidos são ordenados pela coluna OrderId, que faz parte do índice cluster PK_Orders. Podemos então afirmar que o leaf level de um índice cluster representa a própria página de dados da tabela, descartando a utilização de ponteiros para página de dados. Já índices não-cluster possuem estrutura própria, mantendo-se vinculados às páginas de dados pela utilização de ponteiros.

A tabela de sistema SysIndexes é responsável pelo armazenamento dos metadados do índice. Nessa tabela localizamos o nome do índice, uma indicação de seu tipo (cluster/não cluster), o número de páginas utilizadas, o número de alterações desde que o último cálculo de estatísticas foi executado etc. Tabelas sem índice cluster - conhecidas por heaps - possuem uma linha em SysIndexes para IndId=0. Cada índice não cluster possuirá uma entrada para IndId no intervalo 2..250. Se uma tabela possuir índice cluster, este será identificado por IndId=1. Tabelas com índice cluster NÃO possuem entrada para IndId=0. Portanto, se você quiser listar as tabelas que não possuem índice cluster em seu database, basta selecionar as entradas em SysIndexes para IndId=0.

Alocação e gerenciamento de espaço para índices e tabelas no SQL Server 2000

Páginas de dados de tabelas com índice cluster são “ligadas” umas às outras, isto é, no cabeçalho de cada página são encontradas referências à página anterior e posterior (=Next/Previous Page in Chain). Para um processo efetuar leitura seqüencial numa tabela com índice cluster – conhecida por clustered index scan – precisará apenas localizar a página inicial em SysIndexes.Root; as páginas seguintes estarão encadeadas.

Já em heaps o processo é diferente pelo fato das páginas de dados não possuírem ordenação. Pode-se iniciar um lote de inserções numa página localizada “no meio da tabela”, utilizando espaço gerado por uma série de deleções e terminar o processo “no fim da tabela”, alocando-se uma nova extent[1]. É como se você estivesse escrevendo um texto num caderno e, após virar a página corrente, se deparasse com uma página totalmente preenchida. O que você faria? Provavelmente sairia folheando o caderno a procura de uma página em branco – ou com espaço suficiente – para dar continuidade. Em heaps o processo é um pouco diferente, porque as páginas NÃO são ligadas umas às outras. Para varrer as páginas pertencentes a uma heap, o SQL Server utiliza páginas especiais – denominadas páginas IAM – que controlam as páginas utilizadas por uma tabela. Portanto, num processo de leitura de uma heap o SQL Server 2000 se norteia pelas páginas IAM.

Até a versão 6.5 do SQL Server, o controle de páginas em heaps era efetuado da mesma maneira que tabelas clusterizadas. Assim, inicialmente as páginas pertencentes a heap estavam ligadas e sequencialmente próximas: a página 10 estava ligada às paginas 09 e 11, e assim por diante. À medida que o tempo passava e aconteciam muitas inclusões e um sem-número de deleções, começavam a surgir vários espaços, que eram detectados e aproveitados em inclusões futuras. Nesse contexto, as páginas que estavam perfeitamente organizadas no início, passavam a apresentar alta fragmentação: a página 10 agora estava ligada às páginas 5322 e 99878. Assim, uma varredura nessa tabela tomando-se por base a ligação entre páginas causaria um excessivo I/O. Decidiu-se então, a partir da versão 7.0 do SQL Server, pela utilização das páginas IAM: quando o banco precisa ler uma heap, recorre às páginas IAM para obter uma lista das extents que devem ser lidas, reduzindo bastante o custo de I/O envolvido na operação.

As principais diferenças entre um índice cluster e outro não-cluster podem ser visualizadas na tabela 1.

Índices não-cluster precisam de ponteiros para “ligar” a linha do índice com a correspondente linha da página de dados da tabela. Se a tabela possui índice cluster, a própria chave do índice cluster será escolhida como ponteiro do índice não-cluster. Se a tabela NÃO possui índice cluster, o ponteiro do índice não-cluster será representado pelo RowID (=Id do FileGroup + Id da Página + Id da Linha).

Mas por que essa diferença? Quando ocorre um page-split, metade das linhas da página objeto do comando insert são transferidas para uma nova página. Se o ponteiro do índice não-cluster fosse baseado na informação do RowID, esse índice teria que ser reprocessado para as linhas que foram realocadas. Fazendo com que o ponteiro do índice não-cluster seja a própria chave do índice cluster, evita-se o reprocessamento do ponteiro para as linhas que foram movidas, pois a chave do índice cluster não é alterada com a mudança de página. Nos resta agora uma dúvida:

Fazer com que o ponteiro do índice não-cluster seja a chave do índice cluster não causa um overhead nas leituras dos índices não-cluster, gerando dois processos de busca nas árvores B-TREE (a primeira para localizar o ponteiro e a segunda para localizar a página de dados)?

Sim, existe um pequeno overhead - mas o custo-benefício compensa. Quem teve a oportunidade de trabalhar com SQL Server 6.5 sabe o quanto os page-splits impactavam em performance nas atualizações. Como um page-split altera o RowID das linhas realocadas, todos os índices não-cluster tinham de ser reprocessados para atualização do novo RowID. Essa operação em ambientes de produção gerava um impacto bastante negativo. Ao trocar o RowID pela chave do índice cluster, eliminou-se por completo esse problema.

Criação de um índice passo a passo

Para criar um índice na tabela Orders do banco de dados NorthWind no Enterprise Manager, expanda o banco de dados selecionando a opção Tables. Clique com o botão direito sobre a tabela Orders, selecione Design Table e na barra de ferramentas clique em Manage Indexes/Keys para que a tela apresentada na figura 2 obtenha o foco principal. As opções disponíveis na tela de manutenção de índices são detalhadas a seguir:

• Table Name: nome da tabela onde se deseja criar o índice.
• Type: selecione New para criar um novo Índice ou Delete para excluir um índice existente. Os tipos possíveis são: Index ou Primary Key.
• Index Name: nome do índice.
• Column Name… Order: colunas que compõe a chave do índice. Para cada uma delas a coluna Order informa se a ordenação é ascendente (default) ou descendente.
• Index Filegroup: indicação do filegroup para criação do índice. Se você não possui discos RAID[2], uma boa opção para ganho de performance é criar tabelas e índices em filegroups diferentes, localizados em dispositivos distintos. Por exemplo, crie as tabelas no drive D e os índices no drive E.

• Create Unique: Unique quer dizer único, que não permite duplicidades. Costumo utilizar essa opção apenas para constraints. Explicarei o porquê: uma constraint unique é utilizada para evitar a duplicidade da(s) coluna(s) a ela associada. Índices são fundamentalmente utilizados para otimizar a performance de queries. Por exemplo, se tenho uma regra de acesso que não permite duplicidades na numeração de uma nota, implemento uma constraint unique e não um índice unique.

  Agora um detalhe interessante: o que aconteceria se você efetuasse um update abrangendo 100 linhas da tabela Orders e esse update gerasse violação de chave em 30 casos? A resposta é: DEPENDE. Se Ignore Duplicate Keys – opção disponível apenas para índices – estiver ativo, 70 updates serão efetivados. Se Ignore Duplicate Keys não for ativado, o rollback se encarregará de desfazer todos os 100 updates.

• Fill Factor: indica o percentual de preenchimento das páginas do índice no momento de sua criação. Um fator de preenchimento de 80% informa que será utilizado somente 80% da capacidade da página para ocupação das linhas do índice, deixando 20% de espaço livre. Mas por que deixar páginas parcialmente preenchidas? Isso não acarretaria um aumento do custo de I/O? Certamente sim. O que tem que ser levado em conta é a fragmentação das páginas do índice decorrente de modificações. O fill factor ideal de um índice será aquele que causar menor fragmentação para um determinado período de análise. No índice Orders.CustomerId talvez fosse interessante criar páginas com algum espaço livre para diminuir o volume de page-splits[3], pois os pedidos da tabela Orders são inseridos com clientes aleatórios.

  Já no índice Orders.OrderDate, fill factor degradaria performance, já que os pedidos são inseridos em ordem crescente de data, não causando fragmentação. Lembre-se que fragmentação de páginas só acontece quando precisamos inserir linhas em páginas totalmente preenchidas, forçando uma divisão de página para acomodar a nova linha. O fill factor atua somente no momento da criação ou reestruturação do índice, não sendo mantido durante os processos posteriores de atualização do índice. Vale a pena destacar também que:

  1. O valor default para fill factor é zero (visível no Query Analyzer sob o comando sp_configure ‘fill factor’). Parece um contra-senso, mas zero é o mesmo que 100%, ou seja: o SQL Server 2000 utilizará 100% de suas páginas na criação do índice. Note que você não pode especificar zero como opção de Fill Factor na criação de um índice; zero é utilizado somente como configuração padrão.
  2. Fill factor é uma opção avançada de otimização, portanto deve ser utilizada somente naqueles índices onde se observou excessiva fragmentação. Utilizar essa opção de uma maneira genérica para todos os índices do database não é uma boa prática.

  Uma questão: seria interessante criar um índice com fill factor numa tabela zerada? Acredito que não. Fill factor foi criado para minimizar o efeito de page-splits; o fato de um índice apresentar um número maior ou menor de quebras de página irá depender da composição do índice e do volume de atualizações. Quando estabelecemos “de cara” um percentual para o fill factor sem monitorar o crescimento da tabela com o comando DBCC ShowContig, podemos incorrer em dois erros: 1) superestimar o volume de quebras de páginas causando uma fragmentação desnecessária; 2) subestimar o volume de quebras e a tabela continuar com número excessivo de page-splits.

• Pad Index: fill factor atua somente no leaf level do índice. Assinalando essa opção, o percentual definido em fill factor será propagado para os níveis intermediários da árvore B-Tree. Não existe uma regra clara sinalizando o momento adequado para se utilizar essa opção, mesmo porque não existem indicadores de fragmentação nos níveis intermediários do índice.

  A fragmentação nos níveis intermediários ocorre numa escala menor que no nível folha, pois nos níveis intermediários trabalha-se com ranges de chaves. As páginas intermediárias são criadas automaticamente com uma folga para duas ou três linhas, possibilitando um pequeno, mas útil espaço para inserções. Pad Index pode ser utilizado como último recurso de otimização, no momento em que você chegou no índice de fill factor que julga ideal para a tabela analisada.

• Create as Clustered: indica que o índice criado será do tipo cluster. Lembre-se que só é possível criar um índice cluster por tabela.

• Do not automatically recompute statistics: as estatísticas de distribuição de dados pela chave do índice são essenciais para o otimizador avaliar uma query e, por default, são atualizadas automaticamente após um determinado número de modificações no índice (veja a matéria “Estatísticas de Distribuição de Dados no SQL Server”, na edição anterior). Ligando essa opção você deverá atualizar manualmente essas estatísticas com o comando update statistics, mas não recomendo essa opção.

  Conforme apresentado na edição 3 em “Otimização e Tuning Parte II”, o objetivo com relação à utilização de fill factor é manter a performance do sistema em níveis aceitáveis até a chegada da próxima rotina de reindexação. Considerando-se um processo semanal de reestruturação de índices, pode-se dizer que o fill factor de determinado índice está adequado à medida que os indicadores do comando DBCC SHOWCONTIG Scan Density e Avg. Page Density (full) se mantém próximos de 100%. Quanto mais distantes de 100%, maior a necessidade de utilização do fill factor para controle dos custosos page-splits. Portanto se você encontrar índices de scan density muito inferiores a 80%, experimente estabelecer um pequeno fill factor e reavalie a fragmentação após o mesmo período. Comece, por exemplo, com um índice de 95% para fill factor e vá diminuindo até encontrar seu ponto ótimo.

A sintaxe do comando T-SQL para a criação de índices pode ser vista na listagem 1.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > :: =
    { PAD_INDEX |
        FILLFACTOR = fillfactor |
        IGNORE_DUP_KEY |
        DROP_EXISTING |
    STATISTICS_NORECOMPUTE |
    SORT_IN_TEMPDB 
}

A linha de comando sugere algumas configurações adicionais, que não aparecem na tela do Enterprise Manager visto anteriormente na figura 2. São elas:

• DROP_EXISTING: Se droparmos o índice cluster numa tabela que possui também índices não-cluster, todos os índices não-cluster serão reconstruídos, pois o ponteiro desses índices para a página de dados passará a ser o RowID. Se criarmos depois o índice cluster, todos os índices não-cluster serão novamente reconstruídos, pois o ponteiro desses índices deverá ser modificado para a chave do índice cluster. Nos casos em que temos que efetuar uma alteração na composição da chave de um índice cluster e, para evitar que os índices não-cluster sejam reconstruídos duas vezes (uma quando dropamos e outra quando criamos novamente o índice cluster), utilizamos a cláusula DROP EXISTING para que o rebuild nos índices seja efetuado SOMENTE UMA VEZ. Uma observação: a cláusula DROP_EXISTING é aplicável somente sobre índices. Se sua primary-key também é um índice cluster, esse comando não poderá ser utilizado. Por exemplo, para alterar o índice-cluster ix_ind_cluster (col_A) para ix_ind_cluster (col_A,col_B) na tabela de nome tab_x, utilize:

  Create clustered index ix_ind_cluster(col_A,col_B) on tab_x with Drop_Existing

Nota: Você só pode executar um create index sobre um índice já existente se utilizar a cláusula DROP_EXISTING, caso contrário acontecerá um erro.

• STATISTICS_NORECOMPUTE: desabilita a atualização automática das estatísticas do índice, informando ao SQL Server 2000 que as estatísticas do índice serão atualizadas por processo manual (via comando update statistics). Estatísticas desatualizadas acarretam na escolha de planos de execução ineficientes, portanto sugiro não utilizar essa opção.
• SORT_IN_TEMPB: se você possui o TempDB localizado num conjunto de discos separados do filegroup do banco de dados, utilize essa opção para ganho de performance na reconstrução do índice. No processo de rebuild, são utilizadas áreas temporárias de armazenamento. Se o TempDB estiver numa área separada, estaremos viabilizando processos de leitura e escrita num conjunto diferente de discos, melhorando assim a performance na reconstrução.

Estudo de caso: análise de performance do índice

Imagine a seguinte situação: você acabou de concluir um relatório que demonstra a produtividade dos vendedores na tiragem de pedidos da empresa NorthWind.Com. Esse relatório sumariza os pedidos existentes na tabela Orders para um determinado vendedor. Após conclusão do relatório, você verifica no plano de execução que o índice existente EmployeeId (ou mesmo EmployeeIdOrders, os dois são iguais...) não está sendo utilizado na seleção de pedidos de um vendedor e você decide investigar o porquê. O select executado na figura 3 comprova que o índice EmployeeId não está sendo selecionado, pois foi utilizado um clustered index scan em PK_Orders.

Como um hávido leitor da SQL Magazine, você não hesita em ir até sua biblioteca e sacar a edição 8 para colocar toda aquela gama de conceitos sobre estatísticas à prova. Você decide então analisar as estatísticas do índice pelo comando DBCC SHOW_STATISTICS, para verificar o histograma relacionado à coluna EmployeeId, vide listagem 2.

 
dbcc show_statistics(orders,employeeid)

 

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

Statistics for INDEX 'employeeid'.

Updated                       Rows    Rows Sampled    Steps    Density        Average key length      

-------------------------   --------   -------------------   -------   -------------   ------------------------

Jul 25 2003  5:53PM   830        830                     9           0.0               8.0

 

All density                  Average Length        Columns

------------------------   ------------------------   ------------------------------

0.11111111                4.0                             EmployeeID

1.2048193E-3             8.0                            EmployeeID, OrderID

 

RANGE_HI_KEY  RANGE_ROWS     EQ_ROWS   DISTINCT_RANGE_ROWS   AVG_RANGE_ROWS          

----------------------   ---------------------    ---------------   -----------------------------------   -----------------------------

1                                                     0.0                                               123.0                  0                                                                           0.0

2                                                     0.0                                                 96.0                  0                                                                           0.0

3                                                     0.0                                               127.0                  0                                                                           0.0

4                                                     0.0                                               156.0                  0                                                                           0.0

5                                                     0.0                                                 42.0                  0                                                                           0.0

6                                                     0.0                                                 67.0                  0                                                                           0.0

7                                                     0.0                                                 72.0                  0                                                                           0.0

8                                                     0.0                                               104.0                  0                                                                           0.0

9                                                     0.0                                                 43.0                  0                                                                           0.0

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Com base nas estatísticas, constatamos que:

• A medida de seletividade da coluna EmployeeId na tabela Orders nos informa que, dos 830 pedidos existentes, 43 (5% do total) foram realizados para o vendedor de código igual a 9;
• A densidade para EmployeeId informa que cada empregado possui uma média de 92 pedidos na tabela Orders(830 * .111111).

Conclusão:

O índice não foi utilizado devido à baixa seletividade da coluna EmployeeId. O benefício da pesquisa na árvore do índice é minimizado pelo processo de bookmark lookup, pois o asterísco presente no comando select força a ida até a página de dados para resgate do restante das colunas. O custo da pesquisa com utilização do índice é maior que o processo de clustered index scan realizado na tabela, podendo ser comprovado no quadro abaixo. Repare que ao forçar a escolha do índice o número de logical reads pulou de 21 para 131!

Covered Indexes: uma alternativa para índices não-cluster com seletividade baixa

Uma query possui cobertura de um índice quando todas as colunas listadas no comando select estão presentes no corpo do índice, não havendo necessidade de acesso às páginas de dados para resgate de colunas adicionais. Índices pouco seletivos, quando criados com o objetivo de efetuar cobertura de queries tornam-se uma alternativa muito interessante para o otimizador, pois eliminam por completo processos de bookmark lookup, considerados o “calcanhar de Aquiles” de índices não-cluster.

Voltemos agora para o exemplo anterior, onde são selecionados todos os pedidos para o vendedor de código igual a 9. Na verdade não precisamos de toda informação contida na linha do pedido, mas somente do número efetivo de pedidos para esse vendedor. Queremos saber quantos pedidos foram emitidos para o vendedor de código 9. Substituamos então o comando select * from Orders where EmployeeId=9 pelo comando select count(*) from Orders where EmployeeId=9 e analisemos o plano de execução na figura 4.

Confirme a mudança no plano de execução, onde o Clustered Index Scan em PK_Orders da figura 3 foi substituído pelo Index Seek em EmployeesOrders na figura 4. O comando count(*) com o filtro para EmployeeId=9 viabiliza a query com cobertura integral do índice EmployeeOrders.

A função CheckSum pode ser utilizada para criar chaves hash [4] em oposição a índices criados sob colunas char ou varchar. A vantagem de trabalhar com índices hash é a redução no tamanho da chave do índice: você pode substituir, por exemplo, aquele índice criado sobre uma coluna com data-type varchar(100) por um valor numérico (100 bytes x 4 bytes). Diminuindo o tamanho da chave estaremos aumentando a densidade de linhas por página de índice, induzindo melhorias de performance.

Índices hash não podem ser utilizados em comparações envolvendo os operadores >, >=, <, <= ou <>. Esse tipo de índice se presta somente para pesquisas igualitárias.

A implementação dessa idéia envolve dois passos: primeiramente devemos criar uma coluna calculada, utilizando a função CheckSum para geração automática do código hash. Por último deveremos criar um índice sobre essa coluna. A listagem 3 ilustra passo a passo a criação do índice hash sobre a coluna CompanyName, na tabela Customers.

/* Criar a coluna calculada com a função CheckSum */

ALTER TABLE Customers

ADD cs_CompanyName AS CheckSum(CompanyName)

 

/* Criar o índice sobre essa coluna */

CREATE INDEX ix_cs_CompanyName on Customers (cs_CompanyName)

 

/* Executar um comando select */

SELECT *

FROM Customers

WHERE cs_CompanyName=checksum(N'The Cracker Box')

AND CompanyName = 'The Cracker Box'

 

------------------------------------------------------------------------------------------------------------------------------------------

 

CustomerID CompanyName                              . . . .

---------- ----------------------------------------

THECR      The Cracker Box                           . . . . .

Perceba que:

1. No select da listagem 3 existe uma redundância na comparação com o nome do cliente, que é referenciado na linha da função CheckSum “... where cs_CompanyName=checksum(N'The Cracker Box')” e na comparação “.. AND CompanyName = 'The Cracker Box' … “. Esse procedimento é necessário já que existe uma pequena (mas não descartada) possibilidade dessa função gerar o mesmo código hash para inputs distintos, conforme própria indicação da Microsoft. Para evitar resultados indesejados, trabalha-se com a segunda confirmação.
2. Índices hash não podem ser utilizados em pesquisas que utilizem comparações não equalitárias entre strings. Por exemplo, o índice hash não seria utilizado em ...where CompanyName like (‘The Craker%’) .

Dicas, Truques e algo mais para construir e manter índices eficientes

• Quanto mais compacto o tamanho da chave do índice, melhor. À medida que aumentamos o número de linhas na página do índice estaremos otimizando processos de leitura. Considere a substituição de PK´s compostas por colunas identity. Crie então uma constraint unique para a chave composta, para garantia da integridade do negócio.
• Criar um índice composto ou vários índices? O otimizador pode trabalhar com intersecção de índices, cruzando informações de dois índices existentes para resolver uma query. Se você está na dúvida entre criar um índice composto (indice_x: coluna_A+coluna_B) ou dois índices separados (indice_x: coluna_A e índice_y: coluna_B), responda para si mesmo: as queries executadas nessa tabela fornecem sempre as duas colunas ou existem casos onde somente a coluna_B aparece como filtro? Se suas queries utilizam as duas colunas (A e B) ou eventualmente a coluna_A, crie um índice composto. Caso contrário, crie dois índices.
• Processos de Scan (Clustered Index Scan ou Table Scan) em tabelas com grande número de linhas representam gargalos de execução. Atente para esse detalhe quando estiver analisando o plano de execução de suas queries.
• Procure criar sempre um índice cluster em suas tabelas. Rotinas de reindexação não corrigem fragmentação em heaps. Para desfragmentar uma heap, seria necessário copiar seus dados para uma tabela temporária, truncar a tabela original e recopiar os dados da tabela temporária para a tabela principal. Para desfragmentar tabelas com índices cluster, basta reindexá-la com DBCC DBReindex ou desfragmentá-la com DBCC IndexDefrag.
• Bases OLTP são responsáveis por um grande volume de acessos pontuais. Nesses casos, procure criar PK´s clusterizadas e curtas, de preferência desprovidas de qualquer significado. Por exemplo, uma nota fiscal é identificada por seu número, série e emitente. Ao invés de criar uma PK baseada nessas colunas, opte por uma coluna identity para PK e uma constraint unique para a regra de negócio estabelecida pela chave (número, série e emitente).
• Em bases destinadas a consultas, reserve o índice cluster para colunas que são acessadas por range - no caso da nota fiscal, a data de emissão seria uma boa pedida para um relatório de notas fiscais emitidas por data.
• Se sua base de dados é utilizada tanto para operações on-line como para consultas diversas, use o bom senso: se for interessante privilegiar os processos on-line, opte por clusterizar as PK´s. Se for interessante privilegiar os relatórios, reserve o índice cluster para aquelas colunas que são pesquisadas com cláusulas between, order by etc.
• Não crie índices em colunas com baixa seletividade. Colunas com alto grau de duplicidades não são uma boa escolha para índices não-cluster em função do alto custo dos processos de bookmark lookup. Como regra geral para queries sem cobertura de índices pode-se considerar o seguinte:

• Não crie índices em tabelas com pequeno número de linhas. Tabelas que ocupam um pequeno número de páginas normalmente não utilizam índices para agilizar suas leituras. Uma tabela com 100 linhas não precisaria, portanto, de outros índices além daquele já fornecido pela primary-key.
• Mantenha as estatísticas atualizadas. Mantenha as opções Auto-Create/Update Statistics ligadas.
• Crie rotinas de reindexação periódicas. Rotinas de reindexação são fundamentais para garantia de performance. Não se esqueça delas.
• Utilize o Profiler como ferramenta de apoio no rastreamento de queries com longo tempo de execução. Aproveite a oportunidade para criar índices mais eficientes ou mesmo dropar índices inúteis.
• Utilize o Index Tuning Wizard como ferramenta de apoio para tuning de índices.
• Ao criar índices compostos, mantenha a coluna mais seletiva no primeiro nível da chave. Como as estatísticas são criadas para o primeiro elemento de índices compostos, mantendo a coluna mais seletiva no primeiro plano estaremos agilizando o trabalho do otimizador para escolha desse índice.
• Dê preferência por índices baseados em colunas numéricas em oposição a colunas char ou varchar. Índices baseados em colunas numéricas são mais eficientes.
• Não crie índices em duplicidade. Um erro bastante comum é criar índice com a mesma estrutura de outros já existentes. Habitue-se a executar um sp_HelpIndex para confirmação dos índices existentes.

Conclusão

Índices devem ser criados para agilizar a performance do sistema como um todo, mas freqüentemente nos esquecemos disso. Subavaliamos o impacto da criação do índice na performance geral do sistema, e aquilo que foi concebido com o objetivo inicial de ganho de performance resulta em mais um ponto de contenção.

Otimizar um processo pode significar eliminar um índice ineficiente, implementar novos filtros ou alterar os parâmetros da cláusula join das queries em execução. Devemos sim considerar a criação de índices como recurso de otimização, mas numa análise conjunta com todos esses fatores.

Leitura Adicional – Entendendo como funciona a organização física de um database

Quando criamos um database o SQL Server 2000 faz uma pré-alocação de espaço, segmentando o database em páginas de 8kb, numeradas seqüencialmente. Cada conjunto de oito páginas contíguas formam uma unidade lógica maior denominada extent, que é a unidade básica de crescimento dos objetos no banco. Uma tabela nasce numa extent mista e cresce em extents uniformes, por questões de otimização de espaço. O SQL Server 2000 possui um processo especial para gerenciar espaço, responsável por marcar as extents que já foram utilizadas. Assim, quando uma tabela é criada, o SQL Server faz uma consulta nas páginas que controlam extents mistas para obter um endereço de extent com espaço disponível. Da mesma maneira, quando essa tabela precisar se expandir será efetuada uma busca nas páginas que controlam extents uniformes para obter o endereço de uma extent livre. Trocando essa pequena história por algumas siglas, estamos falando de páginas GAM (Global Allocation Map) e SGAM (Shared Global Allocation Map). Páginas GAM controlam a alocação de extents uniformes e SGAM de extents mistas. Essas páginas são criadas no momento da “demarcação” do database, que acontece na sua criação ou no momento de expansão.

Num database, a terceira página será sempre ocupada por uma página GAM e a quarta por uma SGAM, responsáveis por gerenciar as próximas 64.000 extents. A página GAM utiliza um bit para informar se a próxima extent está livre ou não; como existem 8.000 bytes livres numa página, e cada byte controla 8 extents seqüenciais, chegamos no resultado de 64.000 extents controladas por uma página GAM.

Portanto, o dueto de páginas GAM/SGAM controla até 4GB de dados (64.000 * 64KB) (64 kb é o tamanho de uma extent). Se você criar um database com tamanho de 5GB, serão encontradas 2 páginas GAM: a primeira será a página de número 3 e a segunda virá após aproximadamente 64.000 * 8 = 512.000 páginas (na verdade esse número é 511.232, pois são descontados 97 bytes de cada página para controle interno). O mesmo critério vale para as páginas SGAM, ocupando as posições de número 4 e 511.233.

Além de administrar extents com páginas GAM/SGAM, existe um controle adicional, informando se a página está ou não alocada e seu percentual de utilização. Esse controle é exercido por páginas com o anacrônimo PFS, de Page Free Space. Cada página PFS controla 8.088 páginas contíguas num database. A primeira página PFS é a página de número 1, logo após a header do database, representada pela página 0. Na figura abaixo pode-se visualizar o mapeamento de um database:

Existe ainda um controle utilizado para gerenciar as extents utilizadas por heaps e índices, fornecido pelas páginas IAM (Index Allocation Map). Uma página IAM controla até 512.000 páginas de uma tabela. Diferentemente das páginas GAM, SGAM e PFS que são demarcadas na criação e/ou alteração de tamanho do database, páginas IAM são alocadas randomicamente (= “on demand”) à medida que a tabela (ou índice) cresce. Se a primeira página de uma tabela é a página de número 120.000, isso não significa que as próximas 512.000 páginas serão ocupadas por essa mesma tabela; nesse intervalo podem ser encontradas páginas pertencentes a inúmeras tabelas e várias páginas IAM.

As páginas IAM são utilizadas em conjunto com as páginas PFS para orientar o banco nas inclusões. Assim, quando ocorre um insert numa heap e a página atual já se encontra totalmente preenchida, é efetuada uma busca conjunta nas páginas IAM e PFS para determinar uma página já pertencente a essa tabela para acomodar a inserção. Se não encontrar espaço nas páginas PFS, será efetuada uma requisição na página GAM para uma nova extent. Tabelas com índice cluster não se orientam com base nas páginas IAM, pois as inserções não são baseadas na teoria de “onde existe espaço” mas sim na chave do índice cluster.

• [1] Uma extent é um conjunto contíguo de oito páginas.
• [2] RAID (Redundant Arry of Inexpensive Disks) é um sub-sistema de discos rígidos configurado para fornecer tolerância a falhas.
• [3] Page-Split é o nome do processo que ocorre quando uma página totalmente preenchida é dividida em duas para acomodar uma inserção. page-splits foram amplamente discutidos na edição 3 na matéria Otimização e Tuning Parte II.
• [4] Uma chave hash é utilizada como uma boa opção de performance na construção de um índice, sendo utilizado na substituição de longas chaves alfanuméricas por um único número inteiro, gerado à partir de funções matemáticas.