Résoudre les problèmes de lenteur SQL Server des performances causées par des problèmes d’E/S
S’applique à : SQL Server
Cet article fournit des conseils sur les problèmes d’E/S qui ralentissent les performances SQL Server et sur la façon de les résoudre.
Définir des performances d’E/S lentes
Les compteurs de l’Analyseur de performances sont utilisés pour déterminer les performances d’E/S lentes. Ces compteurs mesurent la vitesse à laquelle le sous-système d’E/S dessert chaque demande d’E/S en moyenne en termes de temps d’horloge. Les compteurs spécifiques de l’Analyseur de performances qui mesurent la latence des E/S dans Windows sont Avg Disk sec/ Read, Avg. Disk sec/Writeet Avg. Disk sec/Transfer (cumulés des lectures et des écritures).
Dans SQL Server, les choses fonctionnent de la même façon. En règle générale, vous examinez si SQL Server signale des goulots d’étranglement d’E/S mesurés en temps d’horloge (millisecondes). SQL Server effectue des demandes d’E/S au système d’exploitation en appelant les fonctions Win32 telles que WriteFile(), ReadFile(), WriteFileGather()et ReadFileScatter(). Lorsqu’il publie une demande d’E/S, SQL Server fois la demande et signale la durée de la demande à l’aide de types d’attente. SQL Server utilise des types d’attente pour indiquer les attentes d’E/S à différents endroits du produit. Les attentes liées aux E/S sont les suivantes :
- / PAGEIOLATCH_SHPAGEIOLATCH_EX
- WRITELOG
- IO_COMPLETION
- ASYNC_IO_COMPLETION
- BACKUPIO
Si ces attentes dépassent 10 à 15 millisecondes de manière cohérente, les E/S sont considérées comme un goulot d’étranglement.
Remarque
Pour fournir un contexte et une perspective, dans le monde de la résolution des problèmes SQL Server, Microsoft CSS a observé des cas où une demande d’E/S prenait plus d’une seconde et jusqu’à 15 secondes par transfert de tels systèmes d’E/S ont besoin d’être optimisés. À l’inverse, Microsoft CSS a vu des systèmes où le débit est inférieur à une milliseconde/transfert. Avec la technologie SSD/NVMe d’aujourd’hui, les débits annoncés sont de dizaines de microsecondes par transfert. Par conséquent, le chiffre de 10 à 15 millisecondes/transfert est un seuil très approximatif que nous avons choisi en fonction de l’expérience collective entre Windows et les ingénieurs SQL Server au fil des ans. En règle générale, lorsque les nombres dépassent ce seuil approximatif, SQL Server utilisateurs commencent à voir une latence dans leurs charges de travail et les signalent. En fin de compte, le débit attendu d’un sous-système d’E/S est défini par le fabricant, le modèle, la configuration, la charge de travail et éventuellement plusieurs autres facteurs.
Méthodologie
Un organigramme à la fin de cet article décrit la méthodologie utilisée par Microsoft CSS pour aborder les problèmes d’E/S lents avec SQL Server. Il ne s’agit pas d’une approche exhaustive ou exclusive, mais elle s’est avérée utile pour isoler le problème et le résoudre.
Vous pouvez choisir l’une des deux options suivantes pour résoudre le problème :
Option 1 : Exécuter les étapes directement dans un notebook via Azure Data Studio
Remarque
Avant de tenter d’ouvrir ce notebook, assurez-vous qu’Azure Data Studio est installé sur votre ordinateur local. Pour l’installer, accédez à Découvrir comment installer Azure Data Studio.
Option 2 : Suivre les étapes manuellement
La méthodologie est décrite dans les étapes suivantes :
Étape 1 : SQL Server signale-t-il des E/S lentes ?
SQL Server pouvez signaler une latence d’E/S de plusieurs façons :
- Types d’attente d’E/S
- DMV
sys.dm_io_virtual_file_stats - Journal des erreurs ou journal des événements d’application
Types d’attente d’E/S
Déterminez s’il existe une latence d’E/S signalée par SQL Server types d’attente. Les valeurs PAGEIOLATCH_*, WRITELOGet et ASYNC_IO_COMPLETION les valeurs de plusieurs autres types d’attente moins courants doivent généralement rester inférieures à 10 à 15 millisecondes par demande d’E/S. Si ces valeurs sont plus cohérentes, un problème de performances d’E/S existe et nécessite un examen plus approfondi. La requête suivante peut vous aider à collecter ces informations de diagnostic sur votre système :
#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance"
for ([int]$i = 0; $i -lt 100; $i++)
{
sqlcmd -E -S $sqlserver_instance -Q "SELECT r.session_id, r.wait_type, r.wait_time as wait_time_ms`
FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s `
ON r.session_id = s.session_id `
WHERE wait_type in ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', `
'IO_COMPLETION', 'ASYNC_IO_COMPLETION', 'BACKUPIO')`
AND is_user_process = 1"
Start-Sleep -s 2
}
Statistiques des fichiers dans sys.dm_io_virtual_file_stats
Pour afficher la latence au niveau du fichier de base de données comme indiqué dans SQL Server, exécutez la requête suivante :
#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance"
sqlcmd -E -S $sqlserver_instance -Q "SELECT LEFT(mf.physical_name,100), `
ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END, `
WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END, `
AvgLatency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
ELSE (io_stall / (num_of_reads + num_of_writes)) END,`
LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 'No data' ELSE `
CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN 'Excellent' `
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' `
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' `
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' `
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN 'Bad' `
ELSE 'Deplorable' END END, `
[Avg KBs/Transfer] = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, `
LEFT (mf.physical_name, 2) AS Volume, `
LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]`
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs `
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id `
AND vfs.file_id = mf.file_id `
ORDER BY AvgLatency DESC"
Examinez les AvgLatency colonnes et LatencyAssessment pour comprendre les détails de la latence.
Erreur 833 signalée dans le journal des erreurs ou le journal des événements de l’application
Dans certains cas, vous pouvez observer l’erreur 833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d) dans le journal des erreurs. Vous pouvez case activée SQL Server journaux d’erreurs sur votre système en exécutant la commande PowerShell suivante :
Get-ChildItem -Path "c:\program files\microsoft sql server\mssql*" -Recurse -Include Errorlog |
Select-String "occurrence(s) of I/O requests taking longer than Longer than 15 secs"
Pour plus d’informations sur cette erreur, consultez la section MSSQLSERVER_833 .
Étape 2 : Les compteurs Perfmon indiquent-ils une latence d’E/S ?
Si SQL Server signale une latence d’E/S, reportez-vous aux compteurs du système d’exploitation. Vous pouvez déterminer s’il existe un problème d’E/S en examinant le compteur Avg Disk Sec/Transferde latence . L’extrait de code suivant indique un moyen de collecter ces informations via PowerShell. Il collecte les compteurs sur tous les volumes de disque : « _total ». Remplacez par un volume de lecteur spécifique (par exemple, « D : »). Pour rechercher les volumes qui hébergent vos fichiers de base de données, exécutez la requête suivante dans votre SQL Server :
#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance"
sqlcmd -E -S $sqlserver_instance -Q "SELECT DISTINCT LEFT(volume_mount_point, 32) AS volume_mount_point `
FROM sys.master_files f `
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs"
Rassemblez Avg Disk Sec/Transfer les métriques sur le volume de votre choix :
clear
$cntr = 0
# replace with your server name, unless local computer
$serverName = $env:COMPUTERNAME
# replace with your volume name - C: , D:, etc
$volumeName = "_total"
$Counters = @(("\\$serverName" +"\LogicalDisk($volumeName)\Avg. disk sec/transfer"))
$disksectransfer = Get-Counter -Counter $Counters -MaxSamples 1
$avg = $($disksectransfer.CounterSamples | Select-Object CookedValue).CookedValue
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 30 | ForEach-Object {
$_.CounterSamples | ForEach-Object {
[pscustomobject]@{
TimeStamp = $_.TimeStamp
Path = $_.Path
Value = ([Math]::Round($_.CookedValue, 5))
turn = $cntr = $cntr +1
running_avg = [Math]::Round(($avg = (($_.CookedValue + $avg) / 2)), 5)
} | Format-Table
}
}
write-host "Final_Running_Average: $([Math]::Round( $avg, 5)) sec/transfer`n"
if ($avg -gt 0.01)
{
Write-Host "There ARE indications of slow I/O performance on your system"
}
else
{
Write-Host "There is NO indication of slow I/O performance on your system"
}
Si les valeurs de ce compteur sont constamment supérieures à 10-15 millisecondes, vous devez examiner le problème plus en détail. Les pics occasionnels ne comptent pas dans la plupart des cas, mais veillez à doubler case activée la durée d’un pic. Si le pic a duré une minute ou plus, il s’agit plus d’un plateau qu’un pic.
Si les compteurs de l’Analyseur de performances ne signalent pas la latence, mais SQL Server le fait, le problème se situe entre SQL Server et le Gestionnaire de partitions, c’est-à-dire les pilotes de filtre. Le Gestionnaire de partitions est une couche d’E/S dans laquelle le système d’exploitation collecte les compteurs Perfmon . Pour résoudre la latence, vérifiez les exclusions appropriées des pilotes de filtre et résolvez les problèmes liés aux pilotes de filtre. Les pilotes de filtre sont utilisés par des programmes tels que les logiciels antivirus, les solutions de sauvegarde, le chiffrement, la compression, etc. Vous pouvez utiliser cette commande pour répertorier les pilotes de filtre sur les systèmes et les volumes auxquels ils sont attachés. Ensuite, vous pouvez rechercher les noms des pilotes et les fournisseurs de logiciels dans l’article Altitudes de filtre allouées .
fltmc instances
Pour plus d’informations, consultez Comment choisir un logiciel antivirus à exécuter sur les ordinateurs qui exécutent SQL Server.
Évitez d’utiliser le système de fichiers EFS (Encrypting File System) et la compression du système de fichiers, car elles entraînent la synchronisation des E/S asynchrones et donc plus lentes. Pour plus d’informations, consultez l’article Les E/S de disque asynchrones apparaissent comme synchrones sur Windows .
Étape 3 : Le sous-système d’E/S est-il débordé au-delà de sa capacité ?
Si SQL Server et le système d’exploitation indiquent que le sous-système d’E/S est lent, case activée si la cause est le dépassement de capacité du système. Vous pouvez case activée capacité en examinant les compteurs Disk Bytes/Secd’E/S , Disk Read Bytes/Secou Disk Write Bytes/Sec. Veillez à case activée avec votre administrateur système ou fournisseur de matériel pour connaître les spécifications de débit attendues pour votre SAN (ou un autre sous-système d’E/S). Par exemple, vous ne pouvez pas envoyer (push) un maximum de 200 Mo/s d’E/S via un carte HBA de 2 Go/s ou un port dédié de 2 Go/s sur un commutateur SAN. La capacité de débit attendue définie par un fabricant de matériel définit la façon dont vous procédez ici.
clear
$serverName = $env:COMPUTERNAME
$Counters = @(
("\\$serverName" +"\PhysicalDisk(*)\Disk Bytes/sec"),
("\\$serverName" +"\PhysicalDisk(*)\Disk Read Bytes/sec"),
("\\$serverName" +"\PhysicalDisk(*)\Disk Write Bytes/sec")
)
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 20 | ForEach-Object {
$_.CounterSamples | ForEach-Object {
[pscustomobject]@{
TimeStamp = $_.TimeStamp
Path = $_.Path
Value = ([Math]::Round($_.CookedValue, 3)) }
}
}
Étape 4 : Est-ce que SQL Server pilote l’activité d’E/S intensive ?
Si le sous-système d’E/S est submergé au-delà de sa capacité, déterminez si SQL Server est le coupable en examinant Buffer Manager: Page Reads/Sec (le coupable le plus courant) et Page Writes/Sec (beaucoup moins courant) pour le instance spécifique. Si SQL Server est le pilote d’E/S main et que le volume d’E/S dépasse ce que le système peut gérer, collaborez avec les équipes de développement d’applications ou le fournisseur d’applications pour :
- Paramétrez les requêtes, par exemple : amélioration des index, mise à jour des statistiques, réécriture des requêtes et remaniement de la base de données.
- Augmentez la mémoire maximale du serveur ou ajoutez plus de RAM sur le système. Plus de RAM met en cache davantage de données ou de pages d’index sans relecture fréquente à partir du disque, ce qui réduit l’activité d’E/S.
Causes
En général, les problèmes suivants sont les principales raisons pour lesquelles SQL Server requêtes souffrent de latence d’E/S :
Problèmes matériels :
Une mauvaise configuration SAN (commutateur, câbles, adaptateur HBA, stockage)
Capacité d’E/S dépassée (déséquilibré sur l’ensemble du réseau SAN, pas seulement sur le stockage back-end)
Problèmes liés aux pilotes ou aux microprogrammes
Les fournisseurs de matériel et/ou les administrateurs système doivent être engagés à ce stade.
Problèmes de requête : SQL Server sature les volumes de disque avec des demandes d’E/S et pousse le sous-système d’E/S au-delà de sa capacité, ce qui entraîne des taux de transfert d’E/S élevés. Dans ce cas, la solution consiste à rechercher les requêtes qui provoquent un grand nombre de lectures logiques (ou d’écritures) et à régler ces requêtes pour réduire les E/S disque à l’aide d’index appropriés. En outre, gardez les statistiques à jour car elles fournissent à l’optimiseur de requête suffisamment d’informations pour choisir le meilleur plan. En outre, une conception de base de données et une conception de requête incorrectes peuvent entraîner une augmentation des problèmes d’E/S. Par conséquent, la refonte des requêtes et parfois des tables peut aider à améliorer les E/S.
Pilotes de filtre : La réponse d’E/S SQL Server peut être gravement affectée si les pilotes de filtre de système de fichiers traitent un trafic d’E/S important. Des exclusions de fichiers appropriées de l’analyse antivirus et la conception correcte du pilote de filtre par les éditeurs de logiciels sont recommandées pour éviter tout impact sur les performances d’E/S.
Autres applications : Une autre application sur la même machine avec SQL Server peut saturer le chemin d’E/S avec des demandes de lecture ou d’écriture excessives. Cette situation peut pousser le sous-système d’E/S au-delà des limites de capacité et entraîner une lenteur des E/S pour SQL Server. Identifiez l’application et réglez-la ou déplacez-la ailleurs pour éliminer son impact sur la pile d’E/S.
Représentation graphique de la méthodologie
Informations sur les types d’attente liés aux E/S
Voici une description des types d’attente courants observés dans SQL Server lorsque des problèmes d’E/S de disque sont signalés.
PAGEIOLATCH_EX
Se produit lorsqu’une tâche attend sur un verrou une page de données ou d’index (mémoire tampon) dans une demande d’E/S. La demande de verrou est en mode Exclusif. Un mode Exclusif est utilisé lorsque la mémoire tampon est écrite sur le disque. Des attentes longues peuvent indiquer des problèmes avec le sous-système de disque.
PAGEIOLATCH_SH
Se produit lorsqu’une tâche attend sur un verrou une page de données ou d’index (mémoire tampon) dans une demande d’E/S. La demande de verrou se trouve en mode partagé. Le mode Partagé est utilisé lorsque la mémoire tampon est en cours de lecture à partir du disque. Des attentes longues peuvent indiquer des problèmes avec le sous-système de disque.
PAGEIOLATCH_UP
Se produit lorsqu’une tâche attend sur un verrou une mémoire tampon dans une demande d’E/S. La demande de verrou est en mode Mise à jour. Des attentes longues peuvent indiquer des problèmes avec le sous-système de disque.
WRITELOG
Se produit lorsqu’une tâche attend la fin d’un vidage du journal des transactions. Un vidage se produit lorsque le Gestionnaire de journaux écrit son contenu temporaire sur le disque. Les opérations courantes qui provoquent des vidages de journal sont les validations de transaction et les points de contrôle.
Les raisons courantes WRITELOG des longues attentes sont les suivantes :
Latence du disque du journal des transactions : il s’agit de la cause la plus courante des
WRITELOGattentes. En règle générale, il est recommandé de conserver les fichiers de données et les fichiers journaux sur des volumes distincts. Les écritures dans le journal des transactions sont des écritures séquentielles alors que la lecture ou l’écriture de données à partir d’un fichier de données est aléatoire. La combinaison de fichiers de données et de fichiers journaux sur un volume de lecteur (en particulier les lecteurs de disque tournant classiques) entraîne un déplacement excessif de la tête de disque.Trop de fichiers journaux virtuels : un trop grand nombre de fichiers journaux virtuels (VDF) peut entraîner des
WRITELOGattentes. Un trop grand nombre de fichiers journaux virtuels peut entraîner d’autres types de problèmes, tels que la récupération longue.Trop de petites transactions : bien que les transactions volumineuses puissent entraîner un blocage, un trop grand nombre de petites transactions peut entraîner un autre ensemble de problèmes. Si vous ne commencez pas explicitement une transaction, toute insertion, suppression ou mise à jour entraîne une transaction (nous appelons cette transaction automatique). Si vous effectuez 1 000 insertions dans une boucle, 1 000 transactions seront générées. Chaque transaction de cet exemple doit être validée, ce qui entraîne un vidage du journal des transactions et 1 000 vidages de transactions. Si possible, regroupez les mises à jour, les suppressions ou les insertions individuelles dans une transaction plus importante pour réduire les vidages du journal des transactions et améliorer les performances. Cette opération peut entraîner moins
WRITELOGd’attentes.Les problèmes de planification font que les threads de l’enregistreur de journaux ne sont pas planifiés assez rapidement : avant SQL Server 2016, un seul thread enregistreur de journaux effectuait toutes les écritures de journal. En cas de problèmes de planification des threads (par exemple, un processeur élevé), le thread Log Writer et les vidages du journal peuvent être retardés. Dans SQL Server 2016, jusqu’à quatre threads d’enregistreur de journaux ont été ajoutés pour augmenter le débit d’écriture des journaux. Consultez SQL 2016 - It Just Runs Faster : Multiple Log Writer Workers. Dans SQL Server 2019, jusqu’à huit threads log Writer ont été ajoutés, ce qui améliore encore davantage le débit. En outre, dans SQL Server 2019, chaque thread de travail standard peut effectuer des écritures de journal directement au lieu de publier dans le thread enregistreur de journaux. Avec ces améliorations,
WRITELOGles attentes sont rarement déclenchées par des problèmes de planification.
ASYNC_IO_COMPLETION
Se produit lorsque certaines des activités d’E/S suivantes se produisent :
- Le fournisseur d’insertion en bloc (« Insérer en bloc ») utilise ce type d’attente lors de l’exécution d’E/S.
- Lecture du fichier d’annulation dans LogShipping et direction des E/S asynchrones pour la copie des journaux de transaction.
- Lecture des données réelles à partir des fichiers de données pendant une sauvegarde de données.
IO_COMPLETION
Se produit en attendant que les opérations d’E/S se terminent. Ce type d’attente implique généralement des E/S non liées aux pages de données (mémoires tampons). Les exemples incluent :
- La lecture et l’écriture des résultats de tri/hachage depuis/vers le disque pendant un déversement (case activée performances du stockage tempdb).
- Lecture et écriture de spools hâtif sur le disque (case activée stockage tempdb).
- Lecture des blocs de journal à partir du journal des transactions (pendant toute opération qui entraîne la lecture du journal à partir du disque, par exemple, la récupération).
- Lecture d’une page à partir d’un disque lorsque la base de données n’est pas encore configurée.
- Copie de pages dans une base de données instantané (copie en écriture).
- Fermeture du fichier de base de données et de la décompression de fichier.
BACKUPIO
Se produit lorsqu’une tâche de sauvegarde attend des données ou attend qu’une mémoire tampon stocke les données. Ce type n’est pas classique, sauf lorsqu’une tâche attend un montage sur bande.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour