NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。


スポンサーリンク

【第13回】基本から始める SQL Server【メモリ管理】


スポンサーリンク

第12回では 整合性チェック コマンド (DBCC CHECKDB) を使用し、不整合の発生したデータベースを修復する方法についてまとめてみました。

今回は、SQL Serverのメモリ管理に関する内容をまとめてみようと思います。

 

 

SQL Server メモリ管理について

SQL Server プロセスは既定で動的メモリが有効になっており、パフォーマンスを最大限に発揮するため、使用できる限りのメモリを確保し、必要に応じて解放するという動作が行われています。

※ SQL Server がインストールされたサーバーでは、一般的に SQL Server プロセスで多くのメモリが使用されますが、本動作は正常な動作になります。

なお、SQL Server で使用可能なメモリ領域については、 サーバーメモリの構成オプション「max server memory (MB)」、「min server memory (MB)」で制御することができます。

 

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)」で制御可能なメモリ領域が増えました。

f:id:nobtak:20210116192010p:plain

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 サービスを再起動させることなく、即時反映させることができますが、インストール前に設定値を考慮しておくと良いかと思います。