Hello @all,

this post is an addition of my last post of: How to defrag sharepoint databases

In the past I've seen some environment which have a really high load over the whole day. Doing maintenance tasks like backup, running search crawls and defrag SharePoint database need to be done on a very small time window. In case of overlapping maintenance jobs a situation can happen that the blocking chain increase and the performance goes down.

Since Service Pack 2 in SharePoint exists for each content database a SharePoint timer job, called “Database Statistics”. This timer job run in a daily schedule and start on SQL backend one stored procedure, called “proc_DefragmentIndices”. This stored proc makes a  reindex operration to rebuild the index.

In a scenario of high load and short maintenance windows it’s possible to do the defragmentation in a smarter way. Stored Proc “proc_DefragmentIndices” rebuild the index of all tables. To avoid and to reduce blocking we can do a little trick.

Strategy

1. Disable the sharepoint timer job “Database Statistics”
2. run this maintenance (update index, re-index,…) by your own, (on a smatter way)

The smarter way:

1. Only indexes are being considered where the avg. fragmentation is higher than the given limit and where the number of pages exceeds the given limit (should be at least 50).
2. Only IN_ROW_DATA allocations are being considered.
3. This version also works for partitioned data.
4. Only indexes that meet the requirements (according to BOL) of the chosen mode (Online Rebuild or Reorganize) are being considered.

 [update: 04.11.2010 - adding update statistics to SQL script]

SQL Script to do it:

DECLARE @AVG_FRAG_LMT float
DECLARE @NUM_PAGE_LMT int
DECLARE @REBUILD bit
DECLARE @ONLINE bit
DECLARE @MAX_MINS int

SET @AVG_FRAG_LMT = 50
SET @NUM_PAGE_LMT = 100
SET @REBUILD = 0
SET @ONLINE = 1
SET @MAX_MINS = 240

DECLARE ix_cur CURSOR READ_ONLY FOR
   SELECT quotename(schema_name(t.schema_id)) + N'.' + quotename(t.name),
          quotename(i.name)
     FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ips
     JOIN sys.tables t ON t.object_id = ips.object_id
     JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = ips.index_id
    WHERE ips.alloc_unit_type_desc = N'IN_ROW_DATA' AND
          i.type in (1, 2) AND i.is_disabled = 0 AND
          (@REBUILD <> 0 OR i.allow_page_locks <> 0) AND
          (@REBUILD = 0 OR @ONLINE = 0 OR NOT EXISTS (
          SELECT * FROM sys.partition_schemes s
           WHERE s.data_space_id = i.data_space_id) AND
          (i.type = 1 AND NOT EXISTS (
          SELECT * FROM sys.columns c
           WHERE c.object_id = t.object_id AND
                 (c.user_type_id IN (34, 35, 99) OR c.max_length = -1)) OR
          i.type = 2 AND NOT EXISTS (
          SELECT * FROM sys.index_columns ic JOIN sys.columns c
              ON c.object_id = ic.object_id and c.column_id = ic.column_id
           WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND
                 (c.user_type_id IN (34, 35, 99) OR c.max_length = -1))))
    GROUP BY t.schema_id, t.name, i.name
   HAVING AVG(ips.avg_fragmentation_in_percent) > @AVG_FRAG_LMT AND
          SUM(ips.page_count) >= @NUM_PAGE_LMT

DECLARE @tabname nvarchar(256), @indname sysname, @endtime datetime
SET @endtime = DATEADD(mi, @MAX_MINS, GETDATE())
OPEN ix_cur
FETCH NEXT FROM ix_cur INTO @tabname, @indname

WHILE @@FETCH_STATUS = 0 AND GETDATE() < @endtime
BEGIN
   DECLARE @sql nvarchar(max)
   SET @sql = N'ALTER INDEX ' + @indname + N' ON ' + @tabname +
              CASE WHEN @REBUILD = 0
                   THEN N' REORGANIZE'
                   ELSE N' REBUILD WITH (ONLINE = ' +
                        CASE WHEN @ONLINE = 0 THEN N'OFF)' ELSE N'ON)' END
              END
   EXEC sp_executesql @sql
  
   SET @sql = N'UPDATE STATISTICS ' + @tabname + N' ' + @indname
   EXEC sp_executesql @sql

   FETCH NEXT FROM ix_cur INTO @tabname, @indname
END

CLOSE ix_cur
DEALLOCATE ix_cur

This script runs fine on SQL 2005 - KB 932744 -  Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases

Regards

Patrick