Improve the performance of Centera collections by adding the SQL index 'IX_Collection_Saveset_Partition' to the Saveset table

Article:TECH51231  |  Created: 2007-01-30  |  Updated: 2014-05-02  |  Article URL http://www.symantec.com/docs/TECH51231
Article Type
Technical Solution

Product(s)

Issue



Improve the performance of Centera collections by adding the SQL index 'IX_Collection_Saveset_Partition' to the Saveset table


Solution



If you are running Centera collections, you may experience a degradation in performance as the Saveset table in the vault store database grows. You may have some or all of the following symptoms:
 
  • A backlog of items builds up in the Centera collections area.
  • SQL queries run against the Saveset table take a long time. (You can see this using SQL Profiler Trace).
  • SQL Deadlock errors, generated by Storage File Watch, are reported.

Enterprise Vault 5.0 SP5 created an SQL index called IX_Saveset, but this SQL index was deleted when upgrading to Enterprise Vault 6.0. It was discovered that performance could be affected if Centera vault store partitions had collection enabled, but this index was missing.

Resolution in Enterprise Vault 6.0 to Enterprise Vault 8.0 SP1

In releases Enterprise Vault 6.0 to Enterprise Vault 8.0 SP1 this issue can be resolved by manually adding the index, IX_Collection_Saveset_Partition, to the Saveset table. How to do this is described later in this document.

Resolution in Enterprise Vault 8.0 SP2 and later

From release 8.0 SP2, Enterprise Vault attempts to create the index automatically, if it does not exist. The space required for this index on the SQL Server hosting the relevant vault store database is approximately 27 bytes per row in the Saveset table. The index is created when the Storage service starts, provided the following conditions are satisfied:
 
  • At least one Centera vault store partition is open and enabled for collection.
  • The number of records in the Saveset table is less than, or equal to 1,000,000.
An informational message in the event log will report that the new index has been created.
 
In Enterprise Vault 10.0.3 and later, the index can also be created when a Centera partition is enabled for collections, if the above conditions are satisfied. As the Enterprise Vault SQL role, EVAdminRole, is needed to create the index, a user may need the EVAdminRole role when enabling collections on a vault store for the first time.


If there are more than 1,000,000 records in the Saveset table, then the index is not created automatically and the following warning message is reported in the event log:

Type:  Warning
Event: 7162
Source: Enterprise Vault
Category: Storage File Watch
Description:
You are recommended to create a new SQL index for this vault store database to improve the performance of processing items in the Centera collection area.
Table Name: Saveset
Database: vault store database name
SQLServer: SQL Server name
VaultStoreEntryId: VaultStoreEntryId


You can create the index manually, as described in this document.

How to create the index manually

You should only create this index for Centera vault store partitions that are enabled for collection. If the open partition is not a Centera partition enabled for collection, creating this index may impact archiving performance on the partition.

Before creating the index ensure that there is available space on the SQL Server. The space required for this index on the SQL Server hosting the relevant vault store database is approximately 27 bytes per row in the Saveset table.

In Enterprise Vault 10.0.3 and later, the user that creates the index must have the Enterprise Vault SQL role, EVAdminRole.

To create the index manually:

1. In SQL Server Management Studio run exec SP_helpindex saveset against each vault store database configured for Centera collections.

2. Check for IX_SAVESET or IX_Collection_Saveset_Partition. It should be a non-clustered index on the following saveset columns:
CollectionIdentity, SavesetIdentity, IdPartition

3. If neither index exists, run the following SQL statement against each vault store database configured for Centera collections.

IF NOT EXISTS(SELECT * FROM sysindexes WHERE
NAME = 'IX_Collection_Saveset_Partition' AND id IN (SELECT id FROM sysobjects
where name ='Saveset' AND xtype = 'U')) CREATE NONCLUSTERED INDEX
IX_Collection_Saveset_Partition ON dbo.Saveset (CollectionIdentity,
SavesetIdentity, IdPartition) WITH FILLFACTOR = 80 ON [PRIMARY]




Legacy ID



288036


Article URL http://www.symantec.com/docs/TECH51231


Terms of use for this information are found in Legal Notices