MSSQLSERVER_833

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Détails

Attribut Valeur
Nom du produit SQL Server
ID de l’événement 833
Source de l’événement MSSQLSERVER
Composant SQLEngine
Nom symbolique BUF_LONG_IO
Texte du message SQL Server a rencontré %d occurrence(s) de requêtes d’E/S mettant plus de %d secondes à s’effectuer dans le fichier [%ls] de la base de données [%ls] (%d). Le descripteur de fichier du système d'exploitation est 0x%p. Le décalage de la dernière E/S longue est : %#016I64x.

Explication

Ce message indique que SQL Server a émis une demande de lecture ou d'écriture à partir du disque et que la demande a mis plus de 15 secondes à retourner un résultat. Le SQL Server signale cette erreur et indique un problème avec le sous-système d’E/S. Un système de gestion de base de données (SGBD), tel que SQL Server, s’appuie sur la rapidité des opérations d’entrée et de sortie de fichier (E/S). L’un des éléments suivants peut provoquer des opérations d’E/S bloquées ou bloquées et nuire à SQL Server réactivité et aux performances :

  • Matériel défectueux
  • Matériel mal configuré
  • Paramètres du microprogramme
  • Pilotes de filtre
  • Compression
  • Bogues
  • Autres conditions dans le chemin d’E/S

Ces problèmes d’E/S peuvent entraîner le comportement suivant :

  • Blocage.
  • Contention du verrou et délais d’expiration.
  • Temps de réponse lent.
  • Étirement des limites des ressources.
  • Vous pouvez également remarquer d’autres symptômes associés à ce message, tels que :
    • Temps d’attente élevés pour les attentes PAGEIOLATCH.
    • Avertissements ou erreurs dans le journal des événements système.
    • Indications des problèmes de latence de disque dans les compteurs de surveillance système.

Lorsqu’une opération d’E/S est en attente depuis 15 secondes ou plus, SQL Server effectue les étapes suivantes :

  1. Détecte qu’une opération a été en attente.

  2. Écrit un message d’information dans le journal des erreurs SQL Server comme indiqué dans la section Détails.

    L’explication des différentes sections de ce message d’information est donnée dans le tableau suivant :

Texte du message Description
<Nombre> occurrence(s) Nombre de demandes d’E/S qui n’ont pas terminé l’opération de lecture ou d’écriture en moins de 15 secondes.
informations relatives aux fichiers Nom de fichier complet, nom de la base de données et numéro d’identification de base de données (DBID).
Handle Handle du système d’exploitation du fichier. Vous pouvez utiliser le handle du système d’exploitation avec des débogueurs ou d’autres utilitaires pour vous aider à suivre les demandes de paquets de demandes d’E/S (IRP).
Offset Décalage de la dernière opération d’E/S bloquée ou de la dernière opération d’E/S bloquée. Vous pouvez utiliser le décalage avec des débogueurs ou d’autres utilitaires pour vous aider à suivre les demandes IRP.

Remarque :
Lorsque le message d’information est écrit dans le journal des erreurs SQL Server, l’opération d’E/S peut ne plus être bloquée ou bloquée.

Causes possibles

Le message d’information indique que la charge actuelle peut rencontrer l’une des conditions suivantes :

  • La charge de travail dépasse les fonctionnalités de chemin d’E/S en raison d’une configuration incorrecte du sous-système d’E/S (SAN, NAS et attachement direct) ou parce que la capacité matérielle a été atteinte.
  • La charge de travail dépasse les fonctionnalités système actuelles, telles que les E/S, les processeurs et les adaptateurs HBA.
  • Le chemin d’E/S présente un logiciel défectueux. Il peut s’agir d’un problème de microprogramme ou de pilote.
  • Le chemin d’E/S présente des composants matériels défectueux.
  • Problème de performances au niveau du système d’exploitation.
  • Intervention du pilote de filtre dans le processus d’E/S ou le chemin de stockage des fichiers de base de données. Par exemple, programme antivirus.

SQL Server enregistre l’heure à laquelle il a lancé une demande d’E/S et l’heure à laquelle les E/S ont été effectuées. Si la différence est supérieure ou égale à 15 secondes, cet état est détecté. Cela signifie également que SQL Server n’est pas à l’origine de la condition d’E/S retardée que ce message décrit et signale. Cette condition est appelée E/S bloquées. La plupart des demandes de disque se produisent à la vitesse standard du disque, Cette vitesse de disque typique est fréquemment appelée temps de recherche de disque. Le temps de recherche de la plupart des disques standard est de 10 millisecondes maximum. Par conséquent, 15 secondes est un temps long pour le chemin d’E/S système pour revenir à SQL Server. Pour plus d’informations, consultez la section Informations supplémentaires .

Action requise

Résolvez cette erreur en effectuant les étapes suivantes :

  1. Examinez le journal des événements système pour rechercher les messages d’erreur liés au matériel.
  2. Examinez les journaux spécifiques au matériel s’ils sont disponibles. Utilisez les méthodes et techniques nécessaires pour déterminer la cause du retard dans le système d’exploitation, les pilotes ou le matériel d’E/S.
  3. Mettez à jour tous les pilotes et microprogrammes de périphérique ou effectuez d’autres diagnostics associés à votre sous-système d’E/S.
  4. L’accès au disque peut être ralenti par des pilotes de filtre, par exemple, un programme antivirus. Pour augmenter la vitesse d’accès, excluez les fichiers de données SQL Server spécifiés dans le message d’erreur des analyses antivirus actives. Pour plus d’informations, consultez Comment choisir un logiciel antivirus à exécuter sur des ordinateurs qui exécutent SQL Server (microsoft.com).
    • Utilisez l’utilitaire de ligne de commandefltmc.exe pour interroger tous les pilotes de filtre installés sur le système et comprendre les fonctions qu’il effectue sur le chemin de stockage des fichiers de base de données.
  5. Utilisez le Analyseur de performances pour examiner les compteurs suivants :
    • Moyenne disque s/transfert
    • Longueur moyenne de la file d’attente du disque
    • Longueur actuelle de la file d’attente du disque
  6. Vous pouvez également utiliser des fonctionnalités telles que la journalisation Storport ETW pour mesurer la latence des requêtes adressées à une unité de disque. Un autre kit de dépannage des E/S de disque du même type est disponible sous la forme d’un profil intégré de l’Enregistreur de performance Windows.
  7. Surveillez sys.dm_io_virtual_file_stats et choisissez le niveau de stockage et les E/S par seconde appropriés pour votre débit de stockage.

Pour obtenir une procédure guidée pour le diagnostic et la résolution des problèmes de performances SQL Server qui se produisent en raison de problèmes d’E/S, consultez Résoudre les problèmes de SQL Server de performances lentes causées par les problèmes d’E/S.

Plus d’informations

E/S bloquées et E/S bloquées

E/S bloquées

Les E/S bloquées sont définies comme une demande d’E/S qui ne se termine pas. Souvent, les E/S bloquées indiquent un IRP bloqué. Pour résoudre une condition d’E/S bloquée, vous devez généralement redémarrer l’ordinateur ou effectuer une action similaire. Une condition d’E/S bloquée indique généralement l’un des problèmes suivants :

  • Matériel défectueux.
  • Bogue dans un composant de chemin d’E/S.

E/S bloquées

Les E/S bloquées sont définies comme une demande d’E/S qui ne se termine pas ou qui prend trop de temps. Le comportement d’E/S bloqué se produit généralement en raison de l’une des raisons suivantes :

  • Configuration matérielle.
  • Paramètres du microprogramme.
  • Problème de pilote de filtre qui nécessite l’aide du matériel ou du fournisseur de logiciels pour effectuer le suivi et la résolution.

SQL Server les E/S bloquées et l’enregistrement et la création de rapports d’E/S bloqués

SQL Server support gère chaque année de nombreux cas impliquant des problèmes d’E/S bloqués ou bloqués. Ces problèmes d’E/S apparaissent de différentes manières. Les problèmes d’E/S sont parmi les plus difficiles à diagnostiquer et à déboguer, et ils nécessitent beaucoup de temps et de ressources pour le débogage de Microsoft et du client. La création de rapports et l’enregistrement des demandes d’E/S sont conçus sur une base par fichier. La détection et le signalement des demandes d’E/S bloquées et bloquées sont deux actions distinctes.

Recording

Il existe deux moments où une action d’enregistrement se produit dans SQL Server. La première est lorsque l’opération d’E/S se termine. Le deuxième moment est le moment où l’écrivain paresseux s’exécute. Lorsque l’enregistreur paresseux s’exécute, il vérifie toutes les données en attente et les demandes d’E/S du fichier journal en attente. Si la demande d’E/S dépasse le seuil de 15 secondes, une opération d’enregistrement se produit.

Création de rapports

La création de rapports se produit à intervalles de cinq minutes ou plus. La création de rapports se produit lorsque la demande d’E/S suivante est effectuée sur le fichier. Si une action d’enregistrement s’est produite et que cinq minutes ou plus se sont écoulées depuis le dernier rapport, le message d’information mentionné dans la section Détails est écrit dans le journal des erreurs SQL Server.

Le seuil de 15 secondes n’est pas réglable. Toutefois, vous pouvez désactiver la détection d’E/S bloquées ou bloquées à l’aide de l’indicateur de trace 830, bien que nous vous déconseillons de le faire.

Vous pouvez désactiver la détection des E/S bloquées et bloquées à l’aide de l’indicateur de trace 833. Pour activer cet indicateur chaque fois que SQL Server est démarré, utilisez le paramètre de démarrage -T830. Pour désactiver la détection d’une instance de SQL Server en cours d’exécution, utilisez l’instruction suivante :

    dbcc traceon(830, -1)

Ce paramètre n’est effectif que pendant la durée du processus SQL Server.

Notes

Une demande d’E/S qui est bloquée ou bloquée n’est signalée qu’une seule fois. Par exemple, si le message signale que 10 demandes d’E/S sont bloquées, ces 10 rapports ne se reproduisent plus. Si le message suivant signale que 15 demandes d’E/S sont bloquées, cela signifie que 15 nouvelles demandes d’E/S sont bloquées.

Suivi du paquet de demande d’E/S (IRP)

SQL Server utilise les appels d’API Microsoft Windows standard pour lire et écrire des données. Par exemple, SQL Server utilise les fonctions suivantes :

  • WriteFile
  • ReadFile
  • WriteFileScatter
  • ReadFileGather

La demande de lecture ou d’écriture est gérée par Windows en tant que paquet de demande d’E/S (IRP). Pour déterminer l’état de l’IRP, utilisez les deux fonctionnalités suivantes :

Nous vous recommandons de rechercher les mises à jour disponibles pour les éléments suivants :

  • The BIOS
  • Microprogramme
  • Tout autre composant de chemin d’E/S

Contactez vos fournisseurs de matériel avant d’effectuer des actions de débogage supplémentaires. La session de débogage implique probablement un pilote, un microprogramme ou un composant de pilote de filtre tiers.

Performances système et actions de plan de requête

Dans l’ensemble, les performances du système peuvent jouer un rôle clé dans le traitement des E/S. Vous devez tenir compte de l’intégrité générale du système lors de l’examen des rapports d’opérations d’E/S bloquées ou bloquées. Des charges excessives peuvent ralentir l’ensemble du système, y compris le traitement des E/S. Le comportement du système lorsque le problème se produit peut être un facteur clé pour déterminer la cause racine du problème. Par exemple, si l’utilisation du processeur augmente ou reste élevée pendant que le problème se produit, cela peut indiquer qu’un processus système utilise tellement de processeur que d’autres processus sont affectés négativement.

Compteurs de performance

Pour surveiller les performances d’E/S, examinez les compteurs de performances suivants pour obtenir des informations spécifiques sur le chemin d’E/S :

  • Moyenne disque s/transfert
  • Longueur moyenne de la file d’attente du disque
  • Longueur actuelle de la file d’attente du disque

Par exemple, la durée moyenne du disque s/transfert sur un ordinateur qui exécute SQL Server est généralement inférieure à 15 millisecondes. Si la valeur moyenne disque sec/transfert grimpe, cela indique que le sous-système d’E/S ne répond pas de manière optimale à la demande d’E/S.

Soyez prudent lors de l’utilisation des compteurs de performances, car SQL Server tire pleinement parti des fonctionnalités d’E/S asynchrones qui poussent fortement les longueurs de file d’attente de disque. Par conséquent, les longueurs de file d’attente de disque plus longues n’indiquent pas à elles seules un problème.

Dans le Moniteur système Windows, vous pouvez passer en revue le compteur « Disque physique : Octets de disque/s » pour chaque disque affecté et comparer le taux d’activité par rapport aux compteurs « Processus : Octets de données d’E/S » et « Processus : Autres octets/s » pour chaque processus. Vous effectuez cette opération pour déterminer si un ensemble spécifique de processus génère des demandes d’E/S excessives. Divers autres compteurs liés aux E/S dans l’objet Process révèlent des informations plus précises. Si vous déterminez qu’une instance SQL Server est responsable d’une charge d’E/S excessive sur le serveur, consultez la section suivante sur Index et parallélisme. Pour obtenir une discussion détaillée sur la détection et la résolution des goulots d’étranglement des E/S, consultez Résoudre les problèmes de ralentissement SQL Server de performances causés par les problèmes d’E/S.

Index et parallélisme

Souvent, des rafales d’E/S se produisent parce qu’un index est manquant. Ce comportement peut pousser gravement le chemin d’E/S. Une passe qui utilise l’Assistant Retournement d’index (ITW) peut aider à résoudre la pression des E/S sur le système. Si une requête tire parti d’un index au lieu d’une analyse de table, ou peut-être si elle utilise un tri ou un hachage, le système peut bénéficier des avantages suivants :

  • Une réduction est effectuée dans les E/S physiques requises pour effectuer l’action qui crée directement des avantages en matière de performances pour la requête.
  • Moins de pages dans le cache de données doivent être retournées. Par conséquent, les pages qui se trouve dans le cache de données restent pertinentes pour les requêtes actives.
  • Les tris et les hachages sont utilisés parce qu’un index est peut-être manquant ou parce que les statistiques sont obsolètes. Vous pouvez réduire l’utilisation et la contention tempdb en ajoutant un ou plusieurs index.
  • Une réduction est effectuée dans les ressources, les opérations parallèles ou les deux. Étant donné que SQL Server ne garantit pas l’exécution parallèle des requêtes et que la charge sur le système est prise en compte, il est préférable d’optimiser toutes les requêtes pour l’exécution en série. Pour optimiser une requête, ouvrez l’Analyseur de requêtes et définissez la valeur sp_configure de l’option de degré maximal de parallélisme sur 1. Si toutes les requêtes sont paramétrées pour s’exécuter rapidement en tant qu’opération série, l’exécution parallèle est souvent simplement un meilleur résultat. Toutefois, l’exécution parallèle est souvent sélectionnée, car la quantité de données est importante. Pour un index manquant, un tri important peut devoir se produire. Plusieurs workers qui effectuent l’opération de tri créent une réponse plus rapide. Toutefois, cette action peut augmenter considérablement la pression sur le système. Les demandes de lecture volumineuses de nombreux workers peuvent provoquer une rafale d’E/S avec une utilisation accrue du processeur. Une requête peut souvent être paramétrée pour s’exécuter plus rapidement et utiliser moins de ressources si un index est ajouté ou si une autre action de paramétrage se produit.

Exemples pratiques du support SQL Server

Les exemples suivants ont été gérés par SQL Server support et la prise en charge de l’escalade Windows. Ces exemples sont destinés à fournir un cadre de référence et à définir vos attentes concernant les situations d’E/S bloquées et bloquées. Ils fournissent également une infrastructure permettant de comprendre comment un système peut être affecté ou y répondre. Aucun matériel ou ensemble spécifique de pilotes ne présente un risque spécifique ou un risque accru par rapport à un autre. Tous les systèmes sont identiques à cet égard.

Exemple 1 : Écriture de journal bloquée pendant 45 secondes

Une tentative d’écriture d’un fichier journal SQL Server est régulièrement bloquée pendant environ 45 secondes. L’écriture du journal ne se termine pas en temps opportun. Ce comportement crée une condition de blocage qui provoque des délais d’attente du client de 30 secondes.

L’application a envoyé un commit à SQL Server, et la validation est bloquée en tant qu’écriture de journal en attente. Ce comportement amène la requête à conserver des verrous et à bloquer les demandes entrantes provenant d’autres clients. Ensuite, les autres clients commencent à expirer. Cela aggrave le problème, car l’application ne restaure pas les transactions ouvertes lorsqu’un délai d’expiration de requête se produit. Cela crée des centaines de transactions ouvertes qui détiennent des verrous. Par conséquent, une situation de blocage grave se produit.

Pour plus d’informations sur la gestion et le blocage des transactions, consultez l’article suivant de la Base de connaissances Microsoft : 224453 Comprendre et résoudre les problèmes de blocage SQL Server

L’application dessert un site web à l’aide du regroupement de connexions. À mesure que de plus en plus de connexions sont bloquées, le site web crée davantage de connexions. Ces connexions sont bloquées et le cycle continue.

L’écriture du journal prend environ 45 secondes. Toutefois, à ce stade, des centaines de connexions sont sauvegardées. Les problèmes de blocage entraînent plusieurs minutes de temps de récupération pour SQL Server et l’application. Combiné à des problèmes d’application, la condition d’E/S bloquées a un effet très négatif sur le système.

Résolution :

Le problème est suivi d’une demande d’E/S bloquée dans un pilote HBA (Host Bus Adapter). L’ordinateur dispose de plusieurs cartes HBA avec prise en charge du basculement. Lorsqu’un HBA est en retard ou ne communique pas avec le réseau de zone de stockage (SAN), la valeur de délai d’attente « réessayer avant le basculement » est configurée sur 45 secondes. Lorsque le délai d’attente dépasse, la demande d’E/S est acheminée vers le deuxième HBA. Le deuxième HBA gère la demande et se termine rapidement. Pour éviter de telles conditions de blocage, le fabricant du matériel recommande un paramètre de « nouvelle tentative avant basculement » de cinq secondes.

Exemple 2 : Intervention du pilote de filtre

De nombreux logiciels antivirus et produits de sauvegarde utilisent des pilotes de filtre d’E/S. Ces pilotes de filtre d’E/S font partie de la pile des demandes d’E/S et ont accès à la requête IRP. Les services de support technique Microsoft ont rencontré divers problèmes de bogues qui créent des conditions d’E/S bloquées ou des conditions d’E/S bloquées dans une implémentation de pilote de filtre.

L’une de ces conditions est un pilote de filtre pour le traitement de la sauvegarde qui autorise la sauvegarde des fichiers ouverts lorsque la sauvegarde se produit. L’administrateur système a inclus le répertoire de fichiers de données SQL Server dans les sélections de sauvegarde de fichiers. Lorsque la sauvegarde se produit, la sauvegarde tente de rassembler l’image correcte du fichier au moment du démarrage de la sauvegarde. Cela retarde les demandes d’E/S. Les demandes d’E/S ne sont autorisées à effectuer qu’une par une, car le logiciel les gère.

Au démarrage de la sauvegarde, SQL Server performances diminuent considérablement, car les E/S de SQL Server sont forcées d’effectuer une par une. La logique un par un est telle que l’opération d’E/S ne peut pas être effectuée de manière asynchrone, ce qui aggrave le problème. Par conséquent, lorsque SQL Server prévoit de publier une demande d’E/S et de continuer, le worker est bloqué dans l’appel de lecture ou d’écriture jusqu’à ce que la demande d’E/S soit terminée. Les actions du pilote de filtre désactivent efficacement les tâches de traitement telles que SQL Server lecture anticipée. En outre, un autre bogue dans le pilote de filtre laisse les actions une à la fois dans le processus, même lorsque la sauvegarde est terminée. La seule façon de restaurer SQL Server performances consiste à redémarrer SQL Server afin que le handle de fichier soit libéré et réacquis sans l’interaction du pilote de filtre.

Résolution :

Pour résoudre ce problème, les fichiers de données SQL Server sont supprimés du processus de sauvegarde de fichiers. Le fabricant du logiciel a corrigé le problème qui a laissé le fichier en mode « un par un ».

Exemple 3 : erreurs masquées

De nombreux systèmes haut de gamme ont des chemins d’E/S multicanaux pour gérer l’équilibrage de charge ou des activités similaires. Le support technique Microsoft a détecté des problèmes avec le logiciel d’équilibrage de charge où une demande d’E/S échoue, mais le logiciel ne gère pas correctement la condition d’erreur. Le logiciel peut tenter des tentatives infinies. L’opération d’E/S est bloquée et SQL Server ne peut pas terminer l’action spécifiée. À l’instar de la condition d’écriture de journal décrite précédemment, de nombreux comportements système médiocres peuvent se produire après qu’une telle condition se soit dissoyée du système.

Résolution :

Pour résoudre ce problème, redémarrez le SQL Server. Toutefois, vous devez parfois redémarrer le système d’exploitation pour restaurer le traitement. Nous vous recommandons également d’obtenir une mise à jour logicielle auprès du fournisseur d’E/S.

Exemple 4 : Stockage distant, mise en miroir et lecteurs raid

De nombreux systèmes utilisent la mise en miroir ou adoptent des étapes similaires pour éviter la perte de données. Certains systèmes qui utilisent la mise en miroir sont basés sur des logiciels et d’autres sur le matériel. La situation généralement découverte par Support Microsoft pour ces systèmes est une latence accrue.

Une augmentation du temps d’E/S global se produit lorsque les E/S doivent se terminer avant d’être considérées comme terminées. Pour les installations de miroir à distance, les nouvelles tentatives réseau peuvent être impliquées. Lorsque des défaillances de lecteur se produisent et que le système raid est en cours de reconstruction, le modèle d’E/S peut également être interrompu.

Résolution :

Des paramètres de configuration stricts sont nécessaires pour réduire la latence sur les miroirs ou pour effectuer des opérations de reconstruction raid.

Exemple 5 : Compression

Microsoft ne prend pas en charge les fichiers de données SQL Server et les fichiers journaux sur les lecteurs compressés. La compression NTFS n’est pas sécurisée pour SQL Server, car la compression NTFS interrompt le protocole WAL (Write Ahead Logging). La compression NTFS nécessite également un traitement accru pour chaque opération d’E/S. La compression crée « un par un » comme un comportement qui provoque de graves problèmes de performances.

Résolution :

Pour résoudre ce problème, décompressez les données et les fichiers journaux.

Pour plus d’informations, consultez Prise en charge des bases de données sur les volumes compressés.

Points de données supplémentaires

Les attentes PAGEIOLATCH_* et writelog dans sys.dm_os_wait_stats vues de gestion dynamique (DMV) sont des indicateurs clés pour examiner les performances des chemins d’E/S. Si vous voyez une quantité significative d’attentes PAGEIOLATCH, cela signifie que SQL Server est en attente sur le sous-système d’E/S. Un certain nombre d’attentes PAGEIOLATCH est un comportement typique et attendu. Toutefois, si les temps d’attente moyens de PAGEIOLATCH sont constamment supérieurs à 10 millisecondes, vous devez examiner pourquoi le sous-système d’E/S est sous pression. Pour plus d’informations, consultez les documents suivants :

Informations de référence

SQL Server exige que les systèmes prennent en charge la « livraison garantie à des supports stables », comme indiqué dans les exigences du programme de fiabilité d’E/S SQL Server. Pour plus d’informations sur les exigences d’entrée et de sortie pour le moteur de base de données SQL Server, consultez Exigences d’entrée/sortie du moteur de base de données.

Pour plus d’informations sur les erreurs d’E/S, consultez Concepts de base des E/S Microsoft SQL Server, chapitre 2.