SQL Server でアドホッククエリを頻繁に実行している場合、類似クエリが複数キャッシュされている状態となり、メモリを効率よく使用していない状態となることがあります。
今回の投稿では類似クエリの調査について少しまとめてみたいと思います。
詳細について クエリおよびクエリ プラン ハッシュを使用した類似クエリの検索およびチューニング を。
以下のようなクエリを実行したとします。
IF OBJECT_ID('tempdb..#usp_proc', 'P') IS NOT NULL
DROP PROCEDURE #usp_proc
GO
CREATE PROCEDURE #usp_proc
@param1 int
AS
SELECT * FROM sys.objects WHERE object_id = @param1
GO
SELECT * FROM sys.objects WHERE object_id = 1000
GO
SELECT * FROM sys.objects WHERE object_id = 1001
GO
SELECT * FROM sys.objects WHERE object_id = 1001
GO
SELECT * FROM sys.objects AS so WHERE object_id = 1001
GO
EXEC #usp_proc 3000
GO
DECLARE @val int = 2000
EXEC sp_executesql N'SELECT * FROM sys.objects WHERE object_id = @param1', N'@param1 int', @param1 = @val
GO
アドホッククエリ / ストアドプロシージャ / パラメータ化クエリを実行しています。
これらのクエリは別のキャッシュとしてメモリ上に格納されています。
これらのクエリのクエリハッシュですが、ステートメントレベルでは実施していることは同じため同一になっています。
クエリハッシュを確認するために以下のクエリを実行してみます。
SELECT est.text , query_hash , query_plan_hash FROM sys.dm_exec_query_stats eqs OUTER APPLY sys.dm_exec_sql_text(sql_handle) est ORDER BY est.text
実行結果がこちらになります。
query_hash は同一となっています。
# 今回は各クエリで使用されているプランも同一なので、query_plan_hash も同一となっています。
これらのクエリはステートメントとしては同じになるため、ストアドやパラメーター化クエリで統一することでクエリのキャッシュ領域の使用状況を抑えることができます。
同一のクエリハッシュとなっているクエリを確認する場合は以下のようなクエリでしょうか。
SELECT MAX(est.text) AS text , COUNT(*) AS count , query_hash , query_plan_hash FROM sys.dm_exec_query_stats eqs OUTER APPLY sys.dm_exec_sql_text(sql_handle) est GROUP BY query_hash , query_plan_hash HAVING COUNT(*) > 1 ORDER BY count , text
類似クエリがまとめられないかは開発段階から検討しておきたいですね。