マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

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

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

分割することができる。

  • また、「ファイル グループ」を作成し、
    「ファイル グループ」毎にファイルの配置位置を選択することができる。
  • デフォルトで「プライマリ ファイル グループ」が存在する。
  • これに「ユーザ定義ファイル グループ」を追加することで、
    複数のパスに「データ ファイル(テーブル、インデックス)」を分割して作成することができるようになる。
  • 効果
    • 簡易RAID(ストライピングに相当)
    • また、部分的なバックアップ・リストア、段階的なリストアなど、運用系の時間短縮も可能。

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

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

サポートされていたが、

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

がサポートされた。

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

簡易ストライピング

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

SQL Server のファイルの配置

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

データ ファイルの充填

  • テーブルにデータを追加する場合、
    • 「ファイル グループ」内の最初のファイルが一杯になるまでファイルにデータを追加するのではなく、
    • 「ファイル グループ」内のすべてのファイルを対象に、
      空き領域に比例したエクステントを割り当て、そこに、データを追加するという動きになる。
  • 例えば、
    • 「ファイル グループ」が、「ファイル1」と「ファイル2」から構成されるとする。
    • 「ファイル1」の空き領域が100 MBで、「ファイル2」の空き領域が200 MBの場合、
    • 「ファイル1」のエクステント1つと「ファイル2」のエクステント2つが割り当てられる。
    • これにより、「ファイル1」と「ファイル2」は、ほぼ同時に一杯になる。

データ ファイルの拡張

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

並列クエリ

  • ファイル・グループ(ファイル)毎にI/Oスレッドを起動、
  • これによって、
    • I/Oスレッドを増大させることによって
      初回検索時の大幅な性能向上が期待できるが、これは、基本的に
      • 適切に設計されたOLTP向けの性能向上を期待するものではない(OLAP向け)。
      • I/Oネックにならない様にファイル・グループやRAIDでI/O性能を向上させておく必要がある。
  • なお、バックアップ・リストアについては、
    バックアップ デバイス数と同数のスレッドが使用される。
並列クエリ

次数

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

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

参考

構成要素

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

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

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

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

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

が含まれる。

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

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

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

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

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

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

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

  • 「プライマリ データ ファイル」は「データ ファイル」の開始点で、DB内のほかのファイルを指し示す。
  • 1 つのDBに 1 つ「プライマリ データ ファイル」が必要。
  • 拡張子は「mdf」が推奨される。

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

  • 「セカンダリ データ ファイル」は「プライマリ データ ファイル」以外のすべての「データ ファイル」である。
  • データベースには、
    • 「セカンダリ データ ファイル」がない場合と、
    • 複数の「セカンダリ データ ファイル」がある場合がある。
  • 拡張子は「ndf」が推奨される。

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

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

  • 「データ ファイル」と同様に複数のファイルを作成できる。
  • 「データ ファイル」のような「プライマリ」、「セカンダリ」を区別しない。
  • 拡張子は「ldf」が推奨される。

規則と推奨

規則

  • 1つの「ファイル グループ」を複数のデータベースで使用できない。
    「ファイル グループ」は、1つの「データベース スキーマ」内に定義するため。
  • 1つの「データ ファイル」は1つの「ファイル グループ」にしか所属できない。
    「データ ファイル」は、1つの「ファイル グループ」内に定義するため。
  • 「トランザクション ログ ファイル」を「ファイル グループ」に格納できない。
    「トランザクション ログ ファイル」は「データ ファイル」と扱いが異なるため。

推奨

作成のポイント

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

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

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

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

  • 「データ ファイル」を複数のファイルに分割する場合は、
    • プライマリの「ファイル グループ」の他に、
    • 追加ファイル用のセカンダリの「ファイル グループ」を作成し、

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

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

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

性能向上のポイント

  • 特定の物理ディスク上にデータ ファイル(テーブル・インデックス)を配置できる。
    これにより、負荷の高い特定のテーブルをディスク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',
   FILE NAME=
      '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',
   FILE NAME =
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
   SIZE = 1MB,
   MAXSIZE=10,
   FILEGROWTH=1),
  ( NAME = 'MyDB_FG1_Dat2',
   FILE NAME =
      'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
   SIZE = 1MB,
   MAXSIZE=10,
   FILEGROWTH=1)
LOG ON -- DBの「トランザクション ログ ファイル」を「任意のパス」に配置
  ( NAME='MyDB_log',
   FILE NAME =
      '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

参考


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


添付ファイル: fileParallelQuery.png 312件 [詳細] fileFilegroup.png 333件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-03-09 (木) 09:19:37 (896d)