Início / dba / Perguntas / 177518
Accepted
Serdia
Asked: 2017-06-29 08:27:59 +0800 CST

por que colunas computadas diminuem o desempenho de repente?

  • 772

Os usuários conseguiram gerar relatórios antes das 10h. Depois que os mesmos relatórios ficaram muito lentos, às vezes os usuários simplesmente não tinham paciência para esperar. Após algumas soluções de problemas, encontrei a coluna que estava causando o atraso. Foi computada a coluna que utiliza a função para trazer o resultado.

Aproximadamente ao mesmo tempo, recebi outra reclamação sobre o relatório de execução lenta, que estava sempre funcionando bem. Após algumas soluções de problemas, encontrei as colunas que estavam causando um atraso:

where (Amount - PTD) <> 0

E novamente, a Amountcoluna é a coluna computada.

Então, minha pergunta é: por que todas as colunas computadas repentinamente que sempre faziam parte dos relatórios começaram a diminuir significativamente o desempenho? O que poderia realmente acontecer aproximadamente depois das 10 da manhã? E qual é a desvantagem se eu fizer essas colunas persisted?

obrigada

--FUNCTION que traz a coluna:

ALTER FUNCTION [dbo].[CalcInvoiceAmtPTD]
(@SInvNum INT, @entityGuid uniqueIdentifier) RETURNS MONEY
AS
BEGIN
    DECLARE @Amt MONEY

    declare @toplevel uniqueidentifier
    set @toplevel = (select dbo.gettoplevelentity(@entityguid))

    declare @t table 
    (
        Guid uniqueidentifier
    )

    insert into @t select * from dbo.getlinkedentities(@toplevel) where guid is not null
    declare @tbl table (amount money, glacctid int)

    select @amt = isNull(sum(amount), 0) from tblfin_journalpostings jp
        inner join tblfin_journal j on j.transactnum = jp.transactnum 
            and (voiderfor is null and voidedby is null)and j.transdescid <> 'I'
        inner join tblfin_glaccounttypes glt on glt.glacctid = jp.glacctid and glt.accounttype = 'p'
        inner join @t t on t.guid = jp.entityguid
    where invoicenum = @SInvNum

    RETURN ISNULL(@Amt  , 0)
END
sql-server performance

4 respostas

  1. Eu vou jogar isso lá fora, já que ninguém mais mencionou isso.

    Ter uma função de valor escalar em uma coluna computada é uma péssima ideia.

    • Força TODAS as consultas na tabela a serem executadas em série (mesmo se você não selecionar a coluna computada)
    • Força a manutenção (Index, CHECKDB) a ser executada em série, mesmo se você estiver no Enterprise
    • Executa linha por linha
    • Se usado na WHEREcláusula, pode causar problemas com estimativas de cardinalidade

    Aqui estão alguns posts que escrevi sobre assuntos semelhantes.

    Ainda serial depois de todos esses anos

    Funções escalares em colunas computadas

    Funções escalares em restrições de verificação

    • 7
  2. Best Answer

    É bom saber a diferença de desempenho com e sem a coluna computada e pode levar a uma melhoria geral da consulta, mas não explica realmente por que há uma diferença de desempenho notável para a mesma consulta, com a coluna computada , em diferentes horas do dia.

    Há muitos motivos pelos quais sua consulta pode ficar mais lenta após as 10h. E nós, é claro, sem muita informação sua, é muito improvável que possamos dizer o que está acontecendo em seu sistema que causaria essa mudança neste momento.

    No entanto, aqui estão algumas possibilidades:

    • Aumento da atividade - A possibilidade mais simples é que a consulta esteja sendo executada mais lentamente após as 10h porque seu sistema está mais ativo após as 10h. Nos EUA, uma empresa com escritórios em todo o país, mas com servidores baseados no fuso horário do leste, onde os funcionários geralmente chegam por volta das 9h, teria todos os funcionários nos fusos horários do leste e central no sistema às 10h. Para essas empresas em que trabalhei, isso representa mais de 2/3 da base total de funcionários. Portanto, o sistema geralmente fica um pouco mais lento após esse ponto.

      Também lidei com situações em que certas atividades tendem a ocorrer em determinados momentos do dia de forma consistente e podem ter um grande impacto. Uma desaceleração todas as tardes ao mesmo tempo em um sistema em que trabalhei estava ligada aos funcionários que trabalhavam fora do escritório retornando ao escritório, quase ao mesmo tempo, e sincronizando seus dispositivos para carregar as atividades do dia.

      Ou, de uma direção ligeiramente diferente: talvez a consulta em si esteja sendo executada na mesma velocidade às 10h15 e às 8h15 - mas talvez metade das pessoas no escritório esteja transmitindo conteúdo às 10h e a largura de banda disponível para o os dados do relatório para chegar à área de trabalho de alguém foram reduzidos por um fator de 5.

      Se esse for o seu problema, você precisará determinar exatamente qual é o gargalo (CPU/memória no servidor; largura de banda de rede disponível entre o servidor e os usuários) e ver se pode aumentar os recursos limitadores. (Ou, se for o último cenário, faça com que todos parem de transmitir conteúdo!)

    • Tipo de outra atividade - Além da atividade específica simples, certos tipos de atividade que acontecem em diferentes momentos do dia também podem ter impacto. Se as pessoas estão fazendo chamadas de vendas para o seu banco de dados começando por volta das 10h, pode haver atividade suficiente INSERTem UPDATEuma ou mais mesas que você está acessando para que os SELECTs tenham que esperar que elas sejam liberadas antes que possam ser executadas. Pior ainda, se o aplicativo bloquear uma linha quando alguém iniciar uma chamada de vendas (para garantir que você não tenha duas pessoas tentando ligar para o mesmo cliente potencial ao mesmo tempo), as esperas podem ser ainda maiores.

      Dependendo de seus requisitos de relatório e de como seu aplicativo funciona, você poderá usar READ_COMMITTED_SNAPSHOT_ISOLATION, ou até mesmo WITH (NOLOCK)dicas de tabela, para resolver a situação, se esse for seu problema real.

    • Padrões de uso - Também é possível que haja uma diferença na forma como o relatório é usado antes e depois das 10h. Se você tiver um plano de consulta armazenado em cache que espera que um dos parâmetros passados ​​seja altamente seletivo e, após as 10h, a maioria das execuções da consulta estiver usando um valor menos seletivo, isso poderá afetar as coisas.

      Por exemplo: digamos que sua empresa esteja sediada em Cincinnati, Ohio (que fica perto da fronteira desse estado com outros dois estados, Kentucky e Indiana). 90% de seus clientes estão em Ohio, com os 10% restantes nos dois estados vizinhos. Um relatório é executado por estado em vendas diárias, e o procedimento é executar Ohio por último. Se o plano de consulta for criado com state = 'KY', e for executado com isso ou Indiana antes das 10h e comstate = 'OH' , o plano de consulta poderá simplesmente não funcionar tão bem.

      Por outro lado - talvez alguma alma brilhante tenha tido esse problema no passado - e sua solução foi limpar o cache do plano a cada 15 minutos das 10h às 17h. Isso também pode atrapalhar a consulta (embora você espere que isso mexa com tudo ).

    • Desgaste - Talvez você esteja reconstruindo suas estatísticas todas as noites à 1 da manhã; mas a atividade às 10h alterou os dados o suficiente para que essas estatísticas estejam desativadas.

    • Tempo subjetivo - Os usuários podem iniciar o relatório, tomar um café e voltar para descobrir que está pronto antes das 10h e executá-lo enquanto estão em suas mesas, esperando que ele termine depois. Não estou dizendo que é provável que seja a resposta, mas coisas estranhas aconteceram.

    • "E o resto..." - o acima não é de forma alguma uma lista exaustiva de possibilidades, apenas alguns pensamentos baseados no que encontrei ao longo dos anos.

    Ao receber relatórios de problemas como esse, é importante solicitar feedback útil de sua base de usuários. Pergunte coisas como:

    • A queda de desempenho realmente acontece às 10h todos os dias? varia um pouco de dia para dia, ou talvez só aconteça na segunda e terça-feira?
    • A queda de desempenho realmente afeta apenas este relatório? ou isso afeta outras atividades no sistema? (Se tudo demorar o dobro depois das 10h, mas a maioria das coisas ainda demorar apenas 5-30 segundos, enquanto este relatório demorar 5 minutos antes das 10h e 10 minutos depois, você pode obter o comportamento descrito, mas pode não ter absolutamente nada a ver com o relatório).
    • Existe alguma diferença em como você usa o relatório antes das 10h e depois?
    • Houve um momento em que o relatório voltou tão rápido quanto antes das 10h da manhã toda? dia todo? em caso afirmativo, a mudança foi gradual ou houve um ponto identificável em que o desempenho após as 10h passou de bom para ruim? Se assim for, quando?

    Também observarei que, embora você tenha nos dito que essa consulta leva de 4 minutos para 4 segundos se você descartar a coluna computada, você não nos disse quanto tempo leva às 8h15, 9h15 , 10:15 e 11:15. Você não mencionou o plano de consulta para a consulta e se é o mesmo plano antes e depois das 10h; poste execuções de planos reais antes e depois das 10h para PasteThePlan e as pessoas aqui podem ver o que está diferente e fornecer sugestões informadas sobre o que pode consertar as coisas. Mesmo que essa informação não aponte para uma resposta, pode permitir que algumas possibilidades sejam descartadas.

    • 4

  3. Você deve sempre manter as colunas computadas persistidas, a menos que esteja tendo inserções/atualizações muito consistentes com baixo número de leituras dessa tabela.

    Por que estou dizendo isso?

    Porque adicionar persisted à coluna computada significa que no momento em que o cálculo é feito (em cada inserção/atualização) o valor da coluna é gravado no banco de dados. E sempre que você precisar desses valores novamente, o sql server simplesmente os envia para você já calculados. Dessa forma, a CPU não precisa recalcular todas as vezes que você fizer uma consulta nessa tabela e solicitar a coluna. O que pode economizar muito tempo em seus relatórios, já que você está fazendo algum tipo de cálculo novamente

    A desvantagem é que toda vez que você faz qualquer tipo de inserção ou atualização o sql server precisa calcular o valor novamente, o que pode retardar as inserções/atualizações em uma tabela ocupada.

    Agora, para melhorar ainda mais sua consulta, você precisa estar familiarizado com as consultas SARGable, o que em poucas palavras significa que você não pode fazer nenhum tipo de cálculo/modificação em uma coluna em condições como :

    where (Amount - PTD) <> 0 ; 
where Ammount + ' ' = 'Something' ; 
where (Ammount * 2) = 100

    etc etc

    Para se familiarizar com ele, por favor leia este

    Alternativamente, você poderia fazer algo assim:

     where Amount > PTD or Ammount < PTD

    Para otimizações ainda maiores, depende das colunas e outras condições , mas você também deve ler sobre isso

    1. Usando índices de cobertura para melhorar o desempenho da consulta
    2. Índices agrupados e não agrupados

    Espero que isto ajude

    • 3

  4. Todas as discussões acima são úteis.

    Inline Scalar functione seus benefícios estavam faltando na discussão.

    Reescreva seu UDF e faça-o "Inline Scalar function "

    "Inline Scalar function " definitivamente melhora o desempenho

    Ou não vou usar essa coluna computada e usarCROSS apply .

    Mas primeiro você tem que fazer a consulta correta e otimizar.

    e) seustatistics não está atualizado.

    ii) Ou seus dados cresceram ao longo do tempo.

    • 1

relate perguntas