[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]] -[[戻る>SQL Server]] * 目次 [#hae0f628] #contents *概要 [#wd15b07e] 「ファイル グループ」(Oracleでは「表領域」と呼ぶ)を作成し、~ 「ファイル グループ」毎にファイルの物理的な配置位置を選択することができる。 -構成 --「データ ファイル」を、 ---1つの「プライマリ データ ファイル」と ---複数の「セカンダリ ファイル」に >分割することができる。 --また、「ファイル グループ」を作成し、~ 「ファイル グループ」毎にファイルの配置位置を選択することができる。 ---デフォルトで「プライマリ ファイル グループ」が存在する。 ---これに「ユーザ定義ファイル グループ」を追加することで、~ 複数のパスに「データ ファイル(テーブル、インデックス)」を分割して作成することができるようになる。 -効果 --簡易[[RAID]](ストライピングに相当) --また、部分的なバックアップ・リストア、段階的なリストアなど、運用系の時間短縮も可能。 *「ファイル グループ」による性能の向上 [#xdd38c8c] SQL Server 2000では、ファイル グループを使用することでマシン リソースをフル活用して処理を実行する -「[[簡易ストライピング>#le542327]]」と、 -「[[並列クエリ>#z5226928]]」が サポートされていたが、 SQL Server 2005から、より高度な機能として -「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」 -「[[パーティション分割>SQL Server パーティション分割]]」 がサポートされた。 これらの機能も、内部的には「ファイル グループ」のメカニズムを使用している。 **簡易ストライピング [#le542327] 「ファイル グループ」を使用して、「データ ファイル」、「トランザクション ログ ファイル」を複数のファイルに分割し、これを独自の「ディスク コントローラ」を持つディスクに配置する。~ この場合、クエリは複数ディスクにわたって展開されるため、性能が向上する(読み込み、書き込みともに性能が向上する)。 #ref(Filegroup.png,left,nowrap,SQL Server のファイルの配置) ※ RAID0のデバイスに1つのファイルを作成した場合と同じ効果がある。 -SQL Server 2000 チューニング全工程(2):~ 動的ディスク管理でのチューニングポイント (3/3) - @IT~ http://www.atmarkit.co.jp/ait/articles/0409/25/news011_3.html ***データ ファイルの充填 [#ue55a340] -テーブルにデータを追加する場合、 --「ファイル グループ」内の最初のファイルが一杯になるまでファイルにデータを追加するのではなく、 --「ファイル グループ」内のすべてのファイルを対象に、~ 空き領域に比例したエクステントを割り当て、そこに、データを追加するという動きになる。 -例えば、 --「ファイル グループ」が、「ファイル1」と「ファイル2」から構成されるとする。 --「ファイル1」の空き領域が100 MBで、「ファイル2」の空き領域が200 MBの場合、 --「ファイル1」のエクステント1つと「ファイル2」のエクステント2つが割り当てられる。 --これにより、「ファイル1」と「ファイル2」は、ほぼ同時に一杯になる。 ***データ ファイルの拡張 [#z6f890a5] データベースが自動拡張されるように設定されている場合、~ 「ファイル グループ」内のすべてのファイルが一杯になると、~ ファイルが1つずつラウンドロビン方式で自動拡張される。 **並列クエリ [#z5226928] -ファイル・グループ(ファイル)毎にI/Oスレッドを起動、 -これによって、 --I/Oスレッドを増大させることによって~ 初回検索時の大幅な性能向上が期待できるが、これは、基本的に ---適切に設計されたOLTP向けの性能向上を期待するものではない(OLAP向け)。 ---I/Oネックにならない様にファイル・グループやRAIDでI/O性能を向上させておく必要がある。 -なお、バックアップ・リストアについては、~ バックアップ デバイス数と同数のスレッドが使用される。 #ref(ParallelQuery.png,left,nowrap,並列クエリ) ***次数 [#x582f183] 並列クエリの次数に影響を与えるオプション・"しきい値"は、公開されていない。 なお、複数のユーザが、クエリを実行する場合、~ プランの次数は、その処理開始時点の各CPUの処理量を考慮する。 ***参考 [#h783e84d] -並列クエリの例~ https://msdn.microsoft.com/ja-jp/library/ms175097.aspx -並列処理の次数~ https://msdn.microsoft.com/ja-jp/library/ms188611.aspx *構成要素 [#oaef86ea] **「ファイル グループ」の構成要素 [#t91bbcd1] 「ファイル グループ」には、 -「プライマリ ファイル グループ」 -「ユーザ定義ファイル グループ」がある。 ***「プライマリ ファイル グループ」 [#j90b188c] -「プライマリ ファイル グループ」には、 --「プライマリ データ ファイル」と、 --他の「ファイルグループ」に割り当てられていない「プライマリ ファイル グループ」上の全てのファイル >が含まれる。 -システムDBの テーブル用のすべてのファイルは「プライマリ ファイル グループ」に割り当てられている。 ***「ユーザ定義ファイル グループ」 [#w8c86f01] 「ユーザ定義ファイル グループ」は、「CREATE DATABASE」または「ALTER DATABASE」ステートメント内で「FILEGROUP」キーワードを使用して作成できる。 **「データ ファイル」の構成要素 [#tbc98bab] ***「テーブル」・「インデックス」 [#a092a68d] 「テーブル」・「インデックス」は「データ ファイル」に含まれる。 ***「プライマリ データ ファイル」 [#jdccafad] -「プライマリ データ ファイル」は「データ ファイル」の開始点で、DB内のほかのファイルを指し示す。 -1 つのDBに 1 つ「プライマリ データ ファイル」が必要。 -拡張子は「mdf」が推奨される。 ***「セカンダリ データ ファイル」 [#ae355fe9] -「セカンダリ データ ファイル」は「プライマリ データ ファイル」以外のすべての「データ ファイル」である。 -データベースには、 --「セカンダリ データ ファイル」がない場合と、 --複数の「セカンダリ データ ファイル」がある場合がある。 -拡張子は「ndf」が推奨される。 **「トランザクション ログ ファイル」 [#z8b516b8] 「トランザクション ログ ファイル」は「ファイル グループ」に追加できない。 -「データ ファイル」と同様に複数のファイルを作成できる。 -「データ ファイル」のような「プライマリ」、「セカンダリ」を区別しない。 -拡張子は「ldf」が推奨される。 *規則と推奨 [#o07b24e5] **規則 [#k1eb8dfd] -1つの「ファイル グループ」を複数のデータベースで使用できない。~ 「ファイル グループ」は、1つの「データベース スキーマ」内に定義するため。 -1つの「データ ファイル」は1つの「ファイル グループ」にしか所属できない。~ 「データ ファイル」は、1つの「ファイル グループ」内に定義するため。 -「トランザクション ログ ファイル」を「ファイル グループ」に格納できない。~ 「トランザクション ログ ファイル」は「データ ファイル」と扱いが異なるため。 **推奨 [#fed77f19] ***作成のポイント [#ud2d855b] 大部分のデータベースは、通常、 -1つの「データ ファイル」と -1つの「トランザクション ログ ファイル」で 正常に機能するが、必要に応じて、 以下の「ファイル グループ」の導入を検討する。 -「データ ファイル」を複数のファイルに分割する場合は、 --プライマリの「ファイル グループ」の他に、 --追加ファイル用のセカンダリの「ファイル グループ」を作成し、 >これを既定の「ファイル グループ」に設定する。 -これにより、 -*プライマリの「ファイル グループ」にはシステム オブジェクト だけが格納される。~ ストアド プロシージャ、拡張ストアド プロシージャ、関数、ビューなど。 -セカンダリの「ファイル グループ」にはユーザ オブジェクト だけが格納される。~ テーブル、インデックス、列、別名データ型、.NET Framework 共通言語ランタイム (CLR) ユーザ定義型など。 この分割方法は、「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」や「[[パーティション分割>SQL Server パーティション分割]]」で活用できる。 ***性能向上のポイント [#a947c158] -特定の物理ディスク上にデータ ファイル(テーブル・インデックス)を配置できる。~ これにより、負荷の高い特定のテーブルをディスクI/O性能の高い特定の物理ディスクにマップできる。 -「トランザクション ログ ファイル」は、ディスクI/O性能を考慮して、~ 「データ ファイル」と同じ物理ディスク上に配置しない(分散することが望ましい)。 -「ファイル グループ」により、物理ディスクにファイルを分散させることで、~ 簡易ストライピングによるディスクI/O性能の向上が期待できる。 --ただし、RAIDコントローラを用いてストライピング可能であれば不要。 --アクセス頻度が高いテーブルと、そのテーブルに属する「非クラスタ化インデックス」を、~ 別の「ファイル グループ」にマップし、ディスクI/Oを分散するという方法を採ることもできる。 --同じ結合クエリで使用する各テーブルは~ 別の「ファイル グループ」にマップすることにより、ディスクI/Oを分散して処理できる。 ***保守性の向上 [#d6119cb4] 大きな記憶域を占有する可能性のあるオブジェクトは、~ 別々の「ファイル グループ」に配置することで「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」による~ 柔軟なデータのバックアップ・リストアなど、メンテナンス操作に関してメリットがある。 *例 [#nea2a80d] 次の例では、 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 **参考 [#qd40d160] -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 の雑記>http://blog.engineer-memo.com/2011/10/14/sql-server-%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%A7%8B%E6%88%90%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E6%94%B9%E3%82%81%E3%81%A6%E5%8B%89%E5%BC%B7-%E3%81%9D/]] ---- Tags: [[:データアクセス]], [[:SQL Server]]