Compartilhar via

O SQL Server não conclui a execução de um grande lote de instruções SQL

Este artigo ajuda a resolver o problema que ocorre quando você executa um grande lote de instruções SQL que retorna vários conjuntos de resultados.

Versão original do produto: SQL Server
Número original do KB: 827575

Sintomas

Quando você executa um grande lote de instruções SQL que retorna vários conjuntos de resultados, o Microsoft SQL Server pode interromper o processamento do lote antes que todas as instruções no lote sejam executadas. Os efeitos desse comportamento dependem de quais operações as instruções em lote executam. Por exemplo, se o lote iniciar uma transação no início e confirmar a transação no final, a confirmação poderá não ocorrer. Esse comportamento faz com que os bloqueios sejam mantidos por mais tempo do que o esperado. Isso também pode fazer com que a transação seja revertida quando a conexão for fechada. Se o lote não iniciar uma transação, os sintomas do problema podem ser que algumas instruções não sejam executadas.

A seguir estão os possíveis efeitos desse problema. Os efeitos são variados e dependem exatamente do que seu lote contém.

  • Considere que um lote de instruções de consulta de banco de dados é executado a partir de um aplicativo. Se o lote de instruções de consulta de banco de dados for composto por um BEGIN TRANSACTION no início e COMMIT TRANSACTION no final, a operação de confirmação poderá não ocorrer mesmo que o controle seja retornado ao aplicativo. Isso é um problema porque os bloqueios que possivelmente estão sendo mantidos podem causar uma transação pendente e podem passar despercebidos.

    Nesse cenário, como a transação nunca é confirmada no lote, ela permanece pendente e é revertida na desconexão do SQL Server.

  • Se você usar uma API (interface de programação de aplicativo) para iniciar e confirmar sua transação, poderá ver o seguinte comportamento:

    • Se você usar a API para enviar uma notificação ao servidor para iniciar uma transação e, em seguida, executar o lote, o SQL poderá processar apenas uma parte do lote e, em seguida, retornar o controle para o aplicativo.
    • Após essa etapa, se você usar a API para confirmar a transação, somente a parte do lote que foi processada será confirmada. Nenhum erro ocorre.

    Por exemplo, com o ODBC, você chama SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) para iniciar a transação e, em seguida, usa SQLEndTran(SQL_COMMIT) para confirmar a transação.

Causa

Ao processar os resultados de um lote, o SQL Server preenche o buffer de saída da conexão com os resultados provenientes do lote. Esses resultados devem ser processados pelo aplicativo cliente. Se você estiver executando um lote grande com vários conjuntos de resultados (várias instruções produzindo resultados), o SQL Server preencherá esse buffer de saída até atingir um limite interno e não poderá continuar até que o aplicativo cliente comece a consumir esses resultados. Quando o cliente começa a consumir os conjuntos de resultados, o SQL Server começa a executar o lote novamente porque agora há memória disponível no buffer de saída. Este comportamento ocorre por design.

Em muitos casos, você encontra esse problema ao se conectar ao SQL Server usando o protocolo de pipes nomeados ou o protocolo LPC (memória compartilhada). Isso ocorre devido ao tamanho do buffer interno que o SQL Server tem disponível para os diferentes protocolos.

Solução alternativa

Para contornar o problema, siga estas etapas:

  1. Certifique-se de que o aplicativo cliente consuma todos os conjuntos de resultados de saída. Assim que todos os conjuntos de resultados de saída são consumidos pelo cliente, o SQL Server conclui a execução do lote.

    • Se você estiver usando ODBC (Open Database Connectivity) para se conectar ao SQL Server, poderá chamar o SQLMoreResults método até que o método relate que não há mais conjuntos de resultados.
    • Se você estiver usando o OLE DB para se conectar ao SQL Server, poderá chamar repetidamente o método IMultipleResults::GetResult até que ele retorne DB_S_NORESULT.

  2. Adicione a instrução SET NOCOUNT ON ao início do lote. Se o lote for executado dentro de um procedimento armazenado, adicione a instrução ao início da definição do procedimento armazenado. Isso impede que o SQL Server retorne um conjunto de resultados adicional que mostre o número de linhas processadas, após o conjunto de resultados principal. Portanto, ele pode reduzir os dados a serem enviados para o buffer de saída do servidor. No entanto, isso não garante que o problema não ocorrerá. Isso apenas aumenta a chance de que os dados retornados do servidor sejam pequenos o suficiente para caber em um lote de conjuntos de resultados.

Recomendamos que seu aplicativo cliente sempre consuma todos os conjuntos de resultados provenientes do SQL Server, independentemente do tamanho do lote que você está executando. Se você não processar esses dados e houver conjuntos de resultados bem-sucedidos a serem retornados antes de um erro no lote do conjunto de resultados, o cliente poderá não descobrir os erros do servidor. Os aplicativos cliente devem processar os conjuntos de resultados em sua totalidade para garantir a execução correta.

Etapas para reproduzir o problema

  1. Conecte-se ao SQL Server usando o SSMS (SQL Server Management Studio) e crie um banco de dados de pubs de exemplo.

  2. Crie um procedimento armazenado SQL com pubs um lote relativamente grande de instruções de consulta de banco de dados, como o seguinte:

    CREATE PROC bigBatch AS
BEGIN TRANSACTION
UPDATE authors SET au_fname = 'newname1' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname2' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname3' WHERE au_id='172-32-1176'
-- Add more UPDATE statements here ... 
UPDATE authors SET au_fname = 'newname1000' WHERE au_id='172-32-1176'
COMMIT TRANSACTION

  3. No Pesquisador de Objetos, selecione Eventos Estendidos de Gerenciamento>.

  4. Clique com o botão direito do mouse em Sessões e selecione Assistente de Nova Sessão.

  5. Crie uma nova sessão de evento usando o modelo de sessão TSQL_SPs .

  6. Inicie a sessão e assista aos dados ao vivo. Para obter mais informações, consulte Guia de início rápido: eventos estendidos no SQL Server.

  7. Conecte-se ao SQL Server com ODBC ou OLE DB, execute bigBatche analise os dados dinâmicos da sessão de evento.

Conecte-se ODBC

Para se conectar ao SQL Server usando ODBC, siga estas etapas:

  1. Crie e configure um DSN (Nome da Fonte de Dados) com pubs o banco de dados que se conecta ao SQL Server.
  2. Abra o exemplo de ferramenta de teste ODBC que está disponível com a instalação do SDK de acesso a dados (MDAC).
  3. No menu Conn , selecione Conexão Completa.
  4. Na caixa de diálogo Conexão Completa, selecione o DSN que você criou na etapa 1.
  5. Verifique se a conexão com o SQL Server foi bem-sucedida.
  6. No menu Stmt , selecione SQLExecDirect.
  7. Na caixa StatementText, digite {call bigBatch} e selecione OK.

Nos dados dinâmicos do XEvent, você observa que o processamento do procedimento armazenado não está concluído. No entanto, a ferramenta de teste ODBC indica que a execução foi bem-sucedida. Para buscar todos os conjuntos de resultados e fazer com que o lote seja concluído no servidor, selecione Obter Todos os Dados no menu Resultados .

Conectar-se com OLE DB

Para se conectar ao SQL Server usando o OLE DB, siga estas etapas:

  1. Abra o exemplo de ferramenta OLE DB RowsetViewer que está disponível com o SDK do MDAC.
  2. Conecte-se ao banco de dados do SQL Server pubs usando a opção Conexão Completa.
  3. No menu Comando , aponte para ICommand e selecione Executar.
  4. Na caixa Texto do Cmd, digite {call bigBatch}.
  5. Selecione IID_IMultipleResults na lista REFIID e, em seguida, selecione Propriedades.
  6. Na caixa de diálogo ICommandProperties::SetProperties , selecione DBPROP_IMultipleResults, altere o valor para VARIANT_TRUE e selecione OK.
  7. Selecione OK.

Nos dados dinâmicos do XEvent, você observa que o processamento do procedimento armazenado não está concluído. No entanto, a ferramenta RowsetViewer mostra que a operação foi bem-sucedida. Para recuperar todos os conjuntos de resultados, clique com o botão direito do mouse no objeto MultipleResults no painel esquerdo, aponte para IMultipleResults e selecione GetResult. Repita até que todos os conjuntos de resultados tenham sido consumidos.

Referências