Skip to content

MS_SQLServerFileGroup

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server のファイル・グループ

概要

「ファイル グループ」（Oracle では「表領域」と呼ぶ）を作成し、
「ファイル グループ」毎にファイルの物理的な配置位置を選択することができる。

  • 構成

    • 「データ ファイル」を、

      • 1 つの「プライマリ データ ファイル」と
      • 複数の「セカンダリ ファイル」に

      分割することができる。

    • また、「ファイル グループ」を作成し、
      「ファイル グループ」毎にファイルの配置位置を選択することができる。

      • デフォルトで「プライマリ ファイル グループ」が存在する。
      • これに「ユーザ定義ファイル グループ」を追加することで、
        複数のパスに「データ ファイル（テーブル、インデックス）」を
        分割して作成することができるようになる。

  • 効果

    • 簡易 RAID（MS_RAID.md）（ストライピングに相当）
    • また、部分的なバックアップ・リストア、段階的なリストアなど、運用系の時間短縮も可能。

「ファイル グループ」による性能の向上

SQL Server 2000 では、ファイル グループを使用することで
マシン リソースをフル活用して処理を実行する

  • 「簡易ストライピング」と、
  • 「並列クエリ」が

サポートされていたが、

SQL Server 2005 から、より高度な機能として

がサポートされた。

これらの機能も、内部的には「ファイル グループ」のメカニズムを使用している。

簡易ストライピング

「ファイル グループ」を使用して、「データ ファイル」、
「トランザクション ログ ファイル」を複数のファイルに分割し、
これを独自の「ディスク コントローラ」を持つディスクに配置する。
この場合、クエリは複数ディスクにわたって展開されるため、
性能が向上する（読み込み、書き込みともに性能が向上する）。

SQL Server のファイル・グループ

※　RAID0 のデバイスに 1 つのファイルを作成した場合と同じ効果がある。

移行メモ（正誤）: 「トランザクション ログ ファイル」は
ファイル グループに追加できない（本ページ後半の「規則」に明記されている）。
また、ログ ファイルを複数作っても並列には書かれず順番に使われるだけなので、
ストライピングによる性能向上は得られない。
ここでのストライピングの効果はデータ ファイルに対するものである。

補足（現在も有効か）: RAID コントローラや SAN、
クラウドのマネージド ディスクでストライピングが行われる現在では、
I/O 分散を目的としたファイル グループ分割の必要性は下がっている
（本ページの「推奨」にも「RAID コントローラを用いてストライピング可能であれば不要」
と書かれているとおり）。
現在の主目的は、

  • 段階的リストア（重要な業務データだけ先に復旧する）
  • パーティション分割との組み合わせによる保守性
  • 読み取り専用ファイル グループによるアーカイブ データの保護

といった運用・保守面にある。

データ ファイルの充填

  • テーブルにデータを追加する場合、

    • 「ファイル グループ」内の最初のファイルが一杯になるまで
      ファイルにデータを追加するのではなく、
    • 「ファイル グループ」内のすべてのファイルを対象に、
      空き領域に比例したエクステントを割り当て、そこに、データを追加するという動きになる。

  • 例えば、

    • 「ファイル グループ」が、「ファイル1」と「ファイル2」から構成されるとする。
    • 「ファイル1」の空き領域が 100 MB で、「ファイル2」の空き領域が 200 MB の場合、
    • 「ファイル1」のエクステント 1 つと「ファイル2」のエクステント 2 つが割り当てられる。
    • これにより、「ファイル1」と「ファイル2」は、ほぼ同時に一杯になる。

補足（比例塗りつぶし）: この動作を**比例塗りつぶし（proportional fill）**と呼ぶ。
ファイルのサイズと自動拡張量を揃えておかないと I/O が偏るため、
同一ファイル グループ内のファイルは同じ構成にするのが鉄則である
（tempdb で本数を揃える話と同じ理屈。
SQL Server のファイルの配置参照）。

データ ファイルの拡張

データベースが自動拡張されるように設定されている場合、
「ファイル グループ」内のすべてのファイルが一杯になると、
ファイルが 1 つずつラウンドロビン方式で自動拡張される。

補足（最新化：AUTOGROW_ALL_FILES）: SQL Server 2016 以降は、
ファイル グループの設定で
ALTER DATABASE ... MODIFY FILEGROUP ... AUTOGROW_ALL_FILES を指定でき、
全ファイルを同時に拡張できる（旧来のトレース フラグ 1117 相当）。
ラウンドロビン拡張だとファイル サイズが不揃いになり、
比例塗りつぶしで偏りが生じるため、tempdb では既定で有効になっている。

並列クエリ

  • ファイル・グループ（ファイル）毎に I/O スレッドを起動、

  • これによって、

    • I/O スレッドを増大させることによって
      初回検索時の大幅な性能向上が期待できるが、これは、基本的に
      • 適切に設計された OLTP 向けの性能向上を期待するものではない（OLAP 向け）。
      • I/O ネックにならない様にファイル・グループや RAID で
        I/O 性能を向上させておく必要がある。

  • なお、バックアップ・リストアについては、
    バックアップ デバイス数と同数のスレッドが使用される。

並列クエリ

次数

並列クエリの次数に影響を与えるオプション・"しきい値" は、公開されていない。

なお、複数のユーザが、クエリを実行する場合、
プランの次数は、その処理開始時点の各 CPU の処理量を考慮する。

移行メモ（正誤・最新化）: 「オプション・しきい値は公開されていない」は
当時の記述だが、実際には公開された構成オプションで制御できる。

設定 内容
max degree of parallelism（MAXDOP） 1 クエリが使用できる並列度の上限。1 で並列実行を無効化
cost threshold for parallelism このコストを超える見積りのクエリだけ並列化を検討する

cost threshold for parallelism の既定値は 5 で、
これは 1990 年代のハードウェアを基準にした値。
現在は 25〜50 程度に引き上げるのが一般的な推奨で、
小さいクエリが無用に並列化されて CXPACKET / CXCONSUMER 待ちを
発生させるのを防げる。

MAXDOP は SQL Server 2016 以降、

  • ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = n（DB 単位）
  • クエリ ヒント OPTION (MAXDOP n)（文単位）

でも指定でき、インストール時に NUMA 構成から推奨値が自動設定される。

なお、並列度はファイル グループの数では決まらない。
並列度を決めるのは CPU（スケジューラ）数と上記の設定であり、
ファイル グループは I/O の分散に寄与するものである。

参考

構成要素

「ファイル グループ」の構成要素

「ファイル グループ」には、

  • 「プライマリ ファイル グループ」
  • 「ユーザ定義ファイル グループ」がある。

「プライマリ ファイル グループ」

  • 「プライマリ ファイル グループ」には、

    • 「プライマリ データ ファイル」と、
    • 他の「ファイル グループ」に割り当てられていない
      「プライマリ ファイル グループ」上の全てのファイル

    が含まれる。

  • システム DB のテーブル用のすべてのファイルは
    「プライマリ ファイル グループ」に割り当てられている。

「ユーザ定義ファイル グループ」

「ユーザ定義ファイル グループ」は、CREATE DATABASE または
ALTER DATABASE ステートメント内で FILEGROUP キーワードを使用して作成できる。

「データ ファイル」の構成要素

「テーブル」・「インデックス」

「テーブル」・「インデックス」は「データ ファイル」に含まれる。

「プライマリ データ ファイル」

  • 「プライマリ データ ファイル」は「データ ファイル」の開始点で、
    DB 内のほかのファイルを指し示す。

  • 1 つの DB に 1 つ「プライマリ データ ファイル」が必要。

  • 拡張子は mdf が推奨される。

「セカンダリ データ ファイル」

  • 「セカンダリ データ ファイル」は
    「プライマリ データ ファイル」以外のすべての「データ ファイル」である。

  • データベースには、

    • 「セカンダリ データ ファイル」がない場合と、
    • 複数の「セカンダリ データ ファイル」がある場合がある。

  • 拡張子は ndf が推奨される。

「トランザクション ログ ファイル」

「トランザクション ログ ファイル」は「ファイル グループ」に追加できない。

  • 「データ ファイル」と同様に複数のファイルを作成できる。

  • 「データ ファイル」のような「プライマリ」、「セカンダリ」を区別しない。

  • 拡張子は ldf が推奨される。

規則と推奨

規則

  • 1 つの「ファイル グループ」を複数のデータベースで使用できない。
    「ファイル グループ」は、1 つの「データベース スキーマ」内に定義するため。

  • 1 つの「データ ファイル」は 1 つの「ファイル グループ」にしか所属できない。
    「データ ファイル」は、1 つの「ファイル グループ」内に定義するため。

  • 「トランザクション ログ ファイル」を「ファイル グループ」に格納できない。
    「トランザクション ログ ファイル」は「データ ファイル」と扱いが異なるため。

推奨

作成のポイント

大部分のデータベースは、通常、

  • 1 つの「データ ファイル」と
  • 1 つの「トランザクション ログ ファイル」で

正常に機能するが、必要に応じて、

以下の「ファイル グループ」の導入を検討する。

  • 「データ ファイル」を複数のファイルに分割する場合は、

    • プライマリの「ファイル グループ」の他に、
    • 追加ファイル用のセカンダリの「ファイル グループ」を作成し、

    これを既定の「ファイル グループ」に設定する。

  • これにより、

    • プライマリの「ファイル グループ」にはシステム オブジェクトだけが格納される。
      ストアド プロシージャ、拡張ストアド プロシージャ、関数、ビューなど。
    • セカンダリの「ファイル グループ」にはユーザ オブジェクトだけが格納される。
      テーブル、インデックス、列、別名データ型、
      .NET Framework 共通言語ランタイム (CLR) ユーザ定義型など。

この分割方法は、「段階的リストア」や
「SQL Server パーティション分割」で活用できる。

性能向上のポイント

  • 特定の物理ディスク上にデータ ファイル（テーブル・インデックス）を配置できる。
    これにより、負荷の高い特定のテーブルを
    ディスク I/O 性能の高い特定の物理ディスクにマップできる。

  • 「トランザクション ログ ファイル」は、ディスク I/O 性能を考慮して、
    「データ ファイル」と同じ物理ディスク上に配置しない（分散することが望ましい）。

  • 「ファイル グループ」により、物理ディスクにファイルを分散させることで、
    簡易ストライピングによるディスク I/O 性能の向上が期待できる。

    • ただし、RAID コントローラを用いてストライピング可能であれば不要。

    • アクセス頻度が高いテーブルと、そのテーブルに属する「非クラスタ化インデックス」を、
      別の「ファイル グループ」にマップし、ディスク I/O を分散するという方法を採ることもできる。

    • 同じ結合クエリで使用する各テーブルは
      別の「ファイル グループ」にマップすることにより、
      ディスク I/O を分散して処理できる。

保守性の向上

大きな記憶域を占有する可能性のあるオブジェクトは、
別々の「ファイル グループ」に配置することで「段階的リストア」による
柔軟なデータのバックアップ・リストアなど、メンテナンス操作に関してメリットがある。

例

次の例では、

DB を作成（CREATE DATABASE ステートメント）する際に

  • 「プライマリ ファイル グループ」と
  • 「ユーザ定義ファイル グループ」を

作成し、

  • 1 つの「プライマリ データ ファイル」を「プライマリ ファイル グループ」
  • 2 つの「セカンダリ データ ファイル」を「ユーザ定義ファイル グループ」

に設定、

  • 「ユーザ定義ファイル グループ」をデフォルトの「ファイル グループ」に設定している。
  • その後、「ユーザ定義ファイル グループ」を指定してテーブル（または、インデックス）を作成する。
USE master
GO
-- Create the database with the default data filegroup and the log file.
-- Specify the growth increment and the max size for the primary data file.
CREATE DATABASE MyDB -- DBの「プライマリ データ ファイル」を「プライマリ ファイル グループ」に配置
ON PRIMARY
  ( NAME='MyDB_Primary',
   FILENAME=
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Prm.mdf',
   SIZE=4,
   MAXSIZE=10,
   FILEGROWTH=1),
FILEGROUP MyDB_FG1 -- DBの「セカンダリ データ ファイル」を「ユーザ定義ファイル グループ」に配置
  ( NAME = 'MyDB_FG1_Dat1',
   FILENAME =
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
   SIZE = 1MB,
   MAXSIZE=10,
   FILEGROWTH=1),
  ( NAME = 'MyDB_FG1_Dat2',
   FILENAME =
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
   SIZE = 1MB,
   MAXSIZE=10,
   FILEGROWTH=1)
LOG ON -- DBの「トランザクション ログ ファイル」を「任意のパス」に配置
  ( NAME='MyDB_log',
   FILENAME =
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB.ldf',
   SIZE=1,
   MAXSIZE=10,
   FILEGROWTH=1)
GO
ALTER DATABASE MyDB -- 「ユーザ定義ファイル グループ」をデフォルトの「ファイル グループ」に設定
MODIFY FILEGROUP MyDB_FG1 DEFAULT
GO

-- Create a table in the user-defined filegroup.
-- このテーブルは、「ユーザ定義ファイル グループ」に作成される。
USE MyDB
CREATE TABLE MyTable
  ( cola      int   PRIMARY KEY,
   colb      char(8) )
ON MyDB_FG1
GO

移行メモ（正誤）: 元ページのスクリプトは FILE NAME（スペースあり）に
なっていたが、正しくは FILENAME（スペースなし）である。修正して掲載した。
また、SIZE=4 / MAXSIZE=10 は単位省略時 MB と解釈される。
実運用では、この程度のサイズだと即座に自動拡張が走るため、
あくまで構文のサンプルとして読むこと。

参考

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally