SQL Queries: Escrevendo queries otimizadas no SQL Server

Fique por dentro

Al�m de ser um modelo de boas pr�ticas a serem seguidas, estas t�cnicas de otimizaç�o de consultas ajudam a otimizar o tempo de resposta dos sistemas em ambientes com limitaç�es de hardware e banda de rede.

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:

Favoreça a l�gica de conjuntos ao inv�s da l�gica de procedimentos;

O fator mais importante a ser considerado quando estiver otimizando queries � saber como funciona a manipulaç�o da l�gica de conjuntos de registros;
Cursores e outras construç�es procedimentais geralmente limitam a habilidade do otimizador de consultas para gerar planos de consultas flex�veis.

Teste variaç�es de queries objetivando a performance;

Frequentemente o otimizador pode produzir planos totalmente diferentes para queries logicamente equivalentes;
Teste t�cnicas diferentes, como joins ou subqueries, para descobrir qual delas � mais eficiente.

Evite QUERY HINTS (dar dica ao banco sobre qual �ndice ele deve usar);

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;
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.

Use subqueries correlatas;

O otimizador est� apto a trabalhar para integrar subqueries no fluxo da query principal em uma variedade de caminhos escolhidos por ele;
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;
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)

Evite funç�es definidas pelo usu�rio na cl�usula WHERE;

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;
Usar funç�o escalar na lista do SELECT � muito menos problem�tico porque as linhas j� foram filtradas na cl�usula WHERE.

Use funç�es tabulares (table-valued) como tabelas derivadas;

S�o funç�es que retornam o resultado em formato de tabela;
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;
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.

Evite colunas GROUP BY desnecess�rias;

Quanto mais colunas na lista da cl�usula GROUP BY voc� adicionar, mais o processo de agrupar linhas torna-se dispendioso;
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.

Use express�es CASE;

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;
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.

Divida JOINS em tabelas tempor�rias.

A estrat�gia principal do otimizador � encontrar planos que satisfaçam consultas utilizando operaç�es simples;
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;
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;
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:

Declaraç�o das vari�veis que ir�o armazenar os dados que ser�o retornados pelo cursor;
Uso da sentença DECLARE CURSOR para definir a consulta que esse cursor retornar�;
Uso da sentença OPEN para executar a consulta e popular o cursor;
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;
Uso da sentença CLOSE para fechar o cursor;
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.

Tipo de problema	Descriç�o	Soluç�o recomendada	Quando usar cursores
L�gica complexa	L�gicas complexas s�o normalmente dif�ceis de refatorar e ajustar em uma soluç�o SQL. Geralmente s�o desenvolvidas em uma das soluç�es abaixo: � feita a codificaç�o de toda l�gica em um loop de cursor; � criada uma STORED PROCEDURE que aceita um identificador, processa uma linha e retorna o valor calculado ou atualiza a linha.	Reconstruir a l�gica em um SQL que utiliza a express�o CASE que torna poss�vel manipular diversas variaç�es de sa�da.	Muito raramente
Iteraç�o de c�digo din�mico	� a criaç�o de objetos de banco de dados atrav�s da linguagem DDL dinamicamente.	Para essa situaç�o o uso de cursor � a melhor soluç�o.	Sempre
Desnormalizando uma lista	� a convers�o de uma lista ou conjunto de registros oriundos de diversas tabelas centralizando em apenas uma. Esta t�cnica � bastante utilizada para um ambiente de DW (Data Warehouse).	Para essa tarefa o comando SELECT INTO pode ser utilizado para criar a nova tabela desnormalizada a partir de outras fontes de dados. O mesmo pode ser feito atrav�s da sentença INSERT de um SELECT.	Algumas vezes
Construindo uma consulta de tabela cruzada	A consulta do tipo tabela de refer�ncia cruzada � utilizada para sumarizar informaç�es com a intenç�o de c�lculo estat�stico. O resultado deste tipo de consulta � apenas de leitura. Dados n�o podem ser adicionados, editados ou deletados.	Para construir uma consulta de tabela cruzada � necess�rio utilizar uma s�rie de express�es CASE. Desde o SQL Server 2005 existe uma sintaxe chamada PIVOT, que faz efetivamente a mesma coisa que uma s�rie de express�es CASE.	Nunca
Totais cumulativos (rodando somat�rias)	S�o facilmente adicionados em uma ferramenta de relat�rio, mas algumas vezes estes totais devem ser calculados dentro do SQL Server e escritos em uma tabela. Por exemplo, quando um total cumulativo deve ser retido por raz�es de integridade da informaç�o, como uma coluna de balanço financeiro.	Embora totais cumulativos possam ser obtidos utilizando uma subquery correlata, um cursor resolve melhor este problema.	Sempre
Navegando em uma �rvore hier�rquica	Programadores procedurais geralmente abordam essa tarefa examinando recursivamente cada n�.	Resolve-se usando STORED PROCEDURES ou funç�es definidas pelo usu�rio (user-defined functions) que s�o mais r�pidas que m�todos procedurais.	Nunca

Tabela 1. Relaç�o do uso de cursores X problema.

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.

	ds_ano	ds_mes	ds_valor
1	2008	2	1.00
2	2008	2	10.00
3	2008	3	20.00
4	2008	4	30.00
5	2009	1	40.00
6	2009	2	50.00
7	2009	3	60.00
8	2009	4	70.00
9	2010	1	80.00

Tabela 2. Consultando todas as linhas de tb_venda.

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.

	Ano	Jan	Fev	Mar	Abr
1	2008	NULL	11.00	20.00	30.00
2	2009	40.00	50.00	60.00	70.00
3	2010	80.00	NULL	NULL	NULL

Tabela 3. Resultado da consulta CASE

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.

	Ano	Jan	Fev	Mar	Abr
1	2008	NULL	11.00	20.00	30.00
2	2009	40.00	50.00	60.00	70.00
3	2010	80.00	NULL	NULL	NULL

Tabela 4. Resultado da consulta com a express�o PIVOT.

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:

P�ginas de dados (data pages): Os registros s�o armazenados em p�ginas de dados;

Cada p�gina de dados cont�m 8 KB (kilobytes) de informaç�o. Um grupo de oito p�ginas adjacentes � chamado de um EXTENT;
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.

HEAPS: Uma heap � uma coleç�o de p�ginas de dados de uma tabela que n�o possui um �ndice clusterizado;

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.

�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;

As linhas de dados s�o fisicamente armazenadas em uma ordem baseada no �ndice da chave clusterizada;
Tanto a ordenaç�o f�sica das linhas da tabela quanto a ordem das linhas no �ndice s�o as mesmas.

Estrutura B-Tree (�rvore bin�ria): � uma estrutura de dados com um conjunto finito de elementos denominados v�rtices ou n�s.

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.

Figura 1. Estrutura B-Tree.

Como os dados s�o acessados

O SQL Server acessa os dados de duas maneiras:

Percorrendo todas as p�ginas de dados da tabela (chamado TABLE SCAN). Essa varredura � realizada da seguinte forma:

Começa do in�cio da tabela;
Varre p�gina a p�gina atrav�s de todas as linhas da tabela;
Extrai as linhas que atendem ao crit�rio da consulta.

Utilizando �ndices. Esse processo ocorre da seguinte forma:

Percorre a estrutura da �rvore do �ndice para encontrar as linhas solicitadas pela query;
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:

�ndice clusterizado (clustered index);

Ele reorganiza a ordem f�sica dos dados na tabela em que foi criado;
S� � poss�vel criar um �ndice deste tipo por tabela, porque os dados de uma tabela s� podem ser ordenados em apenas um sentido;
Por padr�o todas as chaves prim�rias criadas no SQL Server utilizam �ndices clusterizados.

�ndice n�o clusterizado (non-clustered index);

Ele armazena uma c�pia dos dados usados para definir sua chave e reorganiza a ordem f�sica desta c�pia de dados;
Al�m disso, se existir um �ndice clusterizado na tabela, o �ndice n�o clusterizado tamb�m armazena os valores da chave clusterizada;
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);
Por usar uma c�pia dos dados, em uma tabela podem ser criados mais de um �ndice deste tipo;
Por padr�o, UNIQUE CONSTRAINTS criadas no SQL Server utilizam �ndices n�o clusterizados.

�ndice �nico (unique index);

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;
Tanto �ndices clusterizados como n�o clusterizados podem ser �nicos.

�ndice com colunas inclusas (index with included columns);

� um �ndice n�o clusterizado que tem adicionado �s colunas chaves outras colunas que n�o fazem parte da chave.

Vis�o indexada (indexed views);

Uma vis�o indexada tem seu resultado permanentemente armazenado em um �ndice clusterizado �nico;
Somente depois de ser criado um �ndice clusterizado na VIEW � que pode ser adicionado outro n�o clusterizado.

�ndice de texto completo (full-text index);

� um tipo especial de �ndice funcional baseado em s�mbolos;
Fornece suporte eficiente para sofisticadas buscas por palavras em linhas de caracteres de dados.

�ndice XML.

� 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.

Examine as caracter�sticas do banco de dados;

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.

Entenda as caracter�sticas das consultas frequentemente usadas e das colunas utilizadas nas queries;

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).

Quando um �ndice � criado ou recriado, � poss�vel escolher algumas opç�es. Decida qual delas ir� prover melhor performance;

As opç�es que podem afetar a efici�ncia de um �ndice s�o: FILLFACTOR e ONLINE.

Determine o melhor local de armazenamento para o �ndice;

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;
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.

Balanceie a performance de escrita e leitura no banco de dados;

�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;
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;
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.

Considere o tamanho das tabelas no banco de dados;

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.

Considere o uso de vis�es indexadas (indexed views).

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:

Consultas de escala;

Esse tipo de consulta, como aquelas que cont�m coluna de data, s�o exigidas comumente em sistemas transacionais e multidimensionais;
Geralmente envolve a necessidade de selecionar todos os dados de certo per�odo;
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;
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.

Consultas de chave prim�ria;

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;
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.

Consultas que retornam dados de v�rias colunas.

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;
Como dito anteriormente, �ndices clusterizados s�o, na realidade, reorganizaç�es f�sicas dos dados f�sicos da tabela;
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;
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:

Quando colunas sofrem mudanças constantes;

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;

Quando estiver usando chaves com muitas colunas.

Chaves grandes consistem em muitas colunas. J� os �ndices n�o clusterizados usam os valores da chave do �ndice clusterizado como chaves de pesquisa;
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.

Figura 2. Planilha de estrat�gia de indexaç�o.

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:

Quando estiver ocorrendo LOCK durante o acesso a um registro;
Controlar o tempo que um registro est� bloqueado durante um LOCK de leitura;
Se operaç�es de leitura estiverem referenciando registros modificados por outra transaç�o:
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:

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;
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.

Este n�vel usar� versionamento para ler a �ltima vers�o confirmada do registro se este estiver bloqueado exclusivamente por outro usu�rio.
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.
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;

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.

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;

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

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.

N�vel de isolamento	Leitura suja	Leitura n�o repet�vel	Fantasma
	Tipo de leitura
Leitura n�o confirmada	Sim	Sim	Sim
Leitura confirmada	N�o	Sim	Sim
Leitura repet�vel	N�o	N�o	Sim
Instant�neo	N�o	N�o	N�o
Serializ�vel	N�o	N�o	N�o

Tabela 5. N�veis de isolamento por tipo de leitura.

Nota: Efeitos de simultaneidade

Existem alguns tipos de efeito de simultaneidade que ocorrem quando duas ou mais transaç�es est�o disputando o mesmo registro, aos quais podemos citar:

1. Atualizaç�es perdidas: As atualizaç�es perdidas acontecem quando duas ou mais transaç�es selecionam a mesma linha e ent�o atualizam a linha com base no valor selecionado originalmente.

Cada transaç�o n�o tem conhecimento das outras. Assim, a �ltima atualizaç�o substitui atualizaç�es feitas pelas outras transaç�es, o que resulta em dados perdidos.

Por exemplo, dois editores fazem uma c�pia eletr�nica do mesmo documento. Cada editor altera a c�pia de maneira independente e salva a c�pia alterada, substituindo, portanto, o documento original.

O editor que salva a c�pia alterada por �ltimo substitui as alteraç�es feitas pelo outro editor. Esse problema poderia ser evitado se um editor n�o pudesse acessar o arquivo at� que o outro tivesse terminado e confirmado a transaç�o;

2. Depend�ncia n�o confirmada (leitura suja): A depend�ncia n�o confirmada acontece quando uma segunda transaç�o seleciona uma linha que est� sendo atualizada por outra transaç�o. Deste modo, a segunda transaç�o est� lendo dados que n�o foram confirmados ainda e podem ser alterados pela transaç�o que atualiza a linha.

Por exemplo, um editor est� fazendo mudanças em um documento eletr�nico. Durante as mudanças, um segundo editor pega uma c�pia do documento que inclui todas as mudanças feitas at� o momento e distribui o documento para a audi�ncia destinada.

O primeiro editor decide ent�o que as mudanças feitas at� o momento est�o erradas, remove as ediç�es e salva o documento. O documento distribu�do cont�m ediç�es que j� n�o existem e que deveriam ser tratadas como se nunca tivessem existido.

Esse problema poderia ser evitado se ningu�m pudesse ler o documento alterado at� que o primeiro editor salvasse a vers�o final com as modificaç�es e confirmasse a transaç�o;

3. An�lise inconsistente (leitura n�o-repet�vel): Ocorre an�lise inconsistente quando uma segunda transaç�o acessa a mesma linha v�rias vezes e l� dados diferentes a cada vez.

A an�lise inconsistente � semelhante � depend�ncia n�o confirmada, no sentido em que outra transaç�o est� alterando os dados que uma segunda transaç�o est� lendo.

No entanto, na an�lise inconsistente os dados lidos pela segunda transaç�o foram confirmados pela transaç�o que fez as alteraç�es. Al�m disso, a an�lise inconsistente envolve leituras m�ltiplas (duas ou mais) da mesma fila, e a cada vez as informaç�es s�o alteradas por outra transaç�o; da� a denominaç�o leitura n�o-repet�vel.

Por exemplo, um editor l� o mesmo documento duas vezes, mas entre cada leitura o escritor reescreve o documento. Quando o editor l� o documento pela segunda vez, este j� foi alterado. A primeira leitura n�o era repet�vel.

Esse problema poderia ser evitado se o escritor n�o pudesse alterar o documento at� que o editor tivesse terminado de l�-lo pela �ltima vez;

4. Leituras fantasma: Leituras fantasmas acontecem quando uma aç�o de inserç�o ou exclus�o � executada em uma linha que pertence a um intervalo de linhas que est�o sendo lidas por uma transaç�o.

A primeira leitura do intervalo de linhas da transaç�o mostra um registro que n�o existir� quando for feita a segunda leitura, porque esse registro foi exclu�do por uma transaç�o diferente.

De maneira semelhante, a segunda leitura da transaç�o mostra um registro que n�o existia na primeira leitura, porque foi realizada uma inserç�o por uma transaç�o diferente.

Por exemplo, um editor faz alteraç�es em um documento enviado por um escritor, mas quando as alteraç�es s�o incorporadas na c�pia mestra do documento pelo departamento de produç�o, descobre-se que material novo n�o editado foi acrescentado ao documento pelo autor.

De modo semelhante � situaç�o de leitura n�o-repet�vel, esse problema poderia ser evitado se ningu�m pudesse acrescentar material novo ao documento at� o editor e o departamento de produç�o terminarem de trabalhar com o documento original.

Nota: Stored procedure SP_LOCK

A STORED PROCEDURE SP_LOCK serve de ferramenta de an�lise de LOCKS para tomada de decis�o na estrat�gia de gerenciamento de bloqueio. � a SP respons�vel por dar as informaç�es de todos os LOCKS que est�o acontecendo no momento, gerando um relat�rio (RESULT SET) contendo as seguintes colunas:

Spid: Informa o c�digo identificador da sess�o Mecanismo de Banco de Dados para o processo solicitando o bloqueio;

Dbid: Informa o c�digo identificador do banco de dados no qual o bloqueio � mantido;

ObjId: Informa o c�digo identificador do objeto no qual o bloqueio � mantido;

IndId: Informa o c�digo identificador do �ndice no qual o bloqueio � mantido;

Type (Tipo): Informa o tipo de bloqueio, dentre eles temos:

1. RID: Bloqueio em uma �nica linha na tabela identificada por um identificador de linha (RID);

2. KEY: Bloqueio dentro de um �ndice que protege um intervalo de chaves em transaç�es serializ�veis;

3. PAG: Bloqueio em uma p�gina de dados ou de �ndice;

4. EXT: Bloqueio em uma extens�o;

5. TAB: Bloqueio em uma tabela inteira, inclusive todos os dados e �ndices;

6. DB: Bloqueio em um banco de dados;

7. FIL: Bloqueio em um arquivo de banco de dados;

8. APP: Bloqueio em um recurso de aplicativo especificado;

9. MD: Bloqueio em metadados ou informaç�es do cat�logo;

10. HBT: Bloqueio em um �ndice heap ou �rvore B;

11. AU: Bloqueio em uma unidade de alocaç�o.

Resource (Recurso): Informa o nome do recurso que est� bloqueado;

Mode (Modo): Informa o modo do bloqueio, que pode ser:

NULL: Nenhum acesso concedido ao recurso. Funciona como espaço reservado;
SCH-S: Estabilidade do esquema. Assegura que um elemento de esquema, como uma tabela ou �ndice, n�o seja cancelado enquanto qualquer sess�o mant�m o bloqueio de estabilidade do esquema no elemento do esquema;
SCH-M: Modificaç�o do esquema. Deve ser mantido por qualquer sess�o que deseje alterar o esquema do recurso especificado. Assegura que nenhuma outra sess�o esteja fazendo refer�ncia ao objeto indicado;
S: Compartilhado. A sess�o base possui acesso compartilhado para o recurso;
U: Atualizar. Indica um bloqueio de atualizaç�o adquirido em recursos que podem ser atualizados eventualmente. � usado para evitar uma forma comum de deadlock que ocorre quando v�rias sess�es bloqueiam recursos para uma atualizaç�o potencial em um momento posterior;
X: Exclusivo. A sess�o base possui acesso exclusivo ao recurso;
IS: Tentativa compartilhada. Indica a intenç�o de colocar bloqueios S em algum recurso subordinado na hierarquia de bloqueio;
IU: Atualizaç�o da tentativa. Indica a intenç�o de colocar bloqueios U em algum recurso subordinado na hierarquia de bloqueio;
IX: Exclusivo da tentativa. Indica a intenç�o de colocar bloqueios X em algum recurso subordinado na hierarquia de bloqueio;
SIU: Atualizaç�o da tentativa compartilhada. Indica o acesso compartilhado a um recurso com a intenç�o de adquirir bloqueios de atualizaç�o em recursos subordinados na hierarquia de bloqueio;
SIX: Exclusivo da tentativa compartilhada. Indica o acesso compartilhado a um recurso com a intenç�o de adquirir bloqueios exclusivos em recursos subordinados na hierarquia de bloqueio;
UIX: Atualizaç�o exclusiva da tentativa. Indica a manutenç�o de um bloqueio de atualizaç�o de um recurso com a intenç�o de adquirir bloqueios exclusivos em recursos subordinados na hierarquia de bloqueio;
BU: Atualizaç�o em massa. Usado por operaç�es em massa;
RangeS_S: Intervalo de chave compartilhada e bloqueio de recurso compartilhado. Indica exame de intervalo serializ�vel;
RangeS_U: Intervalo de chave compartilhada e bloqueio de recurso de atualizaç�o. Indica exame de atualizaç�o serializ�vel;
RangeI_N: Intervalo de chave de inserç�o e bloqueio de recurso nulo. Usado para testar intervalos antes de inserir uma chave nova em um �ndice;
RangeI_S: Bloqueio de convers�o do intervalo de chave. Criado por uma sobreposiç�o dos bloqueios RangeI_N e S;
RangeI_U: Bloqueio de convers�o de intervalo de chave criado por uma sobreposiç�o dos bloqueios RangeI_N e U;
RangeI_X: Bloqueio de convers�o de intervalo de chave criado por uma sobreposiç�o dos bloqueios RangeI_N e X;
RangeIX_S: Bloqueio de convers�o de intervalo de chave criado por uma sobreposiç�o dos bloqueios RangeI_N e RangeS-S;
RangeIX_U: Bloqueio de convers�o de intervalo de chave criado por uma sobreposiç�o dos bloqueios RangeI_N e RangeS-U;
RangeX_X: Bloqueio de intervalo de chave exclusivo e de recurso exclusivo. Este � um bloqueio de convers�o usado na atualizaç�o de uma chave em um intervalo.

Status: � o estado em que o bloqueio se encontra, podendo ser:

CNVRT: O bloqueio est� sendo convertido de outro modo, mas a convers�o est� bloqueada por outro processo que mant�m um bloqueio com um modo conflitante;
GRANT: O bloqueio foi obtido;
WAIT: O bloqueio est� bloqueado por outro processo que mant�m um bloqueio com um modo conflitante.

Os recursos de modos de bloqueio que o mecanismo de banco de dados utiliza s�o:

1. Compartilhado (S): Usado para operaç�es de leitura que n�o alteram ou atualizam dados, como uma instruç�o SELECT.

2. Atualizaç�o (U): Usado em recursos que podem ser atualizados. Evita uma forma comum de DEADLOCK que ocorre quando v�rias sess�es est�o lendo, bloqueando e potencialmente atualizando recursos mais tarde.

3. Exclusivo (X): Usado para operaç�es da modificaç�o de dados, como INSERT, UPDATE ou DELETE. Assegura que v�rias atualizaç�es n�o sejam realizadas no mesmo recurso e ao mesmo tempo.

4. Intencional: Usado para estabelecer uma hierarquia de bloqueio. Os tipos de bloqueios intencionais s�o: Tentativa compartilhada (IS), exclusivo de tentativa (IX) e compartilhado com exclusivo de tentativa (SIX).

5. Esquema: Usado quando uma operaç�o dependente do esquema de uma tabela est� executando. Os tipos de bloqueios de esquema s�o: modificaç�o de esquema (SCH-M) e estabilidade de esquema (SCH-S).

6. Atualizaç�o em massa (BU): Usado quando for copiar dados em massa em uma tabela e a dicaTABLOCK(Especifica que um bloqueio compartilhado ser� usado na tabela bloqueada at� o t�rmino da instruç�o) estiver especificada.

7. Intervalo de chave: Protege o intervalo de leitura de linhas lido por uma consulta ao usar o n�vel de isolamento da transaç�o serializ�vel. Assegura que outras transaç�es n�o possam inserir linhas que se qualifiquem para consultas da transaç�o serializ�vel se estas forem executadas novamente.

Diretrizes para reduzir Locking e Bloqueios

Embora locking e bloqueios abranjam funcionalidades essenciais do SQL Server e forneçam a implementaç�o da propriedade ACID (Atomicidade, Consist�ncia, Isolamento e Durabilidade), voc� deve minimizar qualquer impacto negativo que possa ocorrer no desempenho do seu banco.

Voc� deve planejar v�rias estrat�gias que gerenciem os LOCKS do seu banco, fazendo isso desde o in�cio do processo de desenvolvimento. Deste modo, voc� perceber� que � muito mais f�cil otimizar os LOCKS do banco se considerar realiz�-lo logo no in�cio do ciclo de vida da aplicaç�o do que quando a mesma j� estiver com bastante tempo em produç�o.

V�rios tipos de LOCKS s�o mantidos durante uma transaç�o � dependendo do n�vel de isolamento escolhido. Se for preciso utilizar um alto n�vel de isolamento, garanta que as transaç�es sejam de r�pida execuç�o. Qu�o logo a transaç�o termine, mais cedo os LOCKS ser�o liberados e outras transaç�es estar�o aptas para acessar os dados.

Tente evitar os cursores porque tipicamente s�o lentos devido a sua natureza iterativa, e sempre que poss�vel efetue a refatoraç�o deles. Alguns tipos de cursores ir�o bloquear os dados durante todo o percurso do seu processamento, podendo causar bloqueios extensivos.

O SQL Server tentar� bloquear na menor granularidade poss�vel e atualizar� somente um LOCK de grande granularidade se muitos dados estiverem sendo lidos ou se n�o existirem �ndices dispon�veis que atendam a menor granularidade. Por exemplo, atualizando um LOCK de grande granularidade ocorrer� um alto custo de concorr�ncia, pois, ao bloquear uma tabela inteira ir� restringir o acesso a qualquer parte da tabela por outras transaç�es.

Garanta que suas chaves de �ndices sejam altamente seletivas e que suas queries sejam escritas de maneira que utilizem os �ndices criados para que n�o façam TABLE SCANS. Dessa forma ser� garantido que o maior n�vel poss�vel de granularidade seja usado.

Quanto mais for utilizado o maior n�vel de isolamento para uma transaç�o, mais LOCKS ser�o alocados pelo SQL Server. Enquanto que no menor n�vel de isolamento o SQL Server n�o compartilha nenhum LOCK que faça com que o SGBD leia dados que n�o tenham sido confirmados (feito commit).

A maioria das aplicaç�es n�o precisa utilizar o n�vel mais alto de isolamento. Elas podem tranquilamente utilizar o n�vel default (read committed), que garante um bom equil�brio entre os n�veis de baixa e alta restriç�o.

Mantenha um n�mero baixo de TRIGGERS porque elas s�o disparadas depois de uma operaç�o de manipulaç�o de dados, mas antes que a transaç�o seja confirmada.

Por este motivo, TRIGGERs alongam a duraç�o das transaç�es e causam maior incid�ncia de bloqueios. Se for necess�rio fazer uso de TRIGGERs, garanta que elas sejam o mais eficiente poss�vel, e evite utilizar c�digos procedurais ou construç�es de loops dentro delas.

Mandamentos

Finalizando o estudo, seguem alguns mandamentos a serem obedecidos na hora do desenvolvimento de uma query que ajudar�o na performance geral do sistema:

N�o utilizar�s HINTS;
N�o mexer�s no mecanismo de LOCK;
Exigir�s uma pol�tica de desenvolvimento para todos seguirem;
Dever�s mexer no n�vel de isolamento de transaç�o;
Sempre utilizar�s FULL QUALIFIED NAME. Use FROM db_banco_teste.dbo.cidade ao inv�s de from cidade;
Evitar�s o famoso SELECT asterisco, e colocar�s somente o nome das colunas que desejares que o banco retorne;
Ter�s cautela com SUBQUERIES. Procurar�s sempre que poss�vel troc�-las por Joins. A cl�usula EXISTS com SUBQUERIES ser� uma exceç�o;
Raciocinar�s sempre em trabalhar com conjuntos de dados e nunca unitariamente, isto �, pensar�s em conjuntos de dados e n�o em registros;
Dever�s ter uma PRIMARY KEY (preferencialmente, esta sendo um �ndice clusterizado, pois toda tabela precisar� ter no m�nimo um �ndice destes) em todas as tuas tabelas;
Dever�s ter uma quantidade apropriada de �ndices n�o clusterizados em todas as tabelas. Estes dever�o ser criados nas colunas de uma tabela devido � necessidade de uma QUERY que esteja sendo colocada em produç�o;
Adotar�s a seguinte ordem de prioridade quando qualquer �ndice for criado:

a. Cl�usula WHERE;

b. Cl�usula JOIN;

c. Cl�usula ORDER BY;

d. Cl�usula SELECT.

Remover�s qualquer JOIN desnecess�rio das tuas consultas;
Evitar�s o uso de VIEWS (geralmente s�o lentas e reduzem a performance);
Verificar�s se o HD do servidor de banco de dados possui pelo menos 30% de espaço livre. Isto garante um pouco de performance.

Conclus�o

Como dito em todo o artigo, a atividade de otimizaç�o de queries � uma arte. Portanto, s�o v�rios os sintomas e indicadores que devem ser analisados antes de tomar qualquer decis�o.

Uma equipe deve ser unida para resolver o problema de lentid�o de um sistema em todas as etapas, n�o interessando a sua �rea de trabalho, como: desenvolvimento, banco ou redes. Voc� deve acompanhar a soluç�o at� o final, mesmo que a sua �rea n�o tenha culpa do ocorrido.

O principal caminho a ser seguido para descobrir problemas de lentid�o � utilizar o gr�fico da pir�mide de otimizaç�o (apresentado em artigo publicado na ediç�o 84 da SQL Magazine), mas isto, se todas as possibilidades f�sicas de rede e m�quina que possam estar causando problemas j� estiverem sido descartadas.

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.