How to defragment SharePoint Databases

[UPDATE 29.05.09]

To improve the performance of SharePoint databases you can try to defrag theses SharePoint databases:

• Content Db
• Profil Db
• Search db

The suggestion to reduce the fragmentation is to run  the sql script. Schedule the script in a schedule plan like daily, weekly, or monthly, as appropriate for your situation.

 

How to measure the fragmentation?

SQL 2000 -> using DBCC SHOWCONTIG

SQL 2005 -> using sys.dm_db_index_physical_stats.

To get a Fragmentation list of all databases run this:

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.

The following SQL Server script from KB 943345 tries to perform an online defragmentation first then it switches to offline defragmentation where required.

 

Before you are starting the sql script below please take a look first into the sql background: Link

Take a look into the parts of:

Detecting Fragmentation
The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column

Logical Fragmentation
This is the percentage of out-of-order pages in the leaf pages of an index.

Extent Fragmentation
This is the percentage of out-of-order extents in the leaf pages of a heap.

 

 

 CREATE PROCEDURE [dbo].[proc_DefragmentIndices]
AS
    SET NOCOUNT ON
    DECLARE @objectid int
    DECLARE @indexid int
    DECLARE @command varchar(8000)
    DECLARE @baseCommand varchar(8000)
    DECLARE @schemaname sysname
    DECLARE @objectname sysname
    DECLARE @indexname sysname
    DECLARE @currentDdbId int
    SELECT @currentDdbId = DB_ID()

    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting'

    -- Loop over each of the indices
    DECLARE indexesToDefrag CURSOR FOR 
    SELECT 
        i.object_id, 
        i.index_id, 
        i.name
    FROM 
        sys.indexes AS i
    INNER JOIN 
        sys.objects AS o
    ON
        i.object_id = o.object_id
    WHERE 
        i.index_id > 0 AND
        o.type = 'U'

    OPEN indexesToDefrag
    -- Loop through the partitions.
    FETCH NEXT
    FROM
        indexesToDefrag
    INTO 
        @objectid, 
        @indexid,
        @indexname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Lookup the name of the index
        SELECT 
            @schemaname = s.name
        FROM 
            sys.objects AS o
        JOIN 
            sys.schemas AS s
        ON
            s.schema_id = o.schema_id
        WHERE
            o.object_id = @objectid

        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + @indexname + ' is now being rebuilt.'

        -- Fragmentation is bad enough that it will be more efficient to rebuild the index
        SELECT @baseCommand = 
            ' ALTER INDEX ' + 
                @indexname +
            ' ON ' + 
                @schemaname + '.' + object_name(@objectid) + 
            ' REBUILD WITH (FILLFACTOR = 80, ONLINE = '

        -- Use dynamic sql so this compiles in SQL 2000
        SELECT @command =
            ' BEGIN TRY ' + 
               @baseCommand + 'ON) ' +
            ' END TRY ' +
            ' BEGIN CATCH ' +
               -- Indices with image-like columns can't be rebuild online, so go offline
               @baseCommand + 'OFF) ' +
            ' END CATCH '

        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding'
        EXEC (@command)
        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done'

        FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname
    END
    CLOSE indexesToDefrag
    DEALLOCATE indexesToDefrag

    RETURN 0
GO

 

 

NOTE These script will not be needed for WSS databases (content and config) after Windows SharePoint Services 3.0 SP2 if you are running Sql 2005.

 

[Update]

"What to do if defragment of SharePoint database fails" - Link

 [Update] - AFTER SP2:
"Advanced maintenance for sharepoint database: defrag & update index" - Link

regards

Patrick

Comments

• Anonymous
  January 01, 2003
  Hello @all, this post is an addition of my last post of: How to defrag sharepoint databases In the past

• Anonymous
  August 03, 2015
  SharePoint is a platform that I have found to be surrounded by a lot of misconception. Many people assume

• Anonymous
  August 25, 2015
  Over the past few years I have delivered a number of SharePoint health checks, and one of the most common