The query is running for 11 mins , how we can optimize in a better way
As Aaron said, providing only an old-style anonymized estimate-only execution plan severely limits the advice we can offer, but:
- Executing the complex inner side of the nested loops join anything like the estimated three million times will always take some time to complete. We would need to see the query and understand what it is trying to achieve to suggest improvements.
- The Sort is could probably be removed by providing a suitably-ordered index. This Sort is likely to spill to disk because the plan estimates that 3GB of memory would be needed, while only 332 MB is expected to be available. Perhaps your instance could benefit from more memory.
- The estimated degree of parallelism available is 2. This means you probably only have 4 cores available for SQL Server to use. If no other improvements can be made, the query would likely speed up if you are able to increase the number of logical processors available.
- The two Eager Index Spools indicate that indexes may be missing on those tables. Review the Seek Keys and Output List for these spools to help determine which indexes might be helpful.
- The Merge Join is a potentially-inefficient many-to-many join, which will use a worktable in tempdb to hold and rewind join duplicates. You may be able to turn this into a one-to-many join by creating a unique index or constraint on one of the inputs. If the data is unique, but you have no way to communicate that fact to the optimizer, consider materializing the input to a temporary table and creating the uniqueness guarantee on that.
The points above are mostly generic advice. The greatest improvement will likely come only from knowledge of the data and task at hand. This is partly why we ask for post-execution non-anonymized plans captured directly from Plan Explorer.