Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

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

2018/08/01 06:00

 今回は前回に引き続きSQL Serverのデータベース配置におけるベストプラクティスの残り4項目を紹介していきます。前回紹介した6項目と合わせた計10項目について物理設計時のチェック観点としてぜひチェックするようにしてください。

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

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

※この続きは、会員の方のみお読みいただけます(登録無料)。


※この続きは、会員の方のみお読みいただけます(登録無料)。


著者プロフィール

  • 太田智行(オオタトモユキ)

    NECソリューションイノベータ株式会社 2002年入社以来、SQL Server、Oracle、MySQL、PostgreSQLを活用したSIを多数経験。 2013年Microsoft社と「In-Memory OLTP機能」の徹底検証を実施。 以来、SQL Serverの高...

バックナンバー

連載:SQL Server管理者のための動的管理ビュー入門編
All contents copyright © 2007-2018 Shoeisha Co., Ltd. All rights reserved. ver.1.5