Index Maintenance

  • dtran1127

    SSC Veteran

    Points: 283

    Hello:

    How is it possible to get the SAME index name for the SAME table but the Index ID is different?

    I know that the picture represents a FRAGMENTED index which then I will have to "REBUILD" correct?

    What are your suggestions? I would love to hear some of them as I am starting to build my skills in Maintenance.

    Index Maintenance is my first step and I want to learn all that I can. Please provide any information as well too! Thank you

    What is your Index Maintenance strategy? 

    1. Check Index usage
    2. Check Index Frag
    3. Reorganize or Rebuild
    4. Update statistics

    Did I get the above correct?

  • Vegard Hagen

    Hall of Fame

    Points: 3078

    How is it possible to get the SAME index name for the SAME table but the Index ID is different?

    Well, in theory, that's an easy one: The index ID has precisely nothing to do with neither the index name nor the table name, it's just a numerical value assigned "under the hood". But yes, there is definitely something funky with your screenshot: It shows 2 different indexes, but 5 rows for each one repeating the same 5 "index names" for both. What query did you run to produce this?


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • dtran1127

    SSC Veteran

    Points: 283

    So my query is :

    SELECT
      OBJECT_NAME(A.[object_id]) as 'TableName',
      B.[name] as 'IndexName',
      A.[index_id],
      A.[page_count],
      A.[index_type_desc],
      A.[avg_fragmentation_in_percent],
      A.[fragment_count]
    FROM
      sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A
      INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id]
        and A.index_id = B.index_id

    It's weird that the same INDEX NAME, has different fragmentation percents. You  see it in the snippet but for example:

    Index: EMAIL, AVG Frag perc = 94% and 97%

  • Vegard Hagen

    Hall of Fame

    Points: 3078

    Yup, but also two different index id's and therefore two different indexes - they also have different page counts.

    Hmmmm...


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • dtran1127

    SSC Veteran

    Points: 283

    Yeah, it's quite weird. I just hope someone can shed some light on why that would be ?

    I want to say I need to rebuild the table but how does that affect all the duplicate indexes shown in the snippet? 

    Hoping a expert can help me.

  • andycadley

    SSCertifiable

    Points: 5291

    Are you absolutely sure you didn't mis-type the last line of that query when you tried it in SSMS (or accidentally missed it off). The fact you have a HEAP with a name suggests a mistake in your join conditions (since the name will always by NULL if the type is HEAP) and the rest of the duplications suggest it is the index_id field you've missed.

  • Jeff Moden

    SSC Guru

    Points: 995687

    dtran1127 - Wednesday, August 29, 2018 12:25 PM

    So my query is :

    SELECT
      OBJECT_NAME(A.[object_id]) as 'TableName',
      B.[name] as 'IndexName',
      A.[index_id],
      A.[page_count],
      A.[index_type_desc],
      A.[avg_fragmentation_in_percent],
      A.[fragment_count]
    FROM
      sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A
      INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id]
        and A.index_id = B.index_id

    It's weird that the same INDEX NAME, has different fragmentation percents. You  see it in the snippet but for example:

    Index: EMAIL, AVG Frag perc = 94% and 97%

    Did someone make the mistake of creating an index with a trailing blank or other non-printable character?

    For example:


    CREATE TABLE scratch.JBMTest
    (
    SomeColumn INT
    )
    ;
    GO
    --===== This works...
    CREATE INDEX SomeIndex ON scratch.JBMTEST (SomeColumn)
    GO
    --===== This will not because you cannot have two identically named indexes on the same table.
    CREATE INDEX SomeIndex ON scratch.JBMTEST (SomeColumn)
    ;
    GO
    --==== This WILL work and it'll look like you have the same name index when you execute
        -- sys.dm_db_index_physical_stats.
    CREATE INDEX [SomeIndex ] ON scratch.JBMTEST (SomeColumn)
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Vegard Hagen

    Hall of Fame

    Points: 3078

    The query shows that the "index name" here is actually retrieved from the "name" column in sys.indexes, which all documentation says, yes, this should be the name of the index. But what puzzles me the most is that there appears to be just 2 indexes shown here, each listed 5 times with 5 different names. And the fact that those 5 names appear to be the same for both indexes only adds to the mystery. But I'm thinking andycadley is onto something. Are you sure the query ran exactly as it's shown?


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • dtran1127

    SSC Veteran

    Points: 283

    Thank you Jeff Moden. I just checked and confirmed that there are no unicode indexes. (also, we typically never use the syntax [index name] when creating indexes so we wouldn't get that error. We just type the index name without the brackets. )

    Vegard Hagen,  andycadley, I'm checking the query again but I'm pretty sure I'm running it correctly. I ran it on another Database and it gave me results that wasn't questionable. 

    You might be correct though..I just ran :

    select * from sys.indexes where object_name(object_id) ='Orders'--x

    with the results of :

    So that makes me believe that it was just an incorrect join.

    Would you guys happen to have any Index maintenance tips? Strategies? steps? I would like to implement one.

Viewing 9 posts - 1 through 9 (of 9 total)

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