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?