Quero entender por que haveria uma diferença tão grande na execução da mesma consulta no UAT (executado em 3 segundos) versus PROD (executado em 23 segundos).
Ambos UAT e PROD estão tendo exatamente dados e índices.
CONSULTA:
set statistics io on;
set statistics time on;
SELECT CONF_NO,
'DE',
'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
CONF_TARGET_NO
FROM CONF_TARGET ct
WHERE CONF_NO = 161
AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
AND ( ( REGISTRATION_TYPE = 'I'
AND (SELECT COUNT(1)
FROM PORTFOLIO
WHERE EMAIL_ADDRESS = ct.EMAIL_ADDRESS
AND DEACTIVATED_YN = 'N') > 1 )
OR ( REGISTRATION_TYPE = 'K'
AND (SELECT COUNT(1)
FROM CAPITAL_MARKET
WHERE EMAIL_ADDRESS = ct.EMAIL_ADDRESS
AND DEACTIVATED_YN = 'N') > 1 ) )
NO UAT:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 11 ms, elapsed time = 11 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 2418 ms, elapsed time = 2442 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
No PRO:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 23937 ms, elapsed time = 23935 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Observe que no PROD a consulta sugere um índice ausente e isso é benéfico como testei, mas esse não é o ponto de discussão.
Eu só quero entender que: ON UAT - por que o sql server cria uma tabela de trabalho e no PROD não? Ele cria um spool de tabela no UAT e não no PROD. Além disso, por que os tempos de execução são tão diferentes no UAT vs PROD?
Observação :
Estou executando o sql server 2008 R2 RTM em ambos os servidores (em breve irei corrigir com o SP mais recente).
UAT: Memória máxima de 8 GB. MaxDop, afinidade do processador e threads de trabalho máximo é 0.
Logical to Physical Processor Map:
*------- Physical Processor 0
-*------ Physical Processor 1
--*----- Physical Processor 2
---*---- Physical Processor 3
----*--- Physical Processor 4
-----*-- Physical Processor 5
------*- Physical Processor 6
-------* Physical Processor 7
Logical Processor to Socket Map:
****---- Socket 0
----**** Socket 1
Logical Processor to NUMA Node Map:
******** NUMA Node 0
PROD: memória máxima de 60 GB. MaxDop, afinidade do processador e threads de trabalho máximo é 0.
Logical to Physical Processor Map:
**-------------- Physical Processor 0 (Hyperthreaded)
--**------------ Physical Processor 1 (Hyperthreaded)
----**---------- Physical Processor 2 (Hyperthreaded)
------**-------- Physical Processor 3 (Hyperthreaded)
--------**------ Physical Processor 4 (Hyperthreaded)
----------**---- Physical Processor 5 (Hyperthreaded)
------------**-- Physical Processor 6 (Hyperthreaded)
--------------** Physical Processor 7 (Hyperthreaded)
Logical Processor to Socket Map:
********-------- Socket 0
--------******** Socket 1
Logical Processor to NUMA Node Map:
********-------- NUMA Node 0
--------******** NUMA Node 1
ATUALIZAR :
XML do Plano de Execução UAT:
XML do Plano de Execução do PROD:
UAT Execution Plan XML - com plano gerado para PROD:
Configuração do servidor:
PROD: PowerEdge R720xd - CPU Intel(R) Xeon(R) E5-2637 v2 @ 3,50 GHz.
UAT: PowerEdge 2950 - CPU Intel(R) Xeon(R) X5460 @ 3,16 GHz
Eu postei em answers.sqlperformance.com
ATUALIZAR :
Obrigado a @swasheck pela sugestão
Alterando a memória máxima no PROD de 60GB para 7680 MB, consigo gerar o mesmo plano no PROD. A consulta é concluída ao mesmo tempo que o UAT.
Agora eu preciso entender - POR QUÊ? Além disso, com isso, não poderei justificar este servidor monstro para substituir o servidor antigo!
O tamanho potencial do buffer pool afeta a seleção do plano pelo otimizador de consulta de várias maneiras. Até onde eu sei, o hyper-threading não afeta a escolha do plano (embora o número de agendadores potencialmente disponíveis certamente possa).
Memória do espaço de trabalho
Para planos que contêm iteradores que consomem memória, como classificações e hashes, o tamanho do buffer pool (entre outras coisas) determina a quantidade máxima de concessão de memória que pode estar disponível para a consulta no tempo de execução.
No SQL Server 2012 (todas as versões), esse número é informado no nó raiz de um plano de consulta, na
Optimizer Hardware Dependenciesseção mostrada comoEstimated Available Memory Grant. Versões anteriores a 2012 não informam esse número no plano de shows.A concessão de memória disponível estimada é uma entrada para o modelo de custo usado pelo otimizador de consulta. Como resultado, é mais provável que uma alternativa de plano que exija uma grande classificação ou operação de hash seja escolhida em uma máquina com uma configuração de buffer pool grande do que em uma máquina com uma configuração mais baixa. Para instalações com uma quantidade muito grande de memória, o modelo de custo pode ir longe demais com esse tipo de pensamento - escolher planos com classificações ou hashes muito grandes onde uma estratégia alternativa seria preferível ( KB2413549 - Usar grandes quantidades de memória pode resultar em um plano ineficiente no SQL Server - TF2335 ).
A concessão de memória do espaço de trabalho não é um fator no seu caso, mas é algo que vale a pena conhecer.
Acesso de dados
O tamanho potencial do buffer pool também afeta o modelo de custo do otimizador para acesso a dados. Uma das suposições feitas no modelo é que toda consulta começa com um cache frio - portanto, presume-se que o primeiro acesso a uma página incorra em uma E/S física. O modelo tenta levar em conta a chance de que o acesso repetido venha do cache, um fator que depende do tamanho potencial do buffer pool, entre outras coisas.
Os Clustered Index Scans nos planos de consulta mostrados na pergunta são um exemplo de acesso repetido; as varreduras são rebobinadas (repetidas, sem alteração do parâmetro correlacionado) para cada iteração da semijunção de loops aninhados. A entrada externa para a semijunção estima 28,7874 linhas e, como resultado, as propriedades do plano de consulta para essas verificações mostram retrocessos estimados em 27,7874.
Novamente, apenas no SQL Server 2012, o iterador raiz do plano mostra o número
Estimated Pages CachednaOptimizer Hardware Dependenciesseção. Esse número informa uma das entradas para o algoritmo de custo que procura contabilizar a chance de acesso repetido à página vindo do cache.O efeito é que uma instalação com um tamanho máximo de buffer pool configurado mais alto tenderá a reduzir o custo de varreduras (ou buscas) que leem as mesmas páginas mais de uma vez mais do que uma instalação com um tamanho máximo de buffer pool menor.
Nos planos simples, a redução de custo em uma varredura rebobinada pode ser vista comparando
(estimated number of executions) * (estimated CPU + estimated I/O)com o custo estimado da operadora, que será menor. O cálculo é mais complexo nos planos de exemplo devido ao efeito da semijunção e da união.No entanto, os planos em questão parecem mostrar um caso em que a escolha entre repetir as verificações e criar um índice temporário é bastante equilibrada. Na máquina com um buffer pool maior, repetir as varreduras custa um pouco menos do que criar o índice. Na máquina com um pool de buffer menor, o custo de varredura é reduzido em um valor menor, o que significa que o plano de spool de índice parece um pouco mais barato para o otimizador.
Escolhas de planos
O modelo de custo do otimizador faz várias suposições e contém um grande número de cálculos detalhados. Nem sempre (ou mesmo geralmente) é possível acompanhar todos os detalhes porque nem todos os números que precisaríamos são expostos e os algoritmos podem mudar entre os lançamentos. Em particular, a fórmula de dimensionamento aplicada para levar em consideração a chance de encontrar uma página em cache não é bem conhecida.
Mais especificamente neste caso particular, as opções de plano do otimizador são baseadas em números incorretos de qualquer maneira. O número estimado de linhas do Clustered Index Seek é 28,7874, enquanto 256 linhas são encontradas no tempo de execução - quase uma ordem de grandeza fora. Não podemos ver diretamente as informações que o otimizador tem sobre a distribuição esperada de valores dentro dessas 28,7874 linhas, mas é muito provável que esteja terrivelmente errado também.
Quando as estimativas estão erradas, a seleção do plano e o desempenho do tempo de execução não são melhores do que o acaso. Acontece que o plano com o spool de índice tem um desempenho melhor do que repetir a varredura, mas é muito errado pensar que o aumento do tamanho do buffer pool foi a causa da anomalia.
Where the optimizer has correct information, the chances are much better that it will produce a decent execution plan. An instance with more memory will generally perform better on a workload than another instance with less memory, but there are no guarantees, especially when plan selection is based on incorrect data.
Both instances suggested a missing index in their own way. One reported an explicit missing index, and the other used an index spool with the same characteristics. If the index provides good performance and plan stability, that might be enough. My inclination would be to rewrite the query as well, but that's probably another story.
Paul White has explained in an excellent lucid manner the reason behind - sql server behaviour when running on servers with more memory.
Além disso, um grande obrigado a @swasheck por identificar o problema pela primeira vez.
Abri um case com a microsoft e abaixo está o que foi sugerido.
O problema é resolvido usando o sinalizador de rastreamento T2335 como um parâmetro de inicialização.
O KB2413549 - Usar grandes quantidades de memória pode resultar em um plano ineficiente no SQL Server o descreve com mais detalhes.
As configurações de memória máxima e hyperthreading podem afetar a escolha do plano.
Além disso, noto que suas opções de "definição" são diferentes em cada ambiente:
StatementSetOptions no UAT:
StatementSetOptions no Prod:
O SQL pode gerar planos diferentes com base nas opções SET. Isso acontece com frequência se você estiver capturando o plano de diferentes sessões do SSMS ou de diferentes execuções do aplicativo.
Certifique-se de que os desenvolvedores estejam usando strings de conexão consistentes.