SQL Server 物理設計のベストプラクティス（後編）

７．複数データファイル構成で自動拡張を有効にする場合はデータファイルの同時自動拡張を有効化する

　前回「3. 複数のI/Oデバイスを利用できる場合はデータファイルを分散配置し負荷を平準化する」で解説した通り、ファイルグループ内のデータファイルが複数で構成されているとデータはストライピングされます。この状況下ですべてのファイルが一杯になると自動拡張が発生しますが、既定ではファイル単位で順次拡張されていきます。例えば、A、B、Cと３つのファイルが一杯になると、まずはAから拡張され、そのAが一杯になるとBが拡張される、という具合です。

　この振る舞いの問題点は、拡張されたファイルにI/Oが偏る可能性があることです。すなわちストライピングが十分に機能しなくなります。

　この問題を回避するためにはファイルの拡張をすべてのファイルで同時に処理させます。その振る舞いを有効化するのがAUTOGROW_ALL_FILESデータベースオプションです。この設定をONにすることで、すべてのファイルが同時に拡張されるようになり、引き続きストライピングが機能します。

　AUTOGROW_ALL_FILESはSQL Server 2016以降でサポートされたデータベースオプションでtempdbでは既定で有効、ユーザDBでは既定で無効になっています。また、この制御はSQL Server 2014以前のトレースフラグ1117と同様です。ただし前者はファイルグループの粒度で設定するのに対して、後者はインスタンスの粒度での設定になります。なお、SQL Server 2016以降ではトレースフラグ1117は意味をもたず、AUTOGROW_ALL_FILESにて制御が必要です。

USE UserDB
GO
-- 設定変更（ファイルの同時自動拡張を有効化）
ALTER DATABASE UserDB MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO

-- 設定変更（ファイルの同時自動拡張を無効化）
-- ALTER DATABASE UserDB MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
-- GO

-- 設定確認
SELECT 
  name, is_autogrow_all_files
FROM
  sys.filegroups WHERE name = 'PRIMARY'
GO

　ちなみに、ログファイルについてはファイルへのI/Oパターン（シーケンシャルアクセスでストライピングなし）からも推測できる通り、同時自動拡張の意味はなく、オプションのON/OFFに関わらず制御対象外です。