SQL Server, Lazy Spool runs endlessly in View but not when I run the query directly-CodePudding

I have a long query that feeds a daily Excel report. It stopped working for some reason. The saved view will run endlessly to the point where it crashes the SQL Server. But when I run the query itself (copied and pasted from the ALTER view), it actually runs fine and completes in about 17 seconds.

I've narrowed down the culprit as this Lazy Spool. This is the live query stats running the query directly:

This is the view running:

I stopped it there. It will run endlessly until the Lazy Spool has billions of lines and until it crashes the SQL server.

Not sure how much of the query I can share directly because of employer policies but does anybody have any idea why this is happening and how I can fix it?

CodePudding user response：

Your execution plan is not stable. You might be able to fix it temporarily by updating statistics on any/all of the tables in your view. In the second screenshot the table is placed on the right side of a join. Likely the best way to fix this would be to index your tbl_SO table to better support the join condition within the view.

You could also attempt to use OPTION (NO_PERFORMANCE_SPOOL) on your query to see if that fixes your issue. That hint would have to go outside of your view.

EX: SELECT ... FROM myView WHERE ... OPTION (NO_PERFORMANCE_SPOOL);

CodePudding user response：

But when I run the query itself (copied and pasted from the ALTER view), it actually runs fine and completes in about 17 seconds.

Have you tried running your view query with OPTION (RECOMPILE)? That's my best guess without the whole plan. It's possible that the view query is caching a bad plan (which would explain things). A view (unindexed) is simply ad-hoc SQL that is stored for repeated use and therefore should create the exact same execution plan. At a birds eye level, the culprit is that you are getting a different execution plan for the view and the view code ran as ad-hoc.

Yes, the lazy spool appears to be a culprit but I suspect it is a symptom of the issue. With the ad-hoc query you are retrieving 2,393,900 of 2,393,900 rows from your clustered index, the view query is fetching 8,383,512 of 2,393,900 rows, then the lazy spool is then queueing up 15,127,006 rows. This suggests that something dramatically different is happening behind the scenes, and causes me to even suspect that you are not running the exact same code.

This is a little advanced but in the article Nasty Fast PERCENT_RANK I discuss the lazy spools, when they are bad (often they are great) and how to alter your code to get the same result while avoiding a lazy spool. That might help.