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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.



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.
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?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnujSQL Server DBACommented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.