Alto ASYNC_NETWORK_IO quando o usuário bloqueia o computador com o Excel aberto

ASYNC_NETWORK_IOpode causar problemas de contenção de banco de dados se os resultados enviados ao cliente nunca forem enviados para nenhum lugar (por exemplo, por meio de uma ORDER BYcláusula forçando uma classificação em tempdb).

Se esse problema estiver ocorrendo com o Excel, o problema provavelmente se deve a uma atualização de conexão de banco de dados do Excel no thread em segundo plano e ao usuário bloquear simultaneamente seu computador durante a atualização em segundo plano. Quando o encadeamento em segundo plano estiver concluído, o computador será bloqueado. O problema é que no Win32, um computador bloqueado significa que o objeto de sessão do usuário não está disponível. Quando o objeto de sessão do usuário não está disponível, não há nenhum lugar para o thread em segundo plano retornar os resultados e ele apenas trava, aguardando o Excel.exe ler os resultados. Como os resultados nunca chegam a lugar algum, o ASYNC_NETWORK_IObloqueio causa um livelock em todos os bloqueios adquiridos anteriormente. Em si, ASYNC_NETWORK_IOé inofensivo. É a corrente de bloqueio que está causando o dano.

Para resumir, esse problema pode acontecer nas duas condições a seguir:

1. ASYNC_NETWORK_IO está presente devido a um leitor lento (no meu caso, o leitor lento está bloqueado devido ao objeto de sessão do usuário Win32 indisponível)
2. Os resultados nunca são enviados para algum objeto intermediário, implicitamente (por exemplo, ORDER BYdesencadeando uma classificação na tempdbtabela subjacente, porque os dados são muito grandes para classificar completamente na memória) ou explicitamente ( INSERTresulta em uma tempdbtabela, faça SELECTna tabela tempdb, que libera o livelock).

Duas opções:

1. Se você não tem controle sobre a lentidão do cliente?

uma. Grave os resultados da consulta em uma tabela temporária. Devolva a tabela temporária ao usuário.

b. Além disso, não se esqueça de SET NOCOUNT ON na parte superior do procedimento armazenado, para evitar atrasos de ida e volta devido ao envio de mensagens DONE_IN_PROC TDS ao cliente.

2. Se você tem controle sobre a lentidão do cliente?

uma. Verifique se a NIC está configurada incorretamente ou se o cliente está usando uma VM e a NIC da VM está configurada incorretamente. Veja: https://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/

b. Meça usando Perfmon. A Microsoft usa uma heurística de 10 para o comprimento da fila de saída de rede PerfMon. Consulte: https://technet.microsoft.com/en-us/library/aa997365(v=exchg.80).aspx

c. Adicione os manipuladores de eventos Query BeforeRefresh e AfterRefresh às dicas de registro de instrumentos na Planilha para determinar o que o usuário está fazendo. Em particular, consulte "XL2000: Como usar os eventos de consulta BeforeRefresh e AfterRefresh": https://support.microsoft.com/en-us/help/213187/xl2000-how-to-use-the-query-beforerefresh- e-eventos-após-atualização

d. Escreva um suplemento do Excel que armazene em cache suas chamadas SQL.

ATUALIZAÇÃO 19/01/2018

Ainda estou lidando com problemas altos de ASYNC_NETWORK_IO com usuários do Excel. Acredito ter encontrado mais causas potenciais para isso, mas estas são apenas teorias neste momento:

1. Se você tiver várias tabelas na mesma planilha e a última tabela à direita tiver mais linhas do que as tabelas à esquerda, isso parece fazer com que o Excel "pisque" muito. Da mesma forma, no SolarWinds Database Performance Analyzer, as consultas nesta planilha que retornam o maior número de linhas e colunas também apresentam os maiores problemas de ASYNC_NETWORK_IO. (Os próximos passos para mim são reproduzir esse problema, mas não tenho certeza se vale a pena, pois convenci um usuário de que eles não precisavam desse enorme conjunto de resultados por enquanto.)

2. Encontrei um comentário no Experts Exchange ( https://www.experts-exchange.com/questions/25049176/Slow-queries-excuted-throug-network-ASYNC-NETWORK-IO.html ) onde alguém disse resultados de 5.000 linhas ou mais são conhecidos por causar problemas. Isso parece realmente ser uma recomendação levantada internamente pelo aplicativo Microsoft Dynamics NAV, que é notório por causar instabilidade no SQL Server devido a altas esperas de ASYNC_NETWORK_IO. A mensagem de erro que o aplicativo gera é: "O número de registros na tabela excede o número máximo de 5.000. Defina um filtro para diminuir o número de registros na tabela. Exportar muitos registros de uma vez pode afetar o desempenho do sistema." Mas isso é apenas um palpite.

3. Para tarefas SSIS Excel Destination, se o usuário não tiver permissão para gravar na pasta Temporary Internet Files, ele poderá travar: https://stackoverflow.com/a/23523954/1040437 (novamente, apenas uma teoria, pois uso isso tentar reunir todas as teorias possíveis em um só lugar.)