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:
