High index defragmentation remains constant - always

Question

Post reply

High index defragmentation remains constant - always

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
June 2, 2010 at 7:08 am

#223360

I have a table with following structure
CREATE TABLE [dbo].[ContentTypesHosting](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[ContentTypeId] [int] NULL,
[TenantId] [int] NULL,
[ContentProviderInfoId] [bigint] NULL,
[ContentAccessModeId] [tinyint] NOT NULL DEFAULT ((2)),
CONSTRAINT [PK_ContentTypesHosting] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ContentTypesHosting] WITH NOCHECK ADD CONSTRAINT [FK_ContentTypesHosting_ContentProviderInfoId] FOREIGN KEY([ContentProviderInfoId])
REFERENCES [dbo].[ContentProviderInfo] ([Id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContentTypesHosting] CHECK CONSTRAINT [FK_ContentTypesHosting_ContentProviderInfoId]
GO
ALTER TABLE [dbo].[ContentTypesHosting] WITH CHECK ADD CONSTRAINT [FK_ContentTypesHosting_ContentType] FOREIGN KEY([ContentTypeId])
REFERENCES [dbo].[ContentType] ([Id])
GO
ALTER TABLE [dbo].[ContentTypesHosting] CHECK CONSTRAINT [FK_ContentTypesHosting_ContentType]
GO
ALTER TABLE [dbo].[ContentTypesHosting] WITH CHECK ADD CONSTRAINT [FK_ContentTypesHosting_ContentTypesHosting] FOREIGN KEY([ContentAccessModeId])
REFERENCES [dbo].[ContentAccessMode] ([Id])
GO
ALTER TABLE [dbo].[ContentTypesHosting] CHECK CONSTRAINT [FK_ContentTypesHosting_ContentTypesHosting]
This table has only 700 rows and its showing fregmentation > 83%
with following query i found that it should be REBUILD
SELECT object_name(a.object_id), a.index_id, name, avg_fragmentation_in_percent,
index_type_desc, suggestion = case
when avg_fragmentation_in_percent between 5 and 30 then 'ALTER INDEX REORGANIZE'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX ' + name + ' ON ' + object_name(a.object_id) + ' REBUILD;' end
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent >=5 order by 1;
When I tried to REBUILD the index it does not reduce the fregmentation. There are lots of other similar tables which have some NULL values in columns, also some columns have NVARCHAR/VARCHAR(Max) datatypes. I need to understand why these tables are always remain / caught by query which show high fregmentations.
I also need some help/suggestion if i apply Non-clustered index on each foreign key columns individually to optimze queries that use/JOIN with this table.
Shamshad Ali

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

At 25 bytes per row, and only 700 rows, this is a tiny table taking up only a few data pages.

From Books OnLine, in the ALTER INDEX topic, under the Rebuilding Indexes heading:

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

-Eddie

Eddie Wuerch
MCM: SQL

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

This table has only 700 rows and its showing fregmentation > 83% with following query i found that it should be REBUILD

HOw did you figure out this ? Did you check the exec plan or any DMV ? there should be substantial differnce between actual and estimated rows

-------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 3

Bhuvnesh (6/3/2010)
there should be substantial differnce between actual and estimated rows

Why would there be a row estimation discrepancy?

A discrepancy between estimated and actual rows is usually indicative of stale statistics, not index fragmentation. Index fragmentation is shown by the sys.dm_db_index_physical_stats DMV.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I would not worry about fragmentation on any table under 1000 pages in size.

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