SQL Maintenance for Sharepoint 2003

All replies

• 

  

  1

  Sign in to vote

  Hi Mauricio,

   

  In SharePoint 2007, if we have installed SP2 there should be a timer job to updates SQL Server query optimization statistics for content databases and rebuild database indexes.

   

  Here is the script to do it on SQL 2005, you can compare it with your script:

   

  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

  For more information, please refer to this:

   

  http://blogs.technet.com/b/patrick_heyde/archive/2010/05/27/advanced-maintenance-for-sharepoint-databases-defrag-update-index.aspx

   

  And this article related to maintain database of SQL 2000:

   

  http://msdn.microsoft.com/en-us/library/aa297936(v=SQL.80).aspx

   

  Hope this helps.

   

  Thanks.

  ---

  Pengyu Zhao

  TechNet Community Support

  

  • Marked as answer by Pengyu Zhao Wednesday, December 21, 2011 6:45 AM
  • Unmarked as answer by MauricioUY Wednesday, December 21, 2011 11:48 AM

  Monday, December 19, 2011 4:43 AM
• 

  

  0

  Sign in to vote

  Hello Pengyu,

  I already knows this script, it seems to do the same thing but using ALTER INDEX instead of DBREINDEX.

  I supposed that DBREINDEX shouldn't affect Sharepoint 2003, but I really would like to know what has to say a Sharepoint expert.....

  thanks!

  Mauricio.

  ---

  mpv

  Monday, December 19, 2011 6:56 PM