[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]] -[[戻る>SQL Server]] * 目次 [#pf197325] #contents *概要 [#f0956d1c] ここでは、SQL Server 構築における一般的な設定と、性能の向上につながる重要な「環境設定オプション」の設定と見込まれる効果について解説する。 **基本的な考え方 [#d411889a] -SQL Server は、[[Windows Server>Windows 自動パラメタとチューニング]] と同様に、既定のパラメータ設定で利用しても、大部分のユーザの負荷に対して適切に実行される。 -ただし、高い負荷が長時間変化しないような場合、サーバの設定をチューニングすることにより、性能が向上する可能性がある。 -設定を誤ると、SQL Serverが正しく動作しなくなるため、変更には注意が必要である。 --設定を変更する場合は、最初にシステム データベースをバックアップすること。~ --設定の妥当性の確認には、性能検証テストや、運用テストが必要になる。 -参考 --SQL Server 2000 チューニング全工程(1):~ メモリの自動チューニング機能を完全に把握しよう (1/3) - @IT~ http://www.atmarkit.co.jp/ait/articles/0407/31/news024.html **チューニング可能なメモリ領域 [#h3b7c728] SQL Serverインスタンスのアドレス空間は、SQL Serverインスタンスが使用している、 -「実行コード」領域 -「プール メモリ」領域 の2つの領域から構成される。 このうち、チューニング可能な領域は、「プール メモリ」領域である。~ 「プール メモリ」は、SQL Serverによって、動的にサイズが変更される。 ***「プール メモリ」と役割 [#c21d37fc] |項番|領域名|役割|h |1|システム レベルのデータ構造体|DB記述子、ロック、テーブルなどの、SQL Serverインスタンスにグローバルなデータを保持するデータ構造体| |2|バッファ キャッシュ|データ ページが読み取られるバッファ| |3|プロシージャ・キャッシュ|T-SQLの実行プランが入っているバッファ| |4|ログ キャッシュ|ログ ページの読み書きに使用するキャッシュ。ログ バッファとデータ バッファの同期を減らすため、バッファ キャッシュとは別に管理される。| |5|接続コンテキスト|接続ごとに、「クエリやストアド プロシージャのパラメータ値」、「カーソル位置情報」、「現在参照されているテーブル」等の状態を記録するデータ構造体。| ***「プール メモリ」の自己チューニング [#u51b2e16] #ref(PoolMemory.png,left,nowrap,「プール メモリ」の自己チューニング) **「環境設定オプション」の「設定可能範囲」・「設定値」の確認 [#a63ea5f1] 設定可能な「環境設定オプション」の「設定可能範囲」、「設定値」を確認するには、 -「Management Studio」を使用するか、 -[[「sp_configure」システム ストアドプロシージャを実行する。>#s59d542e]] *環境設定オプション [#e2c861aa] **種類 [#r10ba981] |項番|種類|略号|説明| |1|拡張オプション|A|このオプションを変更する場合は、SQL Serverに詳しい技術者に確認する。&br;参照や設定をする場合は「show advanced options」を1に設定する必要がある。| |2|再起動オプション|RR|設定を有効にするにはSQL Serverインスタンスを再起動する必要がある。| |3|自己設定オプション|SC|SQL Serverがシステムのニーズに合わせて自己設定する。| **確認・設定の方法 [#s59d542e] ***確認 [#v63f2b85] sp_configure '<option name>' -「Management Studio」などを使用して「sp_configure」を呼び出し、「環境設定オプション」を参照する。 -パラメータを指定せずに実行した場合、「sp_configure」は、次に示す「環境設定オプション」の情報を、オプション名のアルファベットの昇順に並べて返す。 -「sp_configure」で出力される情報 |項番|列名|データ型|説明|h |1|name|nvarchar(70)|環境設定オプションの名前| |2|minimum|int|環境設定オプションの最小値| |3|maximum|int|環境設定オプションの最大値| |4|config_value|int|sp_configureで設定した環境設定オプションの値| |5|run_value|int|現在実行中の環境設定オプションの値| ***設定 [#b36cf425] 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 ***拡張オプションの設定 [#o875a3c2] 「拡張オプション」の設定を表示または変更するには、「show advanced options」オプションを1(有効)に設定する必要がある。 -このオプションは、既定では0(無効)に設定されている。 -必要に応じて「show advanced options」オプションを1(有効)にする。 EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure GO **重要な環境設定オプション [#p2982137] ***max server memory [#x29481f2] 確保する「プール メモリ(ページングされるメモリ、仮想記憶のメモリ)」の最大値を設定する。~ 値が「物理メモリ」のサイズを超える場合、実際に確保される「プール メモリ」は、「物理メモリ」の最大値に自動調整される。 -既定値 : 2,147,483,647(MB) --既定値の場合、DBサーバに搭載されている全ての「物理メモリ」に合わせて、SQL Serverインスタンスは可能な限りの「プール メモリ」を確保する。 --このため、他のアプリケーションが「物理メモリ」を必要としている時には、ページングにより他のアプリケーションに「物理メモリ」を割り当てる。 #ref(MaxServerMemory.png,left,nowrap,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 [#p405ad7d] 確保する「プール メモリ(ページングされるメモリ、仮想記憶のメモリ)」の最小値を設定する。~ 「min server memory」オプションを1GBに設定しても、~ SQL Serverが自動的に1GBの「物理メモリ」を取得するわけではない。~ その時々のサーバの負荷に基づいて、必要に応じて割り当てられる。 -既定値 : 0(MB) --既定値の場合、他のアプリケーションが「物理メモリ」を必要として、ページングが発生している場合、動的に確保した「プール メモリ」を解放する。 #ref(MinServerMemory.png,left,nowrap,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 (メモリの固定) [#x33ae295] -「max server memory」オプションと「min server memory」オプションを同じ値に設定する。 -これにより、設定したサイズまで確保した「プール メモリ」を、その後、解放しないようになる。 -DBサーバがSQL Server専用でない場合~ SQL Serverインスタンスで確保する「プール メモリ」を固定し、他の「物理メモリ」が不足しないように、この設定を適用する。 --当該SQL Serverの使用する「物理メモリ」 --他のアプリケーションまたは、他のSQL Serverインスタンスの使用する「物理メモリ」 ***[[recovery interval (min)>SQL Server の障害復旧#lea45631]] [#v4064195] **データベースの設定 [#c46e640c] ***[[自動拡張・自動圧縮>データ ファイルの圧縮と拡張]] [#uaf54335] ***[[復旧モデル>SQL Server の障害復旧#s7f9c8ad]] [#t9cfbce4] *内部リンク [#i8ffdd29] **[[SQL Server の認証]] [#v3de38bc] **[[SQL Server の照合順序]] [#b356a2f0] **[[SQL Server の障害復旧]] [#a825793b] *参考 [#kca40d6b] **@IT:SQL Server 2000チューニング全工程 [#c483f57f] -SQL Server 2000 チューニング全工程(1):メモリの自動チューニング機能を完全に把握しよう --http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_1.html --http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_2.html --http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_3.html -SQL Server 2000 チューニング全工程(2):動的ディスク管理でのチューニングポイント --http://www.atmarkit.co.jp/ait/articles/0409/25/news011.html --http://www.atmarkit.co.jp/ait/articles/0409/25/news011_2.html --http://www.atmarkit.co.jp/ait/articles/0409/25/news011_3.html -SQL Server 2000 チューニング全工程(3):速報! SQL Server 2005のデータパーティション --http://www.atmarkit.co.jp/ait/articles/0411/30/news113.html --http://www.atmarkit.co.jp/ait/articles/0411/30/news113_2.html --http://www.atmarkit.co.jp/ait/articles/0411/30/news113_3.html -SQL Server 2000 チューニング全工程(4):SQL Serverのインデックス構造(前編) --http://www.atmarkit.co.jp/ait/articles/0501/18/news097.html --http://www.atmarkit.co.jp/ait/articles/0501/18/news097_2.html -SQL Server 2000 チューニング全工程(5):SQL Serverのインデックス構造(後編) --http://www.atmarkit.co.jp/ait/articles/0503/18/news123.html --http://www.atmarkit.co.jp/ait/articles/0503/18/news123_2.html **サーバー構成オプション (SQL Server) [#d14f31f8] https://msdn.microsoft.com/ja-jp/library/ms189631.aspx |項番|構成オプション|最小値|最大値|既定|h |1|[[access check cache bucket count (A)>https://msdn.microsoft.com/ja-jp/library/cc645588.aspx]]|0|16384|0| |2|[[access check cache quota (A)>https://msdn.microsoft.com/ja-jp/library/cc645588.aspx]]|0|2147483647|0| |3|[[ad hoc distributed queries (A)>https://msdn.microsoft.com/ja-jp/library/ms187569.aspx]]|0|1|0| |4|[[affinity I/O mask (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms189629.aspx]]|-2147483648|2147483647|0| |5|[[affinity64 I/O mask (A)>https://msdn.microsoft.com/ja-jp/library/ms190753.aspx]]&br;64 ビット版の SQL Server でのみ使用可能|-2147483648|2147483647|0| |6|[[affinity mask (A)>https://msdn.microsoft.com/ja-jp/library/ms187104.aspx]]|-2147483648|2147483647|0| |7|[[affinity64 mask (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms188291.aspx]]&br; (64 ビット版の SQL Server でのみ使用可能)|-2147483648|2147483647|0| |8|[[Agent XPs (A)>https://msdn.microsoft.com/ja-jp/library/ms178127.aspx]]|0|1|0&br;(SQL Server エージェントが起動すると 1 に変わります。SQL Server エージェントが自動的に起動するようにセットアップ時に設定されている場合の既定値は 0 です。)| |9|[[allow updates>https://msdn.microsoft.com/ja-jp/library/ms179262.aspx]]&br; (旧バージョンで使用。使用しない。再構成中にエラーが発生する原因になる場合があります。)|0|1|0| |10|[[backup compression default>https://msdn.microsoft.com/ja-jp/library/bb933863.aspx]]|0|1|0| |11|[[blocked process threshold (A)>https://msdn.microsoft.com/ja-jp/library/ms181150.aspx]]|0|86400|0| |12|[[c2 audit mode (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms187634.aspx]]|0|1|0| |13|[[clr enabled>https://msdn.microsoft.com/ja-jp/library/ms175193.aspx]]|0|1|0| |14|[[common criteria compliance enabled (A、RR)>https://msdn.microsoft.com/ja-jp/library/bb326650.aspx]]|0|1|0| |15|[[contained database authentication>https://msdn.microsoft.com/ja-jp/library/ff929237.aspx]]|0|-|0| |16|[[cost threshold for parallelism (A)>https://msdn.microsoft.com/ja-jp/library/ms190949.aspx]]|0|32767|5| |17|[[cross db ownership chaining>https://msdn.microsoft.com/ja-jp/library/ms188694.aspx]]|0|1|0| |18|[[cursor threshold (A)>https://msdn.microsoft.com/ja-jp/library/ms175817.aspx]]|-1|2147483647|-1| |19|[[Database Mail XPs (A)>https://msdn.microsoft.com/ja-jp/library/ms191189.aspx]]|0|1|0| |20|[[default full-text language (A)>https://msdn.microsoft.com/ja-jp/library/ms175470.aspx]]|0|2147483647|1033| |21|[[default language>https://msdn.microsoft.com/ja-jp/library/ms190682.aspx]]|0|9999|0| |22|[[default trace enabled (A)>https://msdn.microsoft.com/ja-jp/library/ms175513.aspx]]|0|1|1| |23|[[disallow results from triggers (A)>https://msdn.microsoft.com/ja-jp/library/ms186337.aspx]]|0|1|0| |24|[[EKM provider enabled>https://msdn.microsoft.com/ja-jp/library/bb630320.aspx]]|0|1|0| |25|[[filestream_access_level>https://msdn.microsoft.com/ja-jp/library/cc645956.aspx]]|0|2|0| |26|[[fill factor (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms190470.aspx]]|0|100|0| |27|ft crawl bandwidth (max)&br;[[ft crawl bandwidth(A) を参照>https://msdn.microsoft.com/ja-jp/library/ms191204.aspx]]|0|32767|100| |28|ft crawl bandwidth (min)&br;[[ft crawl bandwidth(A) を参照>https://msdn.microsoft.com/ja-jp/library/ms191204.aspx]]|0|32767|0| |29|ft notify bandwidth (max)&br;[[ft notify bandwidth(A) を参照>https://msdn.microsoft.com/ja-jp/library/ms189615.aspx]]|0|32767|100| |30|ft notify bandwidth (min)&br;[[ft notify bandwidth(A) を参照>https://msdn.microsoft.com/ja-jp/library/ms189615.aspx]]|0|32767|0| |31|[[index create memory (A、SC)>https://msdn.microsoft.com/ja-jp/library/ms180967.aspx]]|704|2147483647|0| |32|[[in-doubt xact resolution (A)>https://msdn.microsoft.com/ja-jp/library/ms179586.aspx]]|0|2|0| |33|[[lightweight pooling (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms178074.aspx]]|0|1|0| |34|[[locks (A、RR、SC)>https://msdn.microsoft.com/ja-jp/library/ms190253.aspx]]|5000|2147483647|0| |35|[[max degree of parallelism (A)>https://msdn.microsoft.com/ja-jp/library/ms189094.aspx]]|0|32767|0| |36|[[max full-text crawl range (A)>https://msdn.microsoft.com/ja-jp/library/ms189912.aspx]]|0|256|4| |37-★|[[max server memory (A、SC)>https://msdn.microsoft.com/ja-jp/library/ms178067.aspx]]|16|2147483647|2147483647| |38|[[max text repl size>https://msdn.microsoft.com/ja-jp/library/ms179573.aspx]]|0|2147483647|65536| |39|[[max worker threads (A)>https://msdn.microsoft.com/ja-jp/library/ms190219.aspx]]|128|32767&br;(32 ビット版の SQL Server では 1024 が、64 ビット版の SQL Server では 2048 が推奨されます)|0&br;0 の場合、"256+(<processors> -4) * 8" という式 (32 ビット版の SQL Server の場合。64 ビット版の SQL Server の場合はこの倍) を使用して、プロセッサ数に基づいたワーカー スレッドの最大数が自動的に構成されます。| |40|[[media retention (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms175474.aspx]]|0|365|0| |41|[[min memory per query (A)>https://msdn.microsoft.com/ja-jp/library/ms191303.aspx]]|512|2147483647|1024| |42-★|[[min server memory (A、SC)>https://msdn.microsoft.com/ja-jp/library/ms178067.aspx]]|0|2147483647|0| |43|[[nested triggers>https://msdn.microsoft.com/ja-jp/library/translation/edit/ms189631.aspx?sentenceGuid=990d0eb67465b4279117f98ebf0765c7]]|0|1|1| |44|[[network packet size (A)>https://msdn.microsoft.com/ja-jp/library/ms177437.aspx]]|512|32767|4096| |45|[[Ole Automation Procedures (A)>https://msdn.microsoft.com/ja-jp/library/ms191188.aspx]]|0|1|0| |46|[[open objects (A、RR)&br;旧バージョンで使用>https://msdn.microsoft.com/ja-jp/library/ms190637.aspx]]|0|2147483647|0| |47|[[optimize for ad hoc workloads (A)>https://msdn.microsoft.com/ja-jp/library/cc645587.aspx]]|0|1|0| |48|[[PH_timeout (A)>https://msdn.microsoft.com/ja-jp/library/ms191269.aspx]]|1|3600|60| |49|[[precompute rank (A)>https://msdn.microsoft.com/ja-jp/library/ms191498.aspx]]|0|1|0| |50|[[priority boost (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms188709.aspx]]|0|1|0| |51|[[query governor cost limit (A)>https://msdn.microsoft.com/ja-jp/library/ms191219.aspx]]|0|2147483647|0| |52|[[query wait (A)>https://msdn.microsoft.com/ja-jp/library/ms175463.aspx]]|-1|2147483647|-1| |53-★|[[recovery interval (A、SC)>https://msdn.microsoft.com/ja-jp/library/ms191154.aspx]]|0|32767|0| |54|[[remote access (RR)>https://msdn.microsoft.com/ja-jp/library/ms191464.aspx]]|0|1|1| |55|[[remote admin connections>https://msdn.microsoft.com/ja-jp/library/ms190468.aspx]]|0|1|0| |56|[[remote login timeout>https://msdn.microsoft.com/ja-jp/library/ms175136.aspx]]|0|2147483647|10| |57|[[remote proc trans>https://msdn.microsoft.com/ja-jp/library/ms190773.aspx]]|0|1|0| |58|[[remote query timeout>https://msdn.microsoft.com/ja-jp/library/ms189040.aspx]]|0|2147483647|600| |59|[[Replication XPs (A)>https://msdn.microsoft.com/ja-jp/library/ms366345.aspx]]|0|1|0| |60|[[scan for startup procs (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms187889.aspx]]|0|1|0| |61|[[server trigger recursion>https://msdn.microsoft.com/ja-jp/library/ms190946.aspx]]|0|1|1| |62|[[set working set size (A、RR)>https://msdn.microsoft.com/ja-jp/library/ms189056.aspx]]&br;旧バージョンで使用|0|1|0| |63-★|[[show advanced options>https://msdn.microsoft.com/ja-jp/library/ms188265.aspx]]|0|1|0| |64|[[SMO and DMO XPs (A)>https://msdn.microsoft.com/ja-jp/library/ms190461.aspx]]|0|1|1| |65|[[transform noise words (A)>https://msdn.microsoft.com/ja-jp/library/ms187914.aspx]]|0|1|0| |66|[[two digit year cutoff (A)>https://msdn.microsoft.com/ja-jp/library/ms191004.aspx]]|1753|9999|2049| |67|[[user connections (A、RR、SC)>https://msdn.microsoft.com/ja-jp/library/ms187030.aspx]]|0|32767|0| |68|[[user options>https://msdn.microsoft.com/ja-jp/library/ms190763.aspx]]|0|32767|0| |69|[[xp_cmdshell (A)>https://msdn.microsoft.com/ja-jp/library/ms190693.aspx]]|0|1|0|