【SQL Server】データベースファイルの配置

2021年10月13日2022年6月12日

当ページのリンクには広告が含まれています。

こんにちは、KiYOです。

システムエンジニア歴２０年オーバー。製造現場に特化したシステム開発を主にやってます。

新しい物好きで色々なIT技術を勉強しては、仕事に使って行ってます。

KiYO

今回は、Microsoft SQL Serverのデータベースファイルの配置について解説していきます。

データベースファイルの配置

データベースには、データファイルと、ログファイルの少なくとも２つのファイルがあります。

	ファイル	内容
１	プライマリデータファイル	データベースの起動情報が含まれています。ユーザデータおよびオブジェクトは、このファイルまたはセカンダリデータファイルに格納できます。各データベースには１つのプライマリデータファイルがあります。プライマリデータファイルに推奨されるファイルの拡張子は「.mdf」です。
２	セカンダリデータファイル	セカンダリデータファイルは省略可能です。各ファイルを異なるディスクドライブに配置して、データを分散することができます。推奨されるファイルの拡張子は「.ndf」です。
３	トランザクションログファイル	データベースの復旧に使用されるログ情報が格納されます。推奨されるファイル名の拡張子は「.ldf」です。

規定では、データファイルとトランザクションログファイルは同一のドライブおよびパス上に配置されます。

ディスクの書き込み分散でおすすめは、データファイルとトランザクションログファイルを異なる物理ディスクボリューム上に配置することをおすすめします。

論理ボリュームではダメです。物理ディスクを分けることが重要。

ページ

SQLServerのデータストレージの基本単位はページです。データベースのデータファイルに割り当てられたディスク領域には、０からnの連番がつけられたページに論理的に分割されます。

ディスクI/O操作はこのページレベルで実行されます。

ページの特徴

SQLServerデータベースには１MBあたりに１２８ページが存在します。例えば、同一ページ内に存在する１行と１００行を取得する場合のＩ／Ｏ数は同じになります。

そのため、取得するデータをなるべく同じページ内に含めることで、必要なＩ／Ｏ数を減らすことが可能になります。

エクステント

物理的に連続する８ページをまとめたもので、ページを効率的に管理するために使用されます。全てのページは、エクステントに格納されます。

ログファイルとデータファイルのディスクへのアクセス仕様は大きく異なります。ログファイルのＩ／Ｏは、主にシーケンシャルな書き込み操作で、ランダムなディスクアクセスはほとんど発生しません。

逆に、データファイルのI／Ｏは、非同期に実行される読み取りまたは、書き込み操作でランダムなディスクアクセスが多いです。

そのため、以下がベストプラクティスになります。

データファイルとログファイルの分散配置におけるベストプラクティス

ログファイルとデータファイルを分離する
パフォーマンスと生涯復旧の観点から、分離することが重要です。
各ファイルは物理ディスクレベルで分離する必要があります。
同じサイズの複数のデータファイルを、可能な限り多くの物理ディスクに分散する
複数のデータファイル間で、Ｉ／Ｏの並列処理数を増やすことができる
複数のログファイルを使用してもあまり意味がない
トランザクションログは常に単一ログファイルに書き込まれます。そのため、パフォーマンスの観点からは、複数のログファイルを構成しても意味がありません。

RAID（Redundant Array of Independent Disks）はアレイと呼ばれる複数のディスクドライブから構成されるディスクシステムです。RAIDを使用することで、高性能で信頼性が高いシステムを構築できるようになります。

RAIDはSQLServerの一部ではないですが、パフォーマンスやフォールトトレランスに影響します。

特徴

RAID0(ストライピング）
データはブロックに分割され、アレイ内の全てのディスク間で決まった順序で分散されます。読み取り／書き込みのパフォーマンスが向上します。フォールトトレランスは準備されていません。
RAID1（ミラーリング）
プライマリディスクに書き込まれたデータは全て、ミラーディスクに書き込まれます。
フォールトトレランスを実現し、読み取りパフォーマンスが向上しますが、逆に書き込みパフォーマンスは低下することもあります。
RAID5（パリティ付きストライピング）
RAID10（ストライピング付きミラー化）

リンク

Udemyというオンライン講座でもデータベースの学習はできます。　再生速度を早めてみたり、何度も見返すことができるのでおすすめです!　私も色々なデータベースをここで学習しています。

インデックスについて詳しく解説してくれて、理解しやすいと思います。

その他のパフォーマンスチューニングはこちらをご覧ください。

ITの事や自分の経験談など綴っていきたいと思っています。