Help? Fragmentation still high after drop and re-create indexes...

Hello, i am encountering a strange behaviour on SQL 2005 with dropping and re-creating my indexes not affecting my fragmentation.

I have many tables with the same structure, and on each table i have 3 indexes defined, one clustered and 2 other indexes. After some time, they get pretty fragmented. When i drop the indexes, and re-create across all my tables, some of my tables fragmentation % is still very high, over 60%. Yet some other tables report fragmentations of 0 across my 3 indexes.

I use this query to find out fragmentation:

SELECT a.index_id, name, avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('NS_Time_Daily'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

I also checked my physical disk fragmentation and it has no issue, i.e. it does not recommend a defrag (im on win server 2003)

Why would this be happening?

Any thoughts will be greatly appreciated!! Thanks, Jeff

CREATE TABLE [dbo].[NS_Time_Daily]
(
[MemberId] [bigint] NOT NULL,
[lft] [bigint] NULL,
[rgt] [bigint] NULL,
[modelsiteID] [bigint] NOT NULL,
[depth] [bigint] NOT NULL,
[lvl] [bigint] NOT NULL,
[lvlPosition] [bigint] NOT NULL
)

DROP INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily]

CREATE CLUSTERED INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily](ModelSiteId, MemberId)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily](ModelSiteId, Lvl, LvlPosition)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily](ModelSiteId, Lft, Rgt)

Hi Jeff,

How big is the table mate?

Could you send us the output of the following statement before and after you do the re-index?

selectobject_name(object_id)as TableName, index_id, index_type_desc,

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,

avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('NS_Time_Daily'),

NULL,NULL,NULL)

regards

Jag Sandhu