Início / dba / Perguntas / 205714
Accepted
Neghtasro
Asked: 2018-05-04 07:57:55 +0800 CST

Por que minha UDF escalar tem um desempenho tão diferente em dois servidores diferentes (mas extremamente semelhantes)?

  • 772

Recentemente, eu estava solucionando um problema de desempenho estranho que afetava o ambiente de produção de um aplicativo, mas não nenhum dos ambientes inferiores. Consegui replicar o problema em sua forma mais simples com esta consulta:

SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products

TranslateStatusToActiveé uma UDF escalar muito simples, que basicamente apenas junta o valor dado a outra tabela e retorna 1 ou 0 com base em uma casedeclaração. Eu postaria o código, mas é uma função escrita pelo fornecedor e não estou particularmente interessado em ser processado hoje. (Sim, a lógica pode ser incorporada. Sim, corrige o problema de desempenho. Sim, convencemos o fornecedor a implementar a alteração. Essa não é minha pergunta.)

Ao executar em produção, a consulta levaria entre 10 e 20 segundos para retornar os resultados. Em desenvolvimento, a mesma consulta retorna em menos de 3 segundos. Os planos de execução são quase idênticos, exceto por mostrar que o tempo de CPU foi de cerca de 15.000 ms em produção e 3.000 ms em outros lugares.

Suspeitei que houvesse algumas diferenças ambientais, então configurei outro servidor que replicava as condições de produção o mais próximo possível: verifiquei o número de CPUs, a quantidade de memória fornecida ao SQL Server e o nível de patch específico (13.0.0.1). 4451) foram os mesmos.

Eu restaurei uma cópia do banco de dados de produção para este novo servidor sandbox e, para minha surpresa, a consulta foi executada tão rapidamente quanto no desenvolvimento. Mais uma vez, o plano e os dados eram idênticos, exceto pelo tempo extra de CPU. As esperas listadas no plano de execução eram do mesmo tipo e com poucos ms de distância entre si em todos os ambientes.

Sem saber o que fazer a seguir, habilitei optimize for ad hoc workloadsno servidor de produção. Isso corrigiu o problema de desempenho! Uma coisa, porém: nenhum dos outros ambientes tinha essa configuração habilitada. Eu estava limpando regularmente o procedimento e os caches do sistema em cada ambiente durante o teste, então não acho que foi o resultado de alterar uma configuração causando uma recompilação.

Perguntas

  • O que poderia fazer com que a UDF fosse executada de forma tão diferente em cada ambiente, apesar do plano idêntico e dos sistemas quase idênticos?
  • Por que o ambiente de produção precisava estar optimize for ad hoc workloadshabilitado para funcionar tão bem quanto os outros ambientes, que não o habilitavam?
  • Existe alguma configuração que eu não pensei em verificar que pode causar uma diferença tão grande?

O desenvolvimento é compartilhado, enquanto a produção atualmente é usada apenas por este aplicativo. O uso da terceira caixa seria quase o mesmo da produção. Limpei praticamente todos os caches para os quais eles dão um DBCCcomando. O ambiente de desenvolvimento é usado regularmente como um sistema de treinamento, então estou bastante confiante de que não foi um problema de cache de plano.

A única diferença com a terceira caixa é que não há um aplicativo conectado a ela, mas houve pouco ou nenhum uso de aplicativo enquanto eu estava testando a função em produção, então a diferença foi, com base na minha experiência trabalhando nesse ambiente , insignificante. A única coisa que não pude fazer foi reiniciar o servidor de produção, mas a documentação da Microsoft afirma explicitamente que a habilitação optimize for ad hoc workloadsnão limpa ou afeta nenhum plano existente, então não vejo qual seria a diferença.

sql-server performance

3 respostas

  1. Best Answer

    A situação que você descreveu pode acontecer quando há algum tipo de monitoramento habilitado (trace, sessão de evento estendida, alguma ferramenta de terceiros), que faz algum tipo de log ou trabalho por execução de UDF (ou mesmo por instrução dentro da UDF).

    Se a UDF for executada muitas vezes em uma consulta, pode haver uma sobrecarga muito grande para fazer esse monitoramento. Se o monitoramento estiver ocorrendo apenas em um servidor, você verá uma grande diferença de desempenho entre eles.

    • 8

  2. Vou tentar responder com uma analogia. Pense no SQL Server como um automóvel.

    Considere a configuração "Otimizar para cargas de trabalho ad hoc" como uma caixa de engrenagens automática sofisticada. Quando ele percebe uma subida off-road, ele muda para uma marcha diferente da estrada reta. Os passageiros desfrutam de uma viagem tranquila, independentemente do terreno pelo qual o automóvel passa.

    Mas como isso explica o comportamento diferente da mesma consulta em servidores semelhantes, quando a configuração não existe?

    Nesse caso, as caixas de câmbio ainda são automáticas, mas não tão sofisticadas. Eles percebem e diferenciam entre terrenos, então (no automóvel A) na primeira vez que vê um terreno específico, digamos, uma subida off-road, ele ajusta uma marcha específica. O problema é que eles podem perder certos detalhes. Na próxima vez que ele vir um terreno semelhante (digamos, uma descida off-road ou uma estrada um pouco acima), ele ainda usará a mesma marcha da primeira vez. E os passageiros reclamam porque o equipamento não é o melhor.
    O segundo automóvel (B) partiu em uma rota diferente, primeiro na estrada, depois fora da estrada, então a caixa de câmbio tomou decisões ligeiramente diferentes na primeira vez que encontrou terrenos semelhantes (aos de A). Felizmente as coisas correram bem e os passageiros não reclamaram. Isso pode mudar, é claro, se o terreno após o próximo turno precisar de uma marcha diferente daquela já usada para uma semelhante.

    Terminologia explicada:

                  automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs

    Mais algumas notas:

    • A analogia não é perfeita, é claro. Há muitos detalhes sobre como os planos são salvos e reutilizados (ou não) com a configuração "cargas de trabalho ad hoc" (ativada ou desativada).

    • A configuração não é um botão mágico para resolver todos os problemas. Embora seja útil em muitos casos, há motivos pelos quais o SQL Server o define como OFFpadrão. Tenho certeza de que há muitos casos em que isso não fará nenhuma diferença ou até mesmo degradará o desempenho.

    • Para o problema específico, não posso dizer que tenho certeza de que a descrição acima foi a causa. É apenas uma explicação possível que parece plausível (porque alterar essa configuração a corrigiu). Pode ter havido outras razões para o comportamento diferente (por exemplo, alguma outra configuração que você perdeu entre a produção e o servidor de teste) ou outros serviços/programas em execução nos dois ambientes. É praticamente impossível ter 2 configurações idênticas.

    • Se você puder fornecer a consulta e os planos (semelhantes), há outros usuários no site que podem esclarecer melhor o problema.

    • 3

  3. Eu verificaria se suas opções de conexão (padrão) são as mesmas em ambos os servidores, ao testar consultas que "deveriam" ser executadas de maneira semelhante:

    /*
@@OPTIONS allows you to get the current values that are set for the current session.
When each connection is made, the default values are established and remain set,
unless they are overridden by some other process.

All Sessions Details DMV:

SELECT * FROM sys.dm_exec_sessions;
*/
DECLARE @options INT;
SET @options = @@OPTIONS;

PRINT ('--Options as Integer: ' + CONVERT(VARCHAR(20),@options));

PRINT 'SET DISABLE_DEF_CNST_CHK '   + CASE WHEN 0 < @options &     1 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET IMPLICIT_TRANSACTIONS '  + CASE WHEN 0 < @options &     2 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CURSOR_CLOSE_ON_COMMIT ' + CASE WHEN 0 < @options &     4 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_WARNINGS '          + CASE WHEN 0 < @options &     8 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_PADDING '           + CASE WHEN 0 < @options &    16 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULLS '             + CASE WHEN 0 < @options &    32 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHABORT '             + CASE WHEN 0 < @options &    64 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHIGNORE '            + CASE WHEN 0 < @options &   128 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET QUOTED_IDENTIFIER '      + CASE WHEN 0 < @options &   256 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NOCOUNT '                + CASE WHEN 0 < @options &   512 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_ON '      + CASE WHEN 0 < @options &  1024 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_OFF '     + CASE WHEN 0 < @options &  2048 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CONCAT_NULL_YIELDS_NULL '+ CASE WHEN 0 < @options &  4096 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NUMERIC_ROUNDABORT '     + CASE WHEN 0 < @options &  8192 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET XACT_ABORT '             + CASE WHEN 0 < @options & 16384 THEN 'ON' ELSE 'OFF' END + ';';

SELECT @options Options
    ,CONVERT(BIT,@options &     1) AS [DISABLE_DEF_CNST_CHK]    -- Controls interim or deferred constraint checking.
    ,CONVERT(BIT,@options &     2) AS [IMPLICIT_TRANSACTIONS]   -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
    ,CONVERT(BIT,@options &     4) AS [CURSOR_CLOSE_ON_COMMIT]  -- Controls behavior of cursors after a commit operation has been performed.
    ,CONVERT(BIT,@options &     8) AS [ANSI_WARNINGS]           -- Controls truncation and NULL in aggregate warnings.
    ,CONVERT(BIT,@options &    16) AS [ANSI_PADDING]            -- Controls padding of fixed-length variables.
    ,CONVERT(BIT,@options &    32) AS [ANSI_NULLS]              -- Controls NULL handling when using equality operators.
    ,CONVERT(BIT,@options &    64) AS [ARITHABORT]              -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
    ,CONVERT(BIT,@options &   128) AS [ARITHIGNORE]             -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
    ,CONVERT(BIT,@options &   256) AS [QUOTED_IDENTIFIER]       -- Differentiates between single and double quotation marks when evaluating an expression.
    ,CONVERT(BIT,@options &   512) AS [NOCOUNT]                 -- Turns off the message returned at the end of each statement that states how many rows were affected.
    ,CONVERT(BIT,@options &  1024) AS [ANSI_NULL_DFLT_ON]       -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
    ,CONVERT(BIT,@options &  2048) AS [ANSI_NULL_DFLT_OFF]      -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
    ,CONVERT(BIT,@options &  4096) AS [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
    ,CONVERT(BIT,@options &  8192) AS [NUMERIC_ROUNDABORT]      -- Generates an error when a loss of precision occurs in an expression.
    ,CONVERT(BIT,@options & 16384) AS [XACT_ABORT]              -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.
    ;
    • 1

relate perguntas