クラスター化インデックススキャン (Clustered Index Scan) の実行プランが発生した場合、対象のテーブルに対して全件走査 (フルスキャン) が発生するので、データの件数によってはコストの高いクエリとなっている可能性があります。
勉強がてら、キャッシュされているプランからクラスター化インデックススキャンが発生している実行プランを抽出するクエリを書いてみました。
元になるクエリは 暗黙の型変換の影響 を参考にさせていただきました。
■Clustered Index Scan のプランを抽出
以下のようなクエリを使用するとクラスター化インデックスキャンが発生している実行プランを抽出することができます。
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp) SELECT RO.C.value(N'@LogicalOp','varchar(20)') as LogicalOp , text , query_plan , creation_time --, RO.C.value(N'@EstimateRows','numeric(20,5)') as EstimateRows , min_elapsed_time , max_elapsed_time , min_logical_reads , max_logical_reads , min_logical_writes , max_logical_writes FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY query_plan.nodes(N'//sp:RelOp') as RO(C) WHERE RO.C.value(N'@LogicalOp','varchar(20)') = 'Clustered Index Scan' --ORDER BY --EstimateRows DESC
実行すると以下のようなデータが取得できます。
XML 列のリンクをクリックするとプランが見れますので、どこでクラスター化インデックスキャンが行われていたかを確認できます。
2.5 GB / 3 万 5000 クエリ程度のプランキャッシュがある状態で SSMS で情報を取得する場合、2 分程度かかり、1 スレッド分の CPU は貼り付けそうでした…。
# プランキャッシュの増加は以下のクエリを実行しています。
SET NOCOUNT ON GO DECLARE @sql nvarchar(max) DECLARE @i int = ASCII(‘A’) WHILE(@i <= 50000) BEGIN SET @sql = N’DECLARE @tmp nvarchar(max);SELECT @tmp = N”’ SET @sql += REPLICATE(NCHAR(@i), 4000) SET @sql += ”’ FROM Table_5′ EXEC (@sql) IF (@i % 1000 = 0 ) BEGIN PRINT @i END SET @i += 1 END |
インデックスの使用状況や更新状況は [sys.dm_db_index_operational_stats] [sys.dm_db_index_usage_stats] で確認することができるので、始めに DMV で [user_scan] を確認し、[user_scan] の多いテーブルに絞って XML を検索するという方法もあるかもしれないですね。