SQL Server non completa l'esecuzione di un batch di istruzioni SQL di grandi dimensioni

Questo articolo illustra come risolvere il problema che si verifica quando si esegue un batch di grandi dimensioni di istruzioni SQL che restituisce più set di risultati.

Versione originale del prodotto: SQL Server
Numero KB originale: 827575

Sintomi

Quando si esegue un batch di grandi dimensioni di istruzioni SQL che restituisce più set di risultati, Microsoft SQL Server potrebbe interrompere l'elaborazione del batch prima dell'esecuzione di tutte le istruzioni nel batch. Gli effetti di questo comportamento dipendono dalle operazioni eseguite dalle istruzioni batch. Ad esempio, se il batch avvia una transazione all'inizio e esegue il commit della transazione alla fine, il commit potrebbe non verificarsi. Questo comportamento fa sì che i blocchi vengano mantenuti più a lungo del previsto. Ciò può anche causare il rollback della transazione quando la connessione viene chiusa. Se il batch non avvia una transazione, i sintomi del problema potrebbero essere che alcune istruzioni non vengono eseguite.

Di seguito sono riportati i possibili effetti di questo problema. Gli effetti sono vari e dipendono esattamente da ciò che contiene il batch.

  • Si consideri che un batch di istruzioni di query di database viene eseguito da un'applicazione. Se il batch di istruzioni di query del database è costituito da un BEGIN TRANSACTION oggetto all'inizio e COMMIT TRANSACTION alla fine, l'operazione di commit potrebbe non verificarsi anche se il controllo viene restituito all'applicazione. Si tratta di un problema perché i blocchi che potrebbero essere mantenuti possono causare una transazione in sospeso e potrebbero rimanere non rilevati.

    In questo scenario, poiché la transazione non viene mai sottoposta a commit nel batch, rimane in sospeso e viene eseguito il rollback alla disconnessione da SQL Server.

  • Se si usa un'API (Application Program Interface) per avviare ed eseguire il commit della transazione, è possibile che venga visualizzato il comportamento seguente:

    • Se si usa l'API per inviare una notifica al server per avviare una transazione e quindi si esegue il batch, SQL può elaborare solo una parte del batch e quindi restituire il controllo all'applicazione.
    • Dopo questo passaggio, se si usa l'API per eseguire il commit della transazione, viene eseguito il commit solo della parte del batch elaborato. Non si verifica alcun errore.

    Ad esempio, con ODBC si chiama SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) per avviare la transazione e quindi si usa SQLEndTran(SQL_COMMIT) per eseguire il commit della transazione.

Causa

Quando si elaborano i risultati di un batch, SQL Server riempie il buffer di output della connessione con i risultati provenienti dal batch. Questi risultati devono essere elaborati dall'applicazione client. Se si esegue un batch di grandi dimensioni con più set di risultati (più istruzioni che producono risultati), SQL Server riempie il buffer di output fino a quando non raggiunge un limite interno e non può continuare fino a quando l'applicazione client non inizia a utilizzare tali risultati. Quando il client inizia a utilizzare i set di risultati, SQL Server inizia a eseguire nuovamente il batch perché è ora disponibile memoria nel buffer di output. Questo comportamento è impostato a livello di progettazione.

In molti casi, questo problema si verifica quando ci si connette a SQL Server usando il protocollo Named pipe o il protocollo LPC (Shared Memory). Ciò è dovuto alla dimensione interna del buffer disponibile per i diversi protocolli di SQL Server.

Soluzione alternativa

Per risolvere il problema, seguire questa procedura:

  1. Assicurarsi che l'applicazione client utilizza tutti i set di risultati di output. Non appena tutti i set di risultati di output vengono utilizzati dal client, SQL Server completa l'esecuzione del batch.

    • Se si usa Open Database Connectivity (ODBC) per connettersi a SQL Server, è possibile chiamare il SQLMoreResults metodo fino a quando il metodo non segnala che non sono presenti altri set di risultati.
    • Se si usa OLE DB per connettersi a SQL Server, è possibile chiamare ripetutamente il metodo IMultipleResults::GetResult finché non restituisce DB_S_NORESULT.

  2. Aggiungere l'istruzione SET NOCOUNT ON all'inizio del batch. Se il batch viene eseguito all'interno di una stored procedure, aggiungere l'istruzione all'inizio della definizione della stored procedure. Ciò impedisce a SQL Server di restituire un set di risultati aggiuntivo che mostra il numero di righe elaborate, dopo il set di risultati principale. Pertanto, può ridurre i dati da restituire nel buffer di output del server. Tuttavia, ciò non garantisce che il problema non si verifichi. Aumenta solo la probabilità che i dati restituiti dal server siano sufficientemente piccoli da adattarsi a un batch di set di risultati.

È consigliabile che l'applicazione client consumi sempre tutti i set di risultati provenienti da SQL Server indipendentemente dalle dimensioni del batch in esecuzione. Se non si elaborano questi dati e vengono restituiti set di risultati con esito positivo prima di un errore nel batch del set di risultati, il client potrebbe non individuare gli errori del server. Le applicazioni client devono elaborare i set di risultati nella loro interezza per garantire l'esecuzione corretta.

Procedura per riprodurre il problema

  1. Connettersi a SQL Server usando SQL Server Management Studio (SSMS) e creare un database pub di esempio.

  2. Creare una stored procedure SQL in pubs con un batch di query di database relativamente grande, come illustrato di seguito:

    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. In Esplora oggetti selezionare Gestione>eventi estesi.

  4. Fare clic con il pulsante destro del mouse su Sessioni e quindi scegliere Creazione guidata nuova sessione.

  5. Creare una nuova sessione eventi usando il modello di sessione TSQL_SPs .

  6. Avviare la sessione e osservare i dati in tempo reale. Per altre informazioni, vedere Guida introduttiva: Eventi estesi in SQL Server.

  7. Connettersi a SQL Server con ODBC o OLE DB, eseguire bigBatche quindi analizzare i dati live della sessione eventi.

Connettersi con ODBC

Per connettersi a SQL Server tramite ODBC, seguire questa procedura:

  1. Creare e quindi configurare un nome origine dati (DSN) con pubs il database che si connette a SQL Server.
  2. Aprire l'esempio dello strumento di test ODBC disponibile con l'installazione di Data Access (MDAC) SDK.
  3. Nel menu Conn selezionare Connessione completa.
  4. Nella finestra di dialogo Connessione completa selezionare il DSN creato nel passaggio 1.
  5. Assicurarsi che la connessione a SQL Server abbia esito positivo.
  6. Nel menu Stmt selezionare SQLExecDirect.
  7. Nella casella StatementText digitare {call bigBatch}, quindi selezionare OK.

Nei dati live XEvent si noterà che l'elaborazione della stored procedure non è stata completata. Tuttavia, lo strumento test ODBC indica che l'esecuzione è stata completata correttamente. Per recuperare tutti i set di risultati e per fare in modo che il batch termini nel server, selezionare Recupera tutti i dati dal menu Risultati .

Connettersi con OLE DB

Per connettersi a SQL Server tramite OLE DB, seguire questa procedura:

  1. Aprire l'esempio dello strumento OLE DB RowsetViewer disponibile con MDAC SDK.
  2. Connettersi al database di SQL Server pubs usando l'opzione Connessione completa.
  3. Scegliere ICommand dal menu Comando e quindi selezionare Esegui.
  4. Nella casella Di testo cmd digitare {call bigBatch}.
  5. Selezionare IID_IMultipleResults nell'elenco REFIID e quindi selezionare Proprietà.
  6. Nella finestra di dialogo ICommandProperties::SetProperties selezionare DBPROP_IMultipleResults, modificare il valore in VARIANT_TRUE e quindi selezionare OK.
  7. Seleziona OK.

Nei dati live XEvent si noterà che l'elaborazione della stored procedure non è stata completata. Tuttavia, lo strumento RowsetViewer indica che l'operazione ha avuto esito positivo. Per recuperare tutti i set di risultati, fare clic con il pulsante destro del mouse sull'oggetto MultipleResults nel riquadro sinistro, scegliere IMultipleResults e quindi selezionare GetResult. Ripetere fino a quando non vengono utilizzati tutti i set di risultati.

Riferimenti

  • Last updated on