Por que o SQL Server está aguardando confirmação?

Por padrão, o SQL Server adere às regras de banco de dados ACID. O A em ACID significa atomic, o que significa que toda a transação acontece ou nada disso. O I em ACID significa isolamento, o que significa que as transações são isoladas umas das outras, portanto, todos os resultados de uma transação são visíveis para outra transação ou nenhum deles é. Isso é feito no SQL Server usando bloqueios para leituras e gravações (pelo menos por padrão). Reads ( SELECTs) obtêm um bloqueio de leitura, o que impede qualquer gravação ( UPDATEouINSERT) operações nesses itens (ou páginas, ou a tabela inteira, dependendo do que o algoritmo de escalonamento de bloqueio de magia negra do SQL decidir). Para preservar o isolamento, os bloqueios de leitura para consultas devem ser mantidos até que todos os resultados tenham sido enviados ou armazenados em buffer. Isso impede que as gravações obtenham seus bloqueios (ou melhor, faz com que esperem para obtê-los), a menos que as gravações sejam isoladas (consulte outra resposta sobre isolamento de instantâneo). Caso contrário, alguns dos resultados de uma gravação subsequente podem acabar nos resultados da consulta que ocorreu antes da gravação.

Existem várias soluções para isso, cada uma com compensações:

1. Armazene os resultados no lado do cliente (leia todas as linhas imediatamente o mais rápido possível e faça o processamento posterior). Isso pode exigir muita memória do lado do cliente se houver muitos resultados e ainda bloqueará as atualizações até que o cliente termine o buffer (é por isso que deve ser completamente armazenado em buffer antes de qualquer processamento subsequente).
2. Buffer de resultados no lado do servidor. Você pode fazer isso selecionando os dados em uma tabela temporária e retornando a tabela temporária em vez da consulta original. Isso exigirá memória e/ou disco do lado do servidor e, portanto, pode interferir em outras operações do lado do servidor.
3. Use WITH (NOLOCK)no SELECTfinal da operação de leitura. Isso diz ao SQL Server para ignorar o isolamento para esta leitura, o que significa que você pode obter a versão original de uma linha no início dos resultados e uma versão modificada de outra linha posteriormente nos resultados quando ambas as linhas foram modificadas pela mesma transação e as versões retornadas nunca estavam no banco de dados ao mesmo tempo (ou seja, as linhas foram modificadas durante a consulta, depois que a primeira linha foi retornada, mas antes da segunda linha). Essa resposta tem o menor custo de desempenho, mas produz resultados não ACID quebrando o isolamento e/ou a atomicidade (dependendo de como você olha para isso).
4. Use o isolamento de instantâneo (consulte outra resposta - não sou especialista em isolamento de instantâneo). Observe que tive problemas significativos de desempenho com o isolamento de instantâneo, e isso também exigirá muitos recursos do lado do servidor.

Pode haver outras soluções também, mas essas são as mais comuns.

O SQL Server enviará algumas linhas ao aplicativo cliente e aguardará uma resposta - no seu caso, uma solicitação para outro lote de linhas.

Geralmente (mas nem sempre), a espera ASYNC_NETWORK_IO ocorre devido ao design do aplicativo. Em vez de armazenar os dados em cache e enviar uma resposta ao SQL Server quase imediatamente, ele executa algum tipo de operação RBAR (Row By Agonizing Row) nos dados que recebe.

Enquanto os dados estão sendo lidos, um bloqueio de compartilhamento será colocado no recurso e nada poderá atualizar esse recurso até que o bloqueio S seja liberado. Se o aplicativo estiver processando linha por linha à medida que é recebido (em vez de processar nas 20 mil linhas quando a consulta for concluída), você manterá S bloqueios por mais tempo e as atualizações, infelizmente, terão que esperar sua vez.

Se eu vejo isso, eu olho para o aplicativo antes da rede.

Se você usar níveis de isolamento de instantâneo, nenhum bloqueio S será colocado nas linhas, portanto, os UPDATEs não serão bloqueados. Certifique-se de entender a diferença entre instantâneo e instantâneo de leitura confirmada e que você teste seu aplicativo para esses níveis de isolamento. Além disso, teste sua carga de trabalho para carga adicional em tempdb para o armazenamento de versão.

Editar: RE: desvantagem de ativar o isolamento de instantâneo

Ativar o isolamento de instantâneo per se (ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION = ON) adiciona uma coluna extra oculta de 14 bytes a cada linha em cada tabela (quando ocorrem gravações, as linhas não tocadas por gravações não têm versão).
O uso de READ_COMMITTED_SNAPSHOT deve ser validado em todo o aplicativo usando o banco de dados (é uma configuração no nível do banco de dados), porque o aplicativo pode ter sido escrito com o bloqueio pessimista em mente e o bloqueio otimista pode quebrar algumas partes dele.
O isolamento SNAPSHOT (não READ_COMMITTED_SNAPSHOT) tem o controle de conflitos de bloqueio otimista ativado, portanto, você deve considerar isso e lidar com os erros de acordo.