SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

類似クエリの調査

leave a comment

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

 

実行結果がこちらになります。

image

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

image

類似クエリがまとめられないかは開発段階から検討しておきたいですね。

Written by masayuki.ozawa

8月 7th, 2014 at 9:14 pm

Posted in SQL Server

Tagged with

Leave a Reply

*