Eu tenho uma consulta em um servidor que o otimizador estima que terá um custo de 0,01. Na realidade acaba por correr muito mal.
- ele acaba executando uma verificação de índice clusterizado
Nota : Você pode encontrar o ddl, sql, tabelas, etc. aqui no Stackoverflow . Mas essa informação, embora interessante, não é importante aqui - o que é uma questão não relacionada. E essa pergunta nem precisa de DDL.
Se eu forçar o uso de uma busca de índice de cobertura, ele estima que o uso desse índice terá um custo de subárvore de 0,04.
- varredura de índice clusterizado: 0,01
- cobrindo a varredura do índice: 0,04
Portanto, não é de surpreender que o servidor opte por usar o plano que:
- na verdade, causa 147.000 leituras lógicas do índice clusterizado
- em vez das 16 leituras muito mais rápidas de um índice de cobertura
Servidor A:
| Plan | Cost | I/O Cost | CPU Cost |
|--------------------------------------------|-----------|-------------|-----------|
| clustered index scan (optimizer preferred) | 0.0106035 | 116.574 | 5.01949 | Actually run extraordinarily terrible (147k logical reads, 27 seconds)
| covering index seek (force hint) | 0.048894 | 0.0305324 | 0.0183616 | actually runs very fast (16 logical reads, instant)
Isto é com estatísticas atualizadas COM FULLSCAN nada menos.
Tente em outro servidor
Então eu tento em outro servidor. Recebo estimativas da mesma consulta, com uma cópia recente do banco de dados de produção, também com estatísticas atualizadas (COM FULLSCAN).
- Este outro servidor também é SQL Server 2014
- mas ele percebe corretamente que as varreduras de índice clusterizado são ruins
- e naturalmente prefere a busca do índice de cobertura (porque o custo é 5 ordens de magnitude menor!)
Servidor B :
| Plan | Cost | I/O Cost | CPU Cost |
|-------------------------------------------|-------------|------------|-----------|
| Clustered index scan (force hint) | 115.661 | 110.889 | 4.77115 | Runs extraordinarily terrible as server A (147k logical reads, 27 seconds)
| Covering index seek (optimizer preferred) | 0.0032831 | 0.003125 | 0.0001581 | Runs fast (16 logical reads, near instant)
O que não consigo descobrir é porque para esses dois servidores, com cópias quase idênticas do banco de dados, ambos com estatísticas atualizadas, ambos SQL Server 2014:
- pode-se executar a consulta tão corretamente
- o outro cai morto
Eu sei que parece um caso clássico de estatísticas desatualizadas. Ou planos de execução em cache ou sniffing de parâmetros. Mas essas consultas de teste estão sendo emitidas com OPTION(RECOMPILE), por exemplo:
SELECT MIN(RowNumber) FROM Transactions
WITH (index=[IX_Transactions_TransactionDate]) WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)
Se você olhar de perto, parece que a estimativa do "operador" está errada
A varredura de índice clusterizado é uma coisa ruim. E um dos servidores sabe disso. É uma operação muito cara, e a operação de varredura deve me dizer isso.
Se eu forçar a verificação de índice clusterizado e observar as operações de verificação estimadas em ambos os servidores, algo salta à vista:
| Cost | Server A | Server B |
|---------------------|-------------|------------|
| I/O Cost | 116.573 | 110.889 |
| CPU Cost | 5.01945 | 4.77155 |
| Total Operator Cost | 0.0106035 | 115.661 |
mistakenly | avoids it
uses it |
O custo do operador no servidor A é muito baixo.
- o custo de E/S é razoável
- o custo da CPU é razoável
- mas em conjunto, o custo geral do Operador é 4 ordens de magnitude muito baixo.
Isso explica por que está escolhendo erroneamente o plano de execução ruim; simplesmente tem um custo de operador ruim . O servidor B descobriu isso corretamente e evita a verificação de índice clusterizado.
O operador não é = cpu + io?
Em quase todos os nós do plano de execução sobre os quais você passará o mouse e em todas as capturas de tela dos planos de execução no dba, stackoverflow e em todos os blogs, você verá isso sem falhas:
operatorCost >= max(cpuCost, ioCost)
E, na verdade, geralmente é :
operatorCost = cpuCost + ioCost
Então o que está acontecendo aqui?
O que pode explicar o servidor decidir que os custos de 115 + 5 são quase nada e, em vez disso, decide algo 1/10000 desse custo?
Eu sei que o SQL Server tem opções para ajustar o peso interno aplicado às operações de CPU e E/S:
DBCC TRACEON (3604); -- Show DBCC output
DBCC SETCPUWEIGHT(1E0); -- Default CPU weight
DBCC SETIOWEIGHT(0.6E0); -- I/O multiplier = 0.6
DBCC SHOWWEIGHTS; -- Show the settings
E quando você fizer isso, o custo do operador pode acabar abaixo do custo de CPU + E/S:
Mas ninguém tem brincado com eles. É possível que o SQL Server tenha algum ajuste automático de peso baseado no ambiente, ou baseado em alguma comunicação com o subsistema de disco?
Se o servidor fosse uma máquina virtual, usando um disco SCSI virtual, conectado por um link de fibra a uma Storage Area Network (SAN), ele poderia decidir que os custos de CPU e E/S podem ser ignorados?
Exceto que não pode ser alguma coisa de ambiente permanente neste servidor, porque todas as outras consultas que encontrei se comportam corretamente:
I/O: 0.0112613
CPU: +0.0001
=0.0113613 (theoretical)
Operator: 0.0113613 (actual)
O que pode explicar o servidor não tomar:
I/O Cost + Cpu Cost = Operator Cost
corretamente nesta instância ?
SQL Server 2014 SP2.
Metas de linha
Se uma meta de linha for definida na consulta, isso poderá afetar as estimativas de linha e o custo.
Você pode confirmar se isso está causando o problema executando a consulta com o sinalizador de rastreamento 4138 ativado (o que removerá a influência do objetivo da linha).
Tamanho do conjunto de buffers
O custo estimado para algumas operações de E/S pode ser reduzido se houver um pool de buffers maior disponível (o servidor com custo reduzido tem 14 GB de RAM, versus 6 GB na outra máquina).
Você pode verificar a influência desse comportamento procurando por "EstimatedPagesCached" no XML do plano. Um valor mais alto para essa propriedade pode reduzir o custo de E/S de partes do plano de execução que potencialmente acessam os mesmos dados.
Agendadores disponíveis
Para uma consulta paralela, o custo de CPU de um operador pode ser reduzido em até "# de agendadores / 2". Você pode verificar qual valor isso tem procurando por "EstimatedAvailableDegreeOfParallelism" no XML do plano.
Menciono isso porque notei que a "consulta lenta" rodava em um servidor com 4 núcleos, enquanto a mais rápida rodava em um servidor com 1 núcleo.
Os custos são estranhos e quebrados
Forrest fala sobre várias maneiras diferentes pelas quais os custos podem acabar não fazendo sentido em seu blog: Percentage Non Grata
Depende.
É uma pena que outra pessoa tenha deletado seu post porque eu tive ideias semelhantes.
Metas de linha
Isso não é o que você está enfrentando com base nas capturas de tela, mas é um fator no cálculo do custo do Operador. Os custos de E/S e CPU não são dimensionados, eles mostrarão um custo por execução se uma meta de linha não estiver em vigor. O custo do operador é dimensionado para mostrar a meta da linha. Esta é uma instância em que I/O e CPU não compreendem exatamente o custo do Operador, o número estimado de execuções é algo a ser levado em consideração. Como você vê essas estatísticas depende se você está olhando para a entrada interna ou externa.
Fonte : Inside the Optimizer: Row Goals In Depth por Paul White - 18 de agosto de 2010 ( arquivo )
Uso do buffer pool
Isso pode ser um fator que está afetando você.
Fonte : Modelo de Custo do Plano de Execução por Joe Chang - julho de 2009 ( arquivo )
Para o seu problema
Podemos ver em suas capturas de tela que você tem um custo de subárvore muito interessante no servidor que não está funcionando bem. O interessante é que ele tem mais memória para usar e menos CPU.
As informações acima me indicam que você provavelmente tem um problema com o custo da subárvore e o custo do operador é um sintoma.
Fonte : Custos reais do plano de execução por Grant Fritchey - 20 de agosto de 2018 ( arquivo )
Acho que a resposta está nestas frases:
O que eu acho que está acontecendo com você:
Caso contrário, eu adoraria ver os planos de consulta estimados e reais para mergulhar mais fundo no que parece estar acontecendo.
IMPORTANTE, ISSO VAI DOER (Você pode ser demitido) SE VOCÊ EXECUTAR ISSO EM PRODUÇÃO SEM ENTENDER O QUE VAI ACONTECER E SEM PLANEJAR ISSO. É assim que eu limparia o cache para testar novamente com a recompilação.
Diferentes maneiras de liberar ou limpar o cache do SQL Server por Bhavesh Patel - 31 de março de 2017 ( arquivo )
DBCC FREESYSTEMCACHEDBCC FREESESSIONCACHEDBCC FREEPROCCACHEPara mim, parece absolutamente normal que o servidor A escolha a verificação de índice clusterizado. Esta é a melhor decisão dado o conhecimento que o otimizador tem. O estranho é que o servidor B não escolhe o mesmo. Acho que tenho uma resposta para isso, mas primeiro deixe-me explicar por que o otimizador deve escolher a verificação de índice clusterizado.
A razão básica tem a ver com o fato de achar que os valores em RowNumber e TransactionDate são independentes. Como diz aqui :
And the query is
The option are: 1) to start scanning the clustered index, which is sorted on RowNumber, and stop as soon it will encounter the first tuple with TransactionDate >= '20191002 04:00:00.000' which will be the actual answer to the query 2) to search the nonclustered index of TransactionDate for value '20191002 04:00:00.000' and then keep scanning the rest of the index from that value onward, keeping the minimum RowNumber that it will find
I am assuming here that value '20191002 04:00:00.000' is among the largest values in column TransactionDate. Actually, let's assume that it is larger than 95% of the values. Given the independence assumption, in option 1, it reasonable to assume that the answer will be found in a single disk fetch, as each tuple scanned has 5% probability to be the final answer. In option 2, searching the index for the specific date, already involves more disk page fetches, and then we also have to scan the 5% of the index. In reality though, as values in the two columns as directly correlated, what seems to the optimizer as the best option, ends up scanning 95% of the clustered index.
Então, por que o Servidor B não escolhe varrer o índice clusterizado? Obviamente, no Servidor B o índice clusterizado NÃO é classificado em RowNumber, como podemos ver nos planos postados na pergunta original:
Então, por que CPU_cost + I/O_cost >> custo. Parece que o SQL Server para verificação de índice clusterizado relata o custo total de CPU e E/S da tabela, mesmo que seja apenas uma verificação parcial, e relata apenas a estimativa real com base na rapidez com que encontrará o valor esperado como custo total. Você pode ver exatamente o mesmo comportamento no plano postado aqui
E quanto ao que pode ser feito, se RowNumber e TransactionDate estão sempre aumentando, a consulta pode ser reescrita da seguinte forma:
Podemos presumir que os servidores são genuinamente idênticos?
Percebi uma pequena alteração nos custos da etapa de consulta retornados para um plano de execução do SP após alterar o nível de compatibilidade do banco de dados em um servidor sql2012. (db ocioso, obteve o primeiro plano xml, aplicou a mudança de opção, recompilou o sp, obteve o segundo plano xml) O plano em si parece idêntico. Mais opções estão disponíveis no otimizador, possivelmente calculando de forma ligeiramente diferente. Se você tiver um patch / compatibilidade diferente nos servidores 2x, isso poderá resultar no plano real sendo mais radicalmente diferente (errado ..)