Advanced Maintenance for SharePoint Databases – Defrag, update index
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
- Disable the sharepoint timer job “Database Statistics”
- run this maintenance (update index, re-index,…) by your own, (on a smatter way)
The smarter way:
- 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).
- Only IN_ROW_DATA allocations are being considered.
- This version also works for partitioned data.
- 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 intSET @AVG_FRAG_LMT = 50
SET @NUM_PAGE_LMT = 100
SET @REBUILD = 0
SET @ONLINE = 1
SET @MAX_MINS = 240DECLARE 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_LMTDECLARE @tabname nvarchar(256), @indname sysname, @endtime datetime
SET @endtime = DATEADD(mi, @MAX_MINS, GETDATE())
OPEN ix_cur
FETCH NEXT FROM ix_cur INTO @tabname, @indnameWHILE @@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 @sqlSET @sql = N'UPDATE STATISTICS ' + @tabname + N' ' + @indname
EXEC sp_executesql @sqlFETCH NEXT FROM ix_cur INTO @tabname, @indname
ENDCLOSE 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