Problemas de performance em bancos de dados

De Wiki Locaweb
Ir para: navegação, pesquisa


É muito comum depois de um tempo o banco de dados perder o desempenho inicial, isto pode ocorrer falta de manutenção no banco SQL SERVER.
Deixo abaixo algumas dicas para melhorar a performance do seu banco de dados:

Lembrando que todas as dicas abaixo, são alguns exemplos, caso tenha dúvidas avalie com seu desenvolvedor.

Usaremos o exemplo: SP_CADASTRO_CLIENTES


De que forma o SQL interpreta uma stored procedure com o prefixo SP_?
Quando uma procedure é precedida por SP_ o SQL Server busca a procedure no banco Master.
Isso permite que sejam criadas procedures globais para administrar o servidor.
Essas procedures podem ficar no Master e serem chamadas a partir de qualquer banco que o SQL Server as encontra.

Então por que não devemos nomear todas nossas procedures como SP_ ?
O SQL Server mantém um cache de planos de execução das procedures.
Assim sendo, quando uma aplicação executa uma procedure, primeiramente ele procura esta procedure no cache.
Se estiver utilizando estas informações, caso contrário terá que recompilar.
Durante o processo de compilação o SQL Server gera um lock, chamado de compilation lock, que impede que outras aplicações compilem a procedure ao mesmo tempo.
Ocorre que o compilation lock é gerado imediatamente após a procura no cache.
Isto faz com que o SQL Server busque a procedure no cache, e se não achar, gera o lock.


No que isto interfere no desempenho?
Quando a procedure tem o prefixo SP_ ela é primeiramente irá buscar no Master e não no banco em que o usuário está.
Não localizada procedure no Master é gerado o compilation lock.
Posteriormente, antes de compilar a procedure, o SQL Server acaba descobrindo que ela já está no cache de procedures do banco de dados do usuário e reaproveita o cache, sem recompilar a procedure.
Porém TODAS as chamadas desta procedure vão gerar o compilation lock e prejudicar seriamente o tempo de execução da procedure.
Devido a isso, evite utilizar o prefixo SP_ nas suas procedures, utilize apenas quando necessário, para procedures administrativas.

O Set Nocount On é uma instrução que, quando utilizada nas procedures, garante uma melhor otimização, pois evita que a contagem de registros afetados, aquela que normalmente é mostrada pelo query analyzer, seja gerada e transmitida pela rede.
A tabela syscomments contém o código fonte de todas as procedures geradas, e através dela é possível encontrar procedures que não estejam utilizando Set NoCount On.
Através desta query, obtemos as procedures que precisam ser alteradas:
select
  name
from
  syscomments a,
  sysobjects b
where
  a.id=b.id
and
  b.xtype=’P’
and
not exists (select id from syscomments where text like ’%set%nocount%on%’ and id=a.id)
order by
  name
Cursores geram grande prejuízo para a performance. Deve-se verificar as procedures que fazem uso de cursores e analisá-las para garantir que não existe nenhuma forma alternativa de escrevê-las.
Através desta query é possivel buscar as procedures que utilizam estes cursores:
select
  a.id,
  b.name
from
  syscomments a,
  sysobjects b
where
  a.id=b.id
and
  a.text like ’%open %’
A falta do ALL no UNION dificulta uso de índices e prejudica a performance da aplicação.
Através desta query use para identificar procedures que precisam ser corrigidas:
select
  a.id,
  b.name
from
  syscomments a,
  sysobjects b
where
  a.id=b.id
and
  a.text not like ’% UNION ALL%’
and
  a.text like ’% UNION %’
and
  b.xtype=’P’
A montagem e execução de strings é extremamente prejudicial para a performance, pois o SQL Server só é capaz de determinar como a execução será feita em run-time.
Quando existe alternativa, é preferível evitar isso. Para identificar procedures nesta condição, use esta query:
select
  a.id,
  b.name
from
  syscomments a,
  sysobjects b
where
  a.id=b.id
and
  (a.text like ’%EXEC(%’ or a.text like ’%execute(%’)

Os triggers são os objetos mais delicados para o processo de transferência. Isso porque o trigger não é claramente visível entre os objetos do banco de dados (via enterprise manager)
e a falta do trigger não faz com que a aplicação pare de funcionar de imediato. Por isso é comum acontecer na implantação da aplicação, um trigger ser esquecido para trás e a falta dele só ser notada tempos depois do sistema e estar funcionando.
Deve-se, então, ter extremo cuidado com os triggers, garantindo que nenhum seja esquecido para trás. Uma solução é fazer uma query que crie uma listagem de todos os triggers existentes no banco e suas respectivas tabelas.
Executando a query nos dois bancos pode-se com certa facilidade verificar se todos os triggers estão presentes ou não.
Considerando que na metodologia de desenvolvimento para banco, o uso de triggers é exceção e não regra, têm-se poucos triggers a avaliar.

Para obter uma listagem dos triggers e suas respectivas tabelas, use está query :
select
  name,
  object_name(parent_obj) tabela
from
  sysobjects
where
  xtype=’TR’

Veja também