Optimizing the database indexes on SQL Server

Florent_Guillau — Fri, 21 Mar 2014 11:59:15 GMT

What maintenance tasks should I use on SQL Server to optimize the indexes (rebuild/reorganize) in order for the database performance to be constant?

Re: Optimizing the database indexes on SQL Server

Florent_Guillau — Fri, 21 Mar 2014 12:09:12 GMT

This is not specific to Nuxeo but a customer had good results with the following:

USE [NUXEO]
GO
/****** Object:  StoredProcedure [dbo].[refreshIndexes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[refreshIndexes](@db_name varchar(500))
AS

declare @tmptable TABLE(TableName varchar(500),IndexName varchar(500),AvgPageFragmentation dec(10,2),allowPageLocks bit)
declare @table_name varchar(500)
declare @index_name varchar(500)
declare @frag float
declare @allowPageLocks bit
declare @req nvarchar(max)

set @req = '
 SELECT 
  OBJECT_NAME(DPS.OBJECT_ID,DB_ID(''' + @db_name + ''')) AS TableName
  ,SI.NAME AS IndexName
  ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
  allow_page_locks
 FROM sys.dm_db_index_physical_stats (DB_ID(''' + @db_name + '''), NULL, NULL , NULL, NULL) DPS
 INNER JOIN [' + @db_name + '].sys.indexes SI 
  ON DPS.OBJECT_ID = SI.OBJECT_ID 
  AND DPS.INDEX_ID = SI.INDEX_ID
  AND SI.NAME IS NOT NULL
 ORDER BY DPS.avg_fragmentation_in_percent DESC
 '
insert into @tmptable
exec(@req)
--print @req

DECLARE bigindex cursor LOCAL FAST_FORWARD
FOR
 select *from @tmptable where IndexName is not null

open bigindex
FETCH NEXT from bigindex into @table_name,@index_name,@frag,@allowPageLocks
WHILE (@@FETCH_STATUS=0)
BEGIN
 set @req = ''
-- REORGANIZE FAILED = Indexes for which ALLOW_PAGE_LOCKS is OFF
 if (@frag >=40 or (@allowPageLocks=0 and @frag >=10)) 
  set @req = 'ALTER INDEX [' + @index_name + '] ON [' + @db_name + '].dbo.[' + @table_name + '] REBUILD WITH(FILLFACTOR=80,STATISTICS_NORECOMPUTE=OFF,ONLINE=OFF)' 
 else if (@frag >=10 )
  set @req = 'ALTER INDEX [' + @index_name + '] ON [' + @db_name + '].dbo.[' + @table_name + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
 if (@req!='')
  exec(@req)
print @req

FETCH NEXT from bigindex into @table_name,@index_name,@frag,@allowPageLocks
END

CLOSE bigindex
DEALLOCATE bigindex

The following links may also be of interest:

topic Re: Optimizing the database indexes on SQL Server in Nuxeo Forum

Optimizing the database indexes on SQL Server

Re: Optimizing the database indexes on SQL Server