Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Int32 Inden 1, 1 –  Blam Feb 13 '13 at 21:33
    
SSMS - I reorganized and it went to 0.2% –  Blam Feb 13 '13 at 21:37
    
Fillfactor of only 10% seems extremely low..... this means, on average: every page is 90% empty - and also: you're probably using about 10 times as many pages on disk for the same amount of data.... –  marc_s Feb 13 '13 at 22:02
    
Agree 10% was messed up. Will changed it to 50% and rebuild after a backup. Kind of a long story but when I was using @@Iden it was causing fragmentation that got fixed by scope_identity - really. –  Blam Feb 13 '13 at 22:04
add comment

migrated from stackoverflow.com Feb 14 '13 at 14:17

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 6 down vote accepted

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
share|improve this answer
1  
Great tip on fill factor. What about pad index? I will post the table structure. –  Blam Feb 13 '13 at 21:45
    
Query results added to question. I am going to go ahead and mark this as an answer as the question may get closed and I don't know if I can mark an answer on a closed question. Thanks –  Blam Feb 13 '13 at 22:10
    
Get what you are saying on maintenance plan. This is a 550 GB database so I need to maintain smart. My problem but you have helped me understand the problem. Thanks again. –  Blam Feb 13 '13 at 22:45
1  
Doesn't apply here but a PK on an IDENTITY column can lead to near 100% fragmentation if defined in the wrong direction –  Martin Smith Feb 14 '13 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.