SQL Serverの動作パラメータを把握しよう(前編) (1/3):EnterpriseZine(エンタープライズジン)
Shoeisha Technology Media

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

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

テーマ別に探す

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

2017/05/29 06:00

 去年3月7日の衝撃の発表「SQL Server on Linux!」からおよそ1年、先日4月19日に開催されたイベントMicrosoft Data Amp にて、Linux版を含む次期SQL Serverの名称が「SQL Server 2017」になると発表されました。また新たなエンハンスとしてGraphサポート、Pythonサポート、さらにはGPUサポートなどの発表がありました。SQL Server上でGraphを活用したソーシャルネットワーク分析やIoTネットワークのモデリング、PythonやR(Rは現行2016でサポート済み)によるGPUを活用したDeep Learningなど、データの利活用の点でもSQL Serverへの期待(そしてSQL Server専門家の重要度)がますます高まりそうです。

 前回はSQL Serverが稼働するサーバーのHW構成とSW構成の把握を通じて主にSQL Serverの外の環境を確認しました。ここからはSQL Server自身の環境、すなわちSQL Serverの振る舞いを設定する動作パラメータを網羅的に確認していきます。

 SQL Serverの動作パラメータを設定対象で分類すると以下の3つになります。

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

 今回は前編として「データベースパラメータ」と「インスタンスパラメータ」を、次回後編では「OSパラメータ」を確認していきます。

 なお、この記事で紹介する、ビュー、関数、プロシージャ、コマンドはそれぞれ対応するバージョンを表記します(ただしサービスパックによる拡張があるため、各バージョンの最新サービスパックの適用を前提とした表記です)。また、すべての実行例はSQL Server 2016 SP1 Enterprise Edition on Windows Server 2016 Datacenter環境での実行結果です。お使いの環境によってはこの記事の結果と相違がでる可能性に留意ください。

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

データベースに対して設定されたパラメータを確認する

 ここではそれぞれのデータベースに対して設定されたパラメータを、「DBオプション」と「DBスコープオプション」の2つに細分して紹介します。

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

DBオプション:sys.databasesカタログビュー(SQL Server 2008 以降)

SELECT 
  * 
FROM 
  sys.databases
sys.databasesへのクエリ結果
sys.databasesへのクエリ結果

 sys.databases はCREATE DATABASEやALTER DATABASEの実行時に指定したDBオプションの設定がデータベースごとに確認できます。その他にも、データベースの状態(例:トランザクションログの切り捨てができない理由など)や機能(例:CDCやクエリストアなど)の有効・無効といった情報もあわせて確認することができます。なお、いくつかのオプションはDATABASEPROPERTYEX 関数(引数に指定したオプションに対する設定値を返却します)によっても確認することができます。

 以下にDBオプションの中で注目する機会が多い項目をいくつかピックアップして紹介します。

オプション 補足
delayed_durability コミットを遅延させることで性能向上を図るオプションです。ただしトランザクションの持続性とのトレードオフであることに注意して下さい。これはOracleやPostgreSQLの非同期コミットに相当します。
is_auto_update_stats_async_on 統計情報自動更新の遅延を制御するオプションです。
is_mixed_page_allocation_on 混合エクステントの利用を制御するオプションです。
is_parameterization_forced クエリに対する自動的なパラメータ化を制御するオプションです。
is_read_committed_snapshot_on トランザクションの排他モードを制御します。このオプションが有効な場合は、OracleやPostgreSQLと互換性のある排他制御モードで動作します。
log_reuse_wait トランザクションログ切り捨ての阻害要因を示す状態確認のためのオプションです。
page_verify_option  ページ破損の検出レベルを制御します。既定値はCHECKSUMです。古いバージョンからアップグレードしている場合はこの設定値がTORN_PAGE_DETECTIONもしくはNONEである可能性があります。その場合CHECKSUMに変更することを推奨します。
recovery_model  復旧モデルを制御するオプションです。
snapshot_isolation_state  is_read_committed_snapshot_onと同様にトランザクションの排他モードを制御するオプションです。
target_recovery_time_in_seconds 間接チェックポイントの実行頻度を制御するオプションです。

DBスコープオプション:sys.database_scoped_configurationsカタログビュー(SQL Server 2016 以降)

SELECT
  *
FROM 
  sys.database_scoped_configurations
sys.database_scoped_configurationsへのクエリ結果
sys.database_scoped_configurationsへのクエリ結果

 sys.database_scoped_configurations はALTER DATABASE SCOPED CONFIGURATIONの実行時に指定したDBスコープオプションの設定が確認できます。このカタログは従来インスタンススコープで設定していたオプションのいくつかがSQL Server 2016からはデータベーススコープで設定できるようになったことに伴い追加されました。現時点で4つあり、いずれも性能チューニングに利用される興味深いオプションです。これらは今後の記事の中で紹介していきたいと考えています。

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


著者プロフィール

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

    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