Notification Server performance issues due to SQL index fragmentation

Products

• IT Management Suite

Issue/Introduction

After running Notification Server in a production environment for several months, performance continues to degrade. The performance degradation results in sluggish console response, SQL timeout errors in the log files, and extended execution times for reports.

Cause

The performance degradation may be related to SQL index fragmentation in the Altiris database.

Resolution

It is recommended that database index defragmentation is performed. The following SQL script can be run from SQL Query Analyzer to rebuild all indexes in the Altiris database. 

Note: The script will cycle through each table in the Altiris database in a serial fashion and rebuild the indexes. During a table reindex operation, the table will not be available for use. Additionally, on large databases, the reindex operation may take an extended period of time. Preventive maintenance should be configured to run this process periodically (approximately once a month) using a SQL scheduler job.

-- BEGIN
Declare @TableName Char(90)Declare 
Cur Cursor FOR 
SELECT Name 
FROM sysobjects 
WHERE (xtype = 'U')OPEN Cur Fetch Next 
FROM Cur 
 INTO @TableName 
    WHILE (@@fetch_status=0)   
   begin DBCC DBREINDEX (@TableName, '', 0)  
  Fetch Next  FROM Cur INTO @TableName
  END
CLOSE Cur
Deallocate Cur
-- END

Applies To

Notification Server 6.x