さて、前回は“SQL Serverの動作パラメータを把握しよう”の前編として、「データベースパラメータ」と「インスタンスパラメータ」を確認していきました。今回はその後編として「OSパラメータ」を確認していきます。
| 分類 | 解説 | 有効スコープ |
| データベースパラメータ | SQL Serverを介してそれぞれのデータベースに対して設定するパラメータです。 | データベース |
| インスタンスパラメータ | SQL Serverを介してSQL Serverインスタンスに対して設定するパラメータです。 | インスタンス |
| OSパラメータ | SQL Serverを介さずOSに対して設定するパラメータです。設定はインスタンスのスコープで有効です。 |
SQL Serverの動作パラメータを設定対象で分類すると３つ 今回はOSパラメータを見ていきます
なお、今回ご紹介する実行例は前回に引き続きSQL Server 2016 SP1 Enterprise Edition on Windows Server 2016 Datacenter環境での実行結果になります。お使いの環境によって相違がでる可能性に留意ください。
OSで設定されたパラメータを確認する
ここではSQL Serverを稼働させる際にOSレベルで意識すべき代表的なパラメータを3つ確認していきます（現時点はWindows版の紹介です。次期SQL ServerでのLinux版については今後別途機会を作りたいと思います）。
3つのパラメータの確認方法の紹介に先立ち、まずはそれぞれの意味を解説しておきます。
| 設定項目 | 解説 |
| メモリ内のページのロック(Lock Pages in Memory) | SQL Serverが確保するメモリ領域を物理メモリに固定することで、ページングを回避し性能劣化の防止を図るパラメータです。設定はOS側で行います。設定手順はマニュアルを確認ください。なお、このパラメータの有効化する場合は、あわせてサーバー構成オプションのmax server memoryを設定し、SQL Serverが利用するメモリ領域に上限を設けること（≒OSを含むSQL Server以外のSWが利用するメモリ領域を確保すること）を推奨します。 |
| データベースのファイルの瞬時初期化(Instant File Initialization) |
データファイルの追加や拡張に伴う初期化処理（0埋め）を遅延させることで、その処理の高速化を図るパラメータです。例えばINSERTクエリに伴いデータファイルの拡張が必要となった場合、INSERTクエリはデータファイルの拡張が完了するまで待機させられます。このパラメータはこの待機時間を短縮するものです。設定はOS側で行います。設定手順はマニュアルを確認ください。 |
| 高パフォーマンス電源プラン | 近年のCPUは負荷状態に応じて動作周波数（≒性能）と電力消費をトレードオフで調整する機能を持ちます。高パフォーマンス電源プランはCPUの動作を省電力よりも性能を優先させるためのパラメータで、一般的にOLTP用途においてより有効であるとされています。設定はBIOSおよびOS側で行います。BIOSの設定手順はご利用のサーバーのマニュアル等を確認ください。OSの設定手順はマニュアルを確認ください。 |
それではパラメータの確認方法を見ていきます。
メモリ内のページのロック：sys.dm_os_sys_info動的管理ビュー（SQL Server 2016 以降）
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info
このクエリはSQL Serverで有効になっているメモリ管理モデルを返します。メモリ管理モデルは3種類あり、上図の中央の結果「LOCK_PAGES」が得られる場合は、Lock Pages in Memoryが有効です。
| メモリ管理モデル | 解説 |
| CONVENTIONAL | 既定のメモリ管理モデルです。SQL Serverのメモリ領域（バッファプール）はMEM_RESERVEとMEM_COMMITオプションによるVirtualAlloc()によって確保されます。こうして確保された領域はページアウトの対象になる可能性があり、性能劣化の要因となりえます。なお、このモデルで確保されたメモリサイズは、パフォーマンスカウンタのProcess\Private Bytesには反映されますが(ただしWindows Server2012以降)、Process\Working Setには反映されません。 |
| LOCK_PAGES | SQL Serverのサービスアカウントに”メモリ内のページのロック”権限が付与されている際に有効となるメモリ管理モデルです。SQL Serverのメモリ領域（バッファプール）はMEM_PHYSICALオプションによるVirtualAlloc()によって確保されます。こうして確保された領域は物理メモリにロックされ、ページアウトの対象外となります。なお、このモデルで確保されたメモリサイズは、パフォーマンスカウンタのProcess\Private BytesやProcess\Working Setに反映されません。 |
| LARGE_PAGES | LOCK_PAGESと同じ条件（SQL Serverのサービスアカウントに”メモリ内のページのロック”権限が付与されている）に加え、SQL Serverが64-bitバージョンである、物理メモリが8GBを超える、SQL Serverのスタートアップオプションでトレースフラグ834が設定されている、これら4つの条件が真となる場合に有効となるメモリ管理モデルです。SQL Serverのメモリ領域（バッファプール）はMEM_LARGE_PAGESオプションによるVirtualAlloc()によって確保されます。こうして確保された領域は物理メモリにロックされ、ページアウトの対象外となります。なお、このモデルで確保されたメモリサイズは、パフォーマンスカウンタのProcess\Private Bytesには反映されますが、Process\Working Setには反映されません。 |
(*)LARGE_PAGESメモリ管理モデルはメモリ領域をより大きなページで一度に獲得することで仮想アドレス変換のオーバヘッドを低減し性能向上を図るもので、SQL Serverの起動時に必要なバッファプールを一度に連続領域で確保しようと振舞います。そのため、起動に時間を要したり、連続領域で確保できない場合は起動に失敗したりと、こうした留意事項を踏まえ十分な検証の上で利用することを推奨します。また、カラムストアインデックスを利用する場合にはLARGE_PAGESメモリ管理モデルは推奨されていません。この場合はLOCK_PAGESもしくはCONVENTIONALを利用してください。
なお、このsys.dm_os_sys_infoのsql_memory_model_desc列はSQL Server 2016 SP1で追加されました。それ以前のバージョンの場合はsys.xp_readerrorlogを使用しERRORLOGを参照する手段があります。
-- SQL Server 2012 以降の場合 EXEC sys.xp_readerrorlog 0, 1, "Using", "in the memory manager"
-- SQL Server 2008 R2 以前の場合 EXEC sys.xp_readerrorlog 0, 1, "Using", "for buffer pool"
なお、SQL Server 2008 R2以前では「Using XXX for buffer pool.」と表現されていたメッセージがSQL Server 2012以降では「Using XXX in the memory manager.」というように表現が変更されており、実はそこにSQL Serverの進化の歴史が垣間見えます。詳しくは別の記事（前編と後編）で解説されていますので、ぜひご確認ください。
