Index Maintenance

Question

Index Maintenance

dtran1127

Old Hand

Points: 343
More actions
August 29, 2018 at 10:36 am

#408324

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?
May 6, 2021 at 3:38 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 1

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 Old Hand Points: 343 More actions · Answer 2

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 More actions · Answer 3

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 Old Hand Points: 343 More actions · Answer 4

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: 5296 More actions · Answer 5

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: 1003857 More actions · Answer 6

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.

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 More actions · Answer 7

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 Old Hand Points: 343 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 9

I know this is an old post but I just ran across it again while looking for something else. Probably doesn't make any difference to anyone anymore but...

Going through it all again, I went back into the original graphic and looked at it much closer instead of just the limited amount being discussed. The only way to have so many frag percents that are identical out to as many digits to the right of the decimal point is to have some bad code that produced the output and it contains some accidental many-to-many joins and we never asked to see the code that produced the graphic. Good proof of that is that you cannot have a non-clustered index with no name. The index names just form a repeating pattern from the now obvious misjoin in the code we haven't seen.

--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.

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)