September 13, 2010 at 5:54 am
Here I am repeating my actual question:
When we have checked the table we can see same index is appearing 2times, and index_level is different. What I observed is the index which is against to Index_level=0 is getting defraged and remaining indexes with same name are not.
How to handle this situation ?
Table name indexname Fragmentation
MediaService PK__MediaService__02FC7413 25
MediaService PK__MediaService__02FC7413 0
MediaService UQ__MediaService__03F0984C 36.36363636
MediaService UQ__MediaService__03F0984C 0
as you can see same index is repeating 2 times on the same table with different fragmentation values.
So which one we need to take into consideration and if we derag the those indexes, we are seeing the change in only one index and the other one with same name remained unchange.
For ex:
if we defrag index UQ__MediaService__03F0984C one value is showing as 36% which is unchanged and other is showing as 0 which is changed from some 10% to 0.
here what should we need to do ?
Thank You.
Regards,
Raghavender Chavva
September 13, 2010 at 6:25 am
Raghavender (9/13/2010)
some tables are of 20000 pages and more......same situation no change in the fragmentation.... what should we need to do ?
run the above DBCC command for any one of this heavy table
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 13, 2010 at 6:55 am
Raghavender (9/13/2010)
as you can see same index is repeating 2 times on the same table with different fragmentation values.So which one we need to take into consideration and if we derag the those indexes, we are seeing the change in only one index and the other one with same name remained unchange.
I take it you missed my post earlier.
Perfectly normal. When you query index_physical_stats in detailed mode, you get one row per level in the index treel. Index level 0 is the leaf level, the largest level of the index, which is the one you're likely most concerned about.
Are you doing an index rebuild or an index reorganise?
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
September 13, 2010 at 7:53 am
GilaMonster (9/13/2010)
Raghavender (9/13/2010)
as you can see same index is repeating 2 times on the same table with different fragmentation values.So which one we need to take into consideration and if we derag the those indexes, we are seeing the change in only one index and the other one with same name remained unchange.
I take it you missed my post earlier.
Perfectly normal. When you query index_physical_stats in detailed mode, you get one row per level in the index treel. Index level 0 is the leaf level, the largest level of the index, which is the one you're likely most concerned about.
Are you doing an index rebuild or an index reorganise?
We are doing Index Defrag.
Bhuvanesh
run the above DBCC command for any one of this heavy table
Below is the result:
DBCC SHOWCONTIG scanning 'AnnotationRecord' table...
Table: 'AnnotationRecord' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 86302
- Extents Scanned..............................: 10938
- Extent Switches..............................: 10962
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.40% [10788:10963]
- Logical Scan Fragmentation ..................: 1.07%
- Extent Scan Fragmentation ...................: 87.65%
- Avg. Bytes Free per Page.....................: 428.8
- Avg. Page Density (full).....................: 94.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Tablename IndexName Fragmentation
AnnotationRecordPK__AnnotationRecord__108B795B1.07078292309832
AnnotationRecordPK__AnnotationRecord__108B795B86.1286254728878
AnnotationRecordPK__AnnotationRecord__108B795B0
AnnotationRecordPK__AnnotationRecord__108B795B0
AnnotationRecordUQ__AnnotationRecord__117F9D947.83507722969606
AnnotationRecordUQ__AnnotationRecord__117F9D9499.5910020449898
AnnotationRecordUQ__AnnotationRecord__117F9D94100
AnnotationRecordUQ__AnnotationRecord__117F9D940
AnnotationRecordANNOTATIONRECORD_OWNER6.63675453355767
AnnotationRecordANNOTATIONRECORD_OWNER99.0521327014218
AnnotationRecordANNOTATIONRECORD_OWNER0
AnnotationRecordANNOTATIONRECORD_OWNER0
AnnotationRecordANNOTATIONRECORD_CREATED_UPDATED2.87151540557535
AnnotationRecordANNOTATIONRECORD_CREATED_UPDATED84.1666666666667
AnnotationRecordANNOTATIONRECORD_CREATED_UPDATED0
AnnotationRecordANNOTATIONRECORD_OWNER_TRAINABLE_DOCUMENTDATE4.86304740036083
AnnotationRecordANNOTATIONRECORD_OWNER_TRAINABLE_DOCUMENTDATE87.1308016877637
AnnotationRecordANNOTATIONRECORD_OWNER_TRAINABLE_DOCUMENTDATE90.9090909090909
Thank You.
Regards,
Raghavender Chavva
September 13, 2010 at 8:19 am
Raghavender (9/13/2010)
We are doing Index Defrag.
Defrag only works on the leaf level of the index (level 0), so what you are seeing is expected and by design. If you want all the levels of the index to be rebuilt, rebuild the index, don't reorganise it.
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
September 13, 2010 at 10:12 pm
Raghavender (9/13/2010)
- Scan Density [Best Count:Actual Count].......: 98.40% [10788:10963]- Logical Scan Fragmentation ..................: 1.07%
It doesn't seem that this table requires any defragmentation.scan density is very much closer to 100% which signifies that this table is defragmented.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2010 at 9:26 pm
GilaMonster (9/13/2010)
Defrag only works on the leaf level of the index (level 0) .
Then what about the intermediate level ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 16, 2010 at 1:19 am
Bhuvnesh (9/15/2010)
GilaMonster (9/13/2010)
Defrag only works on the leaf level of the index (level 0) .
Then what about the intermediate level ?
Defrag (Alter index Reorganise) does not touch anything other than the leaf level of the index. Hence it's perfectly normal for the non-leaf levels (index_level>0) to have their fragmentation unchanged after an index reorganise.
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
September 16, 2010 at 1:30 am
GilaMonster (9/16/2010)
Bhuvnesh (9/15/2010)
GilaMonster (9/13/2010)
Defrag only works on the leaf level of the index (level 0) .
Then what about the intermediate level ?
Defrag (Alter index Reorganise) does not touch anything other than the leaf level of the index. Hence it's perfectly normal for the non-leaf levels (index_level>0) to have their fragmentation unchanged after an index reorganise.
thanks Gail, but when intermediate level affects the performance ? , How can we came to know that it is upset ? and how can it be resolved ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 16, 2010 at 1:36 am
Sorry, don't understand what you're asking.
Do you know why fragmentation affects performance?
Also, think of the relative sizes of the leaf and non-leaf levels of an index, and the suggested thresholds for reindexing (page count)
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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply