Sql-server – Index fragmentation increased significantly after rebuild

fragmentationindexsql serversql-server-2005

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%!

Screenshot of fragmentation information

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.

Best Answer

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/