SQL Server ではソート / ハッシュ結合 / 並列クエリの実行時などに Workspace Memory (ワークスペースメモリ) が使用されます。
今回の投稿ではこの Workspace Memory の最大値について見ていきたいと思います。
■Workspace Memory の利用と tempdb について
Workspace Memory と tempdb には関わりがあります。
SQL Server では警告関連のイベントクラスとして以下のものがあります。
最近では あまり知られていないSQL Server 2012の機能強化点 (後編) でも紹介されていましたね。
一時領域としてメモリを使用する際にメモリが不足していると tempdb が使用され、これらの警告が発生します。
Workspace Memory が不足しているとディスクが使用され、クエリの実行効率に影響が出てきます。
■Workspace Memory の最大値
クエリの一時領域として使用される Workspace Memory ですが、現状の最大してどの程度確保することができるかを簡単に把握するためにパフォーマンスモニターの Memory Manager を利用できます。
DBCC MEMORYSTATUS の Query Memory Objects でもわかります (MEMORY STATUS はページで表示されているので 8KB を×必要があります)
Workspace Memory の最大はバッファプールの 75% ぐらいになるようですね。
この Workspace の最大値ですが一つのクエリでこの最大サイズまで使用できるということではないようです。
各クエリが使用できる最大の Workspace Memory はWorkspace Memory の最大値の 25% がデフォルトとなっています。
これはリソースガバナーの default で制御している 25% が使用されています。
リソースガバナーの設定を変更する前は以下のサイズの Workspace Memory が確保 (Granted Workspace Memory) できています。
Workspace Memory で必要になるメモリは実行プランのデータの推定サイズなども参考になってきます。
リソースガバナーのメモリ許可の割合を 5% に変更して再度クエリを実行してみます。
先程は 1.4GB 程度取得できていたものが 750MB で制限されていることが確認できます。
15,600 MB × 5% = 780 MB なので設定どおりに動作していますね。
CREATE WORKLOAD GROUP (Transact-SQL) の説明で、この辺の内容が書いてありますね。
REQUEST_MAX_MEMORY_GRANT_PERCENT =value 1 つの要求にプールから割り当てられる最大メモリ量を指定します。このパーセンテージは、MAX_MEMORY_PERCENT で指定したリソース プールのサイズが基準になります。
■Workspace memory の枯渇
5% に設定した状態で複数スレッドでソートを含むクエリのテストをしてみたいと思います。
今回はツールとして SQLQueryStress を使用してみました。
このツールを使用して20 スレッドでクエリを実行してみます。
クエリ実行中の Workspace Memory の状態がこちらです。
先程実行した結果では、5% の状態では 1 クエリにつき 750MB 使用されていました。
Workspace Memory の確保状況を見るためのカウンタとして、Memory Grants Outstanding と Memory Grants Pending があります。
Memory Grants Outstanding は Workspace Memory が確保できているスレッドになり、Memory Grants Pending は Workspace Memory が確保できていない (待ち) となっているクエリとなります。
今回は、20 スレッドで大量のソートが発生するクエリを実行してい
るのですが、20 スレッド中 1 スレッドが待ち状態となっています。
メモリ許可の状態を確認する DMV として sys.dm_exec_query_memory_grants があります。
この DMV を確認すると grant_time が NULL になっているものが 1 つあります。
パフォーマンスモニターの Memory Grants Pending と一致しますね。
この時の実行状況を sys.dm_exec_requests で確認してみるとひとつだけ wait_type が異なるセッションがあるのが確認できます。
リソースガバナーの設定を 25% に戻すとこのような形になります。
ひとつのクエリで使用できる Workspace Memory が増加していますので、先程と比較して Memory Grants Pending が増加しているのが確認できますね。
1GB 以上 Workspace Memory を使用するクエリを並列で動作させるのはめったにない (クエリを見直す必要があるかと) とおもいますが。
今までクエリエンジン回りはあまり勉強していなかったのですが、この辺の動作が PASS のセッションで詳しく解説されているので少しずつまとめていければと思います。