第12回では 整合性チェック コマンド (DBCC CHECKDB) を使用し、不整合の発生したデータベースを修復する方法についてまとめてみました。
今回は、SQL Serverのメモリ管理に関する内容をまとめてみようと思います。
SQL Server メモリ管理について
SQL Server プロセスは既定で動的メモリが有効になっており、パフォーマンスを最大限に発揮するため、使用できる限りのメモリを確保し、必要に応じて解放するという動作が行われています。
※ SQL Server がインストールされたサーバーでは、一般的に SQL Server プロセスで多くのメモリが使用されますが、本動作は正常な動作になります。
なお、SQL Server で使用可能なメモリ領域については、 サーバーメモリの構成オプション「max server memory (MB)」、「min server memory (MB)」で制御することができます。
[参考URL]
「max server memory (MB)」は既定で "2,147,483,647 メガバイト (MB)" が設定されており、サーバーに搭載されたすべてのメモリが使用可能になっています。
なお、SQL Server プロセス以外のプロセス (OS 含む) で大量のメモリが要求された場合、OS のページング機能により、SQL Server プロセスによって確保されたメモリ領域がページアウトされる可能性があり、ページ イン／アウトの処理がボトルネックとなり、クエリ処理のパフォーマンスが低下する恐れがあります。
そのため、SQL Server プロセス以外で使用されるメモリ領域のベースラインをパフォーマンス カウンタなどにより採取し、物理搭載メモリ量 - SQL Server 以外のプロセスで使用されているメモリ量 + α の値を 「max server memory (MB)」に設定すると良いかと思います。
「min server memory (MB)」は既定で "0 (MB)" が設定されています。
SQL Server と同じサーバーに他のソフトウェア／ミドルウェアをインストールし、SQL Server 以外のプロセスで多くのメモリが消費してサーバー全体の空きメモリ容量が不足すると、SQL Server プロセスではサーバー全体のメモリの空き容量が不足していることを検知し、自身で確保したメモリ領域を「min server memory (MB)」 に指定した値まで下げるように動作します。そのため、SQL Server 以外のプロセスで大量のメモリが消費されると、SQL Server で使用可能なメモリが不足し、メモリ不足を示すエラー (エラー 701 : 「このクエリを実行するには、リソース プール '%1!' のシステム メモリが不足しています。」、エラー 802 : 「バッファー プールで使用できるメモリが不足しています。」 など) が発生する場合があります。
そのため、明示的に 「min server memory (MB)」を指定し、SQL Server プロセスで最低限、使用可能なメモリを確保するように設定すると良いかと思います。(例えば、SQL Server ハードウェア要件に記載されているメモリの最小値 : 1024 MB を設定。)
max server memory で制御可能なメモリ領域について
SQL Server 2012 以降、大幅にメモリ管理のアーキテクチャが変更され、「max server memory (MB)」で制御可能なメモリ領域が増えました。
max server memory で制御可能なメモリ領域 (詳細)
【Single Page に含まれるキャッシュ】(一部抜粋)
- SQL バッファプール (実際のデータを含むページデータのキャッシュ)
- プロシージャ キャッシュ (実行プラン情報などが含まれるキャッシュ)
- ログ プール (トランザクション ログのキャッシュ)
- ストレージエンジン キャッシュ (データベース構造のメタデータ、TempDB バージョン情報、ファイル制御ブロック (FCB) などが含まれるキャッシュ)
- SQL オプティマイザ キャッシュ (実行プランを生成するプロセスで使用されるキャッシュ)
- クエリ ワークスペース (クエリ実行時のデータの並び替え、結合処理時に使用されるキャッシュ)
- SQL コネクション プール (クライアントからの接続時に使用されるキャッシュ)
- SQL グローバル アクセス キャッシュ (ロック、セッション情報などで使用されるキャッシュ)
【Multi Page に含まれるキャッシュ】(一部抜粋)
- 拡張ストアドプロシージャ
- XML
- リンク サーバー
- 8KBを超えた実行プラン情報
- 8KBを超えたネットワーク パケット
【SQL CLR に含まれるキャッシュ】(一部抜粋)
- SQL CLRで使用されるメモリ領域 (アセンブリ情報など)
max server memory で制御できないメモリ領域 (詳細)
- SQL Server プロセスのスタック用メモリ領域
- OS により直接メモリ割り当て (Direct Windows Allocations (DWA)) が行われたメモリ領域。SQL Server プロセスのメモリ空間にロードされた実行モジュール (DLLなど) 、オートメーション プロシージャ (sp_OA) で作成されたオブジェクトからのメモリ割り当てについては、SQL Server Memory Clerk 経由でのメモリ割り当てが出来ないため、max server memory で制御することが出来ない。
まとめ
今回は、SQL Serverのメモリ管理に関する内容についてまとめてみました。
SQL Server の場合、一般的に既定の設定でインストールするのみで、パフォーマンス良く動作しますが、サーバーメモリの構成オプション「max server memory (MB)」、「min server memory (MB)」については、後から設定を変更し、SQL Server サービスを再起動させることなく、即時反映させることができますが、インストール前に設定値を考慮しておくと良いかと思います。
【第14回】基本から始める SQL Server【ページとエクステント】へ
