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. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time 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