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