Post reply

How do I rebuild or defrag the index that is auto created for tables containing image columns

  • April 5, 2011 at 3:13 pm

    #143617

    I've been looking into some possible index violations on a table. Unfortunately the errors we get from the software don't tell us much. After talking to the vendor we ran DBCC SHOWCONTIG, which showed an index that I had not seen before (IndexID 255). After some digging I found that the ID 255 is reserved, and auto created. The Index however, has a Logical Fragmentation of 99.28%. What can be done to correct this?

    Thanks,

    Micah

  • April 5, 2011 at 4:08 pm

    #1307755

    It's not actually an index in the sense of the other indexes.

    Does DBCC IndexDefrag have a compactLOB option? (can't recall)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 5, 2011 at 6:01 pm

    #1307771

    GilaMonster (4/5/2011)

    It's not actually an index in the sense of the other indexes.

    Does DBCC IndexDefrag have a compactLOB option? (can't recall)

    Just checked, and unfortunately it does not.

  • April 5, 2011 at 6:26 pm

    #1307772

    Micah Ritchie (4/5/2011)

    I've been looking into some possible index violations on a table. Unfortunately the errors we get from the software don't tell us much. After talking to the vendor we ran DBCC SHOWCONTIG, which showed an index that I had not seen before (IndexID 255). After some digging I found that the ID 255 is reserved, and auto created. The Index however, has a Logical Fragmentation of 99.28%. What can be done to correct this?

    Not much easily, really. Online rebuilds for LOB data isn't supported. Just to confirm you're in the right place, you're on 2k, right? You're pretty much looking at a rebuild of the data (new column, ship the data, drop the old one...). That's the most common method for it. There used to be a really good article and discussion of what you could and couldn't do for LOB data, but I can't seem to find it at the moment. I'll try again tonight and repost if I locate it.

    A question: What index violations are you getting that you're concerned about the LOB data?

    Also, they did put the LOB data into its own filegroup/.ndf file, right?

    Edit: Sorry, I should have mentioned, defragging the LOB that's external (not stored in row) isn't as important as you might think at first glance. You can compact it, but a reorg doesn't get you as much as you would usually assume for LOB data, thus why I asked about your violations.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 4 posts - 1 through 3 (of 3 total)

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