none
Lentidão: trocar por Stored Procedures RRS feed

  • Pergunta

  • Temos um sistema em ASP.NET que atualmente quase não utiliiza Stored Procedures, ou seja, monta as instruções SQL dinamicamente, no código-fonte mesmo, e executa no banco de dados.

    Só que estão acontecendo casos de o SQL Server ficar usando muito do processador e o sistema ficar lento.
    Os comandos SQL estão sendo executados sem nenhum problema, mas estão demorando muito.
    Só que não parece ser porque retorna resultados muito complexos ou algo assim, pois um mesmo comando rodando em outro ambiente, até com um servidor mais fraco, que é executado instantaneamente está levando alguns segundos nesses casos de lentidão.


    Agora a questão é a seguinte:

    Se eu alterar as instruções SQL mais executadas pelo sistema por Stored Procedures deve reduzir a necessidade de processamento do SQL Server, aliviando pelo menos um pouco e podendo ficar mais rápido?


    PS: O sistema e o SQL Server estão no mesmo servidor e a conexão é feita via Shared Memory.
    segunda-feira, 8 de março de 2010 11:37

Respostas

  • Olha, toda vez que o SQL Server vai executar alguma query pela primeira vez, ela passa por alguns processos.. como Parse para verificar a sintaxe, Optimiza para verificar melhores meios de executar.. compila e assim por diante... isso tudo vai para o cache do servidor.
    Com stored procedure já faz diferente, ele só executa esses passos quando vc cria ou altera ela. Nas execuções ele já tem a certeza que a sua sintaxe já está correta que os objetos referenciados existem (caso usar schemabiding), fazendo com que seja mais rapida de executar..

    Uma outra coisa a se ver são seus indices. Verifique se estao sendo usados ou se estão fragmentados. Uma boa idéia é usar o SQL Profile para ver o que realmente está acontecendo e o DTA para analizar a performance do banco
    • Marcado como Resposta Fernando H. Rosa terça-feira, 9 de março de 2010 20:54
    segunda-feira, 8 de março de 2010 13:45
  • Fernando H. Rosa,

    para verificar se o parse é o culpado, escolha algumas das suas consultas dinâmicas(eu escolheria entre 5 e 10) e ao executá-las, verifique as estatísticas de usuário com os seguintes comandos

    SET STATISTICS IO ON - ao ligar esta opção você poderá averiguar o tempo gasto em I/O, o que pode te levar a pesquisar mais detalhes como tables scan, fragmentação de índice, etc.
    SET STATISITICS TIME ON - nos tempos apresentados você  poderá verificar se o tempo de parse(análise sintática) é o seu gargalo.

    Analisar os planos de execução também poderão te ajudar a achar a causa da lentidão.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    • Marcado como Resposta Fernando H. Rosa terça-feira, 9 de março de 2010 20:54
    segunda-feira, 8 de março de 2010 14:17
  • Boa Tarde,

    As stored procedures podem prover alguns benefícios de desempenho mas em raras ocasiões podem inclusive denegrir o desempenho. Antes propriamente de apostar suas fichas nas SPs para melhorar o desempenho eu sugiro que resolva os problemas de desempenho para só depois partir para as SPs. Alguns pontos para verificar

    Modelagem
    Será que o modelo de dados físico da sua aplicação está adequado ? Há tabelas não normalizadas que estejam provocando perda de desempenho ?

    Qualidade das consultas
    As consultas SQL estão bem escritas ? Há presença de grande complexidade nessas consultas e (ou) uso de funções nas cláusulas WHERE ?

    Índices
    Como estão os índices ? As principais consultas estão se beneficiando deles ? Há índices que não são utilizados ?

    Concorrência
    E o nível de isolamento ? Você está sofrendo com a presença de bloqueios ou algo do tipo ?

    Administração de recursos
    Os recursos de hardware estão bem dimensionados ? Se o servidor e a aplicação estão na mesma máquina há memória disponível para o SQL Server ?

    Acredito que após a investigação desses pontos você possa preparar-se para implementar as SPs. Sem isso creio que não haverá grandes melhoras do ponto de vista de desempenho.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de março de 2010 16:39

Todas as Respostas

  • Olha, toda vez que o SQL Server vai executar alguma query pela primeira vez, ela passa por alguns processos.. como Parse para verificar a sintaxe, Optimiza para verificar melhores meios de executar.. compila e assim por diante... isso tudo vai para o cache do servidor.
    Com stored procedure já faz diferente, ele só executa esses passos quando vc cria ou altera ela. Nas execuções ele já tem a certeza que a sua sintaxe já está correta que os objetos referenciados existem (caso usar schemabiding), fazendo com que seja mais rapida de executar..

    Uma outra coisa a se ver são seus indices. Verifique se estao sendo usados ou se estão fragmentados. Uma boa idéia é usar o SQL Profile para ver o que realmente está acontecendo e o DTA para analizar a performance do banco
    • Marcado como Resposta Fernando H. Rosa terça-feira, 9 de março de 2010 20:54
    segunda-feira, 8 de março de 2010 13:45
  • Fernando H. Rosa,

    para verificar se o parse é o culpado, escolha algumas das suas consultas dinâmicas(eu escolheria entre 5 e 10) e ao executá-las, verifique as estatísticas de usuário com os seguintes comandos

    SET STATISTICS IO ON - ao ligar esta opção você poderá averiguar o tempo gasto em I/O, o que pode te levar a pesquisar mais detalhes como tables scan, fragmentação de índice, etc.
    SET STATISITICS TIME ON - nos tempos apresentados você  poderá verificar se o tempo de parse(análise sintática) é o seu gargalo.

    Analisar os planos de execução também poderão te ajudar a achar a causa da lentidão.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    • Marcado como Resposta Fernando H. Rosa terça-feira, 9 de março de 2010 20:54
    segunda-feira, 8 de março de 2010 14:17
  • Boa Tarde,

    As stored procedures podem prover alguns benefícios de desempenho mas em raras ocasiões podem inclusive denegrir o desempenho. Antes propriamente de apostar suas fichas nas SPs para melhorar o desempenho eu sugiro que resolva os problemas de desempenho para só depois partir para as SPs. Alguns pontos para verificar

    Modelagem
    Será que o modelo de dados físico da sua aplicação está adequado ? Há tabelas não normalizadas que estejam provocando perda de desempenho ?

    Qualidade das consultas
    As consultas SQL estão bem escritas ? Há presença de grande complexidade nessas consultas e (ou) uso de funções nas cláusulas WHERE ?

    Índices
    Como estão os índices ? As principais consultas estão se beneficiando deles ? Há índices que não são utilizados ?

    Concorrência
    E o nível de isolamento ? Você está sofrendo com a presença de bloqueios ou algo do tipo ?

    Administração de recursos
    Os recursos de hardware estão bem dimensionados ? Se o servidor e a aplicação estão na mesma máquina há memória disponível para o SQL Server ?

    Acredito que após a investigação desses pontos você possa preparar-se para implementar as SPs. Sem isso creio que não haverá grandes melhoras do ponto de vista de desempenho.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de março de 2010 16:39
  • Obrigado a todos.
    Todas as dicas foram de grande ajuda.
    Comecei criando alguns índices e vi que já ajudou bastante.
    Agora estou vendo se há algo a melhorar nos SQLs e criando Stored Procedures para algumas coisas de rotina, como inserir e atualizar registros.
    terça-feira, 9 de março de 2010 20:55