マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

ここでは、SQL Server 構築における一般的な設定と、性能の向上につながる重要な「環境設定オプション」の設定と見込まれる効果について解説する。

基本的な考え方

  • SQL Server は、Windows Server と同様に、既定のパラメータ設定で利用しても、大部分のユーザの負荷に対して適切に実行される。
  • ただし、高い負荷が長時間変化しないような場合、サーバの設定をチューニングすることにより、性能が向上する可能性がある。
  • 設定を誤ると、SQL Serverが正しく動作しなくなるため、変更には注意が必要である。
    • 設定を変更する場合は、最初にシステム データベースをバックアップすること。
    • 設定の妥当性の確認には、性能検証テストや、運用テストが必要になる。

チューニング可能なメモリ領域

SQL Serverインスタンスのアドレス空間は、SQL Serverインスタンスが使用している、

  • 「実行コード」領域
  • 「プール メモリ」領域

の2つの領域から構成される。

このうち、チューニング可能な領域は、「プール メモリ」領域である。
「プール メモリ」は、SQL Serverによって、動的にサイズが変更される。

「プール メモリ」と役割

項番領域名役割
1システム レベルのデータ構造体DB記述子、ロック、テーブルなどの、SQL Serverインスタンスにグローバルなデータを保持するデータ構造体
2バッファ キャッシュデータ ページが読み取られるバッファ
3プロシージャ・キャッシュT-SQLの実行プランが入っているバッファ
4ログ キャッシュログ ページの読み書きに使用するキャッシュ。ログ バッファとデータ バッファの同期を減らすため、バッファ キャッシュとは別に管理される。
5接続コンテキスト接続ごとに、「クエリやストアド プロシージャのパラメータ値」、「カーソル位置情報」、「現在参照されているテーブル」等の状態を記録するデータ構造体。

「プール メモリ」の自己チューニング

「プール メモリ」の自己チューニング

「環境設定オプション」の「設定可能範囲」・「設定値」の確認

設定可能な「環境設定オプション」の「設定可能範囲」、「設定値」を確認するには、

環境設定オプション

種類

項番種類略号説明
1拡張オプションAこのオプションを変更する場合は、SQL Serverに詳しい技術者に確認する。
参照や設定をする場合は「show advanced options」を1に設定する必要がある。
2再起動オプションRR設定を有効にするにはSQL Serverインスタンスを再起動する必要がある。
3自己設定オプションSCSQL Serverがシステムのニーズに合わせて自己設定する。

確認・設定の方法

確認

sp_configure '<option name>'

  • 「Management Studio」などを使用して「sp_configure」を呼び出し、「環境設定オプション」を参照する。
  • パラメータを指定せずに実行した場合、「sp_configure」は、次に示す「環境設定オプション」の情報を、オプション名のアルファベットの昇順に並べて返す。
  • 「sp_configure」で出力される情報
    項番列名データ型説明
    1namenvarchar(70)環境設定オプションの名前
    2minimumint環境設定オプションの最小値
    3maximumint環境設定オプションの最大値
    4config_valueintsp_configureで設定した環境設定オプションの値
    5run_valueint現在実行中の環境設定オプションの値

設定

sp_configure '<option name>', <value>

  • 「sp_configure」を呼び出し、「環境設定オプション」を変更する。
  • 変更後、「Reconfigure」ステートメントを実行していない場合、変更結果が即時反映されない(「config_value」と、「run_value」が異なる)。
  • 即時反映させるためには、「Reconfigure」ステートメントを使用する(「run_value」を更新する)。
  • 「環境設定オプション」によっては、設定変更後、SQL Serverインスタンスを再起動しないと、現在実行中の値(run_value)を更新できないものがあるため注意が必要である。
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure
GO

拡張オプションの設定

「拡張オプション」の設定を表示または変更するには、「show advanced options」オプションを1(有効)に設定する必要がある。

  • このオプションは、既定では0(無効)に設定されている。
  • 必要に応じて「show advanced options」オプションを1(有効)にする。
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure
GO

重要な環境設定オプション

max server memory

確保する「プール メモリ(ページングされるメモリ、仮想記憶のメモリ)」の最大値を設定する。
値が「物理メモリ」のサイズを超える場合、実際に確保される「プール メモリ」は、「物理メモリ」の最大値に自動調整される。

  • 既定値 : 2,147,483,647(MB)
    • 既定値の場合、DBサーバに搭載されている全ての「物理メモリ」に合わせて、SQL Serverインスタンスは可能な限りの「プール メモリ」を確保する。
    • このため、他のアプリケーションが「物理メモリ」を必要としている時には、ページングにより他のアプリケーションに「物理メモリ」を割り当てる。
max server memory
  • サーバ種類
  • SQL Server専用のDBサーバの場合
    既定値の設定を使用する。
  • DBサーバがSQL Server専用でない場合
    (他のアプリケーションまたは、他のSQL Serverインスタンスが定期的に「物理メモリ」を必要とする場合)
    • 他のアプリケーションまたは、他のSQL Serverインスタンスの使用する「物理メモリ」が不足しないように、当該SQL Serverインスタンスの、「max server memory」オプションの設定を変更する。
    • DBサーバに搭載されている全ての「物理メモリ」の合計から、他のアプリケーションに必要な「物理メモリ」を引いて、SQL Serverインスタンスが確保する「プール メモリ」の最大値を計算する。
    • 他のアプリケーションに必要な「物理メモリ」は、起動に必要な「物理メモリ」の量だけでなく、ニーズの変化に対応できるように残す「物理メモリ」の量を考慮する。
  • 「sp_configure」による設定
    EXEC sp_configure 'max server memory', nnnn
    RECONFIGURE
    EXEC sp_configure
    GO

nnnnの単位は、MBで指定する。

min server memory

確保する「プール メモリ(ページングされるメモリ、仮想記憶のメモリ)」の最小値を設定する。

「min server memory」オプションを1GBに設定しても、
SQL Serverが自動的に1GBの「物理メモリ」を取得するわけではない。
その時々のサーバの負荷に基づいて、必要に応じて割り当てられる。

  • 既定値 : 0(MB)
    • 既定値の場合、他のアプリケーションが「物理メモリ」を必要として、ページングが発生している場合、動的に確保した「プール メモリ」を解放する。
min server memory
  • サーバ種類
  • SQL Server専用のDBサーバの場合
    既定値の設定を使用する。
  • DBサーバがSQL Server専用でない場合
    (他のアプリケーションまたは、他のSQL Serverインスタンスにより「物理メモリ」を奪われ、当該SQL Serverの使用する「物理メモリ」が不足する可能性がある場合)
    • 「物理メモリ」が不足しないように、当該SQL Serverインスタンスの、「min server memory」オプションの設定を変更する。
    • 例えば、サーバがSQL ServerのDBMS機能と、印刷とファイル サービス機能を提供する場合、
      印刷とファイル サービス機能に「物理メモリ」を消費され、SQL Server用の「物理メモリ」が不足し、応答時間が遅くならないようにする。
  • 「sp_configure」による設定
    EXEC sp_configure 'min server memory', nnnn
    RECONFIGURE
    EXEC sp_configure
    GO

nnnnの単位は、MBで指定する。

max server memory = min server memory (メモリの固定)

  • 「max server memory」オプションと「min server memory」オプションを同じ値に設定する。
  • これにより、設定したサイズまで確保した「プール メモリ」を、その後、解放しないようになる。
  • DBサーバがSQL Server専用でない場合
    SQL Serverインスタンスで確保する「プール メモリ」を固定し、他の「物理メモリ」が不足しないように、この設定を適用する。
    • 当該SQL Serverの使用する「物理メモリ」
    • 他のアプリケーションまたは、他のSQL Serverインスタンスの使用する「物理メモリ」

recovery interval (min)

データベースの設定

自動拡張・自動圧縮

復旧モデル

内部リンク

SQL Server の認証

SQL Server の照合順序

SQL Server の障害復旧

SQL Server での設定取得方法

つながらない!- SQL Server

参考

Microsoft SQL Server Japan Support Team Blog

@IT:SQL Server 2000チューニング全工程

真・Dr. K's SQL Serverチューニング研修

サーバー構成オプション (SQL Server)

https://msdn.microsoft.com/ja-jp/library/ms189631.aspx

項番構成オプション最小値最大値既定
1access check cache bucket count (A)0163840
2access check cache quota (A)021474836470
3ad hoc distributed queries (A)010
4affinity I/O mask (A、RR)-214748364821474836470
5affinity64 I/O mask (A)
64 ビット版の SQL Server でのみ使用可能
-214748364821474836470
6affinity mask (A)-214748364821474836470
7affinity64 mask (A、RR)
(64 ビット版の SQL Server でのみ使用可能)
-214748364821474836470
8Agent XPs (A)010
(SQL Server エージェントが起動すると 1 に変わります。SQL Server エージェントが自動的に起動するようにセットアップ時に設定されている場合の既定値は 0 です。)
9allow updates
(旧バージョンで使用。使用しない。再構成中にエラーが発生する原因になる場合があります。)
010
10backup compression default010
11blocked process threshold (A)0864000
12c2 audit mode (A、RR)010
13clr enabled010
14common criteria compliance enabled (A、RR)010
15contained database authentication0-0
16cost threshold for parallelism (A)0327675
17cross db ownership chaining010
18cursor threshold (A)-12147483647-1
19Database Mail XPs (A)010
20default full-text language (A)021474836471033
21default language099990
22default trace enabled (A)011
23disallow results from triggers (A)010
24EKM provider enabled010
25filestream_access_level020
26fill factor (A、RR)01000
27ft crawl bandwidth (max)
ft crawl bandwidth(A) を参照
032767100
28ft crawl bandwidth (min)
ft crawl bandwidth(A) を参照
0327670
29ft notify bandwidth (max)
ft notify bandwidth(A) を参照
032767100
30ft notify bandwidth (min)
ft notify bandwidth(A) を参照
0327670
31index create memory (A、SC)70421474836470
32in-doubt xact resolution (A)020
33lightweight pooling (A、RR)010
34locks (A、RR、SC)500021474836470
35max degree of parallelism (A)0327670
36max full-text crawl range (A)02564
37-★max server memory (A、SC)1621474836472147483647
38max text repl size0214748364765536
39max worker threads (A)12832767
(32 ビット版の SQL Server では 1024 が、64 ビット版の SQL Server では 2048 が推奨されます)
0
0 の場合、"256+(<processors> -4) * 8" という式 (32 ビット版の SQL Server の場合。64 ビット版の SQL Server の場合はこの倍) を使用して、プロセッサ数に基づいたワーカー スレッドの最大数が自動的に構成されます。
40media retention (A、RR)03650
41min memory per query (A)51221474836471024
42-★min server memory (A、SC)021474836470
43nested triggers011
44network packet size (A)512327674096
45Ole Automation Procedures (A)010
46open objects (A、RR)
旧バージョンで使用
021474836470
47optimize for ad hoc workloads (A)010
48PH_timeout (A)1360060
49precompute rank (A)010
50priority boost (A、RR)010
51query governor cost limit (A)021474836470
52query wait (A)-12147483647-1
53-★recovery interval (A、SC)0327670
54remote access (RR)011
55remote admin connections010
56remote login timeout0214748364710
57remote proc trans010
58remote query timeout02147483647600
59Replication XPs (A)010
60scan for startup procs (A、RR)010
61server trigger recursion011
62set working set size (A、RR)
旧バージョンで使用
010
63-★show advanced options010
64SMO and DMO XPs (A)011
65transform noise words (A)010
66two digit year cutoff (A)175399992049
67user connections (A、RR、SC)0327670
68user options0327670
69xp_cmdshell (A)010

Tags: :データアクセス, :SQL Server


添付ファイル: fileMinServerMemory.png 509件 [詳細] fileMaxServerMemory.png 523件 [詳細] filePoolMemory.png 458件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-08-07 (火) 19:44:25 (133d)