SQL Server Performance

Table Spool/Eager Spool

Discussion in 'Performance Tuning for DBAs' started by hominamad, Feb 27, 2004.

  1. hominamad Member

    Does anyone know of a way to get rid of a Table/Eager Spool? We're doing an INSERT statement like:

    INSERT INTO
    TABLE
    SELECT
    n
    FROM
    n

    This could sometimes INSERT up to 1000 rows. We are seeing 4 Eager Spools, I believe for each of the 4 indexes on the table. This consumes 75% of the query. When inserting about 1000 rows, it takes about 6 or 7 secs. Is this normal? Any suggestions?

    Thanks!
    H
  2. satya Moderator

    It appears to me that the situation would occur when there are multiple rows to handle and it is using default transaction isolation level and concurrency handling. So long as there is only one row to be inserted at a time, then it should be OK.

    The INSERT for multiple rows will be treated as a single transaction which is not committed until all rows are written. A SELECT only reads committed information. Therefore when it performs the function in relation to the second row to be inserted, the first is not yet committed, so it cannot see the value that was placed in it.

    May try using READ UNCOMMITTED and see it gives any help.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. mjhancock New Member

    quote:Originally posted by hominamad

    Does anyone know of a way to get rid of a Table/Eager Spool? We're doing an INSERT statement like:

    INSERT INTO
    TABLE
    SELECT
    n
    FROM
    n

    This could sometimes INSERT up to 1000 rows. We are seeing 4 Eager Spools, I believe for each of the 4 indexes on the table. This consumes 75% of the query. When inserting about 1000 rows, it takes about 6 or 7 secs. Is this normal? Any suggestions?

    Thanks!
    H
  4. FrankKalis Moderator

Share This Page