000033814 - RSA ECAT Database Tuning and Optimization

Document created by RSA Customer Support Employee on Jun 6, 2017Last modified by RSA Customer Support Employee on Jun 6, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000033814
Applies ToRSA Product Set: ECAT
RSA Product/Service Type: ECAT
RSA Version/Condition: 4..0.x,4.1.x,4.2.x
Platform: Windows
Platform (Other): SQL 2012, SQL 2014
IssueThis is a how-to article dedicated to performance enhancements and otherwise advice on tuning the performance of the ECAT database.

Database Performance/Tuning

It is necessary to understand what the minimum recommendations are and to remember that disk space is heavily influenced by the flow of data, along with most other factors in NetWitness Endpoint. For instance, if the QueuedData folder is inaccessible to the database, files will build in the folder over time, potentially consuming enormous amounts of space since agents will continue to check in with the console server and queue up data. So to start, it is good to begin with the recommended minimum specs for the database starting with the below:
  • ECAT DB: 400GB @ 3000 IOPS* (+ 50Gb for Bit9 DB)##
  • Temp DB: 100GB @ 1500 IOPS*
  • DB Log: 100GB @ 1500 IOPS*
  • Optional System DBs: 30Gb
Below is a table indicating guidelines regarding different size deployments. Typically, an ECAT deployment will start with 12 cores and expand from there, depending on number of agents and overall database performance.
User-added image
These are not hard or fast rules. Requirements depend on the size of the environment (number of agents, database hardware, etc.) and vary over time; this is an example of a good rule of thumb for a typical setup. Below are suggestions to assist in tuning the database for better performance:
  1. Reduce load by reducing the frequency of scans. Tips include:
    1. Use machine groups to schedule scans and stagger them to avoid kicking off all scans at once. This can be done in Configure>Machine Groups> Edit Group with a selected group. Under the Schedule section check Use Start Randomization and select a percentage to stagger start times of scanning.
    2. Avoid using Full Scans except manually during investigations. Quick Scans provide the needed information along with Tracking data to track down module history.
    3. Scans should generally be no more frequent than weekly at most. Scans are very heavy on system resources. To verify where resources are being consumed, it is advised to check using a combination of the BPM query to examine performance and the Batches query which will provide a breakdown of where the database is spending the bulk of its time.
  2. Improving IOPS by reducing access to the DB disk from other applications/DBs.
  3. Do not add, remove, or modify indexes. If an index is desired, please open a support case to request they be added by engineering who will review the listed index.
  4. Adjust parallelism using the linked kb article 000034021.
  5. Use 64Kb block size(cluster size) for the partitions the database will be installed on for better SQL performance. Occasionally Windows will try to install other block sizes(such as 4k) for database partitions, so being aware of partition sizes and adjusting can improve performance.

 ## Starting with Versions 4.1.2.x  and above, BIT9 Database has been replacing by File Reputation Service
NotesSQL Server Best Practices
Below are the BPM query and Batches queries for reference