patternsqlMinor
Index fragmentation increased significantly after rebuild
Viewed 0 times
afterfragmentationincreasedrebuildsignificantlyindex
Problem
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
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 gi
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 gi
Solution
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/
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Context
StackExchange Database Administrators Q#131132, answer score: 2
Revisions (0)
No revisions yet.