Tenho uma exibição que é executada rapidamente (alguns segundos) para até 41 registros (por exemplo, TOP 41), mas leva vários minutos para 44 ou mais registros, com resultados intermediários se executado com TOP 42ou TOP 43. Especificamente, ele retornará os primeiros 39 registros em alguns segundos e parará por quase três minutos antes de retornar os registros restantes. Esse padrão é o mesmo ao consultar TOP 44ou TOP 100.
Esta visão originalmente derivou de uma visão base, adicionando à base apenas um filtro, o último no código abaixo. Parece não haver diferença se eu encadear a visão filho da base ou se eu escrever a visão filho com o código da base alinhado. A visualização base retorna 100 registros em apenas alguns segundos. Eu gostaria de pensar que posso fazer com que a visão infantil funcione tão rapidamente quanto a base, não 50 vezes mais devagar. Alguém já viu esse tipo de comportamento? Algum palpite sobre a causa ou resolução?
Esse comportamento tem sido consistente nas últimas horas enquanto eu testo as consultas envolvidas, embora o número de linhas retornadas antes que as coisas comecem a desacelerar tenha aumentado e diminuído ligeiramente. Isso não é novo; Estou olhando para ele agora porque o tempo total de execução foi aceitável (<2 minutos), mas vi essa pausa nos arquivos de log relacionados por meses, pelo menos.
Bloqueio
Nunca vi a consulta bloqueada e o problema existe mesmo quando não há outra atividade no banco de dados (conforme validado por sp_WhoIsActive). A visão base inclui NOLOCKtudo, pelo que vale a pena.
Consultas
Aqui está uma versão reduzida da visualização filho, com a visualização base alinhada para simplificar. Ele ainda exibe o salto no tempo de execução em cerca de 40 registros.
SELECT TOP 100 PERCENT
Map.SalesforceAccountID AS Id,
CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress END AS BillingStreet,
CASE WHEN C.City = 'Unknown' THEN '' ELSE SUBSTRING(C.City, 1, 40) END AS BillingCity,
SUBSTRING(C.Region, 1, 20) AS BillingState,
CASE WHEN C.PostalCode = 'Unknown' THEN '' ELSE SUBSTRING(C.PostalCode, 1, 20) END AS BillingPostalCode,
CASE WHEN C.Country = 'Unknown' THEN '' ELSE SUBSTRING(C.Country, 1, 40) END AS BillingCountry,
CASE WHEN C.PhoneNumber = 'Unknown' THEN '' ELSE C.PhoneNumber END AS Phone,
CASE WHEN C.FaxNumber = 'Unknown' THEN '' ELSE C.FaxNumber END AS Fax,
TransC.WebsiteAddress AS Website,
C.AccessKey AS AccessKey__c,
CASE WHEN dbo.ValidateEMail(C.EMailAddress) = 1 THEN C.EMailAddress END, -- Removing this UDF does not speed things
TransC.EmailSubscriber
-- A couple dozen additional TransC fields
FROM
WarehouseCustomers AS C WITH (NOLOCK)
INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) ON C.CustomerID = TransC.CustomerID
LEFT JOIN Salesforce.AccountsMap AS Map WITH (NOLOCK) ON C.CustomerID = Map.CustomerID
WHERE
C.DateMadeObsolete IS NULL
AND C.EmailAddress NOT LIKE '%@volusion.%'
AND C.AccessKey IN ('C', 'R')
AND C.CustomerID NOT IN (243566) -- Exclude specific test records
AND EXISTS (SELECT * FROM Orders AS O WHERE C.CustomerID = O.CustomerID AND O.OrderDate >= '2010-06-28') -- Only count customers who've placed a recent order
AND Map.SalesforceAccountID IS NULL -- Only count customers not already uploaded to Salesforce
-- Removing the ORDER BY clause does not speed things up
ORDER BY
C.CustomerID DESC
Esse Id IS NULLfiltro descarta a maioria dos registros retornados por BaseView; sem uma TOPcláusula, eles retornam 1.100 registros e 267K, respectivamente.
Estatisticas
Ao executar TOP 40:
SQL Server parse and compile time: CPU time = 234 ms, elapsed time = 247 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.
(40 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 39112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 2199 ms, elapsed time = 7644 ms.
Ao executar TOP 45:
(45 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 98268, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 1788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 2152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 41980 ms, elapsed time = 177231 ms.
Estou surpreso ao ver o número de leituras pular ~ 3x para essa modesta diferença na saída real.
Comparando os planos de execução, eles são os mesmos exceto o número de linhas retornadas. Assim como nas estatísticas acima, as contagens reais de linhas para as primeiras etapas são muito mais altas na TOP 45consulta, não apenas 12,5% mais altas.
Em resumo, ele está digitalizando um índice de cobertura de Pedidos, buscando os registros correspondentes de Clientes de Armazém; loop-junção disso para TransactionalCustomers (consulta remota, plano exato desconhecido); e mesclando isso com uma varredura de tabela de AccountsMap. A consulta remota representa 94% do custo estimado.
Notas diversas
Anteriormente, quando eu executava o conteúdo expandido da exibição como uma consulta independente, ele rodava bem rápido: 13 segundos para 100 registros. Agora estou testando uma versão reduzida da consulta, sem subconsultas, e essa consulta muito mais simples leva três minutos para solicitar o retorno de mais de 40 linhas, mesmo quando executada como uma consulta autônoma.
A visualização filho inclui um número substancial de leituras (~1M por sp_WhoIsActive), mas nesta máquina (oito núcleos, 32 GB de RAM, 95% da caixa SQL dedicada) isso normalmente não é um problema.
Eliminei e recriei ambas as visualizações várias vezes, sem alterações.
Os dados não incluem nenhum campo TEXT ou BLOB. Um campo envolve um UDF; removê-lo não impede a pausa.
Os tempos são semelhantes, seja consultando no próprio servidor ou em minha estação de trabalho a 1.400 milhas de distância, portanto, o atraso parece ser inerente à própria consulta, em vez de enviar os resultados ao cliente.
Algumas coisas para tentar:
Verifique seus índices
Todos os
JOINcampos-chave estão indexados? Se você usa muito essa visão, eu iria tão longe a ponto de adicionar um índice filtrado para os critérios na visão. Por exemplo...CREATE INDEX ix_CustomerId ON WarehouseCustomers(CustomerId, EmailAddress) WHERE DateMadeObsolete IS NULL AND AccessKey IN ('C', 'R') AND CustomerID NOT IN (243566)Atualizar estatísticas
FULLSCAN. Se houver um grande número de linhas, é possível que os dados tenham mudado significativamente sem acionar um recálculo automático.Limpe a consulta
Faça o
MapJOINaNOT EXISTS- Você não precisa de nenhum dado dessa tabela, pois deseja apenas registros não correspondentesRemova o arquivo
ORDER BY. Eu sei que os comentários dizem que não importa, mas acho isso muito difícil de acreditar. Pode não ter importado para seus conjuntos de resultados menores, pois as páginas de dados já estão armazenadas em cache.O problema é a consulta remota. Se você trouxer essa tabela localmente, nunca verá o problema. Para explicar mais, o mecanismo de consulta remota enviará valores de junção remotamente se você tiver menos do que um determinado valor. Se estiver acima desse limite, ele puxará toda a tabela localmente (este é o bit lento) e, em seguida, executará a junção. Ao alterar para uma cláusula EXISTS, você está ingressando na consulta remota após todas as outras uniões e, portanto, menos valores são necessários para a união na tabela remota e eles podem ser enviados remotamente.
Mas você ainda pode ter o mesmo problema se retornar mais linhas das tabelas locais.
Uma maneira de resolver o problema no passado é retornar os resultados da parte local da consulta para uma tabela temporária. Em seguida, selecione os valores de junção remota da tabela temporária em uma variável XML e passe como um parâmetro NVARCHAR para um procedimento armazenado remoto que escrevi. O procedimento armazenado remoto pega o parâmetro e o converte de volta em XML e, em seguida, em uma tabela temporária. Ele junta isso com a tabela remota para obter as linhas de volta. Estes são então unidos em outra consulta à tabela temporária local para obter o resultado final. Um pouco mais complicado, mas quando você só precisa de algumas centenas de linhas de uma tabela com dezenas de milhões de linhas, vale a pena o esforço para evitar um grande download a cada vez. A consulta passou de vinte minutos para 13 segundos.
Melhoria 1 Remova a SubQuery para Pedidos e converta-a em join
Melhoria 2 - Manter os registros filtrados TransactionalCustomers em uma tabela Temporária Local
consulta final
Ponto 3 - Presumo que você tenha índices em CustomerID, EmailAddress, OrderDate
Notas sobre a solução originalmente deixada no corpo da questão pelo autor:
A correção acabou sendo simples: substituir
LEFT JOINto Map por umaNOT EXISTScláusula. Isso causa apenas uma pequena diferença no plano de consulta, juntando-se à tabela TransactionCustomers (uma consulta remota) depois de juntar-se à tabela Map em vez de antes. Isso pode significar que ele está solicitando apenas os registros necessários do servidor remoto, o que reduziria o volume transmitido em cerca de 100 vezes.Normalmente sou o primeiro a torcer por
NOT EXISTS; geralmente é mais rápido que umaLEFT JOIN...WHERE ID IS NULLconstrução e um pouco mais compacto. Nesse caso, é complicado porque a consulta do problema é criada em uma exibição existente e, embora o campo necessário para a antijunção seja exposto pela exibição base, ele é primeiro convertido de inteiro para texto. Portanto, para obter um desempenho decente, preciso descartar o padrão de duas camadas e, em vez disso, ter duas exibições quase idênticas, com a segunda incluindo aNOT EXISTScláusula.Obrigado a todos pela ajuda na resolução deste problema! Pode ser muito específico para minhas circunstâncias para ajudar qualquer outra pessoa, mas espero que não. Se nada mais, é um exemplo de
NOT EXISTSser mais do que marginalmente mais rápido queLEFT JOIN...WHERE ID IS NULL. Mas a verdadeira lição provavelmente é garantir que as consultas remotas sejam unidas da maneira mais eficiente possível; o plano de consulta afirma que representa 2% do custo, mas nem sempre estima com precisão.