PK Fragmentation on INT IDENTITY column

Question

Have simple Int Identity as my PK.

Table has 2.2 million rows.

Zero row deleted, never disabled the Identity.

Inserts were via .NET a row at a time with every insert retrieving the SCOPE_IDENTITY().

20 some FK relationships to it.

I have fragmentation of 98%.

Any idea what may have caused that?

I know the fix is to rebuild and will do that tonight.

I am wondering how to avoid it.

I had added 3 columns since it was populated.
A few of the columns have nearly every value revised.
Length of properties has not changed much.

Table definition

/****** Object:  Table [dbo].[docSVsys]    Script Date: 02/13/2013 15:52:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[docSVsys](
    [sID] [int] IDENTITY(1,1) NOT NULL,
    [sParID] [int] NULL,
    [docID] [varchar](100) NOT NULL,
    [attBeg] [varchar](100) NULL,
    [attEnd] [varchar](100) NULL,
    [docDate] [smalldatetime] NULL,
    [addDate] [smalldatetime] NOT NULL,
    [mimeType] [varchar](40) NULL,
    [docIDpar] [varchar](50) NULL,
    [addBy] [smallint] NOT NULL,
    [IPROlink] [varchar](300) NULL,
    [textSize] [int] NULL,
    [textHash] [char](32) NULL,
    [FTSstatus] [tinyint] NOT NULL,
    [FTSdate] [smalldatetime] NULL,
    [nativeFileName] [varchar](200) NULL,
    [nativeFileSize] [bigint] NULL,
    [nativeMD5] [char](32) NULL,
    [nativeUNC] [varchar](600) NULL,
    [nativeDateCreate] [smalldatetime] NULL,
    [nativeDateModify] [smalldatetime] NULL,
    [nativeExtension] [varchar](20) NULL,
    [caseID] [char](1) NULL,
    [textUniqueWordCount] [int] NULL,
    [nativeUNCrendition] [varchar](300) NULL,
    [nativeXPS] [varchar](300) NULL,
    [FTSnumNearDup] [int] NULL,
    [FTSnearDupSIDcenter] [int] NULL,
    [FTSnearDupMatchToCenter] [decimal](8, 2) NULL,
    [FTSnearDupID] [int] NULL,
 CONSTRAINT [PK_docSVsys] PRIMARY KEY CLUSTERED 
(
    [sID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[docSVsys]  WITH CHECK ADD  CONSTRAINT [FK_docSVsys_docSVsys_sParID] FOREIGN KEY([sParID])
REFERENCES [dbo].[docSVsys] ([sID])
GO

ALTER TABLE [dbo].[docSVsys] CHECK CONSTRAINT [FK_docSVsys_docSVsys_sParID]
GO

ALTER TABLE [dbo].[docSVsys] ADD  CONSTRAINT [DF_docSVsys_addDate]  DEFAULT (getdate()) FOR [addDate]
GO


Table Name                                                                                                                       Index name                                                                                                                       alloc_unit_type_desc                                         index_depth index_level avg_fragmentation_in_percent
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------- ----------- ----------------------------
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           0           0.01
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           1           0
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           2           0
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           3           0

Answer 1

A primary key on a INT IDENTITY ought to be very close to optimal, and as such should not lead to a significant index fragmentation.

However: since your PK is (by default and unless you specifically changed it) also your clustering index, and the leaf level of the clustering index are the actual data pages.

If your data structures has changed significantly over time, new columns were added, others possibly dropped, the length of string-based columns changed - this can lead to significant index fragmentation (on the leaf level) since pages need to be rearranged to make room for new data columns.

Also: if you have a good number of variable length columns (varchar(x)) and those have been updated, if the length of the varchar column increases, this can lead to page splits. This is especially true if you have a FILLFACTOR of 100% on your PK index - in that case, even a single extra character can lead to a page split - one page is split into two, the data distributed amongst the two new pages and this contributes to index fragmentation.

So in light of all this, consider:

• even having a maintenance plan for a perfect INT IDENTITY index isn't a bad idea
• if you have lots of varchar columns, and they could well be growing over time (text gets longer and longer) - consider a FILLFACTOR lower than 100% (the default)

PS: if you still have your situation before the reorganization - try this query (and put your table name as the second parameter for dm_db_index_physical_stats) - this will show you index fragmentation on the PK index, on every level of the index:

SELECT 
    'Table Name' = t.NAME,
    'Index name' = i.NAME ,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PutYourTableNameHere'), NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    i.index_id = 1