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

  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

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Hi Patrick, as just discussed on the phone ;-) : an easily done optimization of the code above would be to change the logic to Rebuild OR (Reorganize AND Update Statistics – With Fullscan) Otherwise the freshly created statistics via the Index rebuild get dropped and at best re-created just the same, or even worse just using a Sample. Cheers Andreas

  • Anonymous
    January 01, 2003
    hi Dird,
    the script is an example to do SQL Maintenance. It has still room for improvement ;-) but i believe this will help many People in the first way of thinking... how to do it.

    regards

    Patrick

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    sorry not tested for sql 2008 r2. i only test sql 2005 & 2008.  it's also not neccessry to run it with sharepoint 2010, be ause the .stored procedure proc_DefragmentIndices looks similar to this script above :-)

  • Anonymous
    January 01, 2003
    How is this different than, or related to, Article ID: 943345 -- How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases... support.microsoft.com/.../943345 Is your solution officially supported by Microsoft? Or would we invalidate our support/warranty by using it? What do you think about this super-comprehensive, free, third-party Index Defrag solution? ola.hallengren.com/Documentation.html

  • Anonymous
    March 15, 2011
    Have you tested this script SQL 2008 R2?

  • Anonymous
    March 13, 2012
    How would I loop this through all my databases?

  • Anonymous
    October 21, 2014
    Am I missing something or is @REBUILD never set on an index-by-index basis? From what I see the case will always be @REBUILD=0 so all indexes get reorg'd instead of rebuilt.