Por que o SQL Server retorna algumas linhas enquanto ainda executa a consulta e às vezes não?

A resposta, como sempre (tudo bem, na maioria das vezes), está no plano de execução.

Existem certos operadores que exigem que todas as linhas cheguem a eles antes que possam começar a processá-las e transmiti-las downstream, por exemplo:

• Hash Join (na construção da tabela de hash)
• Correspondência de hash
• Classificar (exceto fluxo de hash distinto)

Eles são chamados de bloqueio ou operadores de parada e partida por causa disso, e geralmente são escolhidos quando o otimizador acha que terá que processar muitos dados para encontrar seus dados.

Existem outros operadores que podem começar a transmitir ou transmitir imediatamente quaisquer linhas encontradas

• Loops aninhados
• Junções de mesclagem compatíveis com índice
• Agregados de fluxo

Quando as consultas começam a retornar dados imediatamente, mas não terminam imediatamente, geralmente é um sinal de que o otimizador escolheu um plano para localizar e retornar algumas linhas rapidamente usando operadores que têm um custo inicial menor.

Isso pode acontecer devido a metas de linha introduzidas por você ou pelo otimizador.

Isso também pode acontecer se um plano ruim for escolhido por algum motivo (falta de SARGability, sniffing de parâmetros, estatísticas insuficientes, etc.), mas isso exige mais pesquisas para descobrir.

Para mais informações, confira o blog de Rob Farley aqui

E a série de Paul White sobre gols de linha aqui , aqui , aqui e aqui .

Deve-se notar também que, se você estiver falando sobre SSMS, as linhas só aparecem quando um buffer inteiro é preenchido, não apenas à toa.

Se eu entendi o que você está observando, é assim que o Management Studio renderiza linhas e tem pouco a ver com como o SQL Server retorna linhas. Na verdade, muitas vezes, quando você está retornando grandes resultados ao SSMS e tentando renderizá-los em uma grade, o SSMS não consegue acompanhar e o SQL Server acaba esperando que o aplicativo processe mais linhas. Nesse caso, você verá o SQL Server acumulando ASYNC_NETWORK_IOesperas.

Você pode controlá-lo um pouco usando Resultados para Texto em vez de Resultados para Grade, pois o SSMS pode desenhar texto mais rápido do que grades, mas você provavelmente descobrirá que isso pode afetar a legibilidade dependendo do número de colunas e dos tipos de dados envolvidos. Ambos são afetados quando o SSMS decide realmente gravar os resultados nesse painel, o que depende de quão cheio está o buffer de saída.

Quando você tem várias instruções e deseja forçar o buffer a renderizar os resultados de saída no painel de mensagens, você pode usar um pequeno truque de impressão entre as instruções:

RAISERROR('', 0, 1) WITH NOWAIT;

Mas isso não ajudará quando você estiver tentando fazer com que o SSMS renderize linhas mais rapidamente quando toda a saída for proveniente de uma única instrução.

Mais diretamente, você pode controlá-lo limitando quantos resultados você está renderizando no SSMS. Muitas vezes vejo pessoas reclamando sobre quanto tempo leva para retornar um milhão de linhas à grade. O que diabos alguém vai fazer com um milhão de linhas em uma grade SSMS, eu não tenho ideia.

Existem alguns hacks como OPTION (FAST 100), que otimizarão a recuperação das primeiras 100 linhas (ou quaisquer 100 linhas se não houver nenhuma externa ORDER BY), mas isso pode custar uma recuperação muito mais lenta para o restante das linhas e um plano que é mais ineficiente em geral, então não é realmente uma opção IMHO.

Sua pergunta não é sobre o SQLServer em si, mas:

• Servidor SQL
• rede
• SSMS como aplicativo cliente

Existe uma maneira de controlar isso?

Resposta curta :

1. Tente sqlcmdem vez de ssmsou sqlcmd-mode dessms
2. Verifique suas configurações de conexão e sessão

Resposta longa :

É claro! Mas não um - prob

1. Execute sua consulta com sqlcmdou em sqlcmd-mode em ssms.
2. Se você deseja excluir a função da rede - execute sua consulta no servidor com conexão de memória compartilhada.
3. Se o desempenho da consulta for insatisfatório mesmo com a conexão de memória compartilhada - analise seus planos de execução. Se a consulta tiver um desempenho ruim na rede - ligue para o administrador de rede da rede para obter ajuda. Se sua consulta tiver um desempenho ruim apenas no SSMS - leia mais.
4. Agora temos certeza de que os problemas estão no lado do cliente (ssms neste caso). Observe as configurações de conexão e sessão no SSMS. Não acredite na interface ssms e verifique com o SQL Profiler: encontre sua conexão spide obterá uma lista completa de configurações de sessão. Compare com as configurações da sqlcmdsessão. Se nada clicar - copie todas as configurações de sessão do criador de perfil em seu script de consulta, execute em sqlcmd-mode e alternando gradualmente as configurações, você encontrará o culpado.

Boa sorte!

Para adicionar à resposta de sp_BlitzErik, pegue o exemplo usando a NOT IN ()com uma sub-seleção. Para determinar se um item está no resultado da consulta aninhada, é (geralmente) necessário recuperar o resultado inteiro.

Então, uma maneira fácil que encontrei para melhorar o desempenho de tais consultas é reescrevê-las como uma LEFT OUTER JOINcondição onde para o RIGHTlado é nulo (você pode inverter, é claro, mas quem usa RIGHT OUTER JOINS?). Isso permite que os resultados comecem a retornar imediatamente.