Escrevendo queries otimizadas no SQL Server

O artigo descreve algumas das principais técnicas associadas à escrita de queries otimizadas, mostrando estruturas que devem ser evitadas dentro das consultas por as tornarem menos eficazes.

O uso destas técnicas serve para extrairmos ao máximo a performance do banco de dados, fazendo o processador de consultas trabalhar a favor na hora de escolher o melhor plano de execução.

Fazer o ajuste (tunning) de uma consulta é mais uma arte do que uma ciência. Uma mesma query pode ter um comportamento totalmente diferente mediante ao montante de registros existente, às chaves presentes, aos recursos do sistema, às estatísticas de dados, aos índices e outros fatores. Neste contexto, este artigo descreve algumas das principais técnicas associadas à escrita de queries otimizadas, mostrando estruturas que devem ser evitadas dentro das consultas por as tornarem menos eficazes.

Neste artigo vamos explorar as técnicas de otimização de queries para mobilizarmos o banco a trabalhar da forma mais otimizada possível.

Abordaremos algumas das técnicas mais avançadas existentes para reconstrução de cursores, expressões de pivotamento (PIVOT) e estratégias de otimização de índices, completando todo o estudo para manter um ambiente de banco de dados que preza pela performance de acordo com as boas práticas orientadas pela engenharia da equipe do SQL Server.

Diretrizes para construção de queries eficientes

Fazer o ajuste (tunning) de uma consulta é mais uma arte do que uma ciência. Uma mesma query pode ter um comportamento totalmente diferente mediante ao montante de registros existente, às chaves presentes, aos recursos do sistema, às estatísticas de dados, aos índices e outros fatores.

O melhor conselho é considerar cada consulta caso a caso e tentar uma variedade de técnicas de otimização. Somente assim você pode aprender com certeza como o seu sistema responde às diferentes técnicas de tunning.

Alguns cuidados precisam ser tomados ao escrever uma query, dentre eles podemos citar:

1. Favoreça a lógica de conjuntos ao invés da lógica de procedimentos;
1. O fator mais importante a ser considerado quando estiver otimizando queries é saber como funciona a manipulação da lógica de conjuntos de registros;
2. Cursores e outras construções procedimentais geralmente limitam a habilidade do otimizador de consultas para gerar planos de consultas flexíveis.

2. Teste variações de queries objetivando a performance;
1. Frequentemente o otimizador pode produzir planos totalmente diferentes para queries logicamente equivalentes;
2. Teste técnicas diferentes, como joins ou subqueries, para descobrir qual delas é mais eficiente.

3. Evite QUERY HINTS (dar dica ao banco sobre qual índice ele deve usar);
1. Query hints diz ao otimizador como se comportar, portanto, sobrescreve a habilidade do otimizador de fazer o seu trabalho adequadamente. Eliminando as escolhas do otimizador, você irá se limitar a um plano que é menos eficaz que o ideal;
2. Utilize essa técnica somente quando tiver absoluta certeza que o otimizador está incorreto na sua escolha perante a lógica do seu negócio.

4. Use subqueries correlatas;
1. O otimizador está apto a trabalhar para integrar subqueries no fluxo da query principal em uma variedade de caminhos escolhidos por ele;
2. As subconsultas podem ser úteis em diversas situações para o ganho de performance, por exemplo, ao realizar um JOIN para uma tabela apenas para verificar a existência de linhas correlatas;
3. Para uma melhor performance, troque estes tipos de JOINS por queries correlatas que fazem uso do operador EXISTS, como mostrado na Listagem 1.

Listagem 1. Usando o LEFT JOIN e o NOT EXISTS.

  SELECT   a.pk_tabela_pai
  FROM     dbo.tabela_pai   a
  LEFT JOIN dbo.tabela_filha b
  ON       a.pk_tabela_pai   = b.fk_tabela_filha
  WHERE    b.fk_tabela_filha IS NULL
  GO
  SELECT   a.pk_tabela_pai
  FROM     dbo.tabela_pai a
  WHERE NOT EXISTS(SELECT *
                  FROM   dbo.tabela_filha b
  WHERE  a.pk_tabela_pai
         b.fk_tabela_filha) 

5. Evite funções definidas pelo usuário na cláusula WHERE;
1. Funções definidas pelo usuário que retornam apenas um valor, diferentemente das subqueries escalares, não são otimizadas no plano principal da query;
2. Usar função escalar na lista do SELECT é muito menos problemático porque as linhas já foram filtradas na cláusula WHERE.

6. Use funções tabulares (table-valued) como tabelas derivadas;
1. São funções que retornam o resultado em formato de tabela;
2. Ao contrário do item cinco, funções tabulares são geralmente úteis, no ponto de vista de performance, quando você as utiliza como tabelas derivadas;
3. O otimizador avalia uma tabela derivada apenas uma vez por consulta. Se uma função definida pelo usuário em uma TABLE-VALUED tiver uma lógica que atenda outras consultas, você pode encapsular e reutilizá-la para outras queries.

7. Evite colunas GROUP BY desnecessárias;
1. Quanto mais colunas na lista da cláusula GROUP BY você adicionar, mais o processo de agrupar linhas torna-se dispendioso;
2. Se a sua consulta possui poucas colunas de agregação, mas muitas colunas agrupadas não agregadas, você deve pensar em reconstruí-la utilizando subqueries escalares correlatas. Isso resultará em menos trabalho para agrupar na consulta.

8. Use expressões CASE;
1. A expressão CASE é uma das mais poderosas ferramentas de lógica disponível para os programadores T-SQL. Utilizando essa expressão você pode, por exemplo, mudar dinamicamente a saída de uma coluna;
2. Isso habilita a consulta a retornar somente os dados que são absolutamente necessários, reduzindo assim operações de I/O e rede, ao invés de gerar um RESULT SET muito grande e desnecessário ao cliente.

9. Divida JOINS em tabelas temporárias.
1. A estratégia principal do otimizador é encontrar planos que satisfaçam consultas utilizando operações simples;
2. Embora essa estratégia funcione para a maioria dos casos, ela pode falhar quando a saída de dados for grande, porque muitos joins requerem muita operação de I/O;
3. Em alguns casos, a melhor opção é reduzir o trabalho gerando uma tabela temporária populada com parte do que se deseja da consulta original, unindo colunas que estavam em tabelas distintas, para diminuir a quantidade de JOINS. Você pode então fazer uma junção com tabelas temporárias para produzir um resultado final;
4. Essa técnica não é muito favorável em sistemas transacionais pesados porque vai ocorrer uma sobrecarga de criação dessas tabelas temporárias, mas isso pode ser muito útil em situações de tomada de decisão (Business Intelligence).

Refactoring de cursor

Quando programadores com experiência em processamento sequencial de arquivos escrevem queries, naturalmente usam cursores TRANSACT-SQL. Porém, essa técnica descarta o poder de processamento do banco de dados relacional e gera um código com uma performance insatisfatória.

Para resolver isso, existe uma técnica chamada refatoração de cursor (cursor refactoring), que consiste em transformar cursores em queries que podem melhorar o desempenho.

Cursores fornecem um mecanismo que possibilita trabalhar com uma linha ou um pequeno conjunto de linhas de uma só vez em um bloco TRANSACT-SQL. Embora sejam úteis em alguns casos, eles tipicamente usam uma grande quantidade de recursos que podem consequentemente reduzir a performance de um sistema.

Antes de aprender a técnica de refatorar um cursor, é necessário entender como criá-los e como funcionam. A construção de cursores segue um padrão em seis passos:

1. Declaração das variáveis que irão armazenar os dados que serão retornados pelo cursor;
2. Uso da sentença DECLARE CURSOR para definir a consulta que esse cursor retornará;
3. Uso da sentença OPEN para executar a consulta e popular o cursor;
4. Uso da sentença FETCH NEXT INTO para recuperar o registro da próxima linha e armazenar os valores das colunas daquela tupla nas variáveis;
5. Uso da sentença CLOSE para fechar o cursor;
6. Uso da sentença DEALLOCATE para liberar todos os recursos alocados ao cursor.

Existem quatro fatores que tornam os cursores lentos. Deste modo, recomenda-se conhecê-los para entender como contornar estes problemas na hora da refatoração:

• Cada FETCH em um cursor tem a mesma performance que uma sentença SQL. No entanto, se um cursor precisar retornar mil linhas, seria equivalente à execução do SELECT interno que o alimenta mil vezes;
• Utilizam grande quantidade de memória;
• Podem causar problemas de LOCKING no banco de dados;
• Consomem muita banda de rede quando os resultados são enviados ao cliente.

Existem algumas razões que levam os desenvolvedores a optarem por uma solução via cursor. Na maioria dos casos a escolha é feita pela facilidade da implementação, quando o correto seria desenvolver uma query com as devidas precauções com a performance. A Tabela 1 contém uma lista de seis tarefas comuns de banco de dados para as quais cursores geralmente são usados. A última coluna informa precisamente quando optar ou não pelo seu uso.

Refatorando um cursor

Embora sejam importantes em alguns casos, na maioria deles os cursores são desnecessários. Para prevenir o seu uso você pode implementar uma técnica de otimização conhecida como cursor refactoring, que consiste na reestruturação do código existente mudando sua estrutura interna sem mudar o seu comportamento externo.

1. Desmembrando a lógica dos cursores

Antes de refatorar um cursor você tem que descobrir o que ele faz e considerar onde e como substituí-lo por uma consulta que será mais performática que ele.

Para identificar o cerne da função de um cursor que servirá de ponto de partida para a refatoração é necessário conferir o trecho do código onde se encontra a sentença FETCH.

A Listagem 2 mostra um cursor que retorna os nomes completos dos empregados e dos seus gerentes.

Listagem 2. Retornando o nome completo via CURSOR.

  /* PARTE 1: DECLARAÇÃO DAS VARIÁVEIS DO CURSOR */
  -- Declaração das variáveis que armazenarão as colunas
  -- retornadas do SELECT da DECLARAÇÃO DO CURSOR
  --
  DECLARE  @nr_contato             INT           ,
  @nr_empregado           INT           ,
  @nr_gerente             INT           ,
  @ds_emp_primeiro_nome   VARCHAR(50)   ,
  @ds_emp_nome_do_meio    VARCHAR(50)   ,
  @ds_emp_nome_de_familia VARCHAR(50)   ,
  @ds_ger_primeiro_nome   VARCHAR(50)   ,
  @ds_ger_nome_do_meio    VARCHAR(50)   ,
  @ds_ger_nome_de_familia VARCHAR(50)   ,
  @ds_emp_nome_completo   VARCHAR(100)  ,
  @ds_ger_nome_completo   VARCHAR(100)  ,
   
  /* PARTE 2: DECLARAÇÃO DO CURSOR */
  -- Definição do SELECT que o cursor irá trabalhar
  --
  DECLARE  cr_empregado CURSOR FOR
           SELECT   tb_empregado.nr_empregado        ,
                     tb_empregado.nr_gerente          ,
                     tb_contato.ds_primeiro_nome      ,
                     tb_contato.ds_nome_do_meio       ,
                     tb_contato.ds_nome_de_familia
           FROM     db_pessoa_p.tb_contato
                JOIN db_recursos_humanos.tb_empregado
                ON   tb_contato.nr_contato
                =    tb_empregado.nr_contato
   
  /* PARTE 3: ABERTURA DO CURSOR */
  -- Neste momento recursos de memória 
  -- são alocados para o cursor
  --
  OPEN cr_empregado
   
  /* PARTE 4: SENTENÇA FECTH */
  -- Pega a primeira linha do SELECT da DECLARAÇÃO DO CURSOR 
  -- E armazena nas variáveis declaradas na PARTE 1
  --
  FETCH NEXT FROM cr_empregado
  INTO  @nr_empregado              ,
        @nr_gerente            ,
        @ds_emp_primeiro_nome  ,
        @ds_emp_nome_do_meio   ,
        @ds_emp_nome_de_familia
   
  -- Enquanto tiver linha a ser retornada pelo SELECT
  -- da DECLARAÇÃO DO CURSOR, faça...
  --
  WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT
      @ds_ger_primeiro_nome   = tb_contato.ds_primeiro_nome,
      @ds_ger_nome_do_meio    = tb_contato.ds_nome_do_meio ,
      @ds_ger_nome_de_familia = tb_contato.ds_nome_de_familia
    FROM db_pessoa_p.tb_contato
           JOIN db_recursos_humanos.tb_empregado
           ON   tb_contato.nr_contato
           =    tb_empregado.nr_contato
    WHERE  tb_empregado.nr_empregado = @nr_gerente
    
    -- Se o SELECT acima não retornar nenhuma linha, faça...
    IF @@ROWCOUNT = 0
       BEGIN
         SET @ds_emp_nome_completo = @ds_emp_primeiro_nome
             + ISNULL(' '+@ds_emp_nome_do_meio+'. ', ' ')
             + @ds_emp_nome_de_familia
         SET @ds_ger_nome_completo = NULL
       END
    ELSE
       BEGIN
         SET @ds_emp_nome_completo = @ds_emp_primeiro_nome
             + ISNULL(' '+@ds_emp_nome_do_meio+'. ', ' ')
             + @ds_emp_nome_de_familia
         SET @ds_ger_nome_completo = @ds_ger_primeiro_nome
            + ISNULL(' '+@ds_ger_nome_do_meio+'. ', ' ')
            + @ds_ger_nome_de_familia
         END
    SELECT  @ds_emp_nome_completo AS Nome_do_Empregado,
           @ds_ger_nome_completo AS Nome_do_Gerente
   
    FETCH NEXT FROM cr_empregado
    INTO  @nr_empregado            ,
          @nr_gerente                   ,
          @ds_emp_primeiro_nome         ,
          @ds_emp_nome_do_meio     ,
          @ds_emp_nome_de_familia
  END -– Para fechar o WHILE
  CLOSE      cr_empregado –-PARTE 5:FECHANDO O CURSOR
  DEALLOCATE cr_empregado –-PARTE 6:DESALOCANDO O CURSOR 

2. Reconstruindo a lógica em múltiplas consultas

Ainda mantendo o cursor da Listagem 2, mas melhorando sua performance, a Listagem 3 mostra uma possível solução para reconstruí-lo de uma forma mais simples. Para isso, é necessário remover algumas variáveis e transformar parte do código que busca linha por linha em duas queries.

Listagem 3. Melhorando o cursor com múltiplas consultas.

  -- Nova lógica da query de população do cursor
  SELECT tb_empregado.nr_empregado,
         tb_empregado.nr_gerente  ,
         tb_contato.ds_primeiro_nome
         + ISNULL(' '+tb_contato.ds_nome_do_meio+'. ', ' ')
         + tb_contato.ds_nome_de_familia AS Nome_Completo
  FROM   db_pessoa_p.tb_contato
   JOIN  db_recursos_humanos.tb_empregado
     ON  tb_contato.nr_contato
      =  tb_empregado.nr_contato
   
  -- Nova consulta da sentença WHILE
  SELECT @ds_ger_nome_completo = tb_contato.ds_primeiro_nome
         + ISNULL(' '+tb_contato.ds_nome_do_meio+'. ', ' ')
         + tb_contato.ds_nome_de_familia
  FROM   db_pessoa_p.tb_contato
   JOIN  db_recursos_humanos.tb_empregado
     ON  tb_contato.nr_contato
      =  tb_empregado.nr_contato
  WHERE  tb_empregado.nr_empregado = @nr_gerente 

3. Reconstruindo a lógica em uma função definida pelo usuário

A Listagem 4 mostra outra solução. Neste exemplo o cursor da Listagem 3 será totalmente substituído por uma função definida pelo usuário (user-defined function) que retornará o mesmo resultado do cursor de uma forma mais eficaz.

Listagem 4. Reconstruindo o cursor com uma função definida pelo usuário.

  -- Função que recebe o número da matrícula do gerente
  -- e retorna o seu nome completo
  CREATE FUNCTION dbo.NomeCompletoGerente
   
  -- Recebe a variável
  -- Número da matrícula do gerente do tipo inteiro
  ( @nr_gerente INT )
   
  -- Informa que o retorno da função será um VARCHAR(100)
  RETURNS VARCHAR (100) 
  AS
  BEGIN
    -- Declaração da variável que receberá
    -- os dados do SELECT
    DECLARE @GerNomeCompleto VARCHAR(100)
    
    -- Seleciona o nome completo do gerente
    -- e insere na variável @GerNomeCompleto
    SELECT  
          @GerNomeCompleto = tb_contato.ds_primeiro_nome
          + ISNULL(' '+tb_contato.ds_nome_do_meio+'. ', ' ')
          + tb_contato.ds_nome_de_familia
    FROM  db_pessoa_p.tb_contato
    JOIN  db_recursos_humanos.tb_empregado
      ON  tb_contato.nr_contato
       =  tb_empregado.nr_contato
    WHERE tb_empregado.nr_empregado = @nr_gerente
    
    -- Caso a consulta não retorne nenhuma linha, faça... 
    IF @@ROWCOUNT = 0
       BEGIN
         SET @GerNomeCompleto = NULL
       END
    -- Exibe o nome complete do gerente
    RETURN @GerNomeCompleto
  END 

4. Reconstruindo a lógica em uma consulta complexa

O passo mais avançado na refatoração é transformar completamente um cursor em um código SQL complexo, como exemplificado na Listagem 5.

O objetivo é substituir a função definida pelo usuário, criada na Listagem 4, por uma codificação mais rebuscada, que visa extrair do processador de consultas o maior ganho de performance possível através de um código.

Listagem 5. Reconstruindo o cursor com uma consulta complexa.

  -- 
  SELECT    CtEmp.ds_primeiro_nome
            + ISNULL(' '+ CtEmp.ds_nome_do_meio+'. ', ' ')
            + CtEmp.ds_nome_de_familia AS Nome_Completo_Emp,
            CtGer.ds_primeiro_nome
            + ISNULL(' '+ CtGer.ds_nome_do_meio+'. ', ' ')
            + CtGer.ds_nome_de_familia AS Nome_Completo_Ger
  FROM      db_pessoa_p.tb_contato AS CtEmp
  JOIN      db_recursos_humanos.tb_empregado
    ON      CtEmp.nr_contato = tb_empregado.nr_contato
  LEFT JOIN db_recursos_humanos.tb_empregado AS Gerente
         ON Gerente.nr_empregado = tb_empregado.nr_gerente
  LEFT JOIN db_pessoa_p.tb_contato AS CtGer
         ON CtGer.nr_contato = Gerente.nr_contato 

Usando as expressões PIVOT e CTE

Desde o SQL Server 2005 foram fornecidas duas funcionalidades muito úteis que ajudam na refatoração de um cursor. Uma é a CTE (Common Table Expression) e a outra é a sentença PIVOT. Estas expressões podem ser utilizadas individualmente ou juntas, visando diminuir a quantidade de código que precisa ser reescrito.

CTE é um resultado temporário que você define dentro de uma sentença SQL. Essa expressão é similar às tabelas temporárias e tabelas derivadas por serem montadas em tempo de execução. Elas permitem a criação de consultas aninhadas e recursivas, utilizando uma sintaxe mais legível do que qualquer outro recurso.

Na sua sintaxe as CTEs são definidas por um nome, uma lista de colunas entre parêntesis (opcional), e de uma consulta SQL. Depois de serem definidas, elas podem ser referenciadas nas sentenças: SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Dentro de um bloco de código as CTEs podem ser referenciadas diversas vezes mediante a necessidade do desenvolvedor.

A Listagem 6 mostra o exemplo de uma CTE em um bloco de código que tem como result set informações de vendas.

Listagem 6. Seleção de vendas utilizando CTE.

  WITH Vendas_CTE (nr_vendedor, nr_pedidos, dt_ult_pedido)
  AS
    (
      SELECT nr_vendedor      ,
            COUNT(*)          ,
            MAX(dt_pedido)    ,
      FROM  db_vendas.tb_pedido
    )
  SELECT E.nr_empregado  ,
         OS.nr_pedidos   ,
         OS.dt_ult_pedido 
  FROM   db_recursos_humanos.tb_empregado AS E
    JOIN Vendas_CTE AS OS
      ON E.nr_empregado = OS.nr_vendedor
      LEFT OUTER JOIN Vendas_CTE AS OM
      ON E.nr_gerente = OM.nr_vendedor
  ORDER BY E.nr_empregado; 

A outra funcionalidade é o comando PIVOT. Este permite que você crie uma visão de dados de uma consulta de tabela cruzada utilizando uma simples e legível sintaxe, ao invés de ter que escrever uma série de sentenças CASE.

Para entendermos como funciona esta sentença vamos preparar um pequeno ambiente para testarmos como o mesmo problema era solucionado no Microsoft SQL Server 2000 – antes de existir a sentença PIVOT – e na versão 2005, quando ela surgiu.

A Listagem 7 contém o código DDL para criação da tabela de trabalho tb_venda, seguido do código DML para inserção dos registros.

Listagem 7. Criando a tabela tb_venda.

  CREATE TABLE tb_venda
  ( ds_ano      INT         NOT NULL,
    ds_mes      INT         NOT NULL,
    ds_valor    NUMERIC(9,2) NOT NULL,
  )
  GO
  INSERT INTO tb_venda VALUES (2003, 2, 10)
  INSERT INTO tb_venda VALUES (2003, 2, 1)
  INSERT INTO tb_venda VALUES (2003, 3, 20)
  INSERT INTO tb_venda VALUES (2003, 4, 30)
  INSERT INTO tb_venda VALUES (2004, 1, 40)
  INSERT INTO tb_venda VALUES (2004, 2, 50)
  INSERT INTO tb_venda VALUES (2004, 3, 60)
  INSERT INTO tb_venda VALUES (2004, 4, 70)
  INSERT INTO tb_venda VALUES (2005, 1, 80)
  GO 

Executando um SELECT na tabela criada na Listagem 7 é obtido o resultado exibido na Tabela 2. Este resultado é fundamental para a conclusão do entendimento da funcionalidade do comando PIVOT.

Para gerar um resultado de pivotamento de tabela, na versão 2000 do Microsoft SQL Server era necessário utilizar uma função de grupo através da sentença CASE mais a cláusula GROUP BY (ver Listagem 8).

Listagem 8. Pivotamento de tabela através da expressão CASE.

  SELECT   ds_ano as Ano, 
      Jan = sum(case when ds_mes=1 then valor end),
      Fev = sum(case when ds_mes=2 then valor end), 
      Mar = sum(case when ds_mes=3 then valor end), 
      Abr = sum(case when ds_mes=4 then valor end) 
  FROM     tb_venda 
  GROUP BY ds_ano 
  ORDER BY ds_ano 

A Tabela 3 exibe o resultado da consulta da Listagem 8.

A Listagem 9 mostra a utilização do comando PIVOT para realizar o pivotamento de uma tabela com valores sumarizados por mês e por ano. O objetivo deste exemplo é servir de comparação com o script da Listagem 8 (que mostra a única solução na época quando não existia o comando PIVOT) para percebemos as diferenças de dois códigos com a mesma intenção de pivotamento.

Listagem 9. Pivotamento de tabela através da expressão PIVOT.

  SELECT   ds_ano as Ano,
           [1]    as Jan,
      [2]    as Fev,
      [3]    as Mar,
      [4]    as Abr 
  FROM     tb_venda 
  PIVOT   (SUM(ds_valor) FOR ds_mes IN ([1],[2],[3],[4])) p 
  ORDER BY 1 

A Tabela 4 exibe o resultado obtido pela execução do código da Listagem 9. Observe que o RESULT SET é exatamente o mesmo da Tabela 3.

Otimizando uma estratégia de indexação

Ao realizar o tunning de um banco de dados, um dos principais recursos para obtenção de uma melhora imediata na lentidão de um sistema é o uso apropriado de índices.

Os índices são a ponte de comunicação entre os dados e a consulta, e eles habilitam o processador de consultas para eficientemente encontrar os dados que você precisa.

Antes de entrarmos no entendimento do que são índices, como eles funcionam e quando devem ser criados, primeiramente temos que compreender como os dados são armazenados e acessados.

Como os dados são armazenados

O SQL Server armazena os dados em páginas independentemente do tipo de entidade, portanto, essa regra serve tanto para tabela com índice clusterizado (clustered index) quanto para HEAP TABLE (tabela não ordenada por índice clusterizado).

Alguns conceitos são necessários para a compreensão do assunto:

1. Páginas de dados (data pages): Os registros são armazenados em páginas de dados;
1. Cada página de dados contém 8 KB (kilobytes) de informação. Um grupo de oito páginas adjacentes é chamado de um EXTENT;
2. Quando uma linha é inserida em uma página que já se encontra cheia, a página é dividida e metade das linhas é movida para a nova página.

2. HEAPS: Uma heap é uma coleção de páginas de dados de uma tabela que não possui um índice clusterizado;
1. As linhas de dados não são armazenadas em uma ordem particular, e não existe nenhuma ordem na sequência das páginas de dados.

3. Índice Clusterizado: Conhecido também como índice agrupado, este tipo é criado automaticamente por DEFAULT na definição de uma chave primária quando não for especificado o tipo de índice desejado (CLUSTERED, que é o default, e NONCLUSTERED) em sua criação;
1. As linhas de dados são fisicamente armazenadas em uma ordem baseada no índice da chave clusterizada;
2. Tanto a ordenação física das linhas da tabela quanto a ordem das linhas no índice são as mesmas.

4. Estrutura B-Tree (Árvore binária): É uma estrutura de dados com um conjunto finito de elementos denominados vértices ou nós.
1. O SQL Server utiliza o mesmo princípio da lista telefônica, gravando as informações dos índices em uma estrutura chamada B-Tree. Uma estrutura B-Tree possui um nó-raiz que contém uma única página de dados, uma ou mais páginas de níveis intermediários e uma ou mais páginas de níveis folha. A Figura 1 mostra um exemplo desta estrutura.

Como os dados são acessados

O SQL Server acessa os dados de duas maneiras:

1. Percorrendo todas as páginas de dados da tabela (chamado TABLE SCAN). Essa varredura é realizada da seguinte forma:
1. Começa do início da tabela;
2. Varre página a página através de todas as linhas da tabela;
3. Extrai as linhas que atendem ao critério da consulta.

2. Utilizando índices. Esse processo ocorre da seguinte forma:
1. Percorre a estrutura da árvore do índice para encontrar as linhas solicitadas pela query;
2. Extrai somente as linhas necessárias que atendem ao critério da consulta.

Ao realizar uma consulta, o SQL Server primeiramente analisa a existência de um índice. Então o otimizador de consultas – componente responsável por gerar o melhor plano de execução para uma query – determina se é mais eficaz varrer toda a tabela ou se utiliza o índice para acessar os dados.

O SQL Server usa índices para facilitar a busca de informações em uma tabela com o menor número possível de operações de leitura, tornado assim a busca mais rápida e eficiente. Com os dados predispostos em uma estrutura de árvore, o otimizador de consultas precisa apenas interrogar um pequeno bloco de linhas de uma tabela para resolver os predicados e as condições de JOIN.

O uso apropriado de índices reduz substancialmente as operações de I/O e memória, portanto, melhora as consultas do banco de dados e o desempenho geral do banco como um todo. Contudo, uma estratégia de indexação ineficiente pode afetar negativamente a performance de um sistema.

Tipos de índice

O processador de consultas relacional do Microsoft SQL Server utiliza árvores binárias (B-trees) para a sua estrutura de índice. Os tipos disponíveis de índices são:

1. Índice clusterizado (clustered index);
1. Ele reorganiza a ordem física dos dados na tabela em que foi criado;
2. Só é possível criar um índice deste tipo por tabela, porque os dados de uma tabela só podem ser ordenados em apenas um sentido;
3. Por padrão todas as chaves primárias criadas no SQL Server utilizam índices clusterizados.

2. Índice não clusterizado (non-clustered index);
1. Ele armazena uma cópia dos dados usados para definir sua chave e reorganiza a ordem física desta cópia de dados;
2. Além disso, se existir um índice clusterizado na tabela, o índice não clusterizado também armazena os valores da chave clusterizada;
3. Caso a tabela seja uma HEAP, ou seja, se não tiver um índice clusterizado, o localizador de linha será um ponteiro para a linha. O ponteiro é criado a partir do ID (identificador) do número da página e do número da linha na página do arquivo. O ponteiro inteiro é conhecido como RID (Identificação de Linha);
4. Por usar uma cópia dos dados, em uma tabela podem ser criados mais de um índice deste tipo;
5. Por padrão, UNIQUE CONSTRAINTS criadas no SQL Server utilizam índices não clusterizados.

3. Índice único (unique index);
1. Ele garante que a chave do índice não conterá valores duplicados, consequentemente todas as linhas da tabela ou da visão são únicas;
2. Tanto índices clusterizados como não clusterizados podem ser únicos.

4. Índice com colunas inclusas (index with included columns);
1. É um índice não clusterizado que tem adicionado às colunas chaves outras colunas que não fazem parte da chave.

5. Visão indexada (indexed views);
1. Uma visão indexada tem seu resultado permanentemente armazenado em um índice clusterizado único;
2. Somente depois de ser criado um índice clusterizado na VIEW é que pode ser adicionado outro não clusterizado.

6. Índice de texto completo (full-text index);
1. É um tipo especial de índice funcional baseado em símbolos;
2. Fornece suporte eficiente para sofisticadas buscas por palavras em linhas de caracteres de dados.

7. Índice XML.
1. É uma representação de grandes objetos XML binários em uma coluna do tipo XML.

Diretrizes para se criar índices

Existem alguns cuidados que devem ser tomados na hora da criação de um índice, pois se as escolhas de indexação forem mal planejadas a intenção de tornar o sistema mais rápido pode ficar comprometida.

1. Examine as características do banco de dados;
1. A sua estratégia de indexação irá diferir, por exemplo, entre um sistema com transações online com atualizações frequentes e um sistema de DATAWAREHOUSE que contém somente dados de leitura.

2. Entenda as características das consultas frequentemente usadas e das colunas utilizadas nas queries;
1. Por exemplo, você pode precisar criar um índice em uma consulta que utiliza joins ou que usa uma coluna única no seu argumento de busca (na cláusula WHERE).

3. Quando um índice é criado ou recriado, é possível escolher algumas opções. Decida qual delas irá prover melhor performance;
1. As opções que podem afetar a eficiência de um índice são: FILLFACTOR e ONLINE.

4. Determine o melhor local de armazenamento para o índice;
1. Você pode escolher entre armazenar índices não clusterizados no mesmo grupo de arquivos (filegroup) que estão armazenados os dados (tabela) ou em outro diferente;
2. Se os índices forem armazenados em um FILEGROUP que está em um disco diferente do HD de dados, a performance de I/O de disco será bem melhor porque múltiplos discos podem realizar leituras ao mesmo tempo.

5. Balanceie a performance de escrita e leitura no banco de dados;
1. Índices não clusterizados podem ser criados em uma tabela, mas é importante lembrar que a cada novo índice criado há um impacto na performance das operações de inserção e atualização;
2. Isso ocorre porque os índices não clusterizados mantêm cópias dos dados, e cada cópia destas requerem operações de I/O, podendo causar uma redução no desempenho de escrita se o banco tiver que escrever muitas cópias;
3. Ao fazer uma estratégia de indexação é preciso examinar a quantidade de operações de consulta (selects) e atualização (updates) para balancear de acordo com a necessidade do sistema.

6. Considere o tamanho das tabelas no banco de dados;
1. O processador de consultas pode demorar mais para varrer um índice de uma tabela pequena do que realizar um TABLE SCAN. Consequentemente, mediante a esta avaliação, o processador de consultas nunca usará este índice, contudo, este índice continuará sendo atualizado quando houver mudança nos dados da tabela.

7. Considere o uso de visões indexadas (indexed views).
1. Podem garantir um ganho significativo de performance quando existirem agregações ou joins de tabela na VIEW.

Índices não clusterizados

Utilize estes índices para aumentar a performance de consultas que não usam o índice clusterizado. Diferentemente dos índices clusterizados, que reorganizam a própria tabela, os índices não clusterizados organizam somente os dados que foram especificamente incluídos no índice.

Índices são armazenados em páginas, e quando o processador de consultas faz uma leitura dos dados no disco, ele lê uma página inteira de uma só vez. Os índices não clusterizados geralmente contêm menos dados que os clusterizados, sendo assim, podem caber mais registros em uma página.

Uma leitura de dados feita utilizando este tipo de índice requer menos operações de I/O do que uma feita através de um índice clusterizado. Por esta razão, este índice é a melhor escolha quando poucas colunas são usadas em uma query.

Quando utilizar índices não clusterizados

Este tipo de índice deve ser criado quando existirem colunas na query envolvidas em:

• Predicados;
• Joins;
• Agregações.

Quando uma query é executada o otimizador de consultas varre o índice não clusterizado com o objetivo de encontrar o local dos dados que estão dentro da tabela e então retorna os valores requeridos na consulta para o solicitante. Isso faz com que estes índices sejam a melhor escolha para consultas com colunas que tenham as funções acima mencionadas.

Depois que o otimizador de consultas encontra todas as entradas no índice, ele pode ir diretamente à página e linha exatas para retornar os dados.

O índice não clusterizado atende uma consulta se no índice contiver todas as colunas utilizadas na query. Neste sentido, a performance vai ser bastante similar a uma consulta que utiliza índice clusterizado.

Índices com colunas inclusas

Desde a versão 2005, o SQL Server fornece a possibilidade de incluir colunas não chave às colunas chave de um índice não clusterizado.

As colunas não chave são armazenadas no último nível da árvore binária de um índice. Esse tipo de índice é muito parecido com um não clusterizado quando este possuir todas as colunas da consulta, porém nele há um ganho maior de desempenho porque as páginas de índices não são movidas se forem realizadas atualizações nas colunas não chave.

A Listagem 10 mostra a sintaxe para criar um índice com colunas inclusas.

Listagem 10. Criação de um índice com colunas inclusas.

  CREATE NONCLUSTERED INDEX idx_empregado
  ON db_recursos_humanos.tb_empregado (nr_empregado)
  INCLUDE (ds_endereco, nr_telefone, nr_CPF) 

Cuidados a serem evitados na criação de um índice não clusterizado

Existem alguns erros de indexação que podem levar a um sistema ineficiente, afetando o desempenho do banco de dados. Vejamos alguns deles:

1. Índices redundantes: São aqueles criados desnecessariamente, com colunas duplicadas em outros. Consomem espaço de disco e também afetam o desempenho de manutenção do índice;

2. Índices compostos por várias colunas: Forçam o processador de consultas a realizar buscas em todas as colunas do índice;

3. Índices para uma consulta: Antes de criar um índice para atender a apenas uma consulta, deve ser considerado se este pode atender outras queries para evitar consumo de espaço físico;

4. Índices não clusterizados que incluem índices clusterizados: Não é necessário adicionar as colunas de um índice clusterizado ao final das colunas de um índice não clusterizado porque elas são incluídas automaticamente.

Quando utilizar índices clusterizados

Existem alguns tipos de consultas que são candidatas potenciais a utilizarem este tipo de índice para obter um ganho maior de desempenho. Dentre elas podemos citar:

1. Consultas de escala;
1. Esse tipo de consulta, como aquelas que contêm coluna de data, são exigidas comumente em sistemas transacionais e multidimensionais;
2. Geralmente envolve a necessidade de selecionar todos os dados de certo período;
3. O processador de consultas pode usar um índice clusterizado para eficientemente retornar páginas de dados baseados em um escopo e ler todos os dados das colunas no BUFFER CACHE;
4. A razão para isso se dá porque depois que o processador de consultas encontra a linha com o primeiro valor do escopo, as linhas com valores subsequentes irão ficar adjacentes fisicamente no índice clusterizado.

2. Consultas de chave primária;
1. Podem causar gargalos na performance se a chave primária não for um índice clusterizado. Neste caso, o processador de consultas irá realizar uma procura utilizando a chave para obter as outras colunas requisitadas pela query;
2. Para evitar um custo alto nas operações de procura, transforme a chave primária para utilizar índice clusterizado ou crie um índice que tenha todas as colunas da consulta.

3. Consultas que retornam dados de várias colunas.
1. Quando o processador de consultas lê os dados do disco, ele sempre lê uma página inteira onde estão os registros solicitados na query, mesmo que estes não ocupem toda a página;
2. Como dito anteriormente, índices clusterizados são, na realidade, reorganizações físicas dos dados físicos da tabela;
3. Consequentemente, quando o processador de consultas lê as páginas de dados utilizando a chave de um índice clusterizado, ele simultaneamente lê todas as colunas associadas na query;
4. Neste sentido, você deve criar índices clusterizados em chaves que são usadas em uma consulta para retornar os dados.

Baseado nas informações mencionadas acima, você deve considerar criar índices clusterizados em colunas que são:

• Únicas ou contêm muitos valores distintos;
• Acessadas com valores sequenciais;
• Usadas como chave estrangeira (foreign key);
• Usadas frequentemente para organizar os dados retornados da tabela.

Quando não usar índices clusterizados

Você deve considerar não usar esse tipo de índice nas seguintes situações:

1. Quando colunas sofrem mudanças constantes;
1. Alterações em uma coluna que possui um índice clusterizado causam a movimentação de toda a linha, pois o processador de consultas mantém os valores dos dados da linha em uma ordem física. Isso pode causar problemas de performance em sistemas que têm dados voláteis;

2. Quando estiver usando chaves com muitas colunas.
1. Chaves grandes consistem em muitas colunas. Já os Índices não clusterizados usam os valores da chave do índice clusterizado como chaves de pesquisa;
2. Se o índice clusterizado tem uma chave grande, o índice não clusterizado também terá um tamanho significativamente grande. Isso ocorre porque nele estão as várias colunas da chave clusterizada, assim como as colunas do próprio índice clusterizado.

Como documentar uma estratégia de indexação

Índices são fundamentais para garantir um banco de dados com alta performance, contudo, você achará difícil decidir quais índices deverão ser criados e como documentar este processo de decisão.

Existe um método que pode ser utilizado para garantir uma boa prática na estratégia de indexação que visa facilitar a documentação e escolha adequada para a criação de um índice. Este método é conhecido como planilha de estratégia de indexação.

Esta planilha deverá conter uma linha para cada coluna de uma tabela e uma coluna para cada STORED PROCEDURE existente. Após montar a planilha, sinalize com um X cada coluna de tabela que possuir uma SP que utiliza aquela coluna.

Adote uma codificação para sinalizar na planilha que aquela SP utiliza a coluna em uma condição de JOIN, ou em um predicado ou na lista do SELECT. Então você pode utilizar essas informações da planilha de estratégia de indexação para orientar na tomada de decisão na hora de criar um índice.

Dessa forma, com uma rápida leitura desta planilha fica fácil saber quais índices devem ser criados para melhorar o desempenho. Assim, você pode definir os índices por um processo de eliminação. A partir disso, considere criar índices clusterizados para as colunas que você marcou como bastante utilizadas em predicados ou em condições de JOIN. Para aquelas colunas menos utilizadas em predicados e em condições de JOIN, pondere criar índices não clusterizados. Finalmente, considere criar um índice se várias consultas utilizam repetidamente o mesmo grupo de colunas.

A Figura 2 exemplifica o preenchimento da planilha de estratégia de indexação que serve de orientação para a criação de índices por sistema.

Gerenciando concorrência

A essência de um Sistema Gerenciador de Bancos de Dados é armazenar e gerenciar registros que geralmente são compartilhados, sendo aptos a atenderem requisições de diversos usuários ao mesmo tempo. Contudo, a habilidade para acessar e modificar concorrentemente o mesmo registro tem um custo.

Se outro usuário estiver modificando um registro que você precise, você terá que esperá-lo terminar para ter acesso a esse dado. O SQL Server implementa essa funcionalidade com o uso de LOCKS que bloqueiam o registro para garantir que os outros usuários vejam apenas o dado correto.

Para criar uma aplicação com um rápido desempenho de resposta, você deve gerenciar a alocação de LOCKS e também qualquer bloqueio que ocorra.

Os níveis de isolamento de transação definem o grau ao qual uma transação deve ser isolada na hora de uma modificação de dados feita por outras transações. Esses níveis ajudam no gerenciamento quando ocorrem efeitos secundários (veja a Nota DevMan 1) em transações concorrentes, como dependências não confirmadas, isto é, leituras sujas ou leituras fantasmas.

Os níveis de isolamento de transações servem para controlar as seguintes situações:

1. Quando estiver ocorrendo LOCK durante o acesso a um registro;
2. Controlar o tempo que um registro está bloqueado durante um LOCK de leitura;
3. Se operações de leitura estiverem referenciando registros modificados por outra transação:
4. Bloqueia o registro até que o LOCK exclusivo seja liberado;
   1. Recupera a versão confirmada do registro que existia no momento em que foi iniciada a transação;
   2. Lê as modificações de registros não confirmados.

Um baixo nível de isolamento aumenta a possibilidade de vários usuários acessarem um registro ao mesmo tempo, e também aumenta a quantidade dos efeitos de concorrência (como leituras sujas ou atualizações perdidas) que os usuários irão se deparar.

Inversamente, um alto nível de isolamento reduz alguns tipos de efeitos de concorrência que usuários podem encontrar, porém exigem mais recursos do sistema e aumenta a chance de uma transação bloquear a outra.

Ao escolher um nível de isolamento apropriado, deve ser posto na balança o custo entre as requisições de integridade de dados da aplicação e o nível de isolamento escolhido.

O mais alto nível de isolamento (serializable) garante que a transação irá recuperar exatamente o mesmo registro toda vez que repetir uma operação de leitura. Contudo, essa garantia requer um nível de bloqueio que irá afetar outros usuários em sistemas multiusuários.

O mais baixo nível de isolamento (read uncommitted) recupera os registros que outras transações modificaram, mas não confirmaram. Este nível minimiza o custo do sistema porque não existe bloqueio de leitura nem versionamento de registro, por outro lado permite a leitura suja de dados.

Os níveis de isolamento existentes são:

1. Leitura não confirmada (Read uncommitted): Este é o nível menos restritivo e especifica que as requisições podem ler o que outras transações modificaram, mesmo que o registro não tenha sido confirmado. As transações são isoladas somente o suficiente para garantir que dados corrompidos fisicamente não sejam lidos. Neste nível são prevenidas as atualizações perdidas, mas podem ocorrer leituras sujas, leituras não repetíveis e leituras fantasmas;
2. Leitura confirmada (Read committed): Este é o nível default do banco de dados. Embora possam ocorrer leituras não repetíveis e fantasmas, as leituras sujas não acontecem. No SQL Server 2005 existe uma nova implementação neste nível – quando a opção READ_COMMITTED_SNAPSHOT for configurada para ON.
1. Este nível usará versionamento para ler a última versão confirmada do registro se este estiver bloqueado exclusivamente por outro usuário.
2. Operações de leitura requerem apenas o nível de bloqueio de tabela SCH-S (veja a Nota DevMan 1) (veja a Nota DevMan 2) e nenhum bloqueio de registro ou página.
3. Quando a opção READ_COMMITTED_SNAPSHOT do banco estiver configurada para OFF, que é o default, o comportamento continua o mesmo das versões anteriores do SQL Server para o isolamento de leituras confirmadas;
3. Leitura Repetível (Repeatable read): Esse nível especifica que requisições não podem ler o registro que outra transação modificou, mas não confirmou. Significa também que outra transação não pode modificar dados que foram lidos pela transação atual até que ela seja completada. Não ocorrem leituras sujas e não repetíveis; contudo, leituras fantasmas podem ocorrer.
1. Devido aos bloqueios compartilhados serem mantidos até que se encerre a transação, em vez de ser liberado ao término de cada sentença SQL, a concorrência é mais baixa que o nível de isolamento READ COMMITED. Utilize esse nível somente quando for necessário;
4. Instantâneo (Snapshot): Este foi um nível de isolamento que surgiu no SQL Server 2005. Utiliza versionamento de registro para fornecer consistência de leitura no nível de transação.

Operações de leitura não recebem bloqueios de registros ou páginas; ocorre somente o bloqueio de tabela do tipo SCH-S. Quando ocorrer leitura de registros modificados por outra transação, ele recupera a versão do registro que existia quando foi iniciada a transação.

Geralmente, transações SNAPSHOT não requerem bloqueios quando os dados estão sendo lidos. Leitura de registros nesse tipo de isolamento não bloqueiam outras transações que estão gravando registros, e as transações que estiverem gravando registros não bloqueiam transações SNAPSHOT que estejam lendo registros. Leituras sujas, não repetíveis, e fantasmas não ocorrem neste nível.

Transações que rodam sob este nível de isolamento que selecionam registros para atualização não recebem bloqueios.

Quando uma linha de dados encontra um critério de atualização, a transação SNAPSHOT verifica se o registro não foi modificado por uma transação concorrente que foi confirmada depois que a transação SNAPSHOT começou. Se a linha foi modificada por uma transação externa à SNAPSHOT, ocorre um conflito de atualização e a transação SNAPSHOT é finalizada.

Um custo que existe neste tipo de isolamento é que ele faz bastante uso do banco tempdb. Cada vez que um registro é modificado por uma transação, a instância do SGBD armazena a imagem da versão previamente confirmada do registro no tempdb. Cada versão é marcada com um número sequencial da transação que fez a modificação.

A Listagem 11 mostra a sintaxe para configurar este nível de isolamento em um banco chamado db_teste.

Listagem 11. Configurando o nível de isolamento SNAPSHOT.

  ALTER DATABASE db_teste
  SET ALLOW_SNAPSHOT_ISOLATION ON
  GO
  SET TRANSACTION ISOLATION LEVEL SNAPSHOT        

5. Serializável (Serializable): Esse nível garante que as transações sejam completamente isoladas uma da outra. Este é o mais restritivo nível de isolamento porque ele bloqueia conjuntos de chaves inteiras (compostas) e segura o bloqueio até que a transação termine. Embora não ocorram leituras sujas, não repetíveis e fantasmas, a concorrência é baixa, portanto, use este nível de isolamento somente quando necessário.

A Tabela 5 mostra os efeitos colaterais de simultaneidade habilitados por níveis de isolamento diferentes.