How to improve performance of SQL Server for ICA

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

SQL Server and Analysis Services Additional Recommended Settings for Information Centric Analytics. To improve performance, you need to tweak SQL settings for the environment to work better with information Centric Analytics.

Resolution

Aside from the SQL server settings detailed in the Risk Fabric Administration Guide (page 10), there are some additional SQL and Analysis Services setting that may help improve the performance of ICA.  Below are recommended configuration modifications:

SQL Server Settings:

Remote Server Connections

1. Open SQL Management Studio
2. Connect to the ICA database server using SQL Management Studio
3. Right-click on the SQL server on SQL Management Studio and select Properties
4. Select the Connections page
   
5. Check Allow remote connections to this server and set the Remote query timeout value to 0 (no timeout)
   
6. Click OK to save changes

Server Memory Options

The minimum and maximum server memory is used to configure the amount of memory (in megabytes) to establish upper and lower limits of memory used by the buffer pool on the Microsoft SQL Server. The SQL Server engine starts with only the memory required to initialize. As the workload increases, it acquires additional memory to support the workload but never acquires more than the level specified by the maximum server memory setting. The default setting for mininum server memory is 0 and the default setting for maximum server memory is 2147483647 MB (or unlimited).  A general rule to follow when configuring these settings is to leave the operating system 20% of total system memory. 

1. Open SQL Management Studio
2. Connect to the ICA database server using SQL Management Studio
3. Right-click on the SQL server on SQL Management Studio and select Properties
4. Select the Memory page
   
5. Enter the appropriate memory size under Maximum server memory (in MB)
   
6. Click OK to save changes