none
SQL Server consumo fora do normal ao executar select RRS feed

  • Pergunta

  • Prezados, a algum tempo venho percebendo um computarmente estranho no servidor sql, exemplo uma mesma consulta que roda no servidor principal, ao executa-la gera um consumo de 100% dos processadores no caso os dois existente, quando vou testar os mesmos bancos em outro computador no caso um desktop core2 quad com 8GB de memoria, executo o mesmo script fica em 25% do processador, lembro que o servidor tem dois processadores totalizando 16 nucleos e 48GB de memoria, bati a configurações do sql server esta semelhantes não entendo o por que disto, o servidor com toda a infra estrutura tendo este comportamento, o que pode ser ?

    Grato

    Cláudio Bruno


    Bruno Avanso

    sexta-feira, 13 de dezembro de 2013 15:15

Respostas

  • Boa noite,

    Utilize os comandos tsql abaixo para ativar estatísticas:

    set statistics io on 
    set statistics time on 

    Coloque para incluir o plano de execução atual. E responda essas perguntas:

    Verifique o plano de execução no servidor, qual operador tem mais custo?

    O SQL Server com base nas estatísticas demora mais na hora de compilar e de dar parse? CPU Time é maior que Elapsed Time?

    O SSMS sugere criação de índices?


    Jefferson Santos [MCTS SQL Server]

    terça-feira, 17 de dezembro de 2013 00:31
  • Bruno,

    Vamos tentar identificar as Querys que mais estão consumindo processamento do seu SQL Server, para isso, utilize o código abaixo:

     SELECT TOP 10
    'Procedure'     = qt.text
    ,DiskReads      = qs.total_physical_reads -- The worst reads, disk reads
    ,MemoryReads    = qs.total_logical_reads  --Logical Reads are memory reads
    ,Executions     = qs.execution_count
    ,CPUTime      = qs.total_worker_time
    ,DiskWaitAndCPUTime = qs.total_elapsed_time
    ,MemoryWrites   = qs.max_logical_writes
    ,DateCached     = qs.creation_time
    ,DatabaseName   = DB_Name(qt.dbid)
    ,LastExecutionTime  = qs.last_execution_time
     FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    ORDER BY qs.total_physical_reads +  qs.total_logical_reads DESC


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 11 de março de 2014 14:02
    Moderador

Todas as Respostas

  • Boa tarde Bruno !

    Fora o consumo de processamento, como esta o tempo de retorno da consulta ? esta melhor ou pior do que no desktop ?

    No momento da consulta ha algo mais rodando na base ou no servidor ?


    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 13 de dezembro de 2013 17:06
  • Alexandre,

                 O Select no Desktop é um tempo bem parecido, dependendo da atividade, no desktop porém com o mesmo ambiente windows 2008 r2 sp1, sql 2008 r2 sp2, ambos 64bits, porém o desktop está raid 0 com disco sata para todos os bancos e tempdb, ja no servidor alem de processador e memoria ja relatados tem controladora de disco com 512mb de cache, discos sas 15k sendo raid 0 somente para o tempdb e raid 10 para os bancos e raid 0 para o S.O., mas o comportamento estranho que quando executo no desktop ocupa somente 25% do processamento, ja no servidor 100%, logico que no servidor está em produto, porém quando o select não é executado o servidor produção geralmente fica entre 0 a 10% de utilização, quando executo chega 100%.

    Grato

    Cláudio Bruno


    Bruno Avanso

    sexta-feira, 13 de dezembro de 2013 18:03
  • Boa noite,

    Utilize os comandos tsql abaixo para ativar estatísticas:

    set statistics io on 
    set statistics time on 

    Coloque para incluir o plano de execução atual. E responda essas perguntas:

    Verifique o plano de execução no servidor, qual operador tem mais custo?

    O SQL Server com base nas estatísticas demora mais na hora de compilar e de dar parse? CPU Time é maior que Elapsed Time?

    O SSMS sugere criação de índices?


    Jefferson Santos [MCTS SQL Server]

    terça-feira, 17 de dezembro de 2013 00:31
  • Bruno,

    Por acaso esta aplicação esta utilizando algum middleware do tipo TomCat ou JBoss?

    Faça um teste, no momento em que o processadores estiverem com 100% de uso, tente limpar os caches do SQL Server, utilizando o seguinte bloco de código:

    -- Forçar a escrita das páginas em disco "limpando-as"
    
    CHECKPOINT
     
    
    -- Eliminar as páginas de buffer limpas
    
    DBCC DROPCLEANBUFFERS
    
     
    -- Eliminar todas as entradas do CACHE de "Procedures"
    
    DBCC FREEPROCCACHE
    
     
    
    -- Limpar as entradas de Cache não utilizadas
    
    DBCC FREESYSTEMCACHE ( 'ALL' )
    

    Provavelmente isso vai derrubar o processamento do seu servidor, o que vai nos indicar que existem transações abertas que não esta sendo comitadas e posteriormente liberadas, ou até mesmo que esta sendo utilizada o conceito de transações implicitas, criando um bloco único de transações, o que pode esta forçando o SQL Server a aumentar o seu buffer de processamento para posteriormente fazer o despejo.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 20 de dezembro de 2013 14:16
    Moderador
  • Boa tarde,

    Acredito que voce deva tentar ver quais os planos de execução das queries nos dois ambientes, o atalho Ctrl+M exibe o plano  de execução da query executada no Managemente Studio. Assim voce pode comparar como o SQL SERVER esta executando o select nos dois ambientes.

    Verifique se não existe diferença entre os indices nas tabelas, pois a falta de um indice pode influenciar na execução do select deixando-o mais custoso.

    segunda-feira, 30 de dezembro de 2013 15:02
  • Bruno,

    Outro ponto importante que deve ser analisado é identificar se estas querys que você esta processamento no seu Desktop não se encontram armazenadas em Cache, algo que reflete em muito no processamento do SQL Server.

    Além disso, a parte de armazenamento de dados, é outro fator que impacta diretamente na performance, isso também tem que ser estudado.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 3 de janeiro de 2014 14:02
    Moderador
  • Bruno,

    Por acaso esta aplicação esta utilizando algum middleware do tipo TomCat ou JBoss?

    Faça um teste, no momento em que o processadores estiverem com 100% de uso, tente limpar os caches do SQL Server, utilizando o seguinte bloco de código:

    -- Forçar a escrita das páginas em disco "limpando-as"
    
    CHECKPOINT
     
    
    -- Eliminar as páginas de buffer limpas
    
    DBCC DROPCLEANBUFFERS
    
     
    -- Eliminar todas as entradas do CACHE de "Procedures"
    
    DBCC FREEPROCCACHE
    
     
    
    -- Limpar as entradas de Cache não utilizadas
    
    DBCC FREESYSTEMCACHE ( 'ALL' )

    Provavelmente isso vai derrubar o processamento do seu servidor, o que vai nos indicar que existem transações abertas que não esta sendo comitadas e posteriormente liberadas, ou até mesmo que esta sendo utilizada o conceito de transações implicitas, criando um bloco único de transações, o que pode esta forçando o SQL Server a aumentar o seu buffer de processamento para posteriormente fazer o despejo.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    Se for seguir estes passos no servidor de produção tome cuidado. É importante alertar que vai limpar todo o teu cache, ou seja, tudo o que o SQL Server precisar a partir deste momento terá que ser buscado no disco, que é muito mais lento, até seu cache ser populado novamente.

    Abs,


    Luiz Mercante MCITP SQL 2008 | MCTS SQL 2008 | MTA Database Fundamentals | MCTS Windows Apps | MCTS Windows Network | MCP 2003 sqldicas@outlook.com http://sqldicas.com.br Se a resposta foi útil de alguma forma, classifique como resposta ou vote como útil.

    quarta-feira, 5 de março de 2014 12:40
    Moderador
  • Bruno,

    Vamos tentar identificar as Querys que mais estão consumindo processamento do seu SQL Server, para isso, utilize o código abaixo:

     SELECT TOP 10
    'Procedure'     = qt.text
    ,DiskReads      = qs.total_physical_reads -- The worst reads, disk reads
    ,MemoryReads    = qs.total_logical_reads  --Logical Reads are memory reads
    ,Executions     = qs.execution_count
    ,CPUTime      = qs.total_worker_time
    ,DiskWaitAndCPUTime = qs.total_elapsed_time
    ,MemoryWrites   = qs.max_logical_writes
    ,DateCached     = qs.creation_time
    ,DatabaseName   = DB_Name(qt.dbid)
    ,LastExecutionTime  = qs.last_execution_time
     FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    ORDER BY qs.total_physical_reads +  qs.total_logical_reads DESC


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 11 de março de 2014 14:02
    Moderador