Home / server / Questions / 163442
In Process
Mike Gleason jr Couturier
Asked: 2010-07-24 07:05:24 +0800 CST

Large insert/update in table causes timeout while SQL Server re-arrange indexes

  • 772

We insert/delete about 1 million rows of a two million rows table with SSIS.

We have 20 indexes on this table.

Right after the SSIS finished, we're experiencing timeouts in the frontend. The timeouts eventually go away.

I'm suspecting the indexes are causing this during re reorganisation of them.

What can we do?

sql-server timeout indexing bulk

3 Answers

  1. The indexes are updated as the data is updated, index updates are synchronous.

    Now updating statistics is an asynchronous operation. After the data update is done manually do an update statistics on the tables in question.

    • 2

  2. By updating more than 20% rows in the table you are triggering a statistics update.

    The default behavior of sql server is to do this synchronously. This means that if an index affected is used in an execution plan the optimizer notices that the statistics are out of date, forces an update right away and re-compiles the execution plan.

    There is a new feature in SQL Server 2005 that will make the statistics update asynchronous for the database, i.e. the optimizer will use the stale statistics, but run the statistics update in the background, so that the next query had take advantage of the new stats. The command is AUTO_UPDATE_STATISTICS_ASYNC ON see BOL for more information.

    The alternative as already suggested is to force an update manually on the table after the load. You can do this with the UPDATE STATISTICS command. Here is the BOL reference.

    • 1

  3. If you're using the Bulk Insert task in SSIS, you could try setting the batch size, this would break it down into smaller chunks with commits in between.

    • 0