これは、最大 RAM 使用量として 15 GB が構成された SQL Server 2016 CU2 インスタンスです。MDOP は 4 です。このインスタンスのデータベースはユーザーによって非常に散発的にクエリされ、これは読み取り専用のセカンダリ レプリカです。
問題は、このサーバーで同時に他のクエリが実行されていない場合でも、毎週、すべてのクエリがメモリ不足のために RESOURCE_SEMAPHORE 状態になることです。インスタンスを再起動すると、クエリは再び動作を開始します。以下の画像から、最大ワークスペース メモリが不足していること、およびこれらのクエリが非常に悪い (クエリ コストが天文学的である) ことがわかります。
再起動前: 使用可能な最大ワークスペース メモリは 1.8 GB のみで、すべての不正なクエリが RESOURCE_SEMAPHORE 状態になります。
インスタンスの再起動後: ワークスペース メモリが十分に確保され (約 11 GB)、ひどいクエリでも RESOURCE_SEMAPHORE はなくなりました。
ご覧のとおり、これは約 2 GB の RAM を要求する厄介なクエリです。この画像では、SSMS は、インスタンスが再起動されたため、必要なメモリが実際に付与されたことを示しています。再起動前は、メモリ付与は NULL のままで、クエリは RESOURCE_SEMAPHORE 待機状態のままです。
ここで知りたいのは、最大ワークスペース メモリが時間の経過とともに減少し、クエリが実行されていないときに解放されないのはなぜかということです。
答え1
割り当てられた合計メモリ 15,728,640,000 バイトのうち、盗まれたサーバー メモリが 12,828,864,000 バイトを占めており、通常の操作を行うには明らかに足りないようです。
文書にはこう記されている SQL Server、メモリ マネージャー オブジェクト:
盗まれたサーバーメモリ (KB)
サーバーがデータベース ページ以外の目的で使用しているメモリの量を指定します。
アン答えさらにこう述べています。
盗まれたメモリとは、ソートやハッシュ操作 (クエリ ワークスペース メモリ) に使用されているバッファ、またはロック、トランザクション コンテキスト、接続情報などの内部データ構造を格納するための割り当て用の汎用メモリ ストアとして使用されているバッファを指します。レイジーライター プロセスは、盗まれたバッファをバッファ プールからフラッシュすることはできません。
無駄なクエリによって膨大なソート操作が実行されたため、SQL Server は割り当てられたメモリを解放しません。メモリはバッファー プールから取得され、他の用途には使用できません。
選択肢はあまり多くありません:
- クエリを改善して、大量のデータをソートしないようにする
- SQL Server にメモリを追加する
- 定期的に、または特にこのクエリを実行した後は、SQL Server を再起動してください。