SQL 2005 Indexing

Posted on 2011-10-19
Last Modified: 2012-05-12
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.
Question by:atorex
    LVL 21

    Expert Comment

    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.




    Author Comment

    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.
    LVL 25

    Expert Comment

    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?
    LVL 75

    Accepted Solution

    >>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.
    LVL 15

    Assisted Solution

    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.


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now