SQL Server I/O の基礎

適用対象:SQL ServerAzure SQL Managed InstanceAzure Virtual Machines 上の SQL Server

SQL Server データベースの主な目的はデータの格納と取得であるため、頻繁なディスク入出力 (I/O) はデータベース エンジンの基本的な特性です。 ディスク I/O 操作は多くのリソースを消費するうえ、完了するのに比較的長い時間がかかるため、SQL Server では I/O の効率を上げることに重点を置いています。

SQL Server のストレージ サブシステムは、メカニカル ドライブやソリッド ステート ストレージなど、複数のフォーム ファクターで提供されます。 この記事では、ドライブ キャッシュの原理を使用して、データベース エンジン I/O を改善する方法について詳しく説明します。

SQL Server では、SQL Server I/O 信頼性プログラムの要件に従い、安定したメディアへの確実なデータ書き込みがシステムでサポートされている必要があります。 SQL Server データベース エンジンの入力と出力の要件の詳細については、「SQL Server データベース エンジン ディスク入出力 (I/O) の要件」を参照してください。

ディスク I/O

バッファー マネージャーはデータベースの読み取りと書き込みだけを行います。 他のファイル操作やデータベース操作 (開く、閉じる、拡張、圧縮など) は、データベース マネージャー コンポーネントおよびファイル マネージャー コンポーネントによって実行されます。

バッファー マネージャーによるディスク I/O 操作には、次の特性があります。

• I/O は、通常、非同期で実行されます。つまり、呼び出し側スレッドでの処理中でも、I/O 操作はバックグラウンドで進行します。 状況によっては (ログ I/O のアラインメントが不適切な場合など)、同期 I/O が発生することがあります。

• すべての I/O は、affinity I/O オプションが使用されていない限り、呼び出し元のスレッドで発行されます。 affinity I/O mask オプションでは、 SQL Server のディスク I/O が、指定した CPU のサブセットに関連付けられます。 ハイエンドな SQL Server オンライン トランザクション処理 (OLTP) 環境では、この拡張機能により、I/O を発行する SQL Server スレッドのパフォーマンスを向上できます。

• 複数ページの I/O は、スキャッター/ギャザー I/O を使用して実行されます。スキャッター/ギャザー I/O を使用すると、連続しないメモリ領域との間でデータを転送できます。 つまり、SQL Server は、複数の物理 I/O 要求を回避しながら、バッファー キャッシュをすばやく使用またはフラッシュできます。

実行時間の長い I/O 要求

バッファー マネージャーは未処理状態が 15 秒以上続いた I/O 要求を報告します。 このプロセスは、システム管理者が SQL Server の問題と I/O サブシステムの問題を区別するのに役立ちます。 SQL Server のエラー ログには、次のようなエラー メッセージ MSSQLSERVER_833 が報告および記録されます。

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

長い I/O には、読み取りまたは書き込みのいずれかを指定できます。メッセージは現在、どちらを示していません。 実行時間の長い I/O のメッセージは、警告であってエラーではありません。 それらのメッセージは SQL Server の問題ではなく、基盤となる I/O システムの問題を示しています。 このメッセージは、システム管理者が SQL Server の応答時間の低下の原因をより迅速に見つけ出し、SQL Server の制御外にある問題を区別するのに役立ちます。 このように、メッセージに対するアクションは不要ですが、システム管理者は I/O 要求が長時間かかっている理由や、かかっている時間が正当であるかどうかを調べる必要があります。

実行時間の長い I/O 要求の原因

長い I/O メッセージは、I/O が完全にブロックされ、完了しない (失われた I/O と呼ばれます) ことを示したり、まだ完了していないことを示したりする可能性があります。 I/O が失われるとラッチ タイムアウトが発生することがよくありますが、どのシナリオが当てはまるかをメッセージから確認することはできません。

長い I/O は、多くの場合、ディスク サブシステムにとって負荷が高すぎる SQL Server ワークロードを示します。 次の場合は、ディスク サブシステムが不十分な可能性があります。

• 負荷の高い SQL Server ワークロード中に、実行時間の長い I/O のメッセージがエラー ログに複数記録される。
• パフォーマンス モニターのカウンターに、長時間のディスク遅延、ディスク キューが長いこと、またはディスクのアイドル時間がないことが表示される。

I/O パス内のコンポーネント (ドライバー、コントローラー、ファームウェアなど) では、新しい要求の処理を優先して、古い I/O 要求のサービスを継続的に延期することで、長い I/O が発生する可能性があります。 この問題は、 iSCSI ネットワークやファイバー チャネル ネットワークなどの相互接続された環境で発生する可能性があります (構成ミスまたはパス障害が原因)。 パフォーマンス モニター ツールを使用すると、ほとんどの I/O が迅速に処理されるため、この問題の確認が困難になる可能性があります。 バックアップと復元、テーブル スキャン、並べ替え、インデックスの作成、一括読み込み、ファイルのゼロアウトなど、大量の順次 I/O を実行するワークロードは、長い I/O 要求を悪化させる可能性があります。

実行時間の長い I/O のうち、これまでの条件に該当しないと考えられる孤立した I/O は、ハードウェアやドライバーの問題が原因である可能性があります。 システム イベント ログに、問題の診断に役立つ関連イベントが記録されていないか確認してください。

非効率的なクエリやフィルター ドライバーに起因する I/O パフォーマンスの問題

低速な I/O は、効率的に書き込まれていないクエリや、インデックスと統計を使用して適切にチューニングされていないクエリが原因で発生する可能性があります。 I/O 待機時間のもう 1 つの一般的な要因として、データベース ファイルをスキャンするウイルス対策ソフトやその他のセキュリティ プログラムの存在が挙げられます。 このスキャン ソフトウェアがネットワーク層にまで及ぶ場合、ネットワーク待機時間が増加し、間接的にデータベースの待機時間にも影響を及ぼすことがあります。 15 秒の I/O について説明したシナリオは、ハードウェア コンポーネントではより一般的ですが、最適化されていないクエリや誤って構成されたウイルス対策プログラムでは、I/O の遅延が短いほど頻繁に観察されます。

これらの問題に対処する方法についての詳細は、「I/O 問題が SQL Server のパフォーマンス低下を引き起こす問題のトラブルシューティング」を参照してください。

SQL Server でウイルス対策保護を構成する方法については、「SQL Server で動作するようにウイルス対策ソフトウェアを構成する」を参照してください。

ストレージ コントローラーでの書き込みキャッシュ

キャッシュを使用しない I/O 転送は、ハード ドライブのスピン レート、ドライブ ヘッドの移動に必要な機械的時間、およびその他の制限要因により、機械的ドライブにはるかに長い時間がかかる場合があります。 SQL Server のインストールは、キャッシュ コントローラーを提供するシステムを対象としています。 これらのコントローラーはディスク上のキャッシュを無効にし、安定したメディア キャッシュを提供して、SQL Server の I/O 要件を満たします。 またキャッシュ コントローラーのさまざまな最適化機能を活用することで、ストレージのシーク時間や書き込み時間に関連するパフォーマンスの問題を回避します。

書き込みキャッシュ (別名ライトバック キャッシュ) ストレージ コントローラを使用すると、SQL Server のパフォーマンスを向上させることができます。 書き込みキャッシュ コントローラーとストレージ サブシステムは、データ クリティカルなトランザクション データベース管理システム (DBMS) 環境で使用するように設計されている場合、SQL Server にとって安全です。 これらの機能は、設計上、システム障害が発生した場合にキャッシュ データを保持できる必要があります。 外部無停電電源装置 (UPS) を使用してこの保護を実現するだけでは一般に不十分です。これは、電源に関係のない障害モードが発生する可能性があるためです。

キャッシュ コントローラーとストレージ サブシステムは、SQL Server で安全に使用できるものもあります。 これらのコントローラーを組み込むほとんどの新しい専用サーバー プラットフォームは安全です。 ただし、データ クリティカルなトランザクション リレーショナル データベース管理システム (RDBMS) 環境でストレージ サブシステムがテストされ、使用が承認されていることをハードウェア ベンダーに確認する必要があります。

キャッシュ サブシステムの安全性に関するガイドライン

ライトバック キャッシュ コントローラーは、特定の安全要件を満たしている場合にパフォーマンスを向上させることができます。

• バッテリベースのキャッシュまたは不揮発性メモリ (NVDIMM やスーパーコンデンサベースのフラッシュなど) を含めます。
• データクリティカルな OLTP データベース環境についてベンダーから認定を受ける。
• 電源損失だけでなく、すべての障害状態をカバーする保護を提供します。

先行書き込みログ

SQL Server データ変更ステートメントは、論理ページの書き込みを生成します。 この書き込みストリームは、ログとデータベース自体の 2 つの場所に移動すると見なすことができます。 パフォーマンス上の理由から、SQL Server は独自のキャッシュ バッファー システムを介してデータベースへの書き込みを延期します。 システムは、 COMMIT 時間までログへの書き込みを一時的に延期するだけです。 これらの書き込みをデータ書き込みと同じ方法でキャッシュすることはありません。 特定のページのログ書き込みは常にページのデータ書き込み前に行われるため、ログは 先書きログ (WAL) と呼ばれることもあります。

先行書き込みログ (WAL) プロトコル

プロトコルは、WAL を表現するのに非常に適した用語です。 SQL Server で使用されている WAL は、ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) と呼ばれています。 詳しくは、「高速データベース復旧の管理」をご覧ください。

これは、データを正しく保存・交換し、障害発生時に既知の状態に復元できるようにするために必要な、特定の定義された実装手順の集合です。 ネットワークにおいて一貫性と保護性のあるデータ交換を実現するために定義されたプロトコルがあるのと同様に、WAL もデータを保護するためのプロトコルを定義しています。 すべてのバージョンの SQL Server は、Win32 の CreateFile 関数を使用してログ ファイルとデータ ファイルを開きます。 このとき、dwFlagsAndAttributes メンバーには、SQL Server によって FILE_FLAG_WRITE_THROUGH オプションが指定されます。

FILE_FLAG_WRITE_THROUGH

SQL Server は、データベース ファイルを作成する際に FILE_FLAG_WRITE_THROUGH フラグを使用します。 このオプションは、途中のキャッシュを介さずにストレージへ直接書き込むようシステムに指示します。 システムは書き込み操作をキャッシュすることはできますが、遅延的にフラッシュすることはできません。 詳細については、「CreateFileA」を参照してください。

FILE_FLAG_WRITE_THROUGH オプションを使用すると、書き込み操作で正常な完了が返されたときに、データが安定したストレージに正しく格納されます。 この機能は、データの整合性を確保するために、Write-Ahead ログ (WAL) プロトコルの仕様に合わせて調整されます。 多くのストレージ デバイス (NVMe、PCIe、SATA、ATA、SCSI、IDE ベースなど) には、512 KB や 1 MB 以上のオンボード キャッシュが搭載されています。 ストレージ キャッシュは通常、バッテリを使用したソリューションではなく、コンデンサを使用しています。 そのため、電源再投入時やそれに類する障害時に書き込みを保証することはできません。 保証されるのはセクター単位の書き込み完了のみです。 ストレージ デバイスの大容量化が進む中で、キャッシュも大型化しており、障害時に失われる可能性のあるデータ量も増大しています。

Linux ディストリビューションによる FUA サポートと SQL Server へのその影響について詳しくは、「SQL Server On Linux: 強制ユニット アクセス (FUA) の内部構造」を参照してください。

トランザクションの整合性と SQL Server の回復

トランザクション整合性は、リレーショナル データベース システムにおける基本的な概念の 1 つです。 トランザクションは、完全に適用されるか、完全にロールバックされるアトミックな作業単位です。 SQL Server の先行書き込みトランザクション ログは、トランザクション整合性を実現するうえで不可欠なコンポーネントです。

あらゆるリレーショナル データベース システムは、トランザクション整合性と密接に関連する概念である、予期しないシステム障害からの回復にも対応する必要があります。 こうした障害は、現実のさまざまな非理想的な要因によって発生する可能性があります。 多くのデータベース管理システムでは、システム障害が発生すると、長時間にわたる人手による手動の回復処理が必要になることがあります。

これに対して、SQL Server の回復メカニズムは自動であり、人手を介さずに動作します。 たとえば、SQL Server がミッションクリティカルな実稼働アプリケーションをサポートしている状態で、瞬間的な電源変動によりシステム障害が発生したとします。 電源が回復すると、サーバー ハードウェアが再起動し、ネットワーク ソフトウェアが読み込んで初期化され、SQL Server が再起動します。 SQL Server は初期化時に、トランザクション ログ内のデータに基づいて自動的に回復処理を実行します。 この一連の処理はすべて、人手を介さずに行われます。 クライアント ワークステーションが再起動すると、ユーザーは入力した最後のトランザクションまで、存在するすべてのデータを見つけます。

SQL Server におけるトランザクション整合性と自動回復は、時間と労力を大幅に節約できる強力な機能です。 書き込みキャッシュ コントローラーがデータクリティカルなトランザクション DBMS 環境で使用するように適切に設計されていない場合、SQL Server が復旧する機能が損なわれる可能性があります。データベースが破損する可能性があります。 この問題は、コントローラーが SQL Server トランザクション ログの書き込みをインターセプトし、コントローラー ボード上のハードウェア キャッシュにバッファーするが、システム障害時にこれらの書き込まれたページを保持しない場合に発生する可能性があります。

ディスク上の書き込みキャッシュのリスク

ほとんどのストレージ デバイス キャッシュ コントローラーは、書き込みキャッシュを実行します。 書き込みキャッシュ関数を常に無効にすることはできません。

サーバーが UPS を使用している場合でも、デバイスはキャッシュされた書き込みのセキュリティを保証しません。 UPS では対処できないさまざまな種類のシステム障害が発生する可能性があります。 たとえば、メモリのパリティ エラー、オペレーティング システム (OS) のトラップ、システム リセットを引き起こすハードウェアの不具合などが、制御不能なシステム中断を引き起こすことがあります。 また、ハードウェアの書き込みキャッシュ内でメモリ障害が発生して、重要なログ情報が失われる場合もあります。

これとは別に、書き込みキャッシュ コントローラーに関連して、システムのシャットダウン時に問題が発生する可能性もあります。 構成の変更中に OS を 循環 させたり、システムを再起動したりすることは珍しくありません。 OS の推奨事項に従って、すべてのストレージ アクティビティが停止するまで待ってからシステムを再起動しても、コントローラー内にキャッシュされた書き込みが残っていることがあります。 When the Ctrl+Alt+Del キーの組み合わせを押した場合や、ハードウェアのリセット ボタンを押した場合、これらのキャッシュされた書き込みが破棄され、データベースが損傷する可能性があります。

ダーティ キャッシュ データを破棄する可能性のあるすべての原因を考慮に入れたハードウェア書き込みキャッシュを設計できるため、データベース サーバーで安全に使用できます。 これらの設計機能の一部には、キャッシュ コントローラーの制御されていないリセットを回避するための RST (リセット) バス信号のインターセプト、オンボード バッテリ バックアップ、ミラー化またはエラー チェックと修正 (ECC) メモリなどがあります。 データ損失を回避するために必要なこれらの機能やその他の機能が書き込みキャッシュに搭載されているかを、ハードウェア ベンダーに確認してください。

SQL Server でストレージ キャッシュを使用する

データベース システムの最も重要な機能は、予期しないシステム障害が発生した場合でも、データを正確に保存し、取得できるようにすることです。

システムは、現在の実行状態や複数のトランザクション、さまざまな障害点を考慮しながら、トランザクションの原子性と永続性を保証する必要があります。 このプロパティは、多くの場合、ACID (原子性、整合性、分離、および持続性) プロパティと呼ばれます。

このセクションでは、ストレージ キャッシュの影響について説明します。 キャッシュと代替障害モードの説明の詳細については、次の記事を参照してください。

• 86903 SQL Server とキャッシュ ディスク コントローラー
• SQL Server でのデータの信頼性を拡張するログ記録アルゴリズムとデータ ストレージ アルゴリズムの説明

また、次のアーカイブされたコンテンツも確認します。

• SQL Server 2000 I/O の基本
• SQL Server I/O の基本第 2 章

これら 2 つの記事の概念は、現在のバージョンの SQL Server に広く適用できます。

バッテリ バックアップ付きキャッシュ ソリューション

高度なキャッシュ コントローラー システムは、ディスク上のキャッシュを無効にし、機能的なバッテリ バックアップ付きキャッシュ ソリューションを提供します。 これらのキャッシュは、キャッシュ内のデータを数日間保持できるうえ、キャッシュ カードを別のコンピューターに移すことも可能です。 電源が正常に復旧すると、未書き込みデータがすべてフラッシュされた後、それ以降のデータ アクセスが許可されます。 これらのシステムの多くは、最適なパフォーマンスを得るために、読み取りキャッシュと書き込みキャッシュの割合を設定できます。 一部のシステムには、大きなメモリ記憶域が含まれています。 ハードウェア ベンダーによっては、数ギガバイトのキャッシュを搭載したハイエンドのバッテリ バックアップ付きドライブ キャッシュ システムを提供しています。 これらのシステムは、データベースのパフォーマンスを大幅に向上させることができます。 バッテリーを使用したキャッシュ ソリューションは、SQL Server が期待するデータの持続性と一貫性を提供します。

ストレージ サブシステムの実装

記憶域サブシステムは、さまざまな種類に存在します。 一般的な例として、RAID (独立ディスクの冗長アレイ) と SAN (記憶域ネットワーク) の 2 つがあります。 通常、これらのシステムでは SCSI ベースのドライブが使用されます。 次のセクションでは、ストレージに関する大まかな考慮事項について説明します。

SCSI、SAS、NVMe

SCSI、SAS、および NVMe ストレージ デバイスは、次の特徴を備えています。

• 通常、頑丈な用途向けに設計されています。
• 通常、複数ユーザーをサポートするサーバーへの実装を対象としています。
• 他の実装と比較して、通常は故障までの平均時間が優れています。
• 差し迫った障害を予測するうえで役立つ高度なヒューリスティック機能を搭載しています。

非 SCSI

IDE、ATA、SATA などのその他のドライブ実装は、次の特徴があります。

• 通常、軽量から中規模の用途向けに設計されています。
• 通常は、シングル ユーザー アプリケーションを対象とします。

SCSI 以外のデスクトップ ベースのコントローラーでは、より多くのメイン プロセッサ (CPU) 帯域幅が必要であり、多くの場合、1 つのアクティブなコマンドによって制限されます。 たとえば、非 SCSI ドライブが不良ブロックを調整している場合、ホストのコマンドは待機を強いられます。 ATA バスも同様で、ATA バスは 2 台のデバイスをサポートしますが、アクティブにできるコマンドは 1 件のみです。 この制限により、一方のドライブはアイドル状態になりますが、もう一方のドライブは保留中のコマンドを処理します。 デスクトップ技術をベースに構築された RAID システムは、これらの問題の影響を受けやすく、最も応答が遅いドライブによって全体の性能が大きく左右されることがあります。 これらのシステムは、高度な設計を採用していない限り、SCSI ベースのシステムほど効率的な性能は発揮できません。

ストレージに関する考慮事項

デスクトップ ベースのドライブまたはアレイは、状況によっては低コストのソリューションになる場合があります。 たとえば、レポート用に読み取り専用データベースを設定した場合、ドライブのキャッシュが無効になっている場合、OLTP データベースのパフォーマンス要因の多くは発生しません。

ストレージ デバイスの容量は年々増加しています。 低コストで大容量のドライブが魅力的な場合があります。 ただし、SQL Server のドライブとビジネス応答時間のニーズを構成する場合は、次の問題を慎重に検討してください。

• アクセス パスの設計
• ディスク上のキャッシュを無効にする必要性

機械式ハード ドライブ

機械式ドライブは、回転する磁気プラッタを使ってデータを保存します。 IDE、SATA、SCSI、シリアル接続 SCSI (SAS) など、いくつかの容量とフォーム ファクターで使用できます。 中には、障害予測機能が組み込まれた SATA ドライブもあります。 SCSI ドライブは、高負荷な使用サイクルと故障率の低減を目的として設計されています。

IDE および ATA ベースのシステムは、不適切なブロック調整などのアクティビティを実行するときにホスト コマンドを延期でき、I/O アクティビティが停止する期間が発生します。

SAS の利点には、最大 256 レベルの高度なキューイング、ヘッドオブキューイングおよびアウトオブオーダーキューイングが含まれます。 SAS バックプレーンは、同一システム内で SAS ドライブと SATA ドライブの両方を使用できるように設計されています。

SQL Server のインストール環境では、ディスク上のキャッシュを無効にし、安定した I/O キャッシュを提供できるかどうかは、コントローラーの能力に依存します。 コントローラーが適切な安定メディア キャッシュ機能を備えている限り、複数のドライブに対して順不同でデータを書き込んでも、SQL Server に悪影響は生じません。 ミラーリングなどの高度なデータ保護技術を用いることで、コントローラーの設計はより複雑になります。

ソリッドステート ストレージ

ソリッドステート ストレージは、機械的 (回転) ハード ドライブよりも利点がありますが、回転メディアと同じ障害パターンの多くを受けやすくなります。 NVM Express (NVMe)、PCI Express (PCIe)、SATA など、さまざまなインターフェイスを使用して、ソリッドステート ストレージをサーバーに接続できます。 ソリッド ステート メディアは回転式メディアと同様に取り扱います。バッテリ バックアップ付きキャッシュ コントローラーなど、電源障害に対する適切な保護機能が備わっていることを確認してください。

電源障害によって発生する一般的な問題は次のとおりです。

• ビット破損: レコードはランダムビットエラーを示します。
• フライング ライト: レコードは正しく形成されているが、誤った場所に書き込まれます。
• ショーン ライト: 処理が、想定されるセクター サイズよりも下位レベルで、部分的に完了します。
• メタデータの破損: フラッシュ変換レイヤー (FTL) のメタデータが破損しています。
• デバイス無応答: デバイスがまったく機能しないか、ほとんど機能しません。
• 非直列化性: ストレージの最終状態が、直列化可能な操作順序の結果と合致しません。

512e

ほとんどのソリッドステート ストレージ デバイスでは、512 バイトのセクター サイズが報告されますが、1 MB の消去ブロック内では 4 KB のページが使用されます。 SQL Server のログ デバイスで 512 バイトに整列されたセクターを使用すると、読み取り/変更/書き込み (RMW) 処理が増加し、パフォーマンスの低下やドライブの摩耗につながる可能性があります。

推奨事項: キャッシュ コントローラーがストレージ デバイスの正しいページ サイズを認識し、ソリッド ステート ストレージ基盤に対して物理書き込みを適切に整列できることを確認してください。

0xFFFFFFFF

新しくフォーマットされたドライブには、通常すべてゼロが書き込まれています。 消去されたソリッドステートデバイスのブロックはすべて1であり、生で読み取ると消去されたブロックはすべて0xFF文字になります。 ただし、通常の I/O 操作中にユーザーが消去済みブロックを読み取ることはほとんどありません。

パターン スタンプ

以前に使用された手法は、ドライブ全体に既知のパターンを書き込む方法です。 その後、同じドライブに対してデータベース アクティビティが実行されると、パターンが予期せず表示された場合、不正確な動作（古い読み取りや書き込みの喪失、または正しくないオフセットの読み取り）が検出される可能性があります。

ただし、この手法はソリッド ステート ストレージではあまりうまく機能しません。 書き込みのための消去アクティビティと RMW アクティビティによってパターンが破棄されます。 ソリッドステート ストレージ ガベージ コレクション (GC) アクティビティ、摩耗平準化、プロポーショナル/セットアサイド リスト ブロック、およびその他の最適化では、スピン メディアのセクター再利用とは異なり、書き込みが異なる物理的な場所を取得する傾向があります。

ファームウェア

ソリッド ステート ストレージで使用されるファームウェアは、回転式メディアと比べて複雑になる傾向があります。 多くのドライブでは、受信リクエストやガベージ コレクション処理を行うために複数の処理コアが使用されています。 既知の問題を回避するために、ソリッド ステート デバイスのファームウェアは常に最新の状態に保ってください。

読み取りによるデータ損傷とウェア レベリング

ソリッド ステート ストレージにおける一般的なガベージ コレクション (GC) の手法は、読み取りを繰り返すことによるデータ損傷を防ぐのに役立ちます。 同じセルを繰り返し読み取ると、電子の動きによってリークが発生し、隣接セルに損傷を与える可能性があります。 ソリッド ステート ストレージは、複数レベルのエラー訂正コード (ECC) やその他のメカニズムによってデータを保護しています。

こうしたメカニズムの 1 つがウェア レベリングです。 ソリッド ステート ストレージは、デバイス上の読み取りおよび書き込みのアクティビティを追跡します。 ガベージ コレクションは、ホットスポットや他の領域よりも摩耗が進んでいる場所を検出できます。 たとえば、ブロックが読み取り専用の状態であり、移動する必要があると GC によって判断されます。 通常は、摩耗が進んでいる別のブロックに移動し、元のブロックを新たな書き込み用に空けます。 このプロセスはドライブの摩耗のバランスを取るのに役立ちますが、読み取り専用データは摩耗が多い場所に移動し、数学的に少しでも故障の可能性を高めます。

摩耗平準化のもう 1 つの副作用は、SQL Server で発生する可能性があります。 たとえば、DBCC CHECKDB を実行してエラーが報告されたとします。 その後、同じコマンドをもう一度実行すると、ソリッド ステート ストレージの GC アクティビティがDBCC CHECKDBその間に変更を加える可能性があるため、DBCC CHECKDB が追加のエラーや異なるパターンのエラーを報告する可能性があります。

OS エラー 665 とデフラグ

回転式メディアでは、ドライブのヘッド移動を最小限に抑えてパフォーマンスを向上させるため、ブロックを近接した位置に配置する必要があります。 ソリッドステート ストレージには物理ヘッドがないため、 シーク時間が不要になります。 多くのソリッドステート デバイスは、さまざまなブロックに対する並列操作を可能にするように設計されています。 そのため、ソリッドステートメディアのデフラグは不要です。 シリアルな処理が、ソリッド ステート ストレージ デバイスにおいて I/O スループットを最大化する最適なパターンです。

推奨事項:

• 書き込み処理を最適化するように設計された、適切なバッテリ バックアップ付きコントローラーを使用してください。 この選択により、パフォーマンスを向上させ、ドライブの摩耗を減らし、物理的な断片化レベルを下げます。

• NTFS の属性制限を回避するために、可能であれば ReFS ファイル システムを使用してください。

• ファイルの拡張設定が適切であることを確認します。

断片化に関連する OS エラー 665 のトラブルシューティングの詳細については、「SQL Server ファイルの OS エラー 665 と 1450 が報告される」を参照してください。

圧縮

ドライブが安定したメディアの意図を維持している限り、圧縮によってドライブの寿命が延び、パフォーマンスにプラスの影響を与える可能性があります。 ただし、ファームウェアによっては、データがすでに目に見えないかたちで圧縮されていることがあります。 運用環境にデプロイする前に、新しいストレージ シナリオを必ずテストしてください。

まとめ

• 適切なバックアップおよびディザスター リカバリーの手順とプロセスを維持する。
• ファームウェアを常に最新の状態に保つ。
• ハードウェアの製造元のガイダンスをよく聞いてください。

ドライブ キャッシュの構成

SQL Server でドライブを使用するには、ドライブキャッシュを無効にします。 既定では、ドライブ キャッシュは有効になっています。 Windows Server で、 ディスクのプロパティ>Hardware>Policy タブを使用して、OS レベルで書き込みキャッシュを無効にします。

OS レベルの設定だけに依存しないでください。 一部のドライブでは Windows の設定が無視され、書き込みキャッシュを無効にするために製造元が提供するユーティリティまたはファームウェア設定が必要です。 書き込みキャッシュが実際に無効になっていることをベンダー ツールで確認します。

キャッシュに関する考慮事項と SQLIOSim

トランザクション持続性の保証を確認するには、運用環境に移行する前に SQLIOSim を使用して I/O サブシステムを検証します。 このユーティリティは、シミュレートされたデータ デバイスとログ デバイスに対する大量の非同期読み取りおよび書き込みアクティビティをシミュレートします。 詳細については、「 SQLIOSim ユーティリティを使用してディスク サブシステム上の SQL Server アクティビティをシミュレートする」を参照してください。

多くの PC メーカーは、書き込みキャッシュを無効にした状態でドライブを出荷しています。 ただし、テストでは、この条件が常に当てはまるとは限らない可能性があるため、常に完全にテストする必要があります。 ストレージ デバイスのキャッシュ状態について不明な点がある場合は、製造元に問い合わせて、書き込みキャッシュ操作を無効にする適切なユーティリティを入手してください。 古いストレージ メディアでは、ジャンパー設定が必要な場合もあります。

SQL Server では、SQL Server I/O 信頼性プログラムの要件に従い、安定したメディアへの確実なデータ書き込みをシステムがサポートしている必要があります。 SQL Server データベース エンジンの入力と出力の要件の詳細については、「SQL Server データベース エンジン ディスク入出力 (I/O) の要件」を参照してください。

不適切な書き込みキャッシュのリスク

適切なセーフガードなしで書き込みキャッシュを有効にすると、一部のストレージ サブシステムは、データが永続的なメディアに安全に書き込まれる前に、書き込み操作を完了として確認します。 停電やシステム障害が発生した場合、次の結果が発生する可能性があります。

• コミットされたトランザクションが永続化されないデータ損失。
• 書き込み順序の保証が壊れているため、データベースが破損しました。

関連するコンテンツ

• ページとエクステントのアーキテクチャ ガイド
• メモリ管理アーキテクチャガイド
• ストレージ: Azure VM 上の SQL Server のパフォーマンスに関するベスト プラクティス
• SQL Server On Linux: 強制ユニット アクセス (FUA) の内部構造
• SQL Server I/O の基本、第 2 章