Article ID: 943345
Article Last Modified on 11/7/2007
APPLIES TO
- Microsoft Windows SharePoint Services 3.0
- Microsoft Office SharePoint Server 2007
INTRODUCTION
This article describes how to defragment the following Microsoft Windows SharePoint Services 3.0 databases and Microsoft Office SharePoint Server 2007 databases:
- Search database
- Profile database
- Content database
MORE INFORMATION
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 measures the level of fragmentation, and then it performs either offline defragmentation or online defragmentation if it is required.
In the following script, the level of fragmentation is defined as the number of blocks that are logically linear and physically nonlinear. If the level of defragmentation is less than 10 percent, defragmentation does not occur. If the level of fragmentation is from 10 percent to 90 percent, online defragmentation occurs. If the level of fragmentation is more than 90 percent, offline defragmentation occurs.
Important The following 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 Support for changes to the databases that are used by Office server products and by Windows SharePoint Services
Note We recommend that you host the SharePoint Server 2007 databases on a computer that is running SQL Server 2005. We recommend that you monitor the level of fragmentation before and after you run this script. Schedule the script to run daily, weekly, or monthly, as appropriate for your situation.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes')
BEGIN
DROP Procedure dbo.proc_DefragIndexes
END
GO
-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds.
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold.
-- Parameters:
-- @onlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform online defragmentation (default 10%).
-- @offlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform offline defragmentation (default 90%).
-- @updateStatsThreshold specifies the number of days since the last statistics update
-- which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE dbo.proc_DefragIndexes
(
@onlineDefragThreshold float = 10.0,
@offlineDefragThreshold float = 90.0,
@updateStatsThreshold int = 7
)
AS
BEGIN
set nocount on
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag float
DECLARE @command varchar(8000)
DECLARE @schemaname sysname
DECLARE @objectname sysname
DECLARE @indexname sysname
declare @AllIndexes table (objectid int, indexid int, fragmentation float)
declare @currentDdbId int
select @currentDdbId = DB_ID()
insert into @AllIndexes
SELECT
object_id, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
OPEN indexesToDefrag;
-- Loop through the partitions.
FETCH NEXT
FROM indexesToDefrag
INTO @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
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
SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid
IF @frag > @onlineDefragThreshold
BEGIN
IF @frag < @offlineDefragThreshold
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REORGANIZE'
EXEC (@command)
END;
IF @frag >= @offlineDefragThreshold
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD'
EXEC (@command)
END;
PRINT 'Executed ' + @command
END
IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
BEGIN
SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
EXEC (@command)
PRINT 'Executed ' + @command
END
FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
END
CLOSE indexesToDefrag;
DEALLOCATE indexesToDefrag;
END
GO
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
932744 Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases
Keywords: kbhowto kbinfo kbexpertiseinter KB943345
