May 5, 2009 at 9:09 am
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
May 5, 2009 at 9:22 am
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
May 5, 2009 at 9:31 am
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
May 5, 2009 at 10:27 am
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
May 5, 2009 at 11:34 am
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
May 5, 2009 at 12:33 pm
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
May 5, 2009 at 12:41 pm
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'
May 5, 2009 at 12:41 pm
Thanks for the information. Can you please send me if you have one script.
Thanks in advance.
Ichbinraj
May 6, 2009 at 7:14 pm
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.
May 7, 2009 at 7:37 am
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
May 7, 2009 at 12:03 pm
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
May 7, 2009 at 12:25 pm
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