Index Defragmentation

Question

Index Defragmentation

prvnrddy-590736

Grasshopper

Points: 22
More actions
September 5, 2010 at 4:55 am

#148679

We have a table called abc and index xyz(Non-Unique,Non-clustered). I need to defrag both the indexes as their fragmentation levels are high. When ever i am running below commands only one of the indexes are being defragging but not both. Can anyone help me in defragging both?
The commands i have used are:
Use database
DBCC Showcontig(tablename,Index name)
DBCC Indexdefrag(dbname,tablename,Index name)
Thanks,
Praveen

Viewing 15 posts - 1 through 15 (of 24 total)

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 1

prvnrddy-590736 (9/5/2010)
We have a table called abc and index xyz(Non-Unique,Non-clustered). I need to defrag both the indexes as their fragmentation levels are high. When ever i am running below commands only one of the indexes are being defragging but not both. Can anyone help me in defragging both?
The commands i have used are:
Use database
DBCC Showcontig(tablename,Index name)
DBCC Indexdefrag(dbname,tablename,Index name)
Thanks,
Praveen

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 ?

Thank You.

Regards,
Raghavender Chavva

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Defragmention doesnt work with small tables (1000 data pages).( my guess is your table is small) if dbcc sohwcontig is not working as you expected then it means that it have good statistics.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

You might defrag the index, but you cannot defrag a table without a clustered index. Set one up, then defrag.

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 4

Steve Jones - Editor (9/5/2010)
You might defrag the index, but you cannot defrag a table without a clustered index. Set one up, then defrag.

Hi Jones,

can you please let me know how to set one up ?

Thank You.

Regards,
Raghavender Chavva

NickDBA Hall of Fame Points: 3792 More actions · Answer 5

You create a clustered index in the same way you create a non clustered index. But you need to be aware of the differences between them (there are plenty of article on here detailing this topic). You'll also have to make a decision on which field(s) is best suited for your key.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

1) If you create a clustered index on a HEAP table, you won't need to defrag it immediately since it will be as defragged as possible upon creation.

2) as said before, small tables aren't worthwhile candidates for defrag operations

3) don't use dbcc... for index maintenance. Newer, better commands exist in 2005+. Also, I HIGHLY recommend getting maintenance stuff from ola.hallengren.com

4) If you don't know how to create a clustered index PLEASE have your company hire a mentor to help train you and to also give your system a review. There are a huge number of things that are almost certainly being done (or not done) incorrectly.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 7

TheSQLGuru (9/6/2010)
1) If you create a clustered index on a HEAP table, you won't need to defrag it immediately since it will be as defragged as possible upon creation.
2) as said before, small tables aren't worthwhile candidates for defrag operations
3) don't use dbcc... for index maintenance. Newer, better commands exist in 2005+. Also, I HIGHLY recommend getting maintenance stuff from ola.hallengren.com
4) If you don't know how to create a clustered index PLEASE have your company hire a mentor to help train you and to also give your system a review. There are a huge number of things that are almost certainly being done (or not done) incorrectly.

All the tables have cluster indexes and when we ran below query:

SELECT

OBJECT_NAME(i.object_id) AS TableName

,

i.name AS TableIndexName

,

phystat.avg_fragmentation_in_percent

FROM

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i

ON i.object_id = phystat.object_id

AND i.index_id = phystat.index_id --WHERE phystat.avg_fragmentation_in_percent > 10

sample Output is:

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 8

can any one please help ?

Thank You.

Regards,
Raghavender Chavva

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Raghavender (9/13/2010)
can any one please help ?

POst its result also

SELECT avg_page_space_used_in_percent

,avg_fragmentation_in_percent

,index_level

,record_count

,page_count

,fragment_count

,avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats(DB_ID('Dbname'),OBJECT_ID('MediaService'),NULL,Null, null)

where index_id in (0,1)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Raghavender (9/7/2010)
as you can see same index is repeating 2 times on the same table with different fragmentation values.

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.

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.

Probably because the index is too small for the rebuild to have an effect. It has to do with the way the first few pages are allocated for the index. There's little point in worrying about fragmentation for small indexes. The rough guideline for when you want to worry about fragmentation is around 1000 pages. Below that there's unlikely to be much if any performance gain from rebuilding.

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 11

Here is The results:

DBCC SHOWCONTIG scanning 'MediaService' table...

Table: 'MediaService' (34099162); index ID: 1, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 32

- Extents Scanned..............................: 11

- Extent Switches..............................: 11

- Avg. Pages per Extent........................: 2.9

- Scan Density [Best Count:Actual Count].......: 33.33% [4:12]

- Logical Scan Fragmentation ..................: 25.00%

- Extent Scan Fragmentation ...................: 81.82%

- Avg. Bytes Free per Page.....................: 110.4

- Avg. Page Density (full).....................: 98.64%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thank You.

Regards,
Raghavender Chavva

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

Raghavender (9/13/2010)
- Pages Scanned................................: 32

it is very small table , you cant expect good defragmentation here , table less then 1000 pages ( as i mentioned in my first post or recent Gail's post) doesn't have good impact of defragmentation.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 13

Bhuvnesh (9/13/2010)
Raghavender (9/13/2010)
can any one please help ?
POst its result also
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('Dbname'),OBJECT_ID('MediaService'),NULL,Null, null)
where index_id in (0,1)

below is the result:

NULL 25 0 NULL 32 11 NULL

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 14

Bhuvnesh (9/13/2010)
Raghavender (9/13/2010)
- Pages Scanned................................: 32
it is very small table , you cant expect good defragmentation here , table less then 1000 pages ( as i mentioned in my first post or recent Gail's post) doesn't have good impact of defragmentation.

as it is 32 pages table you are saying there is no impact in defragging the indexes, but some tables are of 20000 pages and more......same situation no change in the fragmentation.... what should we need to do ?

Thank You.

Regards,
Raghavender Chavva