Post reply

Query performance issue due to Index Spool - Lazy Spool

  • March 1, 2018 at 11:10 pm

    #336428

     

    I want to check if somebody can help advise on high CPU cost of this top consuming query due to index spool - lazy spool in the query execution plan , can anyone help advise and how to troubleshoot further ?

  • March 2, 2018 at 1:58 am

    #1981742

    muzikfreakster - Thursday, March 1, 2018 11:10 PM

     

    I want to check if somebody can help advise on high CPU cost of this top consuming query due to index spool - lazy spool in the query execution plan , can anyone help advise and how to troubleshoot further ?

    Please post an actual execution plan as a .sqlplan attachment - actual plans carry a wealth of useful information which is unobtainable from a picture.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 2, 2018 at 9:42 am

    #1981783

    To fully answer what issues may be going on, getting the full plan is going to be the best bet.

    To give you some general information, an index spool is a mechanism employed by the optimizer to assist with data processing. It's not the cause of your performance problems. No performance problems come directly from an execution plan. The execution plan is the result of the query, your data structure, the constraints and the statistics available to the optimizer. Problems don't lie with the execution plan. They lie with the code, the stats and your structures. Understanding the execution plan can help you understand why your query and structures lead to poor performance, but it is not the poor performance itself. It's always back to those other objects. The plan simply defines how the query is being met.

    An index spool operation can be verify beneficial. It ensures that data is accessed one time from disk and then put into a temporary index for reuse elsewhere in the query. This can result in significant cost savings versus going back to the original data set over and over again.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply