前回の投稿の類似となりますが、SQL Server でカーソルを使用するクエリを実行したときのキャッシュについて軽くまとめてみたいと思います。
今回は以下のようなクエリを実行しています。
SET NOCOUNT ON GO DECLARE @col1 uniqueidentifier DECLARE source_cursor CURSOR FOR SELECT Col1 FROM SourceTable OPEN source_cursor FETCH NEXT FROM source_cursor INTO @col1 BEGIN TRAN WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO DestTable VALUES(@col1) FETCH NEXT FROM source_cursor INTO @col1 END COMMIT TRAN CLOSE source_cursor DEALLOCATE source_cursor
カーソルでソースのテーブルを SELECT し、それをほかのテーブルに INSERT するシンプルなクエリとなっています。
このクエリを実行した後に以下のクエリを実行してキャッシュの状況を調査してみます。
SELECT
st.text,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text,
qs.execution_count,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_logical_reads,
qs.total_physical_reads,
qs.total_logical_writes,
qs.statement_start_offset,
qs.statement_end_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
OPTION (RECOMPILE)
実行結果が以下になります。
この結果を確認することで、カーソルを使用した処理でデータが読み込まれるタイミングを確認することができます。
「DECLARE source_cursor CURSOR FOR SELECT Col1 FROM SourceTable」では、read は発生していません。
そのためカーソルを宣言したタイミングではデータの読み込みは行われていません。
データの読み込みは最初に「FETCH NEXT FROM」したタイミングで発生しています。
今回はキャッシュをクリアした状態でクエリを実行しています。そのため、初回の読み込みについては物理 I/O となるため、「total_physical_reads] が発生することになります。
物理読み込みが発生したのは初回の FETCH のタイミングとなり、それ以降は論理読み込みとなっていることが確認できます。
カーソルを使用した処理では、レコードを進めるための FETCH が処理の中で負荷がかかっていることも確認できますね。