Index fragmentation increased significantly after rebuild

Question

I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario.

I have an index on a huge table (86 million rows) that was about 33% fragmented.

A few days ago I started noticing certain queries were taking too long. It seemed like they weren't using one of the indexes even though the queries are written in such a way that they should use the index (I could be wrong about the index not being used)

So one of the things I did was look at the index fragmentation. It was 33% I did a rebuild at a time when nothing would be using the table (except that as a test I did run a query on the table at the same time as rebuilding the index - Foolish perhaps).

The rebuild took approximately 10 to 20 minutes. For some reason I didn't check the fragmentation immediately after. I checked it the day after (queries still slow) and saw that it had increased to 56.06%!

If I try another rebuild will I make it even worse? Should I try a reorganize instead? (I read that Microsoft recommends a reorganize if fragmentation is less than 30% and a rebuild if more than 30%)

Disclaimer: I am aware that there may be some bad practices going on here (size of table, design, or anything) I didn't create this, I merely inherited responsibility for it.

Edit: I risked another rebuild. This time checked immediately. It was 0.01 fragmented. I checked again ten or so minutes later and it was about 1% fragmented. I checked again approximately one minute intervals and it's becoming gradually more fragmented. Some hours later it's now 53.55% fragmented.

Output of @@version:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Autoshrink is on (shows 'true' in the database properties dialog box)

---

Edit : Some further information to give extra detail to the question....

At a fixed time each day the table gets a bulk update of approximately 50,000 to 100,000 rows of new data. For the rest of the day there are no updates or new records, it's purely queried for data.

The primary key is on 'recordid' and it is an auto increment field. It's value is higher for new inserts than anything else in the table so there's no having to sandwich the data between existing data in the index.

Here is the create index code for the index in question...

USE [EWS]
GO

/****** Object:  Index [IX_AmtoteAccountActivity]    Script Date: 03/03/2016 21:07:14 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity] ON [dbo].[AmtoteAccountActivity] 
(
    [AccountNumber] ASC,
    [_Date] 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 [PRIMARY]
GO

In case it matters/is relevant (I'm not sure if it is) here is the create index code for the primary key...

USE [EWS]
GO

/****** Object:  Index [PK_AmtoteAccountActivity]  Script Date: 03/03/2016 21:10:11 ******/
ALTER TABLE [dbo].[AmtoteAccountActivity] ADD  CONSTRAINT [PK_AmtoteAccountActivity] 
PRIMARY KEY NONCLUSTERED 
(
    [RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]
GO

Finally, as suggested by @Shanky and @KookieMonster I believe the fragmentation (which steadily grows after rebuild) is caused by auto_shrink being on for this database.

Answer 1

Just like @KookieMonster noticed, You have Auto Shrink turned on. And one of disadvantages of shrink commands is fragmenting Your indexes once again:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Answer 2

The Page Fullness is 98.23%. This means there is only 140-ish byte free per page. This is larger than the minimum row size. So any insert in the middle of the key range, and likely any update too, will cause a page split and fragmentation.

You'd need to affect about one million rows to get 30% fragmentation since there are about three million page in the index. Do you have processes which write at this magnitude?

Answer 3

If recordID is your PK and it is not fragmenting then you should flop and make recordID the clustered index.

With IX_AmtoteAccountActivity as a non clustered index give it a fill factor of less then 100%. This will also speed up inserts. Since it is a big table don't go crazy bet even 90% will slow down fragmentation. And just schedule a rebuild of that index every night.

Answer 4

You should set FILLFACTOR=90 or less, when rebuilding your indexes. Default it's 0 which means 100%.

Transaction (INSERT,UPDATE & DELETE) on a tables with 100% FILLFACTOR cause to page split which finally cause to index fragmentation.

There are many reasons which leads you to fragmentation. I can details you all, so please go through given link it's described here.

https://www.mssqltips.com/sqlservertip/2261/sql-server-fragmentation-storage-basics-and-access-methods-part-1-of-9/

Thanks