I will be presenting a day-long pre-conference at SQLPASS on the topic – 21 Essential Scripts: Jump-Start Performance Tuning for Accidental DBAs and regular session as well. Lots of people are asking me what I will be covering in the session, so I have decided to give sneak preview about it on the blog of PASS.
I have written a blog post about Optimizing Lazy Spool on PASS Blog. I am taking the first two paragraph from the blog post to reproduce here. If you want to learn about this subject, you can further visit the blog post and learn how I have fixed Optimizing Lazy Spool.
One of the most common practices in tuning queries is to enable the actual (or estimated) execution plan and optimize any operator that has the highest cost. You can enable execution plan for any query by either pressing CTRL + M (Key Board Shortcut) or by going to Toolbar >> Query >> Include Actual Execution Plan in SQL Server Management Studio (SSMS).
Once the query renders the execution plan, users visually observe the execution plan and try to find the operator that has taken the maximum cost for the query and try to optimize that operator. This method is a very common practice among the SQL Server Performance Tuning Experts, and it usually works for most of the common scenarios.
However, if you come across the lazy spool operator in your query, this common practice will not be as effective.
Here is the link to the blog post which explains how to resolve the issue with Lazy Spool.
Reference: Pinal Dave (https://blog.sqlauthority.com)