Erik Darling hunts the most dangerous prey of all:
I’ve written a bunch about Eager Index Spools, and how to fix them, but I’ve always sort of left the “finding” part up to you, or pointed curious tuners to tools like sp_BlitzCache.
Recently though, I worked with a client who had Eager Index Spools so frequently that we needed to track them down specifically.
This is the plan cache query that I used to do it — they didn’t have Query Store enabled — and I wanted to share it.
Click through for the query.