SQL Server で確保されるメモリ領域は既定の動作として、物理メモリのフリー スペースに対して SQL Server の処理で必要な分だけメモリを確保しようとします。
つまり、既定のままでは確保するメモリの上限がありません。(厳密には設定されていますが、とてつもない容量が設定されています)
そのため、この動作を把握していない状態で SQL Server の運用を行うと、ワークロードの処理が増えるほどメモリが確保され、あたかもメモリ リークが発生しているように見えます。
かといって確保されるメモリが少ないとストレージからデータを読み込まれる回数が増えてオーバーヘッドが生じてしまうので、出来るだけ多くのメモリを確保することがパフォーマンスの観点から望ましいですが、他のアプリケーションに影響を及ぼす可能性もあるので環境に応じたメモリ設定を行うことが大切なのかなと思います。
メモリ最大値の設定
SQL Server が確保できるメモリ最大値の設定については GUI (SSMS) のほか、クエリでも設定することができます。
SSMS ではサーバーのプロパティからメモリの項目で確認することができます。
以下のスクリーンショットの赤枠箇所で設定されている値がメモリ最大値になります。
また、規定値として 2147483647 MB (約 2 PB くらい？) が設定されています、こんなメモリ量を積める時代がいつかくるのかな…
試しに 8 GB くらい設定してみます。
これだけで設定は完了です。
SSMS だとシンプルですね。
一方で、クエリの場合は少しめんどくさくなります。
-- advanced options を有効にする
sp_configure N'show advanced options', 1;
go
reconfigure;
go
sp_configure N'max server memory', 8192;
go
reconfigure;
go
-- advanced options を無効にする
sp_configure N'show advanced options', 0;
go
reconfigure;
go
設定の確認を行うには以下のクエリで可能です。
select [value],[value_in_use]
from sys.configurations
where [name] = N'max server memory (MB)';
メモリ最大値の動作を確認してみる
百聞は一見に如かず、実際に試してみます。
メモリの使用率については Windows のパフォーマンス カウンターから確認するのが一般的なのでしょうか、とりあえず今はぱっと確認ができれば良いので DMV で確認してみます。
分かりやすいように MB に変換してみました。
committed_kb もとい committed_mb では 295 MB と出力されました。
メモリ最大値を試しに 200 MB として、再度確認します。
199 MB とほぼほぼ上限値に張り付いていることが確認できました。
メモリ最小値の設定
最大値のほか、最小値の設定もできます。
こちら勘違いポイントですが、例えばメモリ最小値を 1 GB にしたからって、SQL Server サービスが開始された段階で 1 GB を確保する訳ではありません。
サービス開始時の SQL Server は通常通りのメモリ確保を行います。
処理を行っていくなかで確保されるメモリ量が 1 GB を超え、かつ OS が SQL Server のメモリを解放する必要があると判断した際に 1 GB を下回る解放を防止します。
要は「確保している 1 GB のメモリは解放しないでね」設定です。
SSMS からの設定方法はメモリ最大値を設定する箇所の上部の項目から設定します。
クエリの場合もメモリ最大値の設定同様です。
max server memory を min server memory にしてメモリ量を設定するだけですね。
-- advanced options を有効にする
sp_configure N'show advanced options', 1;
go
reconfigure;
go
sp_configure N'min server memory', 1024;
go
reconfigure;
go
-- advanced options を無効にする
sp_configure N'show advanced options', 0;
go
reconfigure;
go
確認も同様。
select [value],[value_in_use]
from sys.configurations
where [name] = N'min server memory (MB)';
メモリ最小値の動作を確認してみる
最大値と同様にこちらでも確認してみます。
まずは現在のメモリ使用率の確認から。
ここで、メモリ最小値の値を 300 MB、メモリ最大値の値を 200 MB に設定して再度確認してみます。
最大値が 200 MB で設定されていますが、最小値では 300 MB が設定されているので確保されていた 300 MB 分のメモリは解放されていないことが確認できますね。