Avg. Index Fragmentation % Still the Same after Maintenance

Question

Post reply

Avg. Index Fragmentation % Still the Same after Maintenance

MostInterestingMan

SSCertifiable

Points: 6291
More actions
October 7, 2010 at 4:47 pm

#148928

Hi,
I ran a defrag index maintenance that defragged my indexes using the following logic:
index_frag_% < 30: Reorganize Index
index_frag_% >= 30: Rebuild Index
Immediately after the defrag completed I ran the query below and got exactly the same list of indexes with same fragmentation. Running UPDATESTATS did not change this either.
SELECT
page_count,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
--FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0
AND page_count > 8
ORDER BY page_count;
GO
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 7 posts - 1 through 6 (of 6 total)

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

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

What other step do you have in your maintenance plan?

Any shrink?

What is the size of the index?

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

How big is the index? How many pages.

Update stats has nothing to do with index fragmentation, so running a stats update won't do a thing to the index fragmentation stats.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

GilaMonster (10/8/2010)
How big is the index? How many pages.
Update stats has nothing to do with index fragmentation, so running a stats update won't do a thing to the index fragmentation stats.

There are six indexes returned by sys.dm_db_index_physical_stats and below are the page counts:

page counts:

9

10

11

13

17

20

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 4

MostInterestingMan (10/8/2010)
GilaMonster (10/8/2010)
How big is the index? How many pages.
Update stats has nothing to do with index fragmentation, so running a stats update won't do a thing to the index fragmentation stats.
There are six indexes returned by sys.dm_db_index_physical_stats and below are the page counts:
page counts:
9
10
11
13
17
20
Thanks

Gail, is it because those page counts low? While going through the logs, I noticed that indexes with very high page counts were not returned by sys.dm_db_index_physical_stats after the maintenance.

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

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

Way, way, way too small to worry about.

Under 24 pages, an index rebuild will likely do nothing at all. It's generally considered that 1000 pages should be about the point where you worry about index fragmentation.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

GilaMonster (10/8/2010)
Way, way, way too small to worry about.
Under 24 pages, an index rebuild will likely do nothing at all. It's generally considered that 1000 pages should be about the point where you worry about index fragmentation.

Thank you kindly for taking the time and your input. Have a wonderful weekend.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends