question

NatiqRzazade-4750 avatar image
0 Votes"
NatiqRzazade-4750 asked AmeliaGu-msft commented

SQL SERVER Fragmentation problem

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](
[ID] [bigint] NOT NULL,
[SubscriberId] [nvarchar](20) NULL
) 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-general
image.png (17.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NatiqRzazade-4750,
How are things going? Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
DanGuzman avatar image
0 Votes"
DanGuzman answered NatiqRzazade-4750 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We use daily reorganize ( rebuild use resource a lot and our system 7/24 that is why we didn't use rebuild). But daily reorganize also not enough for us in this index. Are you sure fill factor in this situation help to us ? İs there other solution in this situation?

0 Votes 0 ·

Yes, fill factor will help avoid fragmentation as I indicated in my answer. That's the only solution for random keys. I wouldn't be overly concerned with fragmentation because that's not often the root cause for performance issues unless you are in the world of local spinning disks.

0 Votes 0 ·

In our cases count of delete and insert approximately equal. That is why I think that fill factor doesn't help to us. Do I get performance if I change my table to memory optimized table ?

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

This is completely normal and expected behavior.

Are you experiencing an actual performance problem?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

İ think that big value o fragmentation impact to performance. But I must insert data random. Daily reorganize also doesn't help to us. What I must do in this situation ?

0 Votes 0 ·

Fragmentation is not an issue by itself. It CAN cause a performance issue. However, there are many variables if it actually DOES cause a performance issue.

If your query performs reasonably, there is nothing to fix.

0 Votes 0 ·

It impact to performance when we selected for subscriberid and we always use it from application

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.