I/O 問題が SQL Server のパフォーマンス低下を引き起こす問題のトラブルシューティング

[アーティクル]
11/20/2024

適用対象: SQL Server

この記事では、I/O の問題によって SQL Server のパフォーマンスが低下する原因と、問題のトラブルシューティング方法に関するガイダンスを提供します。

低速な I/O パフォーマンスを定義する

パフォーマンスモニターカウンターは、I/O パフォーマンスの低下を判断するために使用されます。これらのカウンターは、I/O サブシステムが各 I/O 要求をクロック時間の観点から平均して処理する速度を測定します。 Windows での I/O 待機時間を測定する特定のパフォーマンスモニターカウンターは、 Avg Disk sec/ Read、 Avg. Disk sec/Write、および Avg. Disk sec/Transfer です (読み取りと書き込みの両方の累積)。

SQL Server では、同じように動作します。一般に、SQL Server がクロック時間 (ミリ秒) で測定された I/O ボトルネックを報告するかどうかを確認します。 SQL Server は、 WriteFile()、 ReadFile()、 WriteFileGather()、 ReadFileScatter()などの Win32 関数を呼び出すことによって、OS に I/O 要求を行います。 I/O 要求をポストすると、SQL Server は要求を回し、 wait 型を使用して要求の期間を報告します。 SQL Server では、待機の種類を使用して、製品のさまざまな場所で I/O 待機を示します。 I/O 関連の待機は次のとおりです。

PAGEIOLATCH_SH / PAGEIOLATCH_EX
WRITELOG
IO_COMPLETION
ASYNC_IO_COMPLETION
BACKUPIO

これらの待機が一貫して 10 ~ 15 ミリ秒を超える場合、I/O はボトルネックと見なされます。

Note

コンテキストと視点を提供するために、SQL Server のトラブルシューティングの世界では、Microsoft CSS では、I/O 要求が 1 秒を超え、転送あたり最大 15 秒を要するケースが観察されています。このような I/O システムでは最適化が必要です。逆に、Microsoft CSS では、スループットが 1 ミリ秒/転送未満のシステムが見られました。現在の SSD/NVMe テクノロジでは、アドバタイズされたスループットレートは転送あたり数十マイクロ秒の範囲です。したがって、10 から 15 ミリ秒/転送の数値は、長年にわたる Windows と SQL Server のエンジニア間の集合的な経験に基づいて選択した非常におおよそのしきい値です。通常、数値がこのおおよそのしきい値を超えると、SQL Server ユーザーはワークロードに待機時間が表示され、レポートが表示されます。最終的に、I/O サブシステムの予想スループットは、製造元、モデル、構成、ワークロード、および他の複数の要因によって定義されます。

方法

この記事の最後にあるフローグラフでは、SQL Server で低速 I/O の問題に対処するために Microsoft CSS が使用する手法について説明します。これは網羅的または排他的なアプローチではありませんが、問題を分離して解決する際に役立つことが実証されています。

次の 2 つのオプションのいずれかを選択して問題を解決できます。

オプション 1: Azure Data Studio を利用してノートブックで直接、手順を実行する

Note

このノートブックを開く前に、Azure Data Studio がローカルコンピューターにインストールされていることを確認してください。インストールするには、「 Azure Data Studio をインストールする方法を学びます。

Azure Data Studio でノートブックを開く

オプション 2: 手順を手動で実行する

手法の概要については、次の手順を実行します。

手順 1: SQL Server レポートの I/O が遅いかどうか

SQL Server では、いくつかの方法で I/O 待機時間が報告される場合があります。

I/O 待機の種類
DMV sys.dm_io_virtual_file_stats
エラーログまたはアプリケーションイベントログ

I/O 待機の種類

SQL Server の待機の種類によって報告される I/O 待機時間があるかどうかを判断します。 PAGEIOLATCH_*、WRITELOG、およびASYNC_IO_COMPLETIONの値、およびその他のあまり一般的でないいくつかの待機の種類の値は、通常、I/O 要求あたり 10 ~ 15 ミリ秒未満である必要があります。これらの値が一貫して大きい場合は、I/O パフォーマンスの問題が存在し、さらに調査する必要があります。次のクエリは、システムでこの診断情報を収集するのに役立ちます。

#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
}

sys.dm_io_virtual_file_statsのファイル統計

SQL Server で報告されたデータベースファイルレベルの待機時間を表示するには、次のクエリを実行します。

#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"

待機時間の詳細については、 AvgLatency 列と LatencyAssessment 列を参照してください。

エラーログまたはアプリケーションイベントログで報告されたエラー 833

場合によっては、エラーログにエラー 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) が表示されることがあります。次の PowerShell コマンドを実行して、システム上の SQL Server エラーログを確認できます。

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"

また、このエラーの詳細については、「 MSSQLSERVER_833 」セクションを参照してください。

手順 2: Perfmon カウンターは I/O 待機時間を示していますか?

SQL Server が I/O 待機時間を報告する場合は、OS カウンターを参照してください。待機時間カウンターの Avg Disk Sec/Transferを調べることで、I/O の問題があるかどうかを判断できます。次のコードスニペットは、PowerShell を使用してこの情報を収集する 1 つの方法を示しています。すべてのディスクボリューム ("_total" のカウンターが収集されます。特定のドライブボリューム ("D:" など) に変更します。データベースファイルをホストするボリュームを見つけるには、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"

選択したボリュームの Avg Disk Sec/Transfer メトリックを収集します。

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"
   }

このカウンターの値が一貫して 10 ~ 15 ミリ秒を超える場合は、問題をさらに確認する必要があります。通常、スパイクはカウントされませんが、スパイクの期間を再確認してください。スパイクが 1 分以上続いた場合は、スパイクよりも高台になります。

パフォーマンスモニターカウンターで待機時間が報告されないが、SQL Server で報告される場合、問題は SQL Server とパーティションマネージャー (つまりフィルタードライバー) の間にあります。パーティションマネージャーは、OS が Perfmon カウンターを収集する I/O レイヤーです。待機時間に対処するには、フィルタードライバーを適切に除外し、フィルタードライバーの問題を解決します。フィルタードライバーは、 Anti-virus software、 Backup ソリューション、 Encryption、 Compression などのプログラムによって使用されます。このコマンドを使用すると、システム上のフィルタードライバーと、それらがアタッチするボリュームを一覧表示できます。次に、割り当てられたフィルター高度記事でドライバー名とソフトウェアベンダーを検索できます。

fltmc instances

詳細については、「 SQL Server を実行しているコンピューターで実行するウイルス対策ソフトウェアを選択する方法を参照してください。

暗号化ファイルシステム (EFS) とファイルシステムの圧縮は、非同期 I/O が同期的になり、そのため低速になるため、使用しないでください。詳細については、「 Asynchronous disk I/O appears as synchronous on Windows article.

手順 3: I/O サブシステムは容量を超えて圧倒されますか?

SQL Server と OS が I/O サブシステムの速度が遅いと示している場合は、システムが容量を超えて過剰に負荷がかかっている原因かどうかを確認します。容量を確認するには、I/O カウンターの Disk Bytes/Sec、 Disk Read Bytes/Sec、または Disk Write Bytes/Secを確認します。 SAN (またはその他の I/O サブシステム) で予想されるスループット仕様については、システム管理者またはハードウェアベンダーにお問い合わせください。たとえば、2 GB/秒の HBA カードまたは SAN スイッチの 2 GB/秒の専用ポートを介して、200 MB/秒以下の I/O をプッシュできます。ハードウェアの製造元によって定義される予想スループット容量によって、ここからの進め方が定義されます。

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)) }
    }
 }

手順 4: SQL Server は大量の I/O アクティビティを推進していますか?

I/O サブシステムの容量が不足している場合は、SQL Server が原因であるかどうかを調べるには、特定のインスタンスの Buffer Manager: Page Reads/Sec (最も一般的な原因) と Page Writes/Sec (あまり一般的ではありません) を確認します。 SQL Server がメインの I/O ドライバーであり、I/O ボリュームがシステムで処理できる量を超えている場合は、アプリケーション開発チームまたはアプリケーションベンダーと協力して次のことを行います。

たとえば、インデックスの改善、統計の更新、クエリの書き換え、データベースの再設計など、クエリを調整します。
最大サーバーメモリ増やすかシステムに RAM を追加します。 RAM が多いほど、ディスクから頻繁に再読み取りすることなく、より多くのデータまたはインデックスページがキャッシュされるため、I/O アクティビティが減少します。メモリを増やすと、 Lazy Writes/secが減る可能性もあります。これは、使用可能な限られたメモリにデータベースページを格納する必要が頻繁にある場合にレイジーライターフラッシュによって駆動されます。
ページ書き込みが大量の I/O アクティビティのソースである場合は、 Buffer Manager: Checkpoint pages/sec を調べて、回復間隔の構成要求を満たすために必要な大量のページフラッシュが原因かどうかを確認します。 Indirect チェックポイントを使用して時間の経過と同時に I/O を均等にするか、ハードウェア I/O スループットを向上させることができます。

原因

一般に、次の問題は、SQL Server クエリで I/O 待機時間が発生する大まかな理由です。

ハードウェアの問題:
- SAN の構成ミス (スイッチ、ケーブル、HBA、ストレージ)
- I/O 容量の超過 (バックエンドストレージだけでなく、SAN ネットワーク全体で不均衡)
- ドライバーまたはファームウェアの問題
ハードウェアベンダーやシステム管理者は、この段階で関与する必要があります。
クエリの問題: SQL Server では、I/O 要求でディスクボリュームが飽和状態になり、I/O サブシステムが容量を超えてプッシュされるため、I/O 転送レートが高くなります。この場合、ソリューションは、多数の論理読み取り (または書き込み) を引き起こしているクエリを見つけ、適切なインデックスを使用してディスク I/O を最小限に抑えるためにそれらのクエリを調整することが、これを行う最初の手順です。また、最適なプランを選択するのに十分な情報がクエリオプティマイザーに提供されるため、統計は更新された状態に保ちます。また、データベースの設計とクエリの設計が正しくないと、I/O の問題が増加する可能性があります。そのため、クエリやテーブルを再設計すると、I/O の向上に役立つ場合があります。
フィルタードライバー: ファイルシステムフィルタードライバーが大量の I/O トラフィックを処理する場合、SQL Server I/O 応答に重大な影響を与える可能性があります。ウイルス対策スキャンからの適切なファイル除外と、ソフトウェアベンダーによる適切なフィルタードライバーの設計は、I/O パフォーマンスへの影響を防ぐために推奨されます。
他のアプリケーション: SQL Server を使用する同じコンピューター上の別のアプリケーションは、過剰な読み取りまたは書き込み要求で I/O パスを飽和させる可能性があります。この状況では、容量制限を超えて I/O サブシステムがプッシュされ、SQL Server の I/O 速度が低下する可能性があります。アプリケーションを特定してチューニングするか、別の場所に移動して、I/O スタックへの影響を排除します。

手法のグラフィカル表現

SQL Server での低速 I/O の問題を修正するための手法を視覚的に表現します。

ディスク I/O の問題が報告されたときに SQL Server で観察される一般的な待機の種類の説明を次に示します。

PAGEIOLATCH_EX

タスクが I/O 要求のデータまたはインデックスページ (バッファー) のラッチを待機しているときに発生します。ラッチ要求は排他モードです。排他モードは、バッファーがディスクに書き込まれるときに使用されます。長い待機は、ディスクサブシステムに関する問題を示している可能性があります。

PAGEIOLATCH_SH

タスクが I/O 要求のデータまたはインデックスページ (バッファー) のラッチを待機しているときに発生します。ラッチ要求は共有モードです。共有モードは、バッファーがディスクから読み取られるときに使用されます。長い待機は、ディスクサブシステムに関する問題を示している可能性があります。

PAGEIOLATCH_UP

タスクが I/O 要求内のバッファーのラッチを待機しているときに発生します。ラッチ要求は更新モードです。長い待機は、ディスクサブシステムに関する問題を示している可能性があります。

WRITELOG

タスクがトランザクションログのフラッシュの完了を待機しているときに発生します。フラッシュは、ログマネージャーが一時的な内容をディスクに書き込むと発生します。ログフラッシュの原因となる一般的な操作は、トランザクションのコミットとチェックポイントです。

WRITELOGで長時間待機する一般的な理由は次のとおりです。

トランザクションログディスクの待機時間: これは、 WRITELOG 待機の最も一般的な原因です。一般に、データファイルとログファイルは別々のボリュームに保持することをお勧めします。トランザクションログの書き込みはシーケンシャル書き込みですが、データファイルからのデータの読み取りまたは書き込みはランダムです。 1 つのドライブボリューム (特に従来の回転ディスクドライブ) にデータファイルとログファイルを混在すると、ディスクヘッドが過剰に移動します。
VTF が多すぎます: 仮想ログファイル (VTF) が多すぎると、 WRITELOG 待機が発生する可能性があります。 VTF が多すぎると、長い復旧など、他の種類の問題が発生する可能性があります。
小さいトランザクションが多すぎます: 大きなトランザクションがブロックされる可能性はありますが、小さいトランザクションが多すぎると、別の問題が発生する可能性があります。トランザクションを明示的に開始しないと、挿入、削除、または更新によってトランザクションが発生します (この自動トランザクションと呼びます)。ループ内で 1,000 個の挿入を行うと、1,000 個のトランザクションが生成されます。この例の各トランザクションはコミットする必要があります。その結果、トランザクションログがフラッシュされ、トランザクションが 1,000 回フラッシュされます。可能な場合は、個々の更新、削除、または挿入を大きなトランザクションにグループ化して、トランザクションログのフラッシュとパフォーマンスを低下させます。この操作により、 WRITELOG 待機が少なくなる可能性があります。
スケジュールの問題により、ログライタースレッドが十分に速くスケジュールされません: SQL Server 2016 より前は、1 つのログライタースレッドですべてのログ書き込みが実行されました。スレッドのスケジュール設定に問題がある場合 (CPU 使用率が高い場合など)、ログライタースレッドとログフラッシュの両方が遅延する可能性があります。 SQL Server 2016 では、ログ書き込みスループットを向上させるために、最大 4 つのログライタースレッドが追加されました。「 SQL 2016 - 実行速度が速い:複数のログライターワーカーを参照してください。 SQL Server 2019 では、最大 8 つのログライタースレッドが追加され、スループットがさらに向上しました。また、SQL Server 2019 では、通常のワーカースレッドごとに、ログライタースレッドに投稿するのではなく、ログ書き込みを直接実行できます。これらの機能強化により、 WRITELOG 待機は、問題のスケジュール設定によってトリガーされることはほとんどありません。

ASYNC_IO_COMPLETION

次の I/O アクティビティの一部が発生したときに発生します。

一括挿入プロバイダー ("一括挿入") は、I/O の実行時にこの待機の種類を使用します。
LogShipping での元に戻すファイルの読み取りと、ログ配布用の非同期 I/O の指示。
データバックアップ中にデータファイルから実際のデータを読み取ります。

IO_COMPLETION

I/O 操作の完了を待機中に発生します。通常、この待機の種類には、データページ (バッファー) に関連しない I/O が含まれます。以下に例を示します。

書き込み中のディスクとの間での並べ替え/ハッシュ結果の読み取りと書き込み ( tempdb ストレージのパフォーマンスを確認します)。
ディスクへの一括スプールの読み取りと書き込み ( tempdb ストレージを確認します)。
トランザクションログからログブロックを読み取ります (ディスクからログを読み取る操作中 (復旧など)。
データベースがまだ設定されていない場合のディスクからのページの読み取り。
データベーススナップショットへのページのコピー (書き込み時コピー)。
データベースファイルとファイルの圧縮解除を閉じます。

BACKUPIO

バックアップタスクがデータを待機している場合、またはバッファーがデータを格納するのを待機しているときに発生します。タスクがテープマウントを待機している場合を除き、この種類は一般的ではありません。

次の方法で共有

I/O 問題が SQL Server のパフォーマンス低下を引き起こす問題のトラブルシューティング

低速な I/O パフォーマンスを定義する

方法

オプション 1: Azure Data Studio を利用してノートブックで直接、手順を実行する

オプション 2: 手順を手動で実行する

手順 1: SQL Server レポートの I/O が遅いかどうか

I/O 待機の種類

sys.dm_io_virtual_file_statsのファイル統計

エラーログまたはアプリケーションイベントログで報告されたエラー 833

手順 2: Perfmon カウンターは I/O 待機時間を示していますか?

手順 3: I/O サブシステムは容量を超えて圧倒されますか?

手順 4: SQL Server は大量の I/O アクティビティを推進していますか?

原因

手法のグラフィカル表現

PAGEIOLATCH_EX

PAGEIOLATCH_SH

PAGEIOLATCH_UP

WRITELOG

ASYNC_IO_COMPLETION

IO_COMPLETION

BACKUPIO

フィードバック

その他のリソース

次の方法で共有

I/O 問題が SQL Server のパフォーマンス低下を引き起こす問題のトラブルシューティング

低速な I/O パフォーマンスを定義する

方法

オプション 1: Azure Data Studio を利用してノートブックで直接、手順を実行する

オプション 2: 手順を手動で実行する

手順 1: SQL Server レポートの I/O が遅いかどうか

I/O 待機の種類

sys.dm_io_virtual_file_statsのファイル統計

エラー ログまたはアプリケーション イベント ログで報告されたエラー 833

手順 2: Perfmon カウンターは I/O 待機時間を示していますか?

手順 3: I/O サブシステムは容量を超えて圧倒されますか?

手順 4: SQL Server は大量の I/O アクティビティを推進していますか?

原因

手法のグラフィカル表現

I/O 関連の待機の種類に関する情報

PAGEIOLATCH_EX

PAGEIOLATCH_SH

PAGEIOLATCH_UP

WRITELOG

ASYNC_IO_COMPLETION

IO_COMPLETION

BACKUPIO

フィードバック

その他のリソース

エラーログまたはアプリケーションイベントログで報告されたエラー 833