Legal Hold Best Practices

This is a guide to achieve optimum performance when applying or removing legal holds.  When the SQL, Enterprise Vault (EV) and Discovery Accelerator (DA) environments are properly tuned, applying of legal holds should achieve a rate between six million to fourteen million items in a twenty four hour period.

Several factors will affect the speed of applying or removing legal holds
- Missing index on the Vault Store HoldSaveset table
- SQL server memory allocation to the SQL instance
- SQL work load
- System tempDB database initial size is set too low.
- Growth settings for data and log files
- Available physical disk space for data and log file growth
- Enterprise Vault(EV) Vault Store Saveset table index fragmentation
- Discovery Accelerator(DA) tblIntDiscoveredItems and tblDiscoveredItemsToSearch table index fragmentation
- TCP Chimney or TCP/IP Offload Engine (TOE) are enabled on the SQL or DA servers
- Miscellaneous DA server activities
- Memory saturation on the EV Storage server


- Missing index on the Vault Store HoldSaveset table
Examine the indexes on the EV Vault Store table HoldSaveset for the index: IX_HoldSaveset_SSID_HGID_HID.  Add the index if it is not present.

- To add the index, run the following script against the EV Vault Store database

CREATE NONCLUSTERED INDEX [IX_HoldSaveset_SSID_HGID_HID] ON [dbo].[HoldSaveset]
(
[SavesetIdentity] ASC,
[HoldGroupIdentity] ASC,
[HoldIdentity] ASC
)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) ON [PRIMARY]
GO



- SQL server memory allocation to the SQL instance
Multiple instances can reside on the same SQL box.  If min and max server memory per instance is not set, an instance will attempt to control as much memory as it can obtain taking away available resources for the other instances and applications.  Releasing memory from the buffer pool is slow and during the applying or releasing of legal holds, the SQL server service will begin to allocate a large quantity of available memory.

How to verify memory allocation and usage;
open task manager processes tab and locate sqlservr.exe and note the Mem Usage value.  If the memory used exceeds seventy percent (70%) of available server memory, set the Max server memory value between 60% - 70% of available server memory, leaving the remaining 30% - 40% for extended stored procedures, COM objects, non-shared DLL's, Ex's, and MAPI components.  After setting the max server memory in the instance, a reboot of the server is required to adjust the new Config settings into the Running settings.
Min server memory should be left a 0 unless rebooting of the SQL server is common practice.  Allocating memory consumes resources and time, thus frequent rebooting of the SQL server with Min server memory setting at 0 is inefficient.  To determine what value to place on min server memory, reboot the SQL server while there is a normal or below normal Sql workload (no legal hold work to be done).  Open Task Manager and Min server memory at the Mem Usage value for sqlservr.exe



- SQL work load
Run a SQL profiler with events of 'Deadlocks' and Deadlock Chains' and no filters.  If this is regularly recording items (one item a minute), the SQL server will have very poor legal hold performance.

How to run a 'Deadlock' profiler; (SQL 2005)
1. Open SQL server profiler, select File - New Trace and connect to the SQL instance.
2. Name the Trace, Use the 'Blank' template
3. Save to file and then select the 'Events Selection' tab
4. Under Events, expand Locks and select: Lock:Deadlock and Lock:Deadlock Chain
5. Press Run

This style of Deadlock profiler will examine the entire SQL server instance for deadlocks.



- System tempDB database initial size is set too low.
By default the initial size of the tempDB MDF file is 8MB and LDF 1MB at 10% autogrowth.  For DA legal hold processing, this is very inefficient use of SQL resources.  SQL's content management will halt all processes while acquiring additional space for the data or log file growth. Veritas recommends setting the initial file sizes to MDF 200MB and LDF 200MB with 100 - 200MB autogrowth.

Read the following Microsoft TechNet article for additional information on optimizing the tempDB database
  https://technet.microsoft.com/en-au/library/cc966545.aspx#ECAA



- Growth settings for data and log files
SQL's content management will halt all processes while acquiring additional space for the data or log file growth.  Default settings for autogrowth is ten percent (10%).  As the database grows in size, the length in time required for the content management to provide the additional space may exceed a worker thread's TTL.  Veritas recommends changing this value to physical growth between 100 - 200 MB with unrestricted growth for both data and log files.  Verify settings for all EV Vault Store, DA and tempDB databases.

How to view autogrowth settings; (SQL 2005)
1. Open Microsoft SQL Server Management Studio and connect to the SQL instance
2. Expand Databases and right click on the appropriate database, select properties
3. From the new properties window, select Files
4. Under the Autogrowth column the settings can be viewed and changed.



- Available physical disk space for data and log file growth
Examine the location of the data and log files for ample free hard disk space.  Verify settings for all EV Vault Store, DA and tempDB databases.

How to determine data and log file location; (SQL 2005 & 2008)
1. Open Microsoft SQL Server Management Studio and connect to the SQL instance
2. Expand Databases and right click on the appropriate database, select properties
3. From the new properties window, select Files
4. Under the Path column will be the physical location of the data and log files.



- Enterprise Vault(EV) Vault Store Saveset table index fragmentation
Run the following SQL query against each EV Vault Store
DBCC SHOWCONTIG (Saveset)

External Fragmentation (pages out of sequence) for any table index with over 1000 pages:
 

If any results is do not fall within the listed parameters, there is fragmentation and SQL maintenance on the database should be performed.
Recommended weekly SQL maintenance to be run in the order presented below:
( For best effectiveness of the maintenance, stop all Enterprise Vault services.  For good effectiveness, p lace the Enterprise Vault services into backup mode (read-only))
 

- Discovery Accelerator(DA) tblIntDiscoveredItems and tblDiscoveredItemToSearch table index fragmentation
Run the following two SQL queries against the DA customer database
DBCC SHOWCONTIG (tblIntDiscoveredItems)
DBCC SHOWCONTIG (tblDiscoveredItemsToSearch)

External Fragmentation (pages out of sequence) for all table indices that have over 1000 pages:
 

If any results is do not fall within the listed parameters, there is fragmentation and SQL maintenance on the database should be performed.

Recommended weekly SQL maintenance to be run in the order presented below:
( Stop the Enterprise Vault Accelerator Manager Service before running SQL maintenance)
 

- TCP Chimney or TCP/IP Offload Engine (TOE) are enabled on the SQL or DA servers
The Legal Hold process is batch driven.  TCP Chimney and TOE do not function as expected when using batch processing on a SQL server.  The legal hold batch may be processed at the SQL server, but improper notification to the DA server results in either a retry of the same batch of legal hold items or the items will be set to Error.

To verify TCP Chimney and TOE are disabled Please refer to the technical note: 'TCP Chimney or TCPIP Offload Engine (TOE) will cause a transport-level error to be logged resulting in inaccurate hit counts for Accelerator searches' in the Related Documents section of this technical note.



- Miscellaneous DA server activities
Other DA activities will access the same SQL tables the legal hold application requires, thus contention between worker threads will cause delays.  Such activities include Search Acceptance, Exports and Case Deletions.

To verify the current health and activity of the DA environment, Please refer to the technical note: 'Routine monitoring and maintenance for the Accelerator environment' in the Related Documents section of this technical note.


- Memory saturation on the EV Storage server
Older versions of Enterprise Vault depleted memory resources on the EV Storage server when applying over two million legal holds concurrently.
-Workaround:  

1. Stop the Enterprise Vault Accelerator Manager Service on the Accelerator server

2. Restart the Enterprise Vault Storage service on the EV Storage server on versions 6, 7, and 2007.  Restart the Enterprise Vault Directory Service for version 8.