none
Problema de performance RRS feed

  • Pergunta

  • Estou com problemas num servidor virtual dedicado ao servidor SQL versão Enterprise 2008 - SP4 com 6 processadores, 10240 Mb de memória total e 7500 Mb  memória alocada para o SQL.

    Temos uma procedure que cria várias tabelas temporárias que são utilizadas para fazer joins entre elas.

    Com o tempo, a execução dessa procedure, simplesmente não retorna resultado.

    Na sysprocesses fica com status de SOS_SCHEDULER_YIELD e mesmo que cancelamos a procedure, o status MISCELLANEOUS. Ela só sai da sysprocesses quando damos um kill

    A pergunta é.. pq a procedure começa a não retornar após um tempo?

    Foi criado uma procedure identica com outro nome e essa executa sem problema.. Detalhe.. o plano de execução é o mesmo.

    Se inicializarmos o servidor ou limpar o cache.. a procedure volta a retornar os resultados novamente.

    O que o SQL faz que a query volta a funcionar só depois que limpamos o cache?

    Help me

    quarta-feira, 22 de janeiro de 2014 17:29

Respostas

  • Estou com problemas num servidor virtual dedicado ao servidor SQL versão Enterprise 2008 - SP4 com 6 processadores, 10240 Mb de memória total e 7500 Mb  memória alocada para o SQL.

    Temos uma procedure que cria várias tabelas temporárias que são utilizadas para fazer joins entre elas.

    Com o tempo, a execução dessa procedure, simplesmente não retorna resultado.

    Na sysprocesses fica com status de SOS_SCHEDULER_YIELD e mesmo que cancelamos a procedure, o status MISCELLANEOUS. Ela só sai da sysprocesses quando damos um kill

    A pergunta é.. pq a procedure começa a não retornar após um tempo?

    Foi criado uma procedure identica com outro nome e essa executa sem problema.. Detalhe.. o plano de execução é o mesmo.

    Se inicializarmos o servidor ou limpar o cache.. a procedure volta a retornar os resultados novamente.

    O que o SQL faz que a query volta a funcionar só depois que limpamos o cache?

    Help me

    haichu1,

    As suas frases no final já indicam o problema:

    Se inicializarmos o servidor ou limpar o cache.. a procedure volta a retornar os resultados novamente.

    O que o SQL faz que a query volta a funcionar só depois que limpamos o cache?

    Existem quatro itens que você precisa verificar(e se necessário, executar) para corrigir este tipo de problema:

    - Criar os índices necessários sobre as colunas consultadas nas tabelas envolvidas. Veja um exemplo abaixo:

    CREATE NONCLUSTERED INDEX IDX_DTCOMPRA ON dbo.TB_SUATABELA
    (DT_COMPRA ASC)
    INCLUDE (CD_CLIENTE, NR_ITEM, NR_VALOR)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRINCIPAL]
    GO

    - Se já existem os índices, provavelmente pode ser necessário fazer um REBUILD de um ou mais destes índices.

    ALTER INDEX IDX_DTCOMPRA ON TB_SuaTabela REBUILD 
    WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

    - Provavelmente seu banco de dados não deve estar criando e/ou atualizando às estatísticas das tabelas. Você pode configurar seu banco para atualizar às estatisticas com a instrução abaixo:

    ALTER DATABASE SeuBanco SET AUTO_CREATE_STATISTICS ON

    - Criar um Plano de Execução para cada instrução SELECT que pode estar gerando esta lentidão. Este Plano de Execução é utilizado pelo SQL Server para identificar qual o melhor caminho à seguir para encontrar uma informação. O SQL Server por padrão cria um plano de execução, mas nem sempre é o melhor caminho.

    Existe um modelo para criar um Plano de Execução na Galeria. Segue o link abaixo:

    http://gallery.technet.microsoft.com/Criando-rapidamente-um-827f75e0

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 23 de janeiro de 2014 10:54

Todas as Respostas

  • Deleted
    quarta-feira, 22 de janeiro de 2014 17:37
  • Boa observaçao!

    Se for, colocar os dados solicitados ajudam demais a dar dicas do que fazer!

    E se nao for colocar os plano de execução e os dados de configuração do servidor continuam ajudando :)

    quarta-feira, 22 de janeiro de 2014 20:48
  • Estou com problemas num servidor virtual dedicado ao servidor SQL versão Enterprise 2008 - SP4 com 6 processadores, 10240 Mb de memória total e 7500 Mb  memória alocada para o SQL.

    Temos uma procedure que cria várias tabelas temporárias que são utilizadas para fazer joins entre elas.

    Com o tempo, a execução dessa procedure, simplesmente não retorna resultado.

    Na sysprocesses fica com status de SOS_SCHEDULER_YIELD e mesmo que cancelamos a procedure, o status MISCELLANEOUS. Ela só sai da sysprocesses quando damos um kill

    A pergunta é.. pq a procedure começa a não retornar após um tempo?

    Foi criado uma procedure identica com outro nome e essa executa sem problema.. Detalhe.. o plano de execução é o mesmo.

    Se inicializarmos o servidor ou limpar o cache.. a procedure volta a retornar os resultados novamente.

    O que o SQL faz que a query volta a funcionar só depois que limpamos o cache?

    Help me

    haichu1,

    As suas frases no final já indicam o problema:

    Se inicializarmos o servidor ou limpar o cache.. a procedure volta a retornar os resultados novamente.

    O que o SQL faz que a query volta a funcionar só depois que limpamos o cache?

    Existem quatro itens que você precisa verificar(e se necessário, executar) para corrigir este tipo de problema:

    - Criar os índices necessários sobre as colunas consultadas nas tabelas envolvidas. Veja um exemplo abaixo:

    CREATE NONCLUSTERED INDEX IDX_DTCOMPRA ON dbo.TB_SUATABELA
    (DT_COMPRA ASC)
    INCLUDE (CD_CLIENTE, NR_ITEM, NR_VALOR)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRINCIPAL]
    GO

    - Se já existem os índices, provavelmente pode ser necessário fazer um REBUILD de um ou mais destes índices.

    ALTER INDEX IDX_DTCOMPRA ON TB_SuaTabela REBUILD 
    WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

    - Provavelmente seu banco de dados não deve estar criando e/ou atualizando às estatísticas das tabelas. Você pode configurar seu banco para atualizar às estatisticas com a instrução abaixo:

    ALTER DATABASE SeuBanco SET AUTO_CREATE_STATISTICS ON

    - Criar um Plano de Execução para cada instrução SELECT que pode estar gerando esta lentidão. Este Plano de Execução é utilizado pelo SQL Server para identificar qual o melhor caminho à seguir para encontrar uma informação. O SQL Server por padrão cria um plano de execução, mas nem sempre é o melhor caminho.

    Existe um modelo para criar um Plano de Execução na Galeria. Segue o link abaixo:

    http://gallery.technet.microsoft.com/Criando-rapidamente-um-827f75e0

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 23 de janeiro de 2014 10:54
  • Haichu1,

    Existem diversos pontos, mas um ponto de atenção seria analisar as estatísticas de índices, colunas e até mesmo do seu banco de dados.

    O código abaixo, apresenta como obter informações sobre estatísticas desatualizadas:

    SELECT name AS stats_name, 
        STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats 
    order by statistics_update_date Desc
    

    No outro código, como obter as estatísticas de índices:

    --Create a table for the outdated statistics
    CREATE TABLE Outdated_statistics
    ([Table name] sysname,
    [Index name] sysname,
    [Last updated] datetime NULL,
    [Rows modified] int NULL)
    GO
    
    --Get the list of outdated statistics
    INSERT INTO Outdated_statistics
    SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
    AND rowmodctr>0 
    AND id IN (SELECT object_id FROM sys.tables)
    GO
    
    Select * from Outdated_statistics
    
    --Set the thresholds when to consider the statistics outdated
    DECLARE @hours int
    DECLARE @modified_rows int
    DECLARE @update_statement nvarchar(300);
    
    SET @hours=24
    SET @modified_rows=10
    
    --Update all the outdated statistics
    DECLARE statistics_cursor CURSOR FOR
    SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
    FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@hours,GETDATE()) 
    AND rowmodctr>=@modified_rows 
    AND id IN (SELECT object_id FROM sys.tables)
     
    OPEN statistics_cursor;
    FETCH NEXT FROM statistics_cursor INTO @update_statement;
     
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
      EXECUTE (@update_statement);
      PRINT @update_statement;
     
     FETCH NEXT FROM statistics_cursor INTO @update_statement;
     END;
     
     PRINT 'The outdated statistics have been updated.';
    CLOSE statistics_cursor;
    DEALLOCATE statistics_cursor;
    GO

    Outro ponto que sempre passa em branco é a estatísticas de tabelas de sistemas, isso também é muito importante, o comando Update Statistics e a System Stored Procedure SP_UpdateStats podem ajudar a melhorar isso, veja os exemplos abaixo:

    -- Update Statistics in Internal Tables --
    Exec sp_updatestats
    Go
    
    -- Update Statistics in User Tables, Index and Data Pages --
    Exec sp_msforeachtable 'Update Statistics ? With FullScan, All'
    Go


    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]

    quarta-feira, 5 de fevereiro de 2014 13:39
    Moderador