MSSQLSERVER_833

S’applique à : SQL Server Azure 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 pris plus de 15 secondes pour retourner. 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 chronologie des opérations d’entrée et de sortie de fichier (E/S). L’un des éléments suivants peut entraîner des opérations d’E/S bloquées ou bloquées et affecter nuire à la réactivité et aux performances de SQL Server :

• 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 :

• Bloquant.
• Contention de verrous et délais d’attente.
• 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 du moniteur système.

Lorsqu’une opération d’E/S est en attente pendant 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 fournie dans le tableau suivant :

Texte du message	Description
<Nombre> d’occurrences	Nombre de requêtes d’E/S qui n’ont pas terminé l’opération de lecture ou d’écriture en moins de 15 secondes.
Informations sur les fichiers	Nom de fichier complet, nom de la base de données et numéro d’identification de base de données (DBID).
Poignée	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 faciliter le suivi des demandes de paquets de demandes d’E/S (IRP).
Contrepartie	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 faciliter le suivi des 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 ABA.
• Le chemin d’E/S a un logiciel défectueux. Il peut s’agir d’un microprogramme ou d’un problème de pilote.
• Le chemin d’E/S a des composants matériels défectueux.
• Problème de performances au niveau du système d’exploitation.
• Filtrer l’intervention du pilote 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 enregistre l’heure à laquelle l’E/S a été terminée. 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 la cause de la condition d’E/S retardée que ce message décrit et signale. Cette condition est appelée E/S bloquée. La plupart des demandes de disque se produisent à la vitesse standard du disque, Cette vitesse de disque classique 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 sont longues pour que le chemin d’E/S système retourne à SQL Server. Pour plus d’informations, consultez la section Plus d’informations.

Action utilisateur

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

1. Examinez le journal des événements système pour 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 de périphérique et microprogramme ou effectuez d’autres diagnostics associés à votre sous-système d’E/S.
4. L’accès au disque peut être ralenti par les 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 exécutant SQL Server (microsoft.com).
   • Utilisez l’utilitaire de ligne de commande fltmc.exe pour interroger tous les pilotes de filtre installés sur le système et comprendre les fonctions qu’il exécute 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 installations telles que la journalisation Storport ETW pour mesurer la latence des requêtes effectuées sur 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 IOPS appropriés pour votre débit de stockage.

Pour obtenir une procédure pas à pas guidée pour diagnostiquer et résoudre les problèmes de performances DE SQL Server qui se produisent en raison de problèmes d’E/S, consultez Résoudre les problèmes de ralentissement des performances SQL Server causées par des 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 requête d’E/S qui ne se termine pas. Fréquemment, 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 en tant que requête d’E/S terminée, ou qui prend un temps excessif pour se terminer. Le comportement des E/S bloqués 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 de l’aide du matériel ou du fournisseur de logiciels pour tracer et résoudre.

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

La prise en charge de SQL Server gère de nombreux cas chaque année qui impliquent des problèmes d’E/S bloqués ou bloqués. Ces problèmes d’E/S apparaissent de différentes façons. Les problèmes d’E/S sont certains des problèmes les plus difficiles à diagnostiquer et à déboguer, et ils nécessitent un temps et des ressources importants pour le débogage de Microsoft et du client. Les rapports et l’enregistrement des demandes d’E/S sont conçus par fichier. La détection et la création de rapports sur les 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 la fin de l’opération d’E/S. Le deuxième moment est le moment où l’enregistreur différé s’exécute. Lorsque l’enregistreur différé s’exécute, il vérifie toutes les demandes d’E/S du fichier journal en attente. Si la requête d’E/S dépasse le seuil de 15 secondes, une opération d’enregistrement se produit.

Génération d’états

La création de rapports se produit dans des intervalles de cinq minutes ou plus. La création de rapports se produit lorsque la requête d’E/S suivante est effectuée sur le fichier. Si une action d’enregistrement s’est produite et que cinq minutes ou plus ont été passé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ée ou bloquée à l’aide de l’indicateur de trace 830, même si 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 830. 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 est efficace uniquement pour la durée du processus SQL Server.

Remarque

Une demande d’E/S qui devient 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 produisent 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 requête 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 :

• Prise en charge de Windows
• Journalisation ETW Storport

Nous vous recommandons de vérifier 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 composant de pilote, de microprogramme ou de pilote de filtre tiers.

Actions de plan de requête et de performances système

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. Les charges excessives peuvent ralentir le système global, 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, il peut indiquer qu’un processus système utilise autant d’UC que d’autres processus sont affectés.

Compteurs de performances

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

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

Par exemple, le temps moyen de disque sec/transfert sur un ordinateur exécutant SQL Server est généralement inférieur à 15 millisecondes. Si la valeur moyenne du disque sec/transfert augmente, elle indique que le sous-système d’E/S ne correspond pas de façon 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 de problème.

Dans Le Moniteur système Windows, vous pouvez passer en revue le compteur « Disque physique : octets de disque/s » pour chaque disque concerné 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. Pour identifier si un ensemble spécifique de processus génère des demandes d’E/S excessives. D’autres compteurs liés aux E/S dans l’objet Process révèlent des informations plus granulaires. 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 les index et le parallélisme. Pour obtenir une discussion détaillée sur la détection et la résolution des goulots d’étranglement d’E/S, consultez Résoudre les problèmes liés aux performances lentes de SQL Server causées par des problèmes d’E/S.

Index et parallélisme

Fréquemment, les 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 Rotation 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 terminer 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 trouvent dans le cache de données restent pertinentes pour les requêtes actives.
• Les tris et les hachages sont utilisés, car un index 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 de requêtes parallèles et que la charge sur le système est considérée, 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ête et définissez la valeur sp_configure de l’option max degree of parallelism 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 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 grand tri peut avoir lieu. 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 entraîner un 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 réglage se produit.

Exemples pratiques de la prise en charge de SQL Server

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

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 n’est pas terminée en temps voulu. Ce comportement crée une condition bloquante qui provoque 30 secondes d’expiration du client.

L’application a envoyé une validation à SQL Server et la validation est bloquée en tant qu’écriture de journal en attente. Ce comportement entraîne la poursuite de la requête en maintenant les verrous et bloquer les requêtes entrantes d’autres clients. Ensuite, d’autres clients commencent à expirer. Cela permet de résoudre le problème, car l’application ne restaure pas les transactions ouvertes lorsqu’un délai d’attente 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 de SQL Server

L’application services un site web à l’aide du regroupement de connexions. À mesure que d’autres connexions deviennent bloquées, le site web crée plus de connexions. Ces connexions deviennent 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 provoquent plusieurs minutes de temps de récupération pour SQL Server et l’application. Combinée à des problèmes d’application, la condition d’E/S bloquée a un effet très négatif sur le système.

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 se trouve derrière 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 requête d’E/S est acheminée vers le deuxième HBA. Le deuxième HBA gère la requête et se termine rapidement. Pour éviter de telles conditions de blocage, le fabricant du matériel recommande un paramètre de « nouvelle tentative avant le basculement » de cinq secondes.

Exemple 2 : Intervention du pilote de filtre

De nombreux programmes 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 de requêtes d’E/S et ont accès à la requête IRP. Microsoft Product Support Services a rencontré différents problèmes liés aux 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 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 collecter l’image correcte du fichier au démarrage de la sauvegarde. Cette opération retarde les demandes d’E/S. Les demandes d’E/S ne sont autorisées à effectuer qu’une seule fois à la fois, car le logiciel les gère.

Au démarrage de la sauvegarde, les performances de SQL Server diminuent considérablement, car les E/S de SQL Server sont forcées de terminer une par une. La logique à la fois est telle que l’opération d’E/S ne peut pas être effectuée de manière asynchrone, ce qui compose le problème. Par conséquent, lorsque SQL Server s’attend à publier une demande d’E/S et à continuer, le worker est bloqué dans l’appel en lecture ou en é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 en lecture-avance. En outre, un autre bogue dans le pilote de filtre laisse celui à la fois des actions dans le processus, même lorsque la sauvegarde est terminée. La seule façon de restaurer les performances de SQL Server consiste à redémarrer SQL Server afin que le handle de fichier soit libéré et réacquiré sans l’interaction du pilote de filtre.

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 à la fois ».

Exemple 3 : Erreurs masquées

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

Solution

Pour résoudre ce problème, redémarrez 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 empêcher la perte de données. Certains systèmes qui utilisent la mise en miroir sont basés sur des logiciels et certains sont basés sur du matériel. La situation qui est généralement découverte par Support Microsoft pour ces systèmes augmente la latence.

Une augmentation du temps d’E/S global se produit lorsque l’E/S doit se terminer avant qu’elle soit considérée comme terminée. Pour les installations de mise en 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.

Solution

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

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 à la fois » comme le comportement qui provoque des problèmes de performances graves.

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 des volumes compressés.

Points de données supplémentaires

PAGEIOLATCH_* et les attentes de journal d’écriture dans sys.dm_os_wait_stats vues de gestion dynamique (DMV) sont des indicateurs clés pour examiner les performances du chemin 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. Une certaine quantité d’attentes PAGEIOLATCH est typique et attendue. Toutefois, si les temps d’attente PAGEIOLATCH moyens 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 :

• Résolution des problèmes de performances lentes de SQL Server dues à des problèmes d’E/S
• sys.dm_os_waiting_tasks (Transact-SQL)
• sys.dm_exec_requests
• Référentiel de type d’attente SQL Server

Informations de référence

• Utiliser DISKSPD pour tester les performances de stockage d'une charge de travail
• 826433 diagnostics SQL Server ajoutés pour détecter les problèmes d’E/S non signalés en raison de lectures obsolètes ou d’écritures perdues
• Algorithmes de journalisation et de stockage des données

SQL Server exige que les systèmes prennent en charge la « remise garantie à un support stable », comme indiqué dans les exigences du programme de fiabilité des 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 Moteur de base de données exigences d’entrée/sortie.

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