Início / dba / Perguntas / 1714
Accepted
Gaius
Asked: 2011-03-13 03:31:21 +0800 CST

Degradação repentina do desempenho do SQL Server

  • 772

Eu tenho um SQL Server 2005 que se tornou imprevisível ultimamente e estou coçando a cabeça para saber o porquê. As consultas que são executadas em segundos estão mudando de planos e demorando minutos (tomando o tempo em full table scan ou index spool). Agora, a primeira e mais óbvia coisa é que as estatísticas estão obsoletas, fazendo com que o otimizador fique confuso, mas estou convencido de que não é o caso - primeiro porque os dados subjacentes não estão mudando significativamente (por exemplo, adicionando os dados de um dia sobre os dados de um ano já em uma tabela) e, em segundo lugar, porque as estatísticas de criação automática e as estatísticas de atualização automática são verdadeiras. No entanto, o otimizador está ficando confuso; executar o SQL no Tuning Advisor me dá muitas instruções de várias colunas CREATE STATISTICSque parecem corrigi-lo (até que o próximo bit do SQL se comporte mal).

Alguma ideia de uma estratégia que eu possa usar para abordar a causa raiz disso? Por que as estatísticas "normais" não são suficientes?

sql-server-2005 performance

3 respostas

  1. Do MSDN :

    " Operações de inserção ocorrem em colunas -chave ascendentes ou descendentes Estatísticas em colunas-chave ascendentes ou descendentes, como IDENTITY ou colunas de registro de data e hora em tempo real, podem exigir atualizações de estatísticas mais frequentes do que o otimizador de consulta executa. As operações de inserção anexam novos valores às colunas ascendentes ou descendentes . O número de linhas adicionadas pode ser muito pequeno para acionar uma atualização de estatísticas. Se as estatísticas não estiverem atualizadas e as consultas selecionarem as linhas adicionadas mais recentemente, as estatísticas atuais não terão estimativas de cardinalidade para esses novos valores. Isso pode resultam em estimativas de cardinalidade imprecisas e desempenho de consulta lento.

    Por exemplo, uma consulta que seleciona as datas de ordem de venda mais recentes terá estimativas de cardinalidade imprecisas se as estatísticas não forem atualizadas para incluir estimativas de cardinalidade para as datas de ordem de venda mais recentes.

    Após as operações de manutenção Considere atualizar as estatísticas após executar procedimentos de manutenção que alteram a distribuição de dados, como truncar uma tabela ou executar uma inserção em massa de uma grande porcentagem das linhas. Isso pode evitar atrasos futuros no processamento de consultas enquanto as consultas aguardam atualizações automáticas de estatísticas."

    Você pode usar "EXEC sp_updatestats" de tempos em tempos em seu sistema (agendado em algum momento) ou usar a função STATS_DATE em todos os objetos e ver quando suas estatísticas foram realmente atualizadas pela última vez e se passou muito tempo desde então, use UPDATE ESTATÍSTICAS para esse objeto específico. Na minha experiência, mesmo com as estatísticas automáticas ativadas, ainda somos forçados a atualizar as estatísticas de tempos em tempos, devido às operações de inserção que não acionam a atualização automática.

    Para adicionar meu código pessoal (usado em um trabalho semanal que constrói declarações dinâmicas para atualização de estatísticas):

    select distinct
        'update statistics [' + stats.SchemaName + '].[' + stats.TableName + ']'
            + case when stats.RowCnt > 50000 then ' with sample 30 percent;'
            else 
                ';' end
        as UpdateStatement
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName, 
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from sys.stats st
            join sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join sysobjects so on so.id = si.id and so.xtype = 'U' --user table
            join sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 10 --more than 10% of the rows have changed
    or ( --update statistics that were not updated for more than 3 months (and rows no > 0)
        datediff(month, stats.LastStatisticsUpdateDate, getdate()) >= 3
        and stats.RowCnt > 0
    )

    Aqui eu pego todos os objetos onde não tiveram estatísticas atualizadas por mais de 3 meses ou desde a última atualização de estatísticas tiveram mais de 10% das linhas alteradas.

    • 8
  2. Best Answer

    Se sua espera máxima for SOS_SCHEDULER_YIELD, parece que você tem alguma pressão na CPU. Mas isso pode ser resultado de outra coisa, como seu design não ser mais suficiente para suas consultas. Sei que você disse que está adicionando apenas um dia de dados, mas pode ter atingido um ponto de inflexão.

    Como suas consultas estão sendo emitidas? É SQL dinâmico? Você está usando procedimentos armazenados? Você está usando sp_executesql? É possível que você tenha um caso de detecção de parâmetros? Como é o design do seu banco de dados? Quais são as relações PK e FK?

    Você tem um exemplo de um bom plano? Se você conseguir determinar um bom plano, poderá usar guias de plano para forçar a execução da consulta de uma maneira específica.

    Você pode dar um exemplo de um bom plano que deu errado?

    Por fim, pegue uma cópia de sp_whoIsActive ( http://whoisactive.com/ ) de Adam Machanic e use-a para determinar mais sobre as consultas que estão sendo executadas. E se você quiser capturar a saída de sp_whoIsActive, acesse http://www.littlekendra.com/2011/02/01/whoisactive/

    • 8

  3. Meu palpite é que uma ou mais de suas tabelas estão ficando grandes o suficiente para não atingirem os 20% de alterações necessárias para ajudar a marcar as estatísticas atuais como obsoletas, para que as estatísticas de atualização automática entrem em ação e ainda haja atualizações suficientes (ou inserções ) que ter estatísticas atualizadas ajudaria muito. Encontrei a mesma coisa recentemente em um ambiente específico após a atualização do SQL 2000 para o SQL 2008.

    Além dos outros sites mencionados nas respostas acima, sugiro verificar os seguintes recursos online.

    1) A Red-Gate tem vários e-books gratuitos disponíveis para download, incluindo "SQL Server Statistics" de Holger Schmeling, onde você encontrará a seguinte citação:

    http://www.red-gate.com/our-company/about/book-store/

    "tabelas com mais de 500 linhas pelo menos 20% dos dados de uma coluna tiveram que ser alterados para invalidar quaisquer estatísticas vinculadas"

    2) O SQL Sentry tem uma ferramenta Plan Explorer gratuita que ajuda a rastrear problemas em um plano SQL, como uma estimativa de linhas demais ou poucas linhas em comparação com o número real de linhas de uma determinada tabela em uma consulta. Basta salvar o plano de execução real do SSMS e percorrer as diferentes partes do plano usando o Plan Explorer. Não é que a informação não esteja disponível no SSMS usando o plano de execução gráfico, mas a ferramenta do SQL Sentry facilita muito a visualização.

    http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

    3) Verifique você mesmo a data de atualização das estatísticas para as tabelas nas consultas nas quais você está mais interessado usando STATS_DATE(), você pode encontrar uma consulta rápida para obter as estatísticas mais antigas usando uma consulta encontrada na discussão a seguir.

    http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/

    Eu espero que isso ajude!

    Acho que você vai gostar especialmente do livro da Red-Gate!

    -Jeff

    • 3

relate perguntas