CODEWITHSUNDEEP
sql-serversql-server-2017
trenton-ftw
trenton-ftw

Reputation: 972

Query hint 'NO_PERFORMANCE_SPOOL' being ignored?

I am working in a vendor's large SQL Server database and I have been tasked with data warehousing large volume of data from there. The server is SQL Server 2017 (14.0.3391.2) and DB where queries are running is set to compatibility level of 140.

I have several queries where the optimizer is deciding to use lazy/eager index spooling within the query plan. In the past (SQL Server 2016 forward) I have been able to easily prevent this using the query hint of 'NO_PERFORMANCE_SPOOL'. In the past, this has always led all index spools in the query plan being removed. For some reason, when applying that hint in the database, index spools still remain in the execution plan? I know I am viewing the actual execution plan (viewed plan provided in SSMS after allowing the query to execute with 'Include Actual Execution Plan' checked). I have not been able to find any other documented examples of someone claiming this is happening to them, so that leads me to believe I am missing something? I know hints can be ignored (or even cause errors) if the plan is not produceable using the provided hints, but I can't imagine that being the case here? I don't understand how it would be possible for an index spool to be REQUIRED for a plan to be compiled? Is my hint just straight up being ignored for some reason?

So I am at a bit of a loss. I know I could eliminate the index spools using other methods like rewriting the queries or adding indexes, but I am not interested in that at this point. I just want to know why this query hint seems like it is being ignored?

PS: For anyone wondering, these queries suffer significantly when plans using index spooling are used. This server in general suffers from I/O bottlenecking and resource semaphore waits (related to memory grants. Again, not my server/issue), so index spooling ends of becoming a large issue.

PS PS: I would post query plans, but I think the data structure/naming and produced query plans are proprietary to the vendor. Plus I don't think it is really needed here. If anyone thinks that might be helpful then I can do more work to see if I can recreate using AdventureWorks or something.

Upvotes: 1

Views: 2585

Answers (1)

Charlieface
Charlieface

Reputation: 72119

To quote the great @PaulWhite on this subject:

While an eager index spool may only appear on the inner side of an nested loops apply, it is not a “performance spool. An eager index spool cannot be disabled with trace flag 8690 or the NO_PERFORMANCE_SPOOL query hint.

A performance spool is a "lazy spool", not an eager spool, and is placed there using different optimization rules.

He also says

In some respects, an eager index spool is the ultimate missing index suggestion

I therefore strongly suggest you index that table to remove the spool

Upvotes: 2

Related Questions