SE の雑記

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

Clustered Index Scan のプランを抽出する

leave a comment

クラスター化インデックススキャン (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

実行すると以下のようなデータが取得できます。
image

XML 列のリンクをクリックするとプランが見れますので、どこでクラスター化インデックスキャンが行われていたかを確認できます。
image

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 を検索するという方法もあるかもしれないですね。
image

Written by masayuki.ozawa

4月 23rd, 2012 at 1:02 pm

Posted in SQL Server

Tagged with

Leave a Reply

*