Clustered index 99% fragmented after one insert

[Sypher2]

I am by no means an expert DBA.

We have a table with 6 million records. It has 4 indexes. One is a clustered primary key (consisting of 3 columns) and the other 3 are non-clustered on one column each.

If I reindex the table, the fragmentation goes down to near zero on all 4 indexes. However, the first time an application inserts records into the database, the clustered index becomes 99% fragmented.

Is there some sort of conflict going on between indexes or what might be causing this?

Thanks

 

[mrdenny]

What is the fill factor of the clustered index?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Sypher2]

It is set at 85. Here is the script that I got from it:

ALTER TABLE [dbo].[DCO_Sessions_P] ADD  CONSTRAINT [PK_P_DCO_Sessions] PRIMARY KEY CLUSTERED 
(
	[Operator_ID] ASC,
	[Sys_Num] ASC,
	[Logoff_Time] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]

 

[mrdenny]

How wide are the rows in the table?

I'd try lowering the fill factor some more to say 70%.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog