Routine monitoring and maintenance for the Accelerator environment

Article:TECH63230  |  Created: 2008-01-12  |  Updated: 2014-10-06  |  Article URL http://www.symantec.com/docs/TECH63230
Article Type
Technical Solution

Product(s)

Issue



Routine monitoring and maintenance for the Accelerator environment


Cause



This document is to assist the Enterprise Vault Accelerator Administrator to properly monitor and maintain the Accelerator environment.
 


Solution



Step 1: Standard Maintenance

The number one action to perform in order to maintain a healthy Accelerator environment is to perform weekly Sql maintenance on the Accelerator customer database(s).  Perform the steps below in the order listed:
 
1. Shrink databases
 Notes for the Shrink databases step:
1.  Do not return space to the operating system.  This will allow the application to run for a while before needing to request log space growth.
2.  Set the Initial database size large enough to avoid growth between maintenance runs.
3.  This is an optional step but strongly recommended in an Accelerator environment to allow for optimum performance as the shrink operation will reorganize the data pages to the front of the database files, which allows for faster access to those pages.

2. Backup and truncate the transaction logs if the shrink database operation was performed.  (This is needed after a shrink operation as the shrink writes a large amount of information to the logs.)
3. Rebuild Indexes
4. Update Statistics

To monitor the overall effectiveness of the weekly maintenance, run the following SQL command against each of the Accelerator customer databases:
 

Please use SHOWCONTIG instead of sys.dm_db_index_physical_stats.  The index physical statistics do not calculate page order (Extent fragmentation).

DBCC SHOWCONTIG WITH ALL_INDEXES

Examine the results for tables that have over 1,000 pages scanned.

- Logical Scan Fragmentation - under 1%  
Latency occurs once logical scan fragmentation exceeds 10%
Once fragmentation exceeds 50%, that index  is no longer used
- Extent Scan Fragmentation - under 50%
- Avg. Page density (full) - over 90%

If the results exceed those limits, it is recommended to perform the action more frequently.
*** Note:  Active searches and productions lock tables from maintenance.  If there are active searches or productions during the maintenance routine the maintenance will skip those locked tables and complete.  The tables will remain fragmented and no performance gain will be realized.  It is recommended to STOP the Enterprise Vault Accelerator Manager service prior to performing the maintenance run.

 
 

 Step 2: Exclude the proper extensions and folders when using anti-virus software on the SQL server.

- How to exclude MS SQL files and folders using Centralized Exceptions.  See Technical Article TECH105240 in the Related Articles Section below for more information on the recommended antivirus exclusions on SQL Servers.
 
- Guidelines for choosing antivirus software to run on the computers that are running SQL server
(Look in section: Directories to exclude from virus scanning)
http://support.microsoft.com/kb/309422/
 
 
 
Step 3: SQL file growth
 
Tempdb and Accelerator Customer database and transaction logs can grow rapidly in an active Accelerator environment.  Running regular maintenance can avoid issues involving the transaction logs, but preemptive steps can be taken to accompany the regular maintenance.
 
- Set the Autogrowth for database files and transaction log files to File Growth: In Megabytes 500 and Maximun File Size: Unrestricted File Growth 
- Verify there is ample space for the database files and transaction log files to grow.
 
 
 
 
Step 4: SQL utilization

Several Accelerator procedures such as Searching, Reviewing, Productions and Legal Hold processes require a considerable amount of SQL resources.  When a SQL server is over utilized deadlocks on the database tables can occur frequently and lead to SQL time outs.  If SQL time outs or Deadlocks are an issue and the first two steps in this document are being followed, it is recommended to move the Accelerator databases to a dedicated SQL server.

To monitor the SQL server for deadlocking issues, start and monitor a SQL Profiler while performing daily activities.
 
  • Open SQL Server Profiler
  • Select File then New Trace
  • Use a Blank Template and expand Locks
  • Select Lock:Deadlocks and Lock:Deadlock Chains
  • Run

The Profiler should remain empty or very limited activity.  
 
 


Step 5: Active Accelerator Searches

Monitoring of active Accelerator searches is essential to Accelerator environmental health.  The following SQL script can be run against each of the Accelerator customer databases to show 'Active' processes:

SELECT tc.[Name] AS 'Case Name', tc.[CaseID] AS 'CaseID', tis.[Name] AS 'Search Name', tis.StatusID AS 'Search StatusID', tis.RunDate
FROM tblIntSearches tis
JOIN tblCase tc ON tc.CaseID = tis.CaseID
WHERE tis.StatusID IN (2, 5, 6)
 
-- Status Codes
-- 2 = Searching
-- 5 = Deleting
-- 6 = Accepting

The status 2, 5 and 6 are active  Inspect the Run Date.  If the Run Date is over 24 hours old there is an issue that needs to be investigated and resolved. 
 
 
Step 6: Paused Accelerator Searches

Paused Accelerator searches can prevent the release of worker threads for use by active searches, thus causing slow or no search processing.  The following SQL script can be run against each of the Accelerator customer databases to show 'Paused' searches:

SELECT tc.[Name] AS 'Case Name', tc.[CaseID] AS 'CaseID', tis.[Name] AS 'Search Name', tis.StatusID AS 'Search StatusID', tis.RunDate
FROM tblIntSearches tis
JOIN tblCase tc ON tc.CaseID = tis.CaseID
WHERE tis.StatusID IN (175,176,177,178,179)

-- Status Codes
-- 175 = Paused
-- 176 = To Be Paused
-- 177 = Pausing
-- 178 = To Be Submitted
-- 179 = Resubmitting

Each paused search needs to be investigated and resolved. 
 
 
Step 7: Accelerator Search in Pending Status

Neglect by Department/Case owners can cause millions of items from searches to be backlogged in the customer database awaiting some action to be performed by the end user.  Run the following SQL script against the Accelerator customer database to determine if searches are awaiting action:  

SELECT tc.[Name] AS 'Case Name', tc.[CaseID] AS 'CaseID', tis.[Name] AS 'Search Name', tis.StatusID AS 'Search StatusID', tis.RunDate
FROM tblIntSearches tis
JOIN tblCase tc ON tc.CaseID = tis.CaseID
WHERE tis.StatusID IN (3, 10)

-- Status Codes
-- 3 = Pending Acceptance
-- 10= Pending Acceptance (with errors)

Status 3 and 10 need user action performed, either accept or reject the search.  
 
Step 8: Export/Production Runs

Monitoring of active Accelerator exports is essential to Accelerator environmental health.  Run the following SQL script against each of the Accelerator customer databases.

SELECT tc.[Name] AS 'Case Name', tp.[Name] AS 'Export Name', tp.StatusID, tp.CreateDate AS 'Create Date'
FROM tblProduction tp
JOIN tblCase tc ON tc.CaseID = tp.CaseID
WHERE tp.StatusID IN (50, 51, 56, 57, 190, 191)

-- Status Codes:
-- 50 = Preparing
-- 51 = Processing
-- 56 = Processing
-- 57 = Processing
-- 190 = To be Stopped
-- 191 = Stopping

The status of 50 and 51 are active.  If the Create Date is over 24 hours old a service call to Symantec technical support is suggested.  The other status codes refer to issues that need to be investigated by the Accelerator Administrator and resolved.  The problem might be a temporary communication issue  and can be resolved by restarting the Enterprise Vault Accelerator manager service and retrying the export.  
 
 

Step 9: Vault Service Account Temporary folder size

All transactions requests from the Accelerator environment to the Enterprise Vault (EV) server and the Accelerator server utilize the Vault Service Account (VSA) temporary folder for temporary storage.  This folder needs to be able to grow large enough to accommodate Batch processing on the Accelerator server and Index (Search) and Storage (Export) requests on the EV server.  Locating the VSA temporary folders on a hard drive with at least 20 gig free space availability is recommended for all EV and Accelerator servers.
 
 

Step 10: Disable Anti virus scanning of the Vault Service Account (VSA) Temporary folder on all EV and Accelerator servers

Locate the VSA temporary folder by:
1. Log into the server as the VSA
2. Open the Properties page of Computer or my Computer
3. Select Advanced System Settings or the Advanced Tab
4. Select the Environment Variables button
5. Note the location path for the TEMP folder under the User variables section.
6. Disable Anti-Virus software from scanning this folder location.

 

Step 11: Disable Anti virus scanning of the Export folder on all Accelerator servers.

1. Run the following query against the Accelerator customer databases to determine all export folder locations:

SELECT ExportOutputDirectory FROM tblCase

 

Step 12: Adjust the web server TCP/IP socket parameters to provide the ASP.NET environment with adequate network sockets at a sufficient reusable rate on EV, Accelerator and SQL servers.

Warning: Incorrect use of the Windows registry editor may prevent the operating system from functioning properly. Great care should be taken when making changes to a Windows registry.  Registry modifications should only be carried-out by persons experienced in the use of the registry editor application. It is recommended that a complete backup of the registry and workstation be made prior to making any registry changes.

Windows 2003 Server:
1.
  On each of the Accelerator, Enterprise Vault and SQL server(s), locate the following key in the Windows registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

2.  Update the following values, or create them if they do not already exist: 
 

Name Type Default Recommended (decimal)
MaxUserPort REG_DWORD 5,000 64,512
TCPTimedWaitDelay REG_DWORD 240 120
MaxFreeTcbs REG_DWORD 2,000 65,536
MaxHashTableSize REG_DWORD 512 16,384

3.  For changes to take effect, restart the server. 

 
Windows 2008, 2008R2, 2012 Server:
On Windows 2008, 2008R2, 2012 servers, by default the operating system allows socket connections to be established between the ports 49152 – 65535; this equates to a little over 13,000 user ports.  Thirteen Thousand TCP ports is inadequate for an Enterprise Vault and Accelerator combined environment.

To display the current ports for the TCP protocol use the netsh command

netsh int ipv4 show dynamicport tcp

-To increase the ports use the net shell command

netsh int ipv4 set dynamicport tcp start=1500 num=63000
 
This will provide 63,000 TCP user ports.

Microsoft has identified an issue running the netsh command to set global TCP parameters on Windows Server 2008 and Vista machines.  Some global parameters, such as TCPTimedWaitDelay, can be change from their default or manually set values to 0xffffffff.  Before running any netsh command noted in this article, Symantec recommends reviewing Microsoft KB Article 967224 (support.microsoft.com/kb/967224).  Upon execution completion of any netsh command noted in this article, Symantec also recommends reviewing the TCP Parameters noted in the KB Article and applying the hotfix from the article if needed.  

NOTE:  
When increasing the MaxUserPort, Microsoft recommends that port 1434 be reserved for use by the SQL Server Browser service (sqlbrowser.exe).

On a Windows 2003 server add the following registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Add a REG_MULTI_SZ value key named ReservedPorts
  1. Click on the Parameters key in the left pane in the regedit utility.
  2. Right click in the right pane that will be showing the Parameters key values listing.
  3. Select the New option.
  4. Select the Multi-String Value option.
  5. Enter the name ReservedPorts into the open name box (may have New Value #1 as the default name)
  6. Press the Enter key or click the OK button to complete the value creation.
Set the span of the ReservedPorts value to 1434-1434:
  1. Double click on the ReservedPorts name to open the Edit Multi-String entry window.
  2. Add the values 1434-1434 into the Value Data: field
  3. Press the Enter key or click the OK button to save the entry and close the Edit Multi-String entry window.
     
On a Windows 2008, 2008R2, 2012, server start the port allocation at 1500 as shown in the previous Windows 2008 netsh command.

 

Step 13: Disable TCP/IP Offload Engine (TOE), TCP Segmentation Offloading (TSO) and  TCP Chimney on all EV servers and SQL Server. 

To disable TOE:

1. Open the properties page of the NIC card, normally located in the Control Panel under Network Connections.
2. Select the configure button, and then the Advanced tab.
3. Any item listed under the Property window with the word Offload, needs to be disabled. 
4. Also included in TOE is a process known as Receive Side Scaling or RSS. This too must be disabled on the NIC card. 
5. On certain NIC cards, TOE must be disabled via the NIC CMOS.
6. Save changes.

To disable TSO: (Virtual Environments only) 

1. Open the properties page of the NIC card, normally located in the Control Panel under Network Connections.
2. Select the configure button, and then the Advanced tab.
3. Under the Property disable TSOEnable. (Value: 0) 
4. Save changes.

To disable TCP Chimney:

 View the current Offload state 
 (Run the following from a Command prompt)
Windows 2003 Server:
Netsh int ip show offload

Windows 2008, 2008R2, 2012 Server:
Netsh int tcp show global

To disable TCP Chimney features on a Windows 2003 Server:
1.
Click Start, click Run, type Regedit, and then click OK.
2. Locate the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
3.
Examine the REG_DWORD EnableTCPChimney registry entry for the value 0, otherwise edit and modify .
4. Examine the REG_DWORD EnableRSS registry entry for the value 0, otherwise edit and modify .
5. Examine the REG_DWORD EnableTCPA registry entry for the value 0, otherwise edit and modify .
6. Restart of the server will be necessary for any changes to the TCPIP parameters to take affect.


To disable TCP Chimney Features on a Windows 2008, 2008R2, 2012 Server:
netsh int tcp set global chimney=disabled  (This disables TCP Chimney)
netsh int tcp set global rss=disabled           (This disables Receive Side Scaling)

(This disables NetDMA)
1. Click Start, click Run, type Regedit, and then click OK.
2. Locate the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters 
3. Add the REG_DWORD EnableTCPA registry entry with the value 0.
4. Restart of the server will be necessary for any changes to the TCPIP parameters to take affect.

 

Step 14:  Keep the Network interface Card (NIC) drivers up to date.

 .NET 2.0 programming with XML batch transfers were relatively new additions to the programming world at the time of this article's first writing, so the age of a NIC driver will make a difference in successful communications between the calling server and the SQL server.  In order for NIC cards to handle the new and ever changing demands of the .NET world, the NIC drivers should be less then two years old. 

To Locate the Driver Date:
1.
  From the Control Panel, open Network Connections.
2.  Open the properties page for the network connection being used.
3.  Click Configure
4.  On the Driver tab, verify the date of the driver is less then two year old.

 

Step 15:  SQL quick checks for possible performance bottlenecks. 

1. Use Task manager to examine the amount of Virtual Memory consumption by the sqlservr.exe service. 
Record the amount of memory paged to Virtual Memory (VM) after a reboot of the server and while the system is performing up to speed to set a bench mark value.  If the VM usage exceeds the bench mark value consistently, consider moving some databases to a new SQL server or adding additional memory.

2. Use Performance Monitor to examine for CPU, memory or hard drive bottlenecks.  See Technical Article TECH124987 in the Related Articles Section below for more information on how to use Performance Monitor for this operation.
 




Legacy ID



308418


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


Terms of use for this information are found in Legal Notices