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)」で制御することができます。

 

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

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

 

【第14回】基本から始める SQL Server【ページとエクステント】へ