Funtime
I’ve added a couple ideas to User Voice.
The ideas are pretty simple:
Eager Index Spools Should Generate Missing Index Requests
In query plans where an Eager Index Spool is directly after a data access operator, a missing index should be generated in the query plan, and/or missing index DMVs that describes the definition of the index needed to make the spool unnecessary.
I would not expect this to happen when a Lazy Index Spool occurs above a subtree.
I’d appreciate it if you’d consider voting for it. It’s something that I was able to implement pretty easily in sp_BlitzCache.
Eager Index Spools Should Generate Wait Stats
In query plans where an Eager Index Spool is directly after a data access operator, wait stats should be generated while the Spool is built. In a parallel plan, EXECSYNC waits are generated, but in a serial plan, you don’t see anything. Problem scenarios will become more common when FROID is released and adopted.
I would not expect this to happen when a Lazy Index Spool occurs above a subtree.
Thanks for reading!
And voting as many times as possible ?
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.