I/O の問題によって発生するSQL Serverパフォーマンスの低下のトラブルシューティング

[アーティクル]
06/20/2023

適用対象: 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()Win32 関数を呼び出すことによって、OS への I/O 要求をReadFileScatter()行います。 I/O 要求を投稿すると、SQL Server要求が倍になり、待機の種類を使用して要求の期間が報告されます。 SQL Serverは、待機の種類を使用して、製品のさまざまな場所で I/O 待機を示します。 I/O 関連の待機は次のとおりです。

/ PAGEIOLATCH_SHPAGEIOLATCH_EX
WRITELOG
IO_COMPLETION
ASYNC_IO_COMPLETION
BACKUPIO

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

注:

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

方法

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

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

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

注:

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

Azure Data Studio で Notebook を開く

オプション 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"

列と LatencyAssessment 列をAvgLatency見て、待機時間の詳細を理解します。

エラーログまたはアプリケーションイベントログで報告されたエラー 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 ミリ秒を超える場合は、問題をさらに確認する必要があります。ほとんどの場合、スパイクはカウントされませんが、スパイクの期間を 2 倍チェックしてください。スパイクが 1 分以上続いた場合は、スパイクよりも高い値になります。

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

fltmc instances

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

暗号化ファイルシステム (EFS) とファイルシステムの圧縮は、非同期 I/O が同期的になり、そのため低速になるため、使用しないでください。詳細については、「非同期ディスク I/O が Windows で同期として表示される」の記事を参照してください。

手順 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 サブシステムが容量を超えて圧倒される場合は、特定のインスタンスの (最も一般的な原因) と Page Writes/Sec (あまり一般的ではない) を調べることでBuffer Manager: Page Reads/Sec、SQL Serverが原因であるかどうかを調べる。 SQL Serverが I/O ドライバーメインであり、I/O ボリュームがシステムで処理できる量を超えている場合は、アプリケーション開発チームまたはアプリケーションベンダーと連携して、次のことを行います。

クエリの調整(インデックスの改善、統計の更新、クエリの書き換え、データベースの再設計など)。
最大サーバーメモリを増やすか、システムに RAM を追加します。 RAM が増えるほど、ディスクから頻繁に再読み取りすることなく、より多くのデータまたはインデックスページがキャッシュされるため、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 つのドライブボリューム (特に従来の回転ディスクドライブ) にデータファイルとログファイルを混在すると、ディスクヘッドの移動が過剰になります。
VDF が多すぎる: 仮想ログファイル (VDF) が多すぎると、待機が発生する可能性があります WRITELOG 。 VDF が多すぎると、長い復旧など、他の種類の問題が発生する可能性があります。
小さなトランザクションが多すぎる: 大きなトランザクションがブロックされる可能性がある一方で、小さなトランザクションが多すぎると別の問題が発生する可能性があります。トランザクションを明示的に開始しない場合、挿入、削除、または更新によってトランザクションが発生します (この自動トランザクションと呼びます)。ループで 1,000 個の挿入を行うと、1,000 個のトランザクションが生成されます。この例の各トランザクションをコミットする必要があります。その結果、トランザクションログがフラッシュされ、トランザクションフラッシュが 1,000 になります。可能であれば、個々の更新、削除、または挿入を大きなトランザクションにグループ化して、トランザクションログのフラッシュを減らし、パフォーマンスを向上させます。この操作により、待機が少なくなる WRITELOG 可能性があります。
スケジュールの問題により、ログライタースレッドが十分に速くスケジュールされません。2016 年SQL Serverより前は、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