Esko Logo Back to Esko Support
Choose your language for a machine translation:

 

Description

When WebCenter is used intensively with frequent updates, database index fragmentation tends to occur. This can affect performance. This article explains how to detect fragmentation and how to resolve it.

This page covers Microsoft SQL Server Database only. If you are running WebCenter with an Oracle Database, please ask your database administration staff to monitor this parameter and act upon it if that is deemed necessary.

Procedure

You will need elevated rights to execute these procedures.

Detection

The following query will generate a distribution of fragmentation of indices within your WebCenter Database. It will divide the list of fragmented indexes into 10 equal bins and show the approximated fragmentation value in each bin.

select  frac, max(avg_fragmentation_in_percent) as [fragmentation_in_percent],count(avg_fragmentation_in_percent) as count
    from (select avg_fragmentation_in_percent, ntile(10) over (order by
            avg_fragmentation_in_percent) as [frac]
                from   sys.dm_db_index_physical_stats(DB_ID('WebCenter4_0'),DEFAULT ,DEFAULT ,DEFAULT ,DEFAULT)
                where avg_fragmentation_in_percent > 0) i
group by frac

This could produce a fragmentation distribution like this:

Because this distribution only takes non zero fragmentation into account, interpretation has to be done with some care. Follow these rules of thumb:

  • If 10th bin fragmentation is above 95%, you probably want to defragment.
  • If the bin size ('count') is not larger than 10, you probably don't have to bother.
  • If the the median (5th bin) exceeds 20%, you probably want to defragment.

Resolution

The solution for index fragmentation is index rebuilding. Following script will rebuild all indices in the WebCenter database:

USE WebCenter4_0 --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
    SELECT table_schema+'.'+table_name FROM information_schema.tables  
        WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC DBREINDEX(@TableName,' ',90)
    FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor 

source: https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754

DO NOT do this during office hours. Even though this can happen online, it will severely impact database performance.

If you are not using the express edition of Microsoft SQL Server, you can make use of the SQL Server Agent to schedule this script to run on a regular basis (after hours, e.g.: on weekends). Evaluate the database fragmentation distribution over time to determine what the appropriate interval is.

Article information
Applies to

WebCenter all versions

Created02-Mar-15
Last revised04-Mar-05
AuthorJEDE
CW Number 
Contents