SQL SERVER Fragmentation problem

Natiq Rzazade 1 Reputation point
2021-04-12T11:11:20.703+00:00

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

86904-image.png

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,707 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,206 Reputation points
    2021-04-12T11:52:30.01+00:00

    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.

  2. Tom Phillips 17,716 Reputation points
    2021-04-12T12:30:44.247+00:00

    This is completely normal and expected behavior.

    Are you experiencing an actual performance problem?

  3. Erland Sommarskog 101K Reputation points MVP
    2021-04-12T21:54:46.607+00:00

    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.

    0 comments