nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

Optimizing the database indexes on SQL Server

Florent_Guillau

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

Find more posts tagged with

Comments

Florent_Guillau

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: