Shoeisha Technology Media

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

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

テーマ別に探す

SQL Serverの動作パラメータを把握しよう(後編)

2017/07/13 06:00

 次期SQL Server 2017ではデータサイエンスの分野で多くの支持を得ているPythonがサポートされます。これにより、SQL Serverのコンテキストの中でデータロード、学習モデル開発、予測実行までの一連を完結できるようになります。もちろんGPUもサポートされており、高精度な学習モデルをより短期間で開発できます。また、GPUマシンについては、既にMicrosoft Azure N シリーズとしてクラウド上でも容易に手配できるようになっています。さっそく、Microsoft Azure上に環境を用意し試してみましたが、サーバー構築から簡単な予測実行までの一連をわずか数時間でできました。マイクロソフトにより機械学習を活用するための敷居はぐっと下がっていることがうかがえ、今後より身近なものになっていきそうです。

 さて、前回は“SQL Serverの動作パラメータを把握しよう”の前編として、「データベースパラメータ」と「インスタンスパラメータ」を確認していきました。今回はその後編として「OSパラメータ」を確認していきます。

分類  解説 有効スコープ
データベースパラメータ SQL Serverを介してそれぞれのデータベースに対して設定するパラメータです。  データベース
インスタンスパラメータ SQL Serverを介してSQL Serverインスタンスに対して設定するパラメータです。 インスタンス
OSパラメータ SQL Serverを介さずOSに対して設定するパラメータです。設定はインスタンスのスコープで有効です。

   SQL Serverの動作パラメータを設定対象で分類すると3つ 今回はOSパラメータを見ていきます

 なお、今回ご紹介する実行例は前回に引き続きSQL Server 2016 SP1 Enterprise Edition on Windows Server 2016 Datacenter環境での実行結果になります。お使いの環境によって相違がでる可能性に留意ください。

この記事で紹介する実行例の環境(SELECT @@VERSIONの結果)
この記事で紹介する実行例の環境(SELECT @@VERSIONの結果)

OSで設定されたパラメータを確認する

 ここではSQL Serverを稼働させる際にOSレベルで意識すべき代表的なパラメータを3つ確認していきます(現時点はWindows版の紹介です。次期SQL ServerでのLinux版については今後別途機会を作りたいと思います)。

OSに対して設定されたパラメータを確認していきます
OSに対して設定されたパラメータを確認していきます

 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
左:既定のモデルが有効、中:Lock Pagesモデルが有効、左:Large Pagesモデルが有効
左:既定のモデルが有効、中:Lock Pagesモデルが有効、右:Large Pagesモデルが有効

 このクエリはSQL Serverで有効になっているメモリ管理モデルを返します。メモリ管理モデルは3種類あり、上図の中央の結果「LOCK_PAGES」が得られる場合は、Lock Pages in Memoryが有効です。

メモリ管理モデル  解説
CONVENTIONAL  既定のメモリ管理モデルです。SQL Serverのメモリ領域(バッファプール)はMEM_RESERVEとMEM_COMMITオプションによるVirtualAlloc()によって確保されます。こうして確保された領域はページアウトの対象になる可能性があり、性能劣化の要因となりえます。なお、このモデルで確保されたメモリサイズは、パフォーマンスカウンタのProcess\Private Bytesや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"
上:既定のモデルが有効、中:Lock Pagesモデルが有効、下:Large Pagesモデルが有効
上:既定のモデルが有効、中:Lock Pagesモデルが有効、下:Large Pagesモデルが有効
 -- SQL Server 2008 R2 以前の場合
EXEC sys.xp_readerrorlog 0, 1, "Using", "for buffer pool"
SQL Server 2008 R2 以前でLock Pagesモデルが有効な場合のメッセージ
SQL Server 2008 R2 以前でLock Pagesモデルが有効な場合のメッセージ

 なお、SQL Server 2008 R2以前では「Using XXX for buffer pool.」と表現されていたメッセージがSQL Server 2012以降では「Using XXX in the memory manager.」というように表現が変更されており、実はそこにSQL Serverの進化の歴史が垣間見えます。詳しくは別の記事(前編後編)で解説されていますので、ぜひご確認ください。

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


著者プロフィール

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

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

バックナンバー

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