Index Fragmentation RRS feed

  • Question

  • Hi,

    We have SQL Server 2005 EE x64 with SP3 having MOSS 2007 SP1 databases.

    To find Index Fragmentation in our content database, I have ran the below query:

    SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats (8, NULL, NULL, NULL, 'limited')
    where avg_fragmentation_in_percent>10 AND page_count>1000 AND Inex_id>0
    order by page_count desc

    Results: Just one index got fragmented as below:

    database_id object_id   index_id    index_type_desc      avg_fragmentation_in_percent page_count
    ----------- ----------- ----------- ------------------------------------------------------------
    8           53575229    2           NONCLUSTERED INDEX     65.31             1332

    If ran the above query by chaning page_count>100, we are getting 5 indexes:

    8           53575229    2           NONCLUSTERED INDEX     65.31             1332
    8           277576027   1           CLUSTERED INDEX        40.49             563
    8           277576027   3           NONCLUSTERED INDEX     88.53             506
    8           277576027   6           NONCLUSTERED INDEX     88.27             503
    8           277576027   2           NONCLUSTERED INDEX     88.44             502

    (5 row(s) affected)

    So performing Index defrag for indexes that are having page_count>1000 only is a valid/acceptable/best practice method or not?

    Can we use the below procedure to defragment content database indexes


    -- Ensure a USE   statement has been executed first.
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000);
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    -- and convert object and index IDs to names.
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

    -- Open the cursor.
    OPEN partitions;

    -- Loop through the partitions.
    WHILE (1=1)
            FETCH NEXT
               FROM partitions
               INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;
            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
            IF @partitioncount > 1
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
            PRINT N'Executed: ' + @command;

    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

    -- Drop the temporary table.
    DROP TABLE #work_to_do;

    • Moved by Mike Walsh FIN Sunday, March 14, 2010 9:39 AM indexing (Isn't a General issue - if not Search its Admin) (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Sunday, March 14, 2010 8:55 AM


  • Hi Samnalla,


    The extent of the Microsoft SQL Server index fragmentation determines whether a fragmented database will be defragmented by an online defragmentation process or by an offline defragmentation process. In online defragmentation, only the SQL Server leaf pages are defragmented, not the SQL Server locked pages. In offline defragmentation, the locked pages and all the leaf pages are defragmented.


    For more information about how to defragment WSS and SharePoint databases, please refer to the following article:

    How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases



    For more information about index fragmentation, please refer to the following article:


    SQL Index defrag and maintenance tasks for Search



    Important The above SQL Server stored procedure makes changes to Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases. This script must be run exactly as specified in this article. Changes that you make to SharePoint Server 2007 databases must follow the Support guidelines that are specified in the following Microsoft Knowledge Base article:

    841057  (http://support.microsoft.com/kb/841057/ ) Support for changes to the databases that are used by Office server products and by Windows SharePoint Services


    Hope this helps.


    Rock Wang

    Rock Wang– MSFT
    Tuesday, March 16, 2010 1:48 AM