SQL SERVER Fragmentation problem

Question

I have one problem about fragmentation. I had one table structure as below

USE [FragmenTests]
GO

/****** Object: Table [dbo].[Test_Fragmentation] Script Date: 4/12/2021 2:54:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].Test_Fragmentation ON [SNAPSHOT]
GO

USE [FragmenTests]
GO

/****** Object: Index [IX_DEBTS_ID] Script Date: 4/12/2021 2:56:33 PM ******/
CREATE UNIQUE CLUSTERED INDEX [IX_DEBTS_ID] ON [dbo].[Test_Fragmentation]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SNAPSHOT]
GO

SET ANSI_PADDING ON
GO

/****** Object: Index [IX_DEBTS_SUBID] Script Date: 4/12/2021 2:56:33 PM ******/
CREATE NONCLUSTERED INDEX [IX_DEBTS_SUBID] ON [dbo].[Test_Fragmentation]
(
[SubscriberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SNAPSHOTIDX]
GO

I must added random data. That is why I create script as follow

declare @n bigint =4305444830
while @n<9305444830
begin
set @n=@n+1

insert into [dbo].[Test_Fragmentation] ([ID],[SubscriberId])
select @n,Cast(ABS(CAST(CHECKSUM(NEWID()) AS bigint) *Cast(100 as bigint)) as nvarchar(20))

end

After this index fragmentation result as follow

My answer is : How to avoid of fragmentation with inserting random data. I try to change fill factor and data type. But it doesn't help me.

Answer 1

Fragmentation is inherent side effect of random key inserts into a b-tree index. You need to periodically rebuild or reorganize the index to reduce existing fragmentation and specify an appropriate fill factor to mitigate fragmentation going forward. The fill factor should be roughly the percent growth between reorgs.

For example, if your table has 1 billion rows and you insert 300M rows and delete 200M per day (10% net growth), a daily reorg with 90 percent fill factor will help avoid page splits between reorgs.

Answer 2

This is completely normal and expected behavior.

Are you experiencing an actual performance problem?

Answer 3

In our cases count of delete and insert approximately equal. That is why I think that fill factor doesn't help to us.

A low fill factor can reduce the amount of page splits, which may be your main performance issue. As Dan said, fragmentation is not much of an issue in the modern world of SSDs.

Then, too low fill factor means that you will not utilise the buffer cache. What is the right value is difficult to say without testing.

Do I get performance if I change my table to memory optimized table ?

Maybe. Maybe not. There are so many "it depends" it is impossible to tell. You will need to test with your workload.