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 (9)

  1. Anonymous says:

    Hi Jerry,

    to loop through all dbs i would create an Integration Services Package.

    1. execute SQL Script to get all databases from server: e.g. select name from sys.databases

    2. in a loop i would execute the script above and use the output of step 1 to set the focus on the specific database

    3. deploy the SSIS package on SQL

    4. define a schedule plan to run SSIS package.

  2. Anonymous says:

    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

  3. Anonymous says:

    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

  4. Anonymous says:

    Hello jpSQLDude,

    kb 943345 was published after MOSS2007 RTM but before MOSS2007 Service Pack 2. So before SP2 was available and KB 943345 was the only offical support statement defrag your sharepoint database. But the idear from sharepoint is easy: SharePoint see SQL and all Databases as black box. Any modification on db schema is not supported but a SQL admin should do a maintaince job (e.g. via script) for each db on sql server. Inside a SQL maintance script is the following included: backup, scrink, defrag, …

    So the sql script above is a optimized version of defrag your database and to refresh your sql index. based on product rules, this script is not forbidden and ready for use, but we have no offical KB created for using/suggesting this script. But one little hint: please compare this script and the implementation of SP2010 -> stored proc "proc_DefragmentIndices". Both scripts looks very similar 😉 *do you still need a KB?*

    Regarding 3td party index defrag solution: the constructor of the 3td party solution can request a support statement from microsoft. based on the implementation you can do alot of stuff correct / wrong.

    regards

    patrick

  5. Anonymous says:

    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 🙂

  6. Anonymous says:

    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

  7. Sonya says:

    Have you tested this script SQL 2008 R2?

  8. Jerry Dille says:

    How would I loop this through all my databases?

  9. Dird says:

    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.

Skip to main content