Optimizing SQL Server Query Plans With Eager Index Spools In Them
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Is this a relatively good way to locate this issue?
SELECT
[Database] = DB_NAME(detqp.dbid),
[Statement] = SUBSTRING( dest.text,
(deqs.statement_start_offset / 2) + 1,
(
CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END – deqs.statement_start_offset
) / 2 + 1
),
[Plan] = CAST(detqp.query_plan AS XML),
[Count] = deqs.EXECUTION_COUNT,
[Time] = deqs.total_elapsed_time,
[Reads] = deqs.total_logical_reads,
[Writes] = deqs.total_logical_writes
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE detqp.query_plan LIKE ‘%Index spool%’;
A Query To Help You Find Plans With Eager Index Spools In SQL Server’s Plan Cache