SQL Server のファイル・グループのバックアップ(No.4)

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

↑

データファイルの拡張 †

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

↑

並列クエリ †

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

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

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

↑

次数 †

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

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

↑

参考 †

並列クエリの例
https://msdn.microsoft.com/ja-jp/library/ms175097.aspx
並列処理の次数
https://msdn.microsoft.com/ja-jp/library/ms188611.aspx

↑

構成要素 †

↑

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

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

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

↑

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

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

が含まれる。

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

↑

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

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

↑

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

↑

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

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

↑

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

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

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

↑

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

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

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

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

↑

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

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

「データファイル」と同様に複数のファイルを作成できる。
「データファイル」のような「プライマリ」、「セカンダリ」を区別しない。

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

↑

規則と推奨 †

↑

規則 †

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

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

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

↑

推奨 †

↑

作成のポイント †

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

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

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

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

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

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

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

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

↑

性能向上のポイント †

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

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

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

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

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

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

↑

保守性の向上 †

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

↑

例 †

次の例では、

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

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

作成し、

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

に設定、

「ユーザ定義ファイルグループ」をデフォルトの「ファイルグループ」に設定している。
その後、「ユーザ定義ファイルグループ」を指定してテーブル（または、インデックス）を作成する。

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

↑

参考 †

MSDN - Microsoft
- データベースファイルとファイルグループ
  https://msdn.microsoft.com/ja-jp/library/ms189563.aspx
- ファイルとファイルグループのアーキテクチャ
  https://technet.microsoft.com/ja-jp/library/ms179316.aspx

SQL Server のデータベースの構成について改めて勉強その 1 at SE の雑記

SQL Server のファイル・グループ のバックアップ(No.4)

目次 †

概要 †

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

簡易ストライピング †

データ ファイルの充填 †

データ ファイルの拡張 †

並列クエリ †

次数 †

参考 †

構成要素 †

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

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

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

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

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

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

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

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

規則と推奨 †

規則 †

推奨 †

作成のポイント †

性能向上のポイント †

保守性の向上 †

例 †

参考 †

SQL Server のファイル・グループのバックアップ(No.4)

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

データファイルの充填 †

データファイルの拡張 †

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

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

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

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

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

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

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