SQL Server Performance

INDEX REBUILD

Discussion in 'Getting Started' started by reethu, Aug 29, 2007.

  1. reethu New Member

    Hi guys,
    how to see how much it takes to rebuild indexes on 30 meg database.
    or how to see how many tables need to rebuild or reorganise after that we need to know how much time it needs to rebuild all indexes. because that might give good idea to maintanance window.
  2. Sangram Singh New Member

    what version of SQL you using ?
  3. martins New Member

    That's a very difficult question, because it depends so much on other factors like hardware, the amount of indexes you have and the nature of your indexes. However, with such a small database I don't see it taking longer than 1 hour.
  4. satya Moderator

    Agree with Martin here, also you have to perform such operation in order to get correct timings. Also be aware it may take longer time during the day and if scheduled during less traffic hours on the database it might finish quickly.
    But for a 30MB database I guess it won't be more than 5 minutes, but for assurance better be execute the process.
  5. chaloner New Member

    you can use the DBCC Showcontig command to study the fragmentation level of the tables.To understand the output you can use BOL.Than you can target specific tables for index rebuilds.
    Also you need to take care that for building a clustered index you need to have 120% of tablesize space free .
    But as yours is a small database eventhe whole of it might not take long as suggested by others above.
  6. reethu New Member

    HI Guys is ther any way to watch the progress of this rebuilding
  7. satya Moderator

    None other than using PROFILER in thsi case.
  8. chaloner New Member

    You can monitor the IO field in the out of sysprocesses table.You must first identify your process id and then monitor the IO count.
  9. satya Moderator

    I believe that is not much efficient than using server side trace though, it might be quick enough sometimes and with trace you can be assured to get exact timings

Share This Page