Fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Fragmentation

Author

Topic

tleal
Starting Member

4 Posts

Posted - 2008-09-29 : 09:16:42

I run the command below to determine the frag. on table T_XXXXX
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'T_XXXXX'), NULL, NULL, 'DETAILED')

It gives me back 87.5

I then run
ALTER INDEX ALL ON T_XXXX
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON
and then when I check frag. again with
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'T_XXXXX'), NULL, NULL, 'DETAILED')
its still 87.5!!! Why????

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-09-29 : 09:27:22

Can you copy output before and after running those queries?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-29 : 09:34:10

it is due to the fact that your table is heap(without Clustered index).

tleal
Starting Member

4 Posts

Posted - 2008-09-29 : 10:21:35

The table only has one index & it is a primary key - clustered

SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'T_PortfolioInflationRates'), NULL, NULL, 'DETAILED')
1 87.5
1 0

ALTER INDEX ALL ON T_PortfolioInflationRates
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)

Command(s) completed successfully

SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'T_PortfolioInflationRates'), NULL, NULL, 'DETAILED')
1 87.5
1 0

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-02 : 21:58:55

Posted - 09/29/2008 : 09:34:10
--------------------------------------------------------------------------------

it is due to the fact that your table is heap(without Clustered index).
_________________________________________________________________

How did you find that table is heap? I would appreciate if you share with us as well

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-02 : 22:12:02

___BOL says________________________________________________________
avg_fragmentation_in_percent -float -
Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.
_______________________________________________________________
I think your index might be spread across multiple secondry files wihtin filegroup and disk itself is fragmented. The avg. frag. column is taking that into account (assuming as per definition). Can you check framgmentation at disk level. In case of SAN you may need to talk to SAN admin and find out that. I am just trying to relate things.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources