I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always added at the end. The average row size is 1570 bytes.
There are a lot of page splits because new rows are added frequently. No rows get updates/deleted and there is a non-clustered index on the table so rows can be selected. Due to the page splits, the clustered index is always fragmented > 65%.
I wonder my table would benefit of removing the clustered index and make it a heap table?
This is how my table + non-clustered index looks like:
CREATE TABLE [dbo].[LogEntry]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Application] [varchar](20) NOT NULL, [EntityFullName] [varchar](80) NOT NULL, [Action] [int] NOT NULL, [UserName] [varchar](25) NOT NULL, [TimeStamp] [datetime] NOT NULL, [EntityId] [varchar](50) NOT NULL, [WhatChanged] [nvarchar](max) NULL, CONSTRAINT [PK_LogEntry] PRIMARY KEY CLUSTERED( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE NONCLUSTERED INDEX [ID_Application_Entity_FullName_TimeStamp] ON [dbo].[LogEntry] ( [Application] ASC, [EntityFullName] ASC, [TimeStamp] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] GO
Update: Someone had enabled auto-shrink behind my back => this will be the cause of the fragmentation