[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

SQL 2005 Indexing

I'm having issues dropping and recreating an Index that is over 60% fragmented, is there anyone that knows why this is and what can I do to resolve this.
I use the script as option to create the drop and recreate scripts, execute the drop the Index does drop but when I recreate it it has the same amount of fragmentation as before the drop and recreate.
Any help is appreciated.
0
atorex
Asked:
atorex
2 Solutions
 
JestersGrindCommented:
I'm not sure why dropping and recreating the index produces the same amount of fragmentation.  Is this a very small table?  I would try the following.

ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)

Greg

0
 
atorexAuthor Commented:
It is strange, even on a maintenance defrag it does not change the fragmentation percentage, I have done it in a lab (not using a backup of this database) and it works fine just the live system is reacting this way.
I will try the proposed statement and hope it resolves it.
0
 
TempDBACommented:
How are you checking its fragmentation? Can you share your script for that. I doubt there is something fishy there.
Are you using any tool for index maintenance purpose?
0
 
Anthony PerkinsCommented:
>>It is strange, even on a maintenance defrag it does not change the fragmentation percentage<<
Not really.  As indicated previously, if the table is small it will show a high state of fragmentation.  You should ignore it.  It does not matter, the index will probably not even be used.
0
 
AnujCommented:
Your fragmented table might be small in size, so the data pages for your table are stored on mixed extends. You can ignore fragmentation on smaller index(Microsoft recommendation ignore fragmentation on Index with page count < 8 i.e 1 extend, )


Microsoft says :-
Rebuilding or reorganizing small indexes does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now