locked
need to rebuild index every morning RRS feed

  • Question

  • Hi,

    I have table with 550 thousand rows.
    I have a stored procedure which fetches data from this table and also from 2 other tables with inner join. These 2 tables also have 300 k to 400 k rows. Every morning around 3 am, some 5 k rows gets added to these table.

    I have setup a clustered index on int column and nonclustered index on datetime column of the table.
    if i run the stored procedure after loading data may be around 9/10 am; it fails with timeout exception.
    after that if I rebuild indexes on that table. stored procedure runs in 15 seconds.

    I added index rebuild task in the SSIS package which loads data in the table. the tasks succeeds but the stored procedure fails.
    But If I run alter index command from SQL Server Management Studio-Query, stored procedure works.

    What would be the best approach to deal with this.

    Thanks in Advance,
    Sachin

    sachin
    Friday, October 23, 2009 4:07 PM

Answers

  • Hello sachin,

    I have have a table with 100 Mio rows and about 200 k rows are added each day; I defrag the index once a week, and rebuild it once a month, and I don't have any performance problems.

    So I like to suggest to evalute the performance problem first.
    When it works slow as you mentioned, are there any locks on the table? May you run the SP while still an other process is writing/updating on that table?

    An index rebuild is an offline operation (or do you have a Enterprise Edition using the Offline option for rebuild?), so the table isn't accessable during that time.
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, October 23, 2009 4:23 PM
  • My experience with up to date servers is that they are very fast.  It's hard to imagine what your SP is doing that takes even 15 seconds with that number of rows, if you're running on a server built in the last couple of years.  Is this on a laptop or workstation?

    What happens if you do not rebuild the index, but run the SP, let it timeout - and then try it a second time?  If the problem is just loading data into RAM, then the second run will be fast anyway, and all the index rebuild is probably doing is loading data into RAM as well.

    Do you have good indexes on the other tables?

    Would you like to post the query and table definitions?

    Josh

    Friday, October 23, 2009 4:36 PM

All replies

  • Hello sachin,

    I have have a table with 100 Mio rows and about 200 k rows are added each day; I defrag the index once a week, and rebuild it once a month, and I don't have any performance problems.

    So I like to suggest to evalute the performance problem first.
    When it works slow as you mentioned, are there any locks on the table? May you run the SP while still an other process is writing/updating on that table?

    An index rebuild is an offline operation (or do you have a Enterprise Edition using the Offline option for rebuild?), so the table isn't accessable during that time.
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, October 23, 2009 4:23 PM
  • My experience with up to date servers is that they are very fast.  It's hard to imagine what your SP is doing that takes even 15 seconds with that number of rows, if you're running on a server built in the last couple of years.  Is this on a laptop or workstation?

    What happens if you do not rebuild the index, but run the SP, let it timeout - and then try it a second time?  If the problem is just loading data into RAM, then the second run will be fast anyway, and all the index rebuild is probably doing is loading data into RAM as well.

    Do you have good indexes on the other tables?

    Would you like to post the query and table definitions?

    Josh

    Friday, October 23, 2009 4:36 PM
  • Hi Sachin

    A side-effect of running an index rebuild is that Statistics get updated. This is possibly what is fixing your performance issue.

    Next time, try running an UPDATE STATISTICS on your table instead of a rebuild and see what the effect is. It's possible that the Query Optimiser has no statistics for the newly added data, so chooses a sub-optimal plan. The change in data volume is not enough to trigger an auto-update.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Saturday, October 24, 2009 9:38 AM