Customers might experience severe performance issues while applying legal holds when the HoldSaveset table in the Vault Store database exceeds twenty million records

Workaround for Enterprise Vault versions prior to 8.0 SP4:
1. Determine the quantity of records contained in the HoldSaveset table within the Enterprise Vault (EV) Vault Store database

2. Run the following procedure to apply an new index to the HoldSaveset table:

Solution:
This issue has been addressed as part of the following release:

Enterprise Vault 8.0 SP4
 http://entsupport.symantec.com/docs/337274

Note:

The default configuration for the above index in EV versions 8.0 SP4 and above is to have a fill factor of 100%.  Using 100% fill factor causes the index to create a new page when the first item is added after a rebuild of the index.  It is recommended to change this fill factor to 70% to improve the hold application processing.

To check the fill factor:

1. Log onto the SQL Server hosting the Vault Store database, using an account with sufficient permissions to modify the database (such as the Vault Service Account).

2. Launch SQL Server Management Studio.

3. Connect to the SQL Server (and instance, as appropriate), when prompted.

4. Expand Databases in the left pane.

5. Expand the Vault Store database under Databases.

6. Expand Tables under the Vault Store database.

7. Expand the HoldSaveset table under the Vault Store database.

8. Expand Indexes under the HoldSaveset table.

9. Right click on the index named IX_HoldSaveset_SSID_HGID_HID.

10. Select the Script Index as > option.

11. Select the CREATE To > option.

12. Select the New Query Editor Window option.  This will open a new query window in the right panel.  This query window will contain the script needed to create this index.

13. Review the last line of the script in the query window to see if the option FILLFACTOR is present.  If this option is present, the line will look something like the line below (FILLFACTOR of 70% used in this example):

    1. ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

14. If the FILLFACTOR option is not present, the line will look something like the line below (with the default FILLFACTOR of 100%):

    1. ) WITH (PAD_INDEX = OFF, STATISTICS_COMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK = ON) ON [PRIMARY]

15. If the FILLFACTOR option is present and set to 70 or 80, no further action is needed and SQL Server Management Studio can be closed and the account logged off of the SQL Server.

16. If the FILLFACTOR option is not present or needs to be adjusted, follow the instructions below.

 To add or adjust the FILLFACTOR for this index:

1. Right click on the index in the left pane.

2. Select the Properties option.

3. Click on the Options link in the left panel of the Properties dialog box that will be displayed.

4. Click on the Set fill factor: check box to place a check mark in it.

5. Change the percentage value to the right of the check box from the default of 100 to 70.

6. Note that the check box for the Rebuild index option has automatically become checked and is grayed out to prevent it from being changed.  This is the desired behavior for this option.

7. Click the OK button to save the change and rebuild the index.  The index rebuild will take from a few seconds to a few minutes depending on the number of rows in the HoldSaveset table.

8. When the index rebuild is completed, the properties dialog box will disappear.  SQL Server Management Studio may now be safely closed and the account logged off of the SQL Server.