「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
概要 †
ここでは、SQL Server 構築における一般的な設定と、性能の向上につながる重要な「環境設定オプション」の設定と見込まれる効果について解説する。
基本的な考え方 †
- SQL Server は、Windows Server と同様に、既定のパラメータ設定で利用しても、大部分のユーザの負荷に対して適切に実行される。
- ただし、高い負荷が長時間変化しないような場合、サーバの設定をチューニングすることにより、性能が向上する可能性がある。
- 設定を誤ると、SQL Serverが正しく動作しなくなるため、変更には注意が必要である。
- 設定を変更する場合は、最初にシステム データベースをバックアップすること。
- 設定の妥当性の確認には、性能検証テストや、運用テストが必要になる。
チューニング可能なメモリ領域 †
SQL Serverインスタンスのアドレス空間は、SQL Serverインスタンスが使用している、
の２つの領域から構成される。
このうち、チューニング可能な領域は、「プール メモリ」領域である。
「プール メモリ」は、SQL Serverによって、動的にサイズが変更される。
「プール メモリ」と役割 †
| 項番 | 領域名 | 役割 |
| 1 | システム レベルのデータ構造体 | DB記述子、ロック、テーブルなどの、SQL Serverインスタンスにグローバルなデータを保持するデータ構造体 |
| 2 | バッファ キャッシュ | データ ページが読み取られるバッファ |
| 3 | プロシージャ・キャッシュ | T-SQLの実行プランが入っているバッファ |
| 4 | ログ キャッシュ | ログ ページの読み書きに使用するキャッシュ。ログ バッファとデータ バッファの同期を減らすため、バッファ キャッシュとは別に管理される。 |
| 5 | 接続コンテキスト | 接続ごとに、「クエリやストアド プロシージャのパラメータ値」、「カーソル位置情報」、「現在参照されているテーブル」等の状態を記録するデータ構造体。 |
「プール メモリ」の自己チューニング †
「環境設定オプション」の「設定可能範囲」・「設定値」の確認 †
設定可能な「環境設定オプション」の「設定可能範囲」、「設定値」を確認するには、
環境設定オプション †
種類 †
| 項番 | 種類 | 略号 | 説明 |
| 1 | 拡張オプション | A | このオプションを変更する場合は、SQL Serverに詳しい技術者に確認する。
参照や設定をする場合は「show advanced options」を１に設定する必要がある。 |
| 2 | 再起動オプション | RR | 設定を有効にするにはSQL Serverインスタンスを再起動する必要がある。 |
| 3 | 自己設定オプション | SC | SQL Serverがシステムのニーズに合わせて自己設定する。 |
確認・設定の方法 †
確認 †
sp_configure '<option name>'
- 「Management Studio」などを使用して「sp_configure」を呼び出し、「環境設定オプション」を参照する。
- パラメータを指定せずに実行した場合、「sp_configure」は、次に示す「環境設定オプション」の情報を、オプション名のアルファベットの昇順に並べて返す。
- 「sp_configure」で出力される情報
| 項番 | 列名 | データ型 | 説明 |
| 1 | name | nvarchar(70) | 環境設定オプションの名前 |
| 2 | minimum | int | 環境設定オプションの最小値 |
| 3 | maximum | int | 環境設定オプションの最大値 |
| 4 | config_value | int | sp_configureで設定した環境設定オプションの値 |
| 5 | run_value | int | 現在実行中の環境設定オプションの値 |
設定 †
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」オプションを１（有効）に設定する必要がある。
- このオプションは、既定では０（無効）に設定されている。
- 必要に応じて「show advanced options」オプションを１（有効）にする。
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure
GO
重要な環境設定オプション †
max server memory †
確保する「プール メモリ（ページングされるメモリ、仮想記憶のメモリ）」の最大値を設定する。
値が「物理メモリ」のサイズを超える場合、実際に確保される「プール メモリ」は、「物理メモリ」の最大値に自動調整される。
- 既定値 : 2,147,483,647（MB）
- 既定値の場合、DBサーバに搭載されている全ての「物理メモリ」に合わせて、SQL Serverインスタンスは可能な限りの「プール メモリ」を確保する。
- このため、他のアプリケーションが「物理メモリ」を必要としている時には、ページングにより他のアプリケーションに「物理メモリ」を割り当てる。
- SQL Server専用のDBサーバの場合
既定値の設定を使用する。
- DBサーバがSQL Server専用でない場合
（他のアプリケーションまたは、他のSQL Serverインスタンスが定期的に「物理メモリ」を必要とする場合）
- 他のアプリケーションまたは、他のSQL Serverインスタンスの使用する「物理メモリ」が不足しないように、当該SQL Serverインスタンスの、「max server memory」オプションの設定を変更する。
- DBサーバに搭載されている全ての「物理メモリ」の合計から、他のアプリケーションに必要な「物理メモリ」を引いて、SQL Serverインスタンスが確保する「プール メモリ」の最大値を計算する。
- 他のアプリケーションに必要な「物理メモリ」は、起動に必要な「物理メモリ」の量だけでなく、ニーズの変化に対応できるように残す「物理メモリ」の量を考慮する。
nnnnの単位は、MBで指定する。
min server memory †
確保する「プール メモリ（ページングされるメモリ、仮想記憶のメモリ）」の最小値を設定する。
「min server memory」オプションを1GBに設定しても、
SQL Serverが自動的に1GBの「物理メモリ」を取得するわけではない。
その時々のサーバの負荷に基づいて、必要に応じて割り当てられる。
- 既定値 : 0（MB）
- 既定値の場合、他のアプリケーションが「物理メモリ」を必要として、ページングが発生している場合、動的に確保した「プール メモリ」を解放する。
- SQL Server専用のDBサーバの場合
既定値の設定を使用する。
- DBサーバがSQL Server専用でない場合
（他のアプリケーションまたは、他のSQL Serverインスタンスにより「物理メモリ」を奪われ、当該SQL Serverの使用する「物理メモリ」が不足する可能性がある場合）
- 「物理メモリ」が不足しないように、当該SQL Serverインスタンスの、「min server memory」オプションの設定を変更する。
- 例えば、サーバがSQL ServerのDBMS機能と、印刷とファイル サービス機能を提供する場合、
印刷とファイル サービス機能に「物理メモリ」を消費され、SQL Server用の「物理メモリ」が不足し、応答時間が遅くならないようにする。
nnnnの単位は、MBで指定する。
max server memory = min server memory (メモリの固定) †
- 「max server memory」オプションと「min server memory」オプションを同じ値に設定する。
- これにより、設定したサイズまで確保した「プール メモリ」を、その後、解放しないようになる。
- DBサーバがSQL Server専用でない場合
SQL Serverインスタンスで確保する「プール メモリ」を固定し、他の「物理メモリ」が不足しないように、この設定を適用する。
- 当該SQL Serverの使用する「物理メモリ」
- 他のアプリケーションまたは、他のSQL Serverインスタンスの使用する「物理メモリ」
データベースの設定 †
内部リンク †
参考 †
Microsoft SQL Server Japan Support Team Blog †
@IT：SQL Server 2000チューニング全工程 †
- （1）：メモリの自動チューニング機能を完全に把握しよう
- （3）：速報！ SQL Server 2005のデータパーティション
- （4）：SQL Serverのインデックス構造（前編）
- （5）：SQL Serverのインデックス構造（後編）
真・Dr. K's SQL Serverチューニング研修 †
サーバー構成オプション (SQL Server) †
https://msdn.microsoft.com/ja-jp/library/ms189631.aspx
Tags: :データアクセス, :SQL Server
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
MinServerMemory.png
MaxServerMemory.png
PoolMemory.png
