SQL Server: ディスク I/O を最小限に抑える
クエリのチューニングとインデックスの作成は、物理ディスクと論理ディスクの I/O を減らす効果的な方法です。
出典: 『SQL Server DMV Starter Pack』(Red Gate Books、2010 年)
Glenn Berry、Louis Davidson、Tim Ford
論理ディスクと物理ディスクの I/O は常に最小限に抑える必要があります。I/O 関連のデータベース管理オブジェクト (DMO) は、データをディスクに書き込んだり、ディスクから読み取ったりするときにシステムで発生する物理ディスク I/O を調査するのに役立ちます。
このカテゴリの DMO では、ディスク サブシステムの観点から見たディスク I/O の状態を明示的に提示します。たとえば、ディスク上にあるさまざまなファイルでどのように I/O が分散されているのか、どこで I/O がボトルネックになって、I/O の停止を引き起こしているのかなどを示します。この情報を使用して、ディスク サブシステムのアーキテクチャを最適化できます。また、データを収集して、ビジネス部門のリーダーに記憶域の拡張を求める際の根拠として使用することもできます。
もちろん、避けられない物理ディスクの I/O もあります。SQL Server では、ディスクにアプリケーション データを書き込む必要があります。また、挿入操作、更新操作、削除操作、一括操作を行うたびにトランザクション ログにデータを書き込む必要があります。しかし、単純にディスクの処理能力を強化する必要があると速断する前に、クエリのチューニングとインデックス作成によって、不要な論理ディスクと物理ディスクの I/O を最小限に抑えられるという点について、できることが多くあることを覚えておいてください。
この記事で説明する DMO (すべて sys.dm_io_ から始まる DMO) から取得する I/O 情報や、なんらかの形で I/O パフォーマンスを参照する次のような他の動的管理ビュー (DMV) のデータも考慮に入れる必要があります。
- sys.dm_exec_query_stats – 特定のクエリを実行している間にコストが発生した I/O に関する情報を返します
- sys.dm_exec_connections – この接続で発生した I/O に関する情報を返します
- sys.dm_exec_sessions – セッション中に発生した I/O に関する情報を返します
- sys.dm_os_workers – 特定のワーカー スレッドで保留中の I/O に関する情報を返します
このセクションのすべてのクエリは、SQL Server 2005、SQL Server 2008、および SQL Server 2008 R2 で動作しますが、どのバージョンでもサーバー状態の表示の権限が必要です。
I/O の停止からディスクのボトルネックを調べる
ここで使用する DMV は sys.dm_io_virtual_file_stats です。これは SQL Server Books Online で「データとログ ファイルの I/O 統計を返します。この動的管理ビューは fn_virtualfilestats 関数に代わるものです。」と説明されています。
この DMV では、database_id および file_id という 2 つの引数を受け取ります。どちらか一方に NULL を指定できます。database_id に NULL を設定した場合は、すべてのデータベースに関する情報を返し、file_id に NULL を設定した場合は、すべてのファイルに関する情報を返します。
この DMV では、情報が累積されることに注意してください。つまり、サーバーが最後に再起動した時点から、データ列の値が増え続けます。これは、ベースラインの測定の後に、実測が必要であることを意味しています。累積した情報から 2 つの情報を差し引くことで、I/O がどこで累積しているかを特定できます。
次のスクリプトを使用すると、特定の SQL Server のインスタンスで実行されているデータベースごとに、データとログ ファイルに対する読み取りと書き込みの回数を確認できます。これは平均 I/O 停止時間 (ミリ秒単位) で並べ替えられています。
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
上記のクエリを使用すると、ディスク I/O の待機時間が最も長いファイルを特定できます。この情報は利用可能なディスク リソースに基づいて、個々のファイルを配置する場所を決めるのに役立ちます。また、特定のファイルによって SQL Server でディスクのボトルネックが発生していることを SAN エンジニアなどに説明するときにも役立ちます。
保留中の I/O からディスクのボトルネックを調べる
これはディスク I/O のボトルネックを調べるための少し違ったアプローチです。ここで使用する DMV は sys.dm_io_pending_io_requests です。これは SQL Server Books Online で「SQL Server で保留中の I/O 要求ごとに 1 行のデータを返します」と説明されています。
DMV のデータでは、システムで保留中の I/O 要求の "特定の時点" (スクリプトを実行した時点) のスナップショットを提供します。
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs.num_of_reads , fs.num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
このデータは処理の特定の時点のスナップショットなので、リストの上部に次々と同じファイル (および同じドライブ文字) が表示されるかどうか、このクエリを複数回実行して確かめる必要があります。この現象が起きたら、特定のファイルまたはドライブ文字が I/O のボトルネックになっている証拠になります。また、特定の LUN によってシステムで I/O の問題が発生していることを、SAN エンジニアに説明するときに、この情報を使用できます。
クエリの最後の 2 つの列では、SQL Server が起動してから (またはファイルが作成されてから (どちらか短い方)) 行われたファイルの読み取りと書き込みの累積回数を返します。この情報は特定のドライブ文字で使用する RAID レベルを決めるのに役立ちます。たとえば、通常、書き込み処理が多いファイルは、RAID 5 LUN よりも RAID 10 LUN の方がパフォーマンスが良くなります。
それぞれのファイルの相対的な読み取り/書き込みの比率を把握していることは、適切な LUN にデータベース ファイルを保存するのに役立ちます。また、これは効果的にクエリをチューニングすることにも役立ちます。
.png)
.png)
.png)
Glenn Berry は、米国コロラド州デンバーの NewsGator Technologies でデータベース アーキテクトを務めています。彼は SQL Server の MVP ですが、MCITP、MCDBA、MCSE、MCSD、MCAD、MCTS など全種類のマイクロソフト認定資格を保有しており、試験好きであることの証明になっています。
Louis Davidson は、データベースの社内開発者およびアーキテクトとして、IT 業界に 16 年間携わっています。彼は、SQL Server の MVP を 6 年連続で受賞しており、データベース設計についての書籍を 4 冊執筆しています。現在は、米国のバージニア州バージニア ビーチとテネシー州ナッシュビルにある企業をサポートする Christian Broadcasting Network のデータ アーキテクトとして働いていますが、データベース管理者として任務に当たることもあります。
Timothy Ford は、SQL Server の MVP で、SQL Server に 10 年以上携わっています。彼は、Spectrum Health の SQL Server プラットフォームにおける主力データベース管理者で、非常に詳しい知識を持っています。また、2007 年からさまざまな Web サイトでテクノロジについての記事を執筆しており、自身のブログ (thesqlagentman.com、英語) では、SQL Server、在宅勤務、および高度な開発のトピックを扱っています。
『SQL Server DMV Starter Pack』の詳細については、red-gate.com/our-company/about/book-store/ (英語) を参照してください。