2

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?

Kiquenet
  • 143
  • 8
Mike Gleason jr Couturier
  • 140
  • 1
  • 7
  • You insert rows then your application times out accessing the just modified table, correct? Do you do anything else after the table modification such as rebuild indexes, update statistics? Is this simply opening the frontend, is it a query that is returning a large number of rows, etc? – jl. Jul 23 '10 at 15:23
  • "[..] modified table, correct?" Yes :) We don't do anything else. After some more investigations, I think that the server doesn't re-index quickly enough so the queries made on indexed columns (but on the new data) eventually times out. The queries are not returning large amount of data but can be quite complex.. – Mike Gleason jr Couturier Jul 23 '10 at 15:28
  • the timeouts are gone after a few minutes.. – Mike Gleason jr Couturier Jul 23 '10 at 15:29

3 Answers3

2

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
1

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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • Hi, if I manually update the statistics, is the procedure will block any other connections from performing queries? Will they be in a queue until the stats are updated? Or the incoming connection will use the old execution plans? Thanks! – Mike Gleason jr Couturier Jul 26 '10 at 14:30
  • It should be an online operation on 2005+ connections will only use old exec plans in ASYNC mode. Don't know if running an update statistics will queue queries if ASYNC is off. Maybe someone else knows? best test it & see. Will get back to you :D – Nick Kavadias Jul 27 '10 at 07:17
  • applies to SQL Server 2012 or prior? – Kiquenet Sep 27 '18 at 11:32
0

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.

SqlACID
  • 2,166
  • 18
  • 18