SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

直近開催のイベントはこちら!

Data Tech 2022

2022年12月8日(木)10:00~15:50

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

SQL Server管理者のための動的管理ビュー入門編

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


 次期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には反映されますが(ただし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"
上:既定のモデルが有効、中: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の進化の歴史が垣間見えます。詳しくは別の記事(前編後編)で解説されていますので、ぜひご確認ください。

次のページ
データベースのファイルの瞬時初期化:sys.dm_server_services動的管理ビュー(SQL Server 2016 以降)

この記事は参考になりましたか?

  • Facebook
  • Twitter
  • Pocket
  • note
SQL Server管理者のための動的管理ビュー入門編連載記事一覧

もっと読む

この記事の著者

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

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

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/9514 2017/11/10 17:11

Job Board

PR

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

2022年12月8日(木)10:00~15:50

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング