November 23, 2011 at 12:44 pm
Hi all,
I am trying to do some research on the best index maintenance practice on tables with lobs and partitions. I am guessing for partitioned tables, normally you only want to rebuild indexes on the active partition and leave static partition alone.
But for tables with lob data, do we treat them as normal tables which get index rebuild/reorg depending on fragmentation threshhold? Or there is more to consider? (I don't mean the online/offline operation).
Thanks,
Alan
November 23, 2011 at 1:04 pm
If you are using either Michelle Ufford's or Ola Hallengren's index maintenance script which checks index stats so that maintenance is done only on indexes that need it, then you don't have to worry about what partition you are touching as, at least Ola's script, takes care of only doing maintenance on the index and partition that it needs it.
For tables with LOB data, I'd do standard maintenance. Do you have indexes on the LOB columns?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2011 at 1:15 pm
Thanks Jack for the very quick reply,
Indeed I am looking at both Ola's and Michelle's index scripts :-). At this stage, I am more for Michelle's script as it's more compact and easier to follow. A couple things it confused me a bit are in the script it detect if there is more than 1 partition or if there is lob in the table, if there is only does reorg? Do you know the reason?
Alan
November 23, 2011 at 1:39 pm
Nope I don't know why she is only re-orging partitioned indexes. I'll ask.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2011 at 1:52 pm
Thanks again
November 23, 2011 at 2:07 pm
Didn't get a response from Michelle yet, but someone did say that it may just be because she hadn't gotten around to coding in the rebuild. I know that Ola's script does a rebuild if it meets the criteria.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2011 at 2:11 pm
Cool. It's not currently a problem for us as we don;t use table partition and I will remove the restriction on lob/re-org.
Have a good weekend!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply