on-line indexing in MS SQL 2005 Problems

  • Usually index rebuilding needs to be done only on tables with a lot of inserts/updates/deletes because it makes indexes and statistics not up-to-date and query optimizer stopping its usage.

    SAP, PeopleSoft or whatever package is running is not that much important. We are using package that checks index defragmentation before running index rebuild/reorganize task. We are using SMO and PowerShell now.

    Alex Prusakov

  • Thanks for the scripts.

    correct me if I am saying wrong ....

    I know its really good to have it but I could see we are checking fragmentation level before doing on-line indexing .. its 2 operation in one script..

    instead of 2 operations I will go with all table on-line index operation s which is 1 process

    Approximately how long it will take to complete Online-Reindex process on 1 TB entire database. just rough figure...

    -ichbinraj

  • ichbinraj (5/5/2009)


    Approximately how long it will take to complete Online-Reindex process on 1 TB entire database. just rough figure...

    -ichbinraj

    Too hard to tell. How many indexes are there is the key item in figuring this out along with a bunch of other performance factors.

    You may want to consider building in statistics updates at intervals to keep performance solid longer and stretch out the time between the index rebuilds.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Also depending on fragmentation levels you could chose to DEFRAG instead of REBUILD.

    Incidentally, DEFRAG is always online and it is available on Standard Edition as well.


    * Noel

  • Hi ,

    I am planning to do on-line Reindex for 100 tables one by one.

    Is it possible to put them in script like when 1 st table done then it goes table 2 like that . so that I can avoid doing manually 100 times...

    Appreciate your suggestion

    Thanks

    Ichbinraj

  • ichbinraj (5/5/2009)


    Hi ,

    I am planning to do on-line Reindex for 100 tables one by one.

    Is it possible to put them in script like when 1 st table done then it goes table 2 like that . so that I can avoid doing manually 100 times...

    Appreciate your suggestion

    Thanks

    Ichbinraj

    Yes, just create a cursor.


    * Noel

  • ichbinraj (5/5/2009)


    Hi ,

    I am planning to do on-line Reindex for 100 tables one by one.

    Is it possible to put them in script like when 1 st table done then it goes table 2 like that . so that I can avoid doing manually 100 times...

    Appreciate your suggestion

    Thanks

    Ichbinraj

    how about ....

    sp_msforeachtable 'alter index all on ? rebuild'

  • Thanks for the information. Can you please send me if you have one script.

    Thanks in advance.

    Ichbinraj

  • Best Practice:

    1.)Identify indexes that are of fragmentation

    2.)Defrag indexes with less fragmetation, thumb of rule 30%.

    Wouldn't be a good idea to rebuild all the indexes since its taking longer time to identify fragmentation, that's not a feasible solution and not a good practice at all.

  • Hi Thanks for information.

    I have fragmentation table which >30 and its nearly 78 tables and I am planing to rebuilding online indexes now..

    Can you please send me Script how to complete 78 tables

    Appreciate your quick response.

    Thanks

    ichbinraj

  • Noeld's suggestion is appropriate here. Use a cursor. Refer to BOL for examples. HTH

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • And you keep asking for scripts. I provided 2 of many links on this site that have scripts to automatically do what you're asking for. Change the fragmentation threshold on the script to what you want to reindex for. No sense recreating the wheel if it's already been done - IMO.

    -- You can't be late until you show up.

Viewing 12 posts - 16 through 26 (of 26 total)

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