Why am I seeing logical reads on insert into #tmp table

  • Here's the table definition. There are no indexes nor constraints on it.

     CREATE TABLE #Action
    (Project_Id INTEGER NOT NULL,
    Agent VARCHAR(64) NOT NULL,
    dCallDate DATE NOT NULL,
    Call_No INTEGER NOT NULL,
    nWeek INTEGER NOT NULL,
    bNesting BIT NOT NULL,
    nWasMonitored TINYINT NOT NULL)

    I do a bunch of inserts, adding up to 225K rows. On each insert, I'm seeing logical reads on that table.

    I don't understand why?

    Thanks!

     

     

  • Where are you seeing logical reads?  How are you inserting data into the table?

    If you are using a query - then that query must read the data, therefore you are going to get logical reads and possibly physical reads on every insert.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I could have been clearer: the logical reads are on the target of the INSERT.

    It's an INSERT #Action/EXECUTE dbo.sp_executeSQL, inside a cursor. Could it be because it's dynamic SQL?

    I SET STATISTICS IO ON:

    Table.... #Action. Scan count 0, logical reads 1474, physical reads 0, read-ahead reads 0, lob logical reads

    I get one of these for each loop of the cursor, of varying reads on #Action, depending on the rows. #Action is not referenced in the query/dynamic SQL.

    • This reply was modified 3 months, 1 week ago by  schleep.
  • Well SQL Server still has to access the table to do the inserts which is IO.

     

  • What is the reason you are concerned about those logical reads?  What issue are you trying to resolve - or is this just educational?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I agree with ZZartin and even have a sample script that replicates this behavior:

    CREATE TABLE #tmpTable (id INT)
    GO
    SET STATISTICS IO ON
    INSERT INTO [#tmpTable]
    (
    [id]
    )
    VALUES
    (
    0-- id - int
    )
    INSERT INTO [#tmpTable]
    (
    [id]
    )
    VALUES
    (
    0-- id - int
    )
    INSERT INTO #tmpTable
    SELECT 1
    UNION
    SELECT 2
    SET STATISTICS IO OFF
    GO
    DROP TABLE [#tmpTable]
    GO

    Creates temp table, turns statistics IO on, does 3 inserts, then cleans itself up.  During the first insert, 1 logical read and 1 row inserted.  Second insert, 1 logical read and 1 row inserted.  3rd insert, 2 logical reads and 2 rows inserted.

    My guess without having access to SQL internals is that the insert needs to read the page out before doing the insert.  So when you are seeing 1474 logical reads, it is reading 1474 pages into memory, modifying them with the new data and writing them back to disk.

     

    Now, one thing I'd be curious about is if that cursor and dynamic SQL is actually required or if it could be written without either the cursor or the dynamic SQL.  Cursors are generally slower bits of code and dynamic SQL is generally a dangerous bit of code.  Not always though; both do have their place, but in general I try to avoid them.

    • This reply was modified 3 months, 1 week ago by  Mr. Brian Gale. Reason: fixing up some of the TSQL

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Thank you all.

    This was a purely educational matter.

    I share your concerns re: cursors and dynamic SQL, but sometimes there is no other way.

    At any rate, this report will likely run off-hours, not more than once-a-day.

    Cheers!

Viewing 7 posts - 1 through 7 (of 7 total)

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