artigo SQL Magazine 02 - Otimização de SQLs e Tunning de Banco de Dados SQL Server

Otimização e tunning de um banco de dados SQL Server 2000 definitivamente não é uma ciência exata : existem o que podemos chamar de “regras de boa conduta” que devem ser implementadas de modo a extrair o máximo de performance, mas nem sempre as configurações válidas num determinado ambiente poderão ser aplicadas com o mesmo êxito noutra configuração, isso porquê para que seja feito um ajuste fino na base de dados são necessários esforços bastante abrangentes, que vão desde a análise de códigos Transact SQL até a análise do hardware do servidor (memória, discos, processador e rede) .Nessa matéria, analisaremos o plano de execução de queries, forneceremos também dicas sobre otimização de códigos Transact SQL e finalizaremos com um breve estudo sobre bloqueios.

Análise do plano de execução de uma query no SQL Server 2000

Analisaremos um comando “select” executado no database NorthWind conforme a seguir, presente nas instalações default do SQL Server 2000:

select *       
from [NorthWind].[dbo].[Orders] o
inner join                       
 [Northwind].[dbo].[Order Details] od
  on o.OrderID = od.OrderID
  where o.orderid =10248

As tabelas utilizadas “Orders” e “Orders Details” (Figura 1) tiveram seus índices alterados conforme demonstrado na Figura 2. O plano de execução gerado pode ser obtido diretamente no Query Analyzer, selecionando “Display Estimated Execution Plan” na opção “Query” e os detalhamentos em amarelo na Figura 3 são obtidos ao posicionar o cursor no respectivo objeto. Toda análise descrita a seguir refere-se a Figura 4.

Figura 1. Estrutura de “Orders” e “Orders Details” no database “NorthWind”

Figura 2.Composição dos indices em “Orders” e “Orders Details”

Figura 3. Análise do plano de execução da query

Figura 4. Análise completa

• A leitura do plano de execução deve ser efetuada da direita para a esquerda, de cima para baixo. A espessura das linhas que ligam os objetos é diretamente proporcional ao custo da operação (calculado pela relação nro.linhas X tamanho da linha), portanto fique atento às linhas mais grossas

• Cada objeto presente no plano de execução representa uma etapa desenvolvida pelo SQL Server 2000. Percorrendo o gráfico, os objetos (1) e (3) indicam que o otimizador está utilizando índices para pesquisas pontuais (=seek). A pesquisa efetuada em “Orders” está sendo realizada por um índice cluster (“Clustered Index Seek”; item (1)); já a pesquisa em “Orders Details” é efetuada por um índice não cluster (“Index Seek”; item(3)). Pesquisas to tipo “seek” são bastante eficientes; fique atento quando se deparar com acessos do tipo “scan” – “Table Scan” ou “Clustered Index Scan”, que indicam varreduras sequenciais por toda a tabela, fatores que degradam bastante a performance das queries. Acessos do tipo “scan” normalmente se devem a pesquisas efetuadas com argumentos insuficientes na clausula “where”, ou índices não qualificados para a pesquisa, exceção seja feita a tabelas com pequeno número de registros, onde um “scan” efetuado diretamente na área de dados se torna mais econômico que o esforço adicional causado pela pesquisa no índice.

• Um detalhe importante é que a escolha dos índices apropriados se baseia em estatísticas pré-armazenadas a respeito da distribuição de dados na tabela. O SQL Server 2000 calcula estatísticas automaticamente para o PRIMEIRO segmento dos índices; portanto fique atento a esse detalhe – selecione para o primeiro segmento de um índice composto a coluna mais seletiva (=menor índice de ocorrências). Se esse detalhe não for observado, o SQL Server 2000 poderá não escolher seu índice pelo fato da seletividade ser baixa. Note que você pode criar manualmente estatísticas para as outras colunas envolvidas no índice, eliminando esse problema. Ainda com relação às estatísticas uma atenção especial deve ser dada para textos em vermelho no plano de execução, eles mostram estatísticas desatualizadas. Se for esse o caso, proceda à atualização.

• O objeto “BookMark Lookup” no item(2) indica que, para cada registro lido no índice não cluster “Orders.PK_Orders” é necessário uma leitura adicional na tabela, pelo fato do índice não contemplar todas as colunas requisitadas na linha do comando “select”. Uma maneira de se evitar esse passo adicional é a criação de índices que contemplem todas as colunas requeridas na linha do “select” (conhecido como “covered index”). Criação de índices adicionais, contudo, devem ser vistos com cautela para que não impactem em má performance em operações de alteração de dados (inserts/updates/deletes).

• O próximo passo na resolução da query é a escolha do tipo de join para resolver a query. Em nosso “select”, o tipo escolhido foi o “Nested Loop”, em função da alta seletividade nas tabelas envolvidas. Abaixo, são detalhadas as características dos três modelos possíveis de join, a saber: Nested Loop, Merge Join e Hash Join.

Nested Loop: o otimizador elege uma tabela (conhecida por “Outer Table”) que servirá de base para a varredura de registros. A cada registro lido nessa tabela, é efetuada uma busca pelo registro correspondente na outra tabela participante do join (conhecida por “Inner Table” e esta deve possuir índice adequado para busca). Esse método é bastante eficiente quando uma das tabelas possui quantidade pequena de registros (ou o join possui filtros que tornam o result-set pequeno) e a outra um índice adequado formado pela(s) coluna(s) que unem as duas tabelas. A tabela com menor número de registros será definida como Outer Table.

Merge Join: se as duas tabelas possuírem índices adequados, que permitam sortear o conteúdo das tabelas participantes do join (considerando os filtros estabelecidos na cláusula “where”) esse tipo será o escolhido. O otimizador recupera uma coluna de cada lista sorteada, efetuando a comparação. Em caso de igualdade, retorna as colunas selecionadas. Caso contrário, a coluna de menor valor será descartada, obtendo o próximo valor dessa mesma lista onde foi efetuado o descarte. O processo se repete até que todas as linhas tenham sido processadas. Esse tipo de join é bastante eficaz, normalmente sendo utilizado para tabelas com grande número de registros, que inviabilizam o Nested Loop.

Hash Join: se não existirem índices adequados para a igualdade definida no join, esse método será utilizado. Para que o join possa acontecer, o otimizador precisará de uma maneira bastante rápida de indexar as duas tabelas, por isso utiliza um algoritmo de hash para codificar as colunas envolvidas no join e estabelecer a combinação.Unir duas tabelas sem índices apropriados ou com baixa seletividade é um fator de queda de performance, portanto investigue as ocorrências desse tipo de join.

Após a escolha do tipo de join, a query é executada retornando o select para a estação

Abaixo, são listados mais alguns objetos importantes na avaliação do plano de execução. A lista completa dos objetos utilizados pelo otimizador na resolução de queries pode ser obtida no procurando por “execution plan icons” na guia “Search” do SQL Server Books On Line. Após pesquisa, selecione “Graphically Displaying the Execution Plan Using SQL Query Analyzer”. (ou no endereço http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp)

• Assert: utilizado para verificar certas condições (integridade referencial (FK), check constraint, etc) agindo como uma espécie de filtro para os registros envolvidos na operação.
• Compute Scalar: esse operador é utilizado pelo otimizador para retornar saídas envolvendo valores calculados (“computed columns”, funções, etc.).
• Index Spool, Row Count Spool e Table Spool: indicam que foi necessária a criação de tabela temporária no database tempdb para rodar a query. Esse passo muitas vezes pode ser evitado reescrevendo-se o join.
• Parallelism: indica que a query está sendo executada em mais de um processador. Em máquinas multi-processadas, o otimizador poderá quebrar queries complexas e executá-las em paralelo, normalmente ganhando performance. A opção pela execução em paralelo depende de uma série de circunstâncias como atividade do servidor no momento da execução e complexidade da query. Existem algumas opções que podem ser configuradas no servidor envolvendo paralelismo, entre elas “max degree of parallelism”, que liga/desliga a execução em paralelo e “cost threshold for parallelism”, que estabelece uma projeção mínima de tempo para que a query seja executada em paralelo.
• Sort: indica que o objeto está sendo sorteado, presente quando você utiliza a cláusula “order by” ou também quando o input precisa ser ordenado para resolução do join, nesse último caso degradando performance (a tabela não possui índice adequado).
• Stream Aggregate: aparece quando utilizamos as cláusulas que agregam valores -“avg”,“distinct”,”sum”, “max”,”min”ou ”count”

Dicas para otimização de códigos Transact-SQL

1 ) Conheça os passos dados pelo otimizador para resolver uma query :

• Identificação dos argumentos de pesquisa (=SARGS O QUE EH SARGS?, utilizados na cláusula “where”) e colunas mencionadas no join ;
• Seleção do índice apropriado, baseando-se nos SARGS. Os índices são avaliados em função de sua seletividade, utilizando para isso as estatísticas de distribuição. Será escolhido o índice que requerer um menor número de leituras para resolver o “select” ;
• Avaliação dos tipos de join possíveis e ordem apropriada de acesso às tabelas. Isso quer dizer que o otimizador definirá a tabela-base do join, independente da ordem especificada no comando “select”. Os comandos a seguir são idênticos :

Listagem 1. Select’s idênticos para o otimizador

select o.OrderId
 from 
    [NorthWind].[dbo].[Orders] o   
 inner join                                                        
    [Northwind].[dbo].[Order Details] od
 on o.OrderID = od.OrderID

Seleção do melhor plano de execução, baseado nos custos calculados no item 3.

2 ) Limite sua busca restringindo ao máximo o número de colunas solicitadas na cláusula “select”. Colunas adicionais, além de consumir mais recursos de I/O e largura de banda, muitas vezes inibem a utilização de índices ou causam buscas desnecessárias na área de dados à partir do índice (bookmark lookup).

3 ) Filtre sempre o resultado de suas pesquisas, fornecendo parâmetros de busca que se adequem à estrutura dos índices existentes, obedecendo a ordenação de suas colunas.

Ex: para o índice composto PK_Order_Detals, formado pelas colunas OrderId e ProductId, é fundamental que uma pesquisa forneça pelo menos o número da ordem a ser pesquisada (OrderId). Fornecendo somente ProductId, torna pouco provável a utilização do índice pelo otimizador.

4 ) Evite utilização de funções diretamente sobre colunas pesquisadas, que inibem a utilização de índices. Ex:

substitua ... where substring(ShipName,1,1) = ‘M’
 por  ………where ShipName like (‘M%’)

Se não for possível evitar a função, considere a criação de índices sobre colunas calculadas:

select datepart(month,ShippedDate)
  from [NorthWind].[dbo].[Orders] 
 where datepart(month,ShippedDate)=7

Pode ser otimizado se criarmos um índice sobre uma coluna calculada:

alter table [NorthWind].[dbo].[Orders]
add Month_OrderDate as datepart(month,ShippedDate)
create index IX_Month_OrderDate 
 on [NorthWind].[dbo].[Orders](Month_OrderDate)

Podemos agora reescrever o “select” :

select Month_OrderDate 
from [NorthWind].[dbo].[Orders] 
where Month_OrderDate=7

Repare que o “Table Scan” foi substituído por um “Indes Seek” !

5 ) Utilize tabelas derivadas em oposição à tabelas temporárias. Tabela derivada é o resultado da utilização de um comando select após a clausula “from” num “select” existente. Apenas para efeito de exemplo :

select o.EmployeeId,od.Quantity
from [NorthWind].[dbo].[Orders] o
inner join
 ( select * from [Northwind].[dbo].[Order Details] od where ProductId=11 ) as od
on o.OrderID = od.OrderID

6 ) Lembre-se que índices existem para comparar igualdades. Evite a utilização de operadores do tipo “<>”,”!>”,”!<”, “NOT”. A utilização de “lógica negativa” inibe a escolha de índices pelo otimizador.

7 ) Para fins de performance, considere a utilização de Indexed Views. A utilização de views simplifica bastante a programação, mas não otimiza performance, haja visto que seu código é executado de maneira integral a cada solicitação.

8 ) Se a sua query utiliza agrupamentos e filtragem de dados na cláusula “having”, considere a opção de filtragem diretamente na cláusula “where”, reduzindo significativamente o trabalho do “group by”, já que um número menor de registros deverão ser processados.

9 ) Utilize a cláusula “like” com critério. Lembre-se que o comando “… where name like(‘SQL%’)“ utilizará um índice formatado para a coluna “name”, se esse índice existir. Já o comando “… where name like (‘%SQL’) “ realizará um table scan (ou clustered index scan, se a tabela possuir índice cluster) na tabela em questão.

10 ) Evite ao máximo a utilização de cursor nos servidores. Experimente reescrever o código utilizando subqueries, tabelas derivadas, tabelas temporárias ou mesmo a cláusula “case”.

11 ) Sempre que possível, utilize variáveis do tipo “table” em oposição à tabelas temporárias.

12 ) Para monitoramento, utilize o Profiler para capturar as queries mais demoradas, analisando seu plano de execução.

13 ) Existem duas configurações de servidor que podem ser utilizadas para limitar o tempo de execução de uma query, são elas : “query governor cost limit” e “query wait”. A primeira (“query governor”) é baseada numa projeção de tempo de execução da query calculada pelo otimizador: se o tempo projetado for superior ao limite pré-definido nessa configuração a query é abortada ANTES de sua execução. “Query wait” simplesmente aborta uma query se esta superar o limite estabelecido nesse parâmetro, o que pode ser desastroso principalmente se a transação for extensa e já houver adquirido muitos locks. Como sugestão, avalie a opção “query governor”. Implante limites que você considera suficientes para seu ambiente (com boa margem de segurança !) e vá reduzindo gradativamente, até chegar ao ponto ótimo.

Fatores que afetam performance : análise de bloqueios e deadlocks

Bloqueios são fundamentais para garantia da consistência de dados em transações. O isolamento fornecido por um bloqueio no SQL Server 2000 permite que uma transação não efetue leituras ou modifique dados que estão sendo utilizados por outra transação.

Existem vários tipos de locks, cada um estabelecendo o isolamento necessário para comandos de manipulação de dados (select / insert / update / delete). O tipo de lock (shared, update, exclusive, shema lock ou bulk update lock) é selecionado automaticamente pelo SQL Server 2000 a menos que você utilize um hint, o que não é aconselhável.

O SQL Server 2000 trabalha com escalonamento de locks, permitindo que um bloqueio de registro seja promovido para um bloqueio de página ou de tabela. O escalonamento possibilita a economia de recursos (um lock consome 64 bytes de memória), pois ao promover um lock os bloqueios de nível menor são liberados. Como ilustração, imagine uma operação de update envolvendo todas as 1000 linhas de uma tabela - o que seria mais eficiente: 1000 locks de registro ou um lock de tabela ? É lógico que a segunda opção, já que todas as linhas serão atualizadas.

O problema relacionado a bloqueios advém de seu tempo de duração. Bloqueios curtos são eficientes, bloqueios longos são um transtorno. Alguns fatores que acarretam no aumento da duração de bloqueios são transações longas, ausência, excesso ou ineficiência de índices, nível de isolamento das transações, bases de dados não normalizadas, utilização indiscriminada de cursores, etc. Nesses ambientes, as mensagens de erro envolvendo query timeout (#1222) ou deadlocks (#1205) tendem a ocorrer com mais freqüência.

Query timeout acontece quando, ao executar um comando de manipulação de dados, aguardamos sua conclusão por tempo superior a um limite previamente estabelecido. Já deadlocks acontecem quando dois processos ficam aguardando pela liberação de recursos que o outro processo mantém, situação essa que é resolvida pelo SQL Server 2000 finalizando-se a conexão que consumir menos recursos.A seguir temos os códigos para gerar dois tipos de deadlock: cíclicos e de conversão.

Tipo cíclico:

1. Abra duas sessões no Query
2. Na sessão-1, execute o cmd abaixo:
   

   begin tran
    update [NorthWind].[dbo].[order details]
      set discount=1
    where orderid=10248 and productid=11

3. Na sessão-2, execute:
   

          begin tran
    update [NorthWind].[dbo].[orders]
      set employeeid=4
    where orderid=10248

4. Voltando na sessão-1, execute :
   

   
   update [NorthWind].[dbo].[orders]
    set employeeid=5
   where orderid=10248
   commit
   

   
   O update acima ficará travado, aguardando a liberação do recurso, bloqueado na sessão-2
5. Na seção-2, execute:
   

   update [NorthWind].[dbo].[order details]
    set discount=0
   where orderid=10248 and productid=11
   commit
   

   
   Nesse momento acontece o deadlock :
   

   Server: Msg 1205, Level 13, State 50, Line 1
   Transaction (Process ID 70) was deadlocked on lock resources with another process 
   and has been chosen as the deadlock victim. Rerun the transaction.
   

Tipo Conversão:

1. Abra duas sessões no Query Analyzer
2. Na sessão-1, execute o cmd abaixo:
   

   begin tran
   select * from [NorthWind].[dbo].[Orders] (holdlock)
    where orderid=10248

   
   
3. Na sessão-2, execute o mesmo comando:
   

   
   begin tran
    select * from [NorthWind].[dbo].[Orders] (holdlock)
    where orderid=10248

4. Voltando na sessão-1, execute :
   

   update [NorthWind].[dbo].[Orders]
    set employeeid=4
   where orderid=10248
   commit

   
   O update acima ficará travado, aguardando a liberação do recurso, bloqueado na sessão-2
5. Na seção-2, execute:
   

   update [Northnd].[dbo].[Orders]
    set employeeid=5
   where orderid=10248
   commit

6. Nesse momento acontece o deadlock :
   

   Server: Msg 1205, Level 13, State 50, Line 1
   Transaction (Process ID 70) was deadlocked on lock resources with another process 
   and has been chosen as the deadlock victim. Rerun the transaction

Uma das maneiras de se monitorar as transações envolvidas num deadlock é ativar as trace flags 3605 e 1204, que geram informações detalhadas no log do SQL Server 2000 à respeito do deadlock. Execute “dbcc traceon(3605) dbcc traceon(1204) “ para habilitar as trace flags; “dbcc traceoff (3605) dbcc traceoff (1204)” para desabilitar.

Outra possibilidade de monitoramento é através do SQL Profiler, habilitando-se o evento “Lock: DeadLock Chain”, que produz resultado semelhante às trace flags habilitadas acima.

Um aspecto interessante é que a duração de um lock é ilimitada no SQL Server 2000. Portanto se você não quiser que um processo aguarde indefinidamente pela liberação de um lock mantido noutra sessão, utilize em sua sessão a cláusula LockTimeOut (set LockTimeOut ) antes de comandos de manipulação de dados e efetue tratamento para erros de código #1222.

Minimizar tempo de bloqueios implica no cumprimento de algumas regras, a saber:

1. Mantenha suas transações “enxutas” – quanto menos código melhor; lembre-se que quanto menor o tempo gasto por um bloqueio menor será a possibilidade de ocorrências de deadlocks e travamentos ;
2. Estude a possibilidade de quebrar horizontal e/ou verticalmente tabelas com grande número de registros. Dados distribuídos permitem maior concorrência, já que os locks que originalmente incidiam sobre um objeto estão dispersos em outras tabelas;
3. Procure atualizar tabelas nas transações seguindo sempre a mesma ordem, evitando assim a ocorrência de deadlocks cíclicos (Figura 7) ;
4. Evite a utilização de select com hint “holdlock” seguidos de um update. Essa combinação explosiva é causa freqüente de deadlocks de conversão (Figura 8);
5. Efetue expurgos periódicos em suas bases OLTP; não mantenha dados históricos em sua base de produção. Operações em tabelas com grande número de registros tendem a ser mais demoradas;
6. Não crie índices desnecessariamente em bases OLTP. Um índice criado para otimizar uma query representará overhead nas operações de atualização de dados;
7. Utilize sp’s em oposição a batchs. Por estarem residentes no servidor e muitas vezes com planos de execução cacheados, as sp’s apresentam performance superior;
8. Trabalhe com locks otimistas em situações em que a leitura e modificação de dados representem processos com considerável separação de tempo. Use e abuse de colunas do tipo “TimeStamp” para essa finalidade. Ex: vamos supor que uma ordem de compra possa ser alterada em vários locais por vários vendedores. Seria interessante que dois vendedores não alterassem a mesma ordem ao mesmo tempo; poderiam alterar colunas diferentes e somente a última alteração se tornaria vigente. Para resolver esse tipo de questão, crie uma coluna chamada “OrderTS”, do tipo “timestamp” e, ao efetuar a leitura do registro na aplicação de front end, carregue o valor dessa coluna. Depois que o vendedor efetuar as alterações necessárias, submeta o registro à atualização, fornecendo o valor da coluna timestamp como argumento de pesquisa. Se o registro não for encontrado significa que aconteceram alterações concorrentes, sendo necessário uma releitura na base para posterior revisão pelo vendedor.

1 ) Adicionar coluna TS tipo TimeStamp em [Orders]

Alter table [Orders] add TS timestamp

2 ) Efetuar leitura de colunas da tabela Orders, submetendo-as a aplicação de front-end

declare @ShipName varchar (80)
 declare @ShipAddress varchar(120)
 declare @TS binary (8)
  
 select  @ShipName=ShipName
          ,@ShipAddress=ShipAddress 
          ,@TS=TS 
 from [Orders] where OrderId=10248
 

3 ) Após alterações pelo vendedor, submeter o comando de update fornecendo a coluna timestamp guardada no passo anterior:

update [Orders]
set  ShipName       = @ShipName
      , ShipAddress = @ShipAddress
 where OrderId =10248 and TS=@TS
 if @@rowcount = 0 
 begin
      /* 
        Significa que o registro foi alterado noutra sessão ; a aplicação deverá ser desviada 
        para o passo-2, relendo a ordem submetendo-a novamente à análise do vendedor 
     */
     raiserror ('Ordem alterada por outro vendedor. Verifique !',11,1)
 end

Conclusão

Normalmente só pensamos em otimização no momento em que nos deparamos com situações realmente críticas de performance, quando pode ser tarde demais.