Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Very strange table spool in query execution plan.

Unanswered Very strange table spool in query execution plan.

  • Wednesday, February 27, 2013 2:20 PM
     
     
    Sign In to Vote
    0
    Good day.
    My query was slow.
    I watched the actual execution plan and see that it has a very strange table spool
    input is the expected count of rows 50 000 and the output (the actual count) 50 million...
    I checked the query to the test server (where query very fast) and has seen in terms of table spool but whose input and output the same count of rows.
    I rebuilt the indexes on the production DB (on the tables involved in the query) but it did not help my, execution plan remains the same.
    Where I have a problem?
    Do I need to worry about the condition of the disc which is tempdb?

    • Edited by Dedyshka Wednesday, February 27, 2013 2:23 PM
    • Edited by Dedyshka Wednesday, February 27, 2013 3:56 PM
    •  
     

All Replies

  • Wednesday, February 27, 2013 3:28 PM
     
     
    Sign In to Vote
    0

    So it seems that the optimizer is making a misestimate somewhere. To say anything useful, we would as a start need to see the query. Most likely we will ask for CREATE TABLE and CREATE INDEX statments for the tables, as well as the query plan.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, February 27, 2013 3:55 PM
     
     
    Sign In to Vote
    0
    Estimate of optimizer is based on the statistics
    Note that a full rebuild indexes (delete, create) does not solve the problem
    and estimated plan shows the correct number of rows in the input and output (equal to each other).
     
  • Friday, March 01, 2013 9:49 PM
     
     
    Sign In to Vote
    0

    Yes, but I am afraid that this is taken us any nearer to a solution. Can you make the actual execution plan (in XML format, not a graphic), available somewhere?

    (Sorry for the late reply, but I use the NNTP Bridge, and it was out of order for two days.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Saturday, March 16, 2013 4:37 PM
    Moderator
     
     
    Sign In to Vote
    0

    Can you post code & DDL?  What is @@version? Thanks.

    >Do I need to worry about the condition of the disc which is tempdb?

    It is helpful with performance if tempdb data and tempdb log are on fast independent dedicated disks:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012