locked
SQL Server 2005 travando RRS feed

  • Pergunta

  • Caros, boa tarde!

    Desde ontem, venho sofrendo com problemas de lentidão e travamento no SQL Server 2005 Std (9.00.1406.00 (Intel X86)   Mar  3 2007).
    O fato concreto é que a memória utilizada pelo serviço do SQL (sqlservr.exe), após a abertura de uma determinada aplicação vai subindo, subindo até encostar (ou ultrapassar) em 1GB, a partir deste momento nenhum usuário da aplicação/bd em questão conseguem continuar o trabalho.
    Ajustei uma trace no Profiler, achando que era algo relacionado à alguma consulta executada pela aplicação, mas o "custo" e a duração das consultas parecem estar normais.
    O servidor é um Xeon 3.02 / 4GB de RAM / W2k3 Std - SP1.
    Neste momento, apenas por desencargo de consciência, estamos atualizando o Service Pack do W2k3 Std para SP2.
    Por favor, alguém já teve uma experiência semelhante ou tem alguma idéia do que está acontecendo?
    Desde já, agradeço pelo auxílio.


    Att,
    Joaquim Filho
    • Movido Gustavo Maia Aguiar quarta-feira, 26 de agosto de 2009 16:52 (De:SQL Server - Desenvolvimento Geral)
    quarta-feira, 26 de agosto de 2009 15:24

Respostas

  • Acesso Indevido ao Banco de Dados SQL Server em Produção por estações de trabalho do setor de construção de software.

    Acesso a informações por origem não autorizada e impacto em performance dos servidores

    Em reunião entre Ten Beatriz e Cap Monteiro, o incidente foi esclarecido e autorizado pelo setor de administração de dados a ativação do usuário utilizado no acesso.


    -- Prove informações sobre usuários correntes e processos na instância no SQL Server
    sp_who

    você pode checar na coluna status do tipo Suspend

    Processos em suspend são normais, porém em uma base de dados com perfil OLTP que são muitas transações (insert, update e delete) e relatórios rápidos que retornam poucas linhas em curtos prazos, os processos em suspend não devem ficar muito tempo neste status.

    Esse tempo de suspend demora muito de ambiente para ambiente e seus diferentes cenários, mas para uma base de dados OLTP acredito que processos em suspend com mais de 20 segundos são considerados suspeitos e merecem atenção, normalmente esse processo é muito rapido as vezes não ultrapassa mais de 2 segundos.

    Sempre que um processo fica suspenso ele está sendo bloqueado por outro processo.

    A query abaixo mostra processos com o request_mode 'X' e 'IX'.

    SELECT
    CASE WHEN o.name IS NULL THEN 'INDX Row' ELSE o.name END as table_name,
    resource_associated_entity_id, request_mode, request_type, request_status,
    request_session_id, request_owner_type, request_owner_id, resource_type, resource_subtype, resource_database_id
        FROM seubanco.sys.dm_tran_locks t LEFT OUTER JOIN seubanco.sys.sysobjects o ON
    t.resource_associated_entity_id = o.id
    where request_mode in ('X','IX') and resource_type in ('OBJECT')--, 'KEY') 
    and resource_database_id = DB_ID('seubanco') ORDER BY request_session_id
    go

    IX significa que o processo pretende ficar exclusivo e ja ganhou esta permissão.
    X significa que o processo garantiu exclusividade no objeto, ou pagina ou extent ou linhas entre outros.

    O fato de possuir muitos processos como X por muito tempo, pode indicar que estes processos estão em uma transação aberta que ainda não foi finalizada, isso indica desenvolvimento pobre de queries e transações e devem ser revistos e são um dos principais motivos para existir muitos processos no status suspend.

    sp_who2 apresenta basicamente as informações do sp_who porém com algum complemento como por exemplo CPU e DiskIO utilizado por determinado processo.

    Muito CPU e IO podem indicar queries ineficientes, principalmente muito IO.

    Outra forma de ver estas informações através de uma ferramenta gráfica é utilizando o Activity Monitor que fica na aba Management de seu Management Studio.

    Isso é apenas uma indicação de que existem transações mal elaboradas em seu ambiente, outro ponto MUITO importante é a quão está atualizado seu produto, desta forma concordo com o Junior e Alex, sobre a atualização através dos services packs e hotfixs.

    O primeiro ponto para conseguir resolver o problema é tentar rastrear e isolar o problema, isso as vezes dispende de uma investigação com várias ferramentas, incluindo profile, perfom, views dinamicas, relatórios disponibilizados através do SQL Server entre outros.

    Neste momento é importante temos um baseline de seu ambiente que são indicativos do que é considerado aceitavel relacionado a recursos utilizados, tempos de respostas, usuários simultanêos entre outros indicadores.

    Tendo em mãos este baseline é mais fácil detectar onde está o problema, pois é possível comparar o antes e o atual comportamento.

    O seu SQL com certeza não está "travando", o que pode estar acontecendo é um maior tempo de resposta devido a vários fatores, alguns deles como transações mal escrita que discutimos aqui.

    Caso não tenha um baseline de seu ambiente é importante criar um, não que o mesmo irá resolver seu problema atual, mas com certeza irá te ajudar em futuros.

    tente análisar e identifcar o problema para facilitar a linha de discussão sobre a solução.

    abraço


    DBA SQL Server MCTS - SQL Server 2005 | ITIL Foundation V2 http://www.bydocs.com
    sexta-feira, 28 de agosto de 2009 21:17

Todas as Respostas

  • Joaquim, você informou que o servidor possui 4gb de RAM, quanto foi configurado de memória máxima e mínima para o SQL Server?

     É um servidor para aplicação e outro para banco ou ambos compartilham o mesmo servidor?

    Você observou se quando este comportamento acontece:

    1 - aparece muitos processos em suspend? aguardando liberação de outros processos?
    2 - Verificou se acontece deadlock?
    3 - Você consegue se logar normalmente pelo Management Studio ou não?
    4 - O que esta aplicação envia para o banco neste momento?

    DBA SQL Server MCTS - SQL Server 2005 | ITIL Foundation V2 http://www.bydocs.com
    quinta-feira, 27 de agosto de 2009 21:14
  • Joaquim,

    O SQL Server está MUITO desatualizado em relação aos SERVICES PACKS já disponíveis.

    1GB de memória para um servidor de banco de dados é muito pouco dependendo da aplicação e do uso, por isso não é de se espantar que o SQL Server chega a consumir mais de 1GB.

    O que você considera como CUSTO e TEMPO de execução normal? quais valores você pegou no seu ambiente?


    Esta postagem é provida "COMO ESTÁ" sem garantias, e não conferem direitos.
    Alex Rosa -- Sharing my knowledge at http://www.keep-learning.com/blog
    quinta-feira, 27 de agosto de 2009 21:37
  • Joaquim,

    Concordo com as orientações do Alex, principalmente em relação a versão do SQL Server.

    Inicialmente vamos começar a melhor este ambiente, baixando todas as atualizações possíveis do SQL Server 2005, dentre elas, services packs, hotfixs e cumulative updates.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    sexta-feira, 28 de agosto de 2009 00:13
    Moderador
  • Joaquim, você informou que o servidor possui 4gb de RAM, quanto foi configurado de memória máxima e mínima para o SQL Server?

     É um servidor para aplicação e outro para banco ou ambos compartilham o mesmo servidor?
     R: Ambos compartilham o mesmo servidor.


    Você observou se quando este comportamento acontece:

    1 - aparece muitos processos em suspend? aguardando liberação de outros processos?
    R: Cleyton, não sei como verificar. Poderia me explicar?

    2 - Verificou se acontece deadlock?
    R: Após ativar os trace flags, executando a sp_readerrorlog não foram encontrados deadlocks. Existe uma outra forma de verificação fora esta?

    3 - Você consegue se logar normalmente pelo Management Studio ou não?
    R: Sim, sem problemas. Consigo executar quaisquer consultas sem problemas, em todas as bases de dados.

    4 - O que esta aplicação envia para o banco neste momento?
    R: Não dá pra rastrear precisamente, pois são muitos usuários logados e o sistema tem um complexo de triggers e sp's que são disparados a todo instante.


    DBA SQL Server MCTS - SQL Server 2005 | ITIL Foundation V2 http://www.bydocs.com
    Muito obrigado.


    Att,
    Joaquim Filho

    sexta-feira, 28 de agosto de 2009 14:41
  • Acesso Indevido ao Banco de Dados SQL Server em Produção por estações de trabalho do setor de construção de software.

    Acesso a informações por origem não autorizada e impacto em performance dos servidores

    Em reunião entre Ten Beatriz e Cap Monteiro, o incidente foi esclarecido e autorizado pelo setor de administração de dados a ativação do usuário utilizado no acesso.


    -- Prove informações sobre usuários correntes e processos na instância no SQL Server
    sp_who

    você pode checar na coluna status do tipo Suspend

    Processos em suspend são normais, porém em uma base de dados com perfil OLTP que são muitas transações (insert, update e delete) e relatórios rápidos que retornam poucas linhas em curtos prazos, os processos em suspend não devem ficar muito tempo neste status.

    Esse tempo de suspend demora muito de ambiente para ambiente e seus diferentes cenários, mas para uma base de dados OLTP acredito que processos em suspend com mais de 20 segundos são considerados suspeitos e merecem atenção, normalmente esse processo é muito rapido as vezes não ultrapassa mais de 2 segundos.

    Sempre que um processo fica suspenso ele está sendo bloqueado por outro processo.

    A query abaixo mostra processos com o request_mode 'X' e 'IX'.

    SELECT
    CASE WHEN o.name IS NULL THEN 'INDX Row' ELSE o.name END as table_name,
    resource_associated_entity_id, request_mode, request_type, request_status,
    request_session_id, request_owner_type, request_owner_id, resource_type, resource_subtype, resource_database_id
        FROM seubanco.sys.dm_tran_locks t LEFT OUTER JOIN seubanco.sys.sysobjects o ON
    t.resource_associated_entity_id = o.id
    where request_mode in ('X','IX') and resource_type in ('OBJECT')--, 'KEY') 
    and resource_database_id = DB_ID('seubanco') ORDER BY request_session_id
    go

    IX significa que o processo pretende ficar exclusivo e ja ganhou esta permissão.
    X significa que o processo garantiu exclusividade no objeto, ou pagina ou extent ou linhas entre outros.

    O fato de possuir muitos processos como X por muito tempo, pode indicar que estes processos estão em uma transação aberta que ainda não foi finalizada, isso indica desenvolvimento pobre de queries e transações e devem ser revistos e são um dos principais motivos para existir muitos processos no status suspend.

    sp_who2 apresenta basicamente as informações do sp_who porém com algum complemento como por exemplo CPU e DiskIO utilizado por determinado processo.

    Muito CPU e IO podem indicar queries ineficientes, principalmente muito IO.

    Outra forma de ver estas informações através de uma ferramenta gráfica é utilizando o Activity Monitor que fica na aba Management de seu Management Studio.

    Isso é apenas uma indicação de que existem transações mal elaboradas em seu ambiente, outro ponto MUITO importante é a quão está atualizado seu produto, desta forma concordo com o Junior e Alex, sobre a atualização através dos services packs e hotfixs.

    O primeiro ponto para conseguir resolver o problema é tentar rastrear e isolar o problema, isso as vezes dispende de uma investigação com várias ferramentas, incluindo profile, perfom, views dinamicas, relatórios disponibilizados através do SQL Server entre outros.

    Neste momento é importante temos um baseline de seu ambiente que são indicativos do que é considerado aceitavel relacionado a recursos utilizados, tempos de respostas, usuários simultanêos entre outros indicadores.

    Tendo em mãos este baseline é mais fácil detectar onde está o problema, pois é possível comparar o antes e o atual comportamento.

    O seu SQL com certeza não está "travando", o que pode estar acontecendo é um maior tempo de resposta devido a vários fatores, alguns deles como transações mal escrita que discutimos aqui.

    Caso não tenha um baseline de seu ambiente é importante criar um, não que o mesmo irá resolver seu problema atual, mas com certeza irá te ajudar em futuros.

    tente análisar e identifcar o problema para facilitar a linha de discussão sobre a solução.

    abraço


    DBA SQL Server MCTS - SQL Server 2005 | ITIL Foundation V2 http://www.bydocs.com
    sexta-feira, 28 de agosto de 2009 21:17