Improve the performance of Centera collections by adding the SQL index 'IX_Collection_Saveset_Partition' to the Saveset table
- 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.
Performance could be affected if Centera vault store partitions have collection enabled, but the SQL index, IX_Saveset, is missing.
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.
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:
Source: Enterprise Vault
Category: Storage File Watch
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
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]