O que pode causar atraso na execução da instrução SELECT e alto ASYNC_NETWORK_IO mesmo com "desconsiderar resultados após a execução"

Estamos enfrentando problemas de desempenho em nossos SQL Servers.

7 servidores, todos executando o mesmo aplicativo, todos têm o mesmo problema.

SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 

Quando você observa as estatísticas de espera total acumuladas do servidor, mostra 99% ASYNC_NETWORK_IO. Primeiro assumimos que isso foi causado pelo aplicativo. No entanto:

Quando pegamos a consulta (capturada do aplicativo) e a executamos como um teste de dentro do SSMS , desconsiderando os resultados após a execução . Demora cerca de 6 segundos quando o executamos em uma conexão TCP e 50 segundos em uma conexão de pipes nomeados. Obtemos resultados muito lentos.

(sem desconsiderar os resultados após a execução a consulta dá um resultado de 57k linhas 227 colunas e um tamanho de 221MB.)

É a edição principal, portanto, não podemos usar uma conexão local. Testamos a conexão remota com TCP e pipes nomeados.

sobre TCP

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 = 4 ms, elapsed time = 4 ms.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1359 ms,  elapsed time = 6868 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.

Portanto, embora leve apenas 0,62 segundos para a verificação do índice clusterizado ser concluída, ainda leva 6,8 segundos para concluir a consulta, mesmo que desconsidere os resultados após a verificação da execução

Sobre Pipes Nomeados

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.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1578 ms,  elapsed time = 54202 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.

Com pipes nomeados é ainda pior. Novamente, leva apenas meio segundo para a verificação do índice clusterizado ser concluída e leva 54 segundos para a conclusão da consulta.

Se eu restaurar o banco de dados no meu laptop e executar a mesma consulta, recebo 0,4 segundos para a verificação de índice clusterizado e 1375 ms de tempo de CPU, 1462 ms de tempo decorrido.

usando https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/

Coletei as estatísticas de espera para o teste com uma conexão TCP e uma conexão de pipe nomeado:

TCP

Pipes nomeados

As esperas não somam o tempo total decorrido. Apenas 0,472 segundos de tempo de espera e 6,8 de tempo decorrido no teste tcp e apenas 40,7 de tempo de espera vs 54,2 de tempo decorrido no teste de Naped Pipe.

mais informações relevantes

• Os SQL Servers são servidores virtuais VMware
• É a edição core do Windows
• 4 núcleos
• 64 GB de RAM, apenas 16 GB atribuídos ao SQL Server
• O serviço de aplicativo está sendo executado no mesmo servidor que o SQL Server
• O próprio aplicativo se conecta com as sessões MARS

Não entendemos de onde vem o atraso. A consulta não deve ser concluída muito mais rápido se você tiver os resultados desconsiderados verificados?