Post reply

Index Affection Performance on Insert

  • December 17, 2008 at 5:17 pm

    #195233

    We have a production DB that goes through an archive process each night

    the Archive DB is basically a copy of the production db

    recently the process started to slow down, so along with the vendor we began investigating why it was the Archive process started to slow down and continue to get worse

    The vendor found that in 3 tables in the Archive database the Primary keys had been removed so therefore the clustered indexes were gone with them

    The vendor has stated to us that this is the cause of the problem because table scans will occur instead of fast index lookups with archive runs and starts inserting records

    to my knowledge the archive process is querying each table in the production db (which has clustered indexes) on a modified_time field and inserting any matching records according to its criteria into the Archive DB (where the 3 tables in question now have no indexes)

    so my question is this: why would the process be performing a table scan when the inital select query is being run against the production db that has clustered indexes, and simply inserting the return result into the archive db?

    Does the query type 'Insert Into Select From' also perform a table scan on the table its inserting into?

  • December 17, 2008 at 5:41 pm

    #914257

    Before inserting the record, SQL Server needs to know whether or not the rows being inserted already exists. This would be defined by either a primary key contstraint - or a unique constraint.

    Without the index to support the constraint - a table scan is required.

    There could be other reasons that a table scan would be required. It really depends on what other indexes still exist on those tables.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

  • December 18, 2008 at 4:52 pm

    #914922

    thanks heaps for that...

    cheers...

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

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