Partager via

SQL Server ne termine pas l’exécution d’un grand lot d’instructions SQL

Cet article vous aide à résoudre le problème qui se produit lorsque vous exécutez un grand lot d’instructions SQL qui retourne plusieurs jeux de résultats.

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 827575

Symptômes

Lorsque vous exécutez un grand lot d’instructions SQL qui retourne plusieurs jeux de résultats, Microsoft SQL Server peut arrêter le traitement du lot avant que toutes les instructions du lot ne soient exécutées. Les effets de ce comportement dépendent des opérations effectuées par les instructions batch. Par exemple, si le lot démarre une transaction au début et valide la transaction à la fin, la validation peut ne pas se produire. Ce comportement entraîne la tenue de verrous plus longs que prévu. Cela peut également entraîner la restauration de la transaction lorsque la connexion est fermée. Si le lot ne démarre pas une transaction, les symptômes du problème peuvent être que certaines instructions ne sont pas exécutées.

Voici les effets possibles de ce problème. Les effets sont variés et dépendent exactement de ce que contient votre lot.

  • Considérez qu’un lot d’instructions de requête de base de données est exécuté à partir d’une application. Si le lot d’instructions de requête de base de données est constitué d’un BEGIN TRANSACTION au début et COMMIT TRANSACTION à la fin, l’opération de validation peut ne pas se produire même si le contrôle est retourné à l’application. Il s’agit d’un problème, car les verrous susceptibles d’être détenus peuvent provoquer une transaction en attente et peuvent rester inaperçus.

    Dans ce scénario, étant donné que la transaction n’est jamais validée dans le lot, elle reste en attente et est restaurée lors de la déconnexion de SQL Server.

  • Si vous utilisez une interface de programme d’application (API) pour commencer et valider votre transaction, vous pouvez voir le comportement suivant :

    • Si vous utilisez l’API pour envoyer une notification au serveur pour démarrer une transaction, puis que vous exécutez le lot, SQL peut traiter uniquement une partie du lot, puis renvoyer le contrôle à l’application.
    • Après cette étape, si vous utilisez l’API pour valider la transaction, seule la partie du lot traité est validée. Aucune erreur ne se produit.

    Par exemple, avec ODBC, vous appelez SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) pour démarrer la transaction, puis vous utilisez SQLEndTran(SQL_COMMIT) pour valider la transaction.

Cause

Lors du traitement des résultats d’un lot, SQL Server remplit la mémoire tampon de sortie de connexion avec les résultats provenant du lot. Ces résultats doivent être traités par l’application cliente. Si vous exécutez un lot volumineux avec plusieurs jeux de résultats (plusieurs instructions produisant des résultats), SQL Server remplit cette mémoire tampon de sortie jusqu’à ce qu’elle atteigne une limite interne et ne puisse pas continuer tant que l’application cliente n’a pas commencé à consommer ces résultats. Lorsque le client commence à consommer les jeux de résultats, SQL Server commence à réexécuter le lot, car il existe désormais de la mémoire disponible dans la mémoire tampon de sortie. Ce comportement est normal.

Dans de nombreux cas, vous rencontrez ce problème lorsque vous vous connectez au serveur SQL Server à l’aide du protocole canaux nommés ou du protocole de mémoire partagée (LPC). Cela est dû à la taille de mémoire tampon interne que SQL Server a disponible pour les différents protocoles.

Solution de contournement

Pour contourner le problème, procédez comme suit :

  1. Vérifiez que l’application cliente consomme tous les jeux de résultats de sortie. Dès que tous les jeux de résultats de sortie sont consommés par le client, SQL Server termine l’exécution du lot.

    • Si vous utilisez Open Database Connectivity (ODBC) pour vous connecter à SQL Server, vous pouvez appeler la SQLMoreResults méthode jusqu’à ce que la méthode signale qu’il n’y a plus de jeux de résultats.
    • Si vous utilisez OLE DB pour vous connecter à SQL Server, vous pouvez appeler à plusieurs reprises la méthode IMultipleResults ::GetResults jusqu’à ce qu’elle retourne DB_S_NORESULT.

  2. Ajoutez l’instruction SET NOCOUNT ON au début de votre lot. Si le lot est exécuté à l’intérieur d’une procédure stockée, ajoutez l’instruction au début de la définition de procédure stockée. Cela empêche SQL Server de retourner un jeu de résultats supplémentaire qui affiche le nombre de lignes traitées, après le jeu de résultats principal. Par conséquent, il peut réduire les données à générer dans la mémoire tampon de sortie du serveur. Toutefois, cela ne garantit pas que le problème ne se produira pas. Cela augmente uniquement la probabilité que les données retournées par le serveur soient suffisamment petites pour s’adapter à un lot de jeux de résultats.

Nous vous recommandons de toujours utiliser tous les jeux de résultats provenant de SQL Server, quelle que soit la taille du lot que vous exécutez. Si vous ne traitez pas ces données et qu’il existe des jeux de résultats réussis à retourner avant une erreur dans le lot du jeu de résultats, le client risque de ne pas découvrir les erreurs du serveur. Les applications clientes doivent traiter les jeux de résultats dans leur intégralité pour garantir une exécution correcte.

Les étapes pour reproduire le problème.

  1. Connectez-vous à SQL Server à l’aide de SQL Server Management Studio (SSMS) et créez un exemple de base de données pubs.

  2. Créez une procédure stockée SQL avec pubs un lot relativement volumineux d’instructions de requête de base de données, comme suit :

    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. Dans l’Explorateur d’objets, sélectionnez Événements étendus de gestion>.

  4. Cliquez avec le bouton droit sur Sessions, puis sélectionnez Assistant Nouvelle session.

  5. Créez une session d’événements à l’aide du modèle de session TSQL_SPs .

  6. Démarrez la session et regardez les données actives. Pour plus d’informations, consultez Démarrage rapide : Événements étendus dans SQL Server.

  7. Connectez-vous à SQL Server avec ODBC ou OLE DB, exécutez bigBatch, puis analysez les données actives de la session d’événements.

Se connecter à ODBC

Pour vous connecter à SQL Server à l’aide d’ODBC, procédez comme suit :

  1. Créez, puis configurez un nom de source de données (DSN) avec pubs une base de données qui se connecte à SQL Server.
  2. Ouvrez l’exemple d’outil de test ODBC disponible avec l’installation du Kit de développement logiciel (SDK) MDAC (Data Access).
  3. Dans le menu Conn , sélectionnez Connexion complète.
  4. Dans la boîte de dialogue Connexion complète , sélectionnez le DSN que vous avez créé à l’étape 1.
  5. Vérifiez que la connexion à SQL Server réussit.
  6. Dans le menu Stmt , sélectionnez SQLExecDirect.
  7. Dans la zone StatementText , tapez {call bigBatch}, puis sélectionnez OK.

Dans les données dynamiques XEvent, vous remarquez que le traitement de la procédure stockée n’est pas terminé. Toutefois, l’outil de test ODBC indique que l’exécution a réussi. Pour extraire tous les jeux de résultats et provoquer la fin du lot sur le serveur, sélectionnez Obtenir tous les données dans le menu Résultats .

Se connecter avec OLE DB

Pour vous connecter à SQL Server à l’aide d’OLE DB, procédez comme suit :

  1. Ouvrez l’exemple d’outil OLE DB RowsetViewer disponible avec le Kit de développement logiciel (SDK) MDAC.
  2. Connectez-vous à la base de données SQL Server pubs à l’aide de l’option Connexion complète .
  3. Dans le menu Commande , pointez sur ICommand, puis sélectionnez Exécuter.
  4. Dans la zone de texte Cmd, tapez {appeler bigBatch}.
  5. Sélectionnez IID_IMultipleResults dans la liste REFIID, puis sélectionnez Propriétés.
  6. Dans la boîte de dialogue ICommandProperties ::SetProperties , sélectionnez DBPROP_IMultipleResults, remplacez la valeur par VARIANT_TRUE , puis sélectionnez OK.
  7. Cliquez sur OK.

Dans les données dynamiques XEvent, vous remarquez que le traitement de la procédure stockée n’est pas terminé. Toutefois, l’outil RowsetViewer indique que l’opération a réussi. Pour récupérer tous les jeux de résultats, cliquez avec le bouton droit sur l’objet MultipleResults dans le volet gauche, pointez sur IMultipleResults, puis sélectionnez GetResult. Répétez jusqu’à ce que tous les jeux de résultats aient été consommés.

References

  • Last updated on