SQL Best practices to enhance the performance of CCS

Products

• Control Compliance Suite
• Control Compliance Suite Standards Server

Issue/Introduction

Control Compliance Suite (CCS)

SQL Best practices to enhance the performance of CCS

Environment

CCS 12.5.1 and CCS 12.6.1 databases

Resolution

This article explains a simple tuning process involving SQL Server settings that can be used to optimize and troubleshoot SQL Server performance.

Recommendation

The following best practices are for enhancing the performance tuning of SQL Server:

1. Verify the database auto-growth options.

To do that, go to the Database Properties dialog box. In the Database files table, do the following:

 

If the initial size of the data file is less than the actual file size, then do one of the following:

• Set the file size to the actual file size at that time.
• Set the default size as 1 GB.

If the initial size of the log file is less than the actual file size, then do one of the following:

• Set the file size to the actual file size at that time.
• Set the default as 100 MB.

Set the Autogrowth properties as follows:

• File Growth field:  data to 500 MB, log to 10% or 100 MB.
• Maximum file size field: unrestricted growth wherever applicable.

Ensure that all data files belong to the PRIMARY filegroup.

In the Options page, verify and set the Recovery model field to Simple.

Note: You must perform the specified steps for the CCS databases that are Tempdb, CSM_DB, and CSM_Reports.

2. Run the Index physical statistics report for CSM_DB and CSM_Reports and do the following:

• If the fragmentation exceeds 50%, then rebuild the indexes.
• If it is recommended to rebuild or reorganize most of the indexes, then create a maintenance plan and run the task.

3. Ensure that the SQL Server is configured to use 70-80% of the maximum available memory.

For example, if you have installed SQL Server on a computer that has 16 GB of physical memory, then set the maximum available memory for SQL between 11-12 GB

NOTE: If CCS Application Server and SQL Server are on the same machine, then the maximum available memory for SQL should be less (i.e. 40-50%).

Note: To check for memory usage, in the SQL Server Properties dialog box, go to the Memory tab and enable AWE. You can get more information on the awe enabled option at the Web site http://msdn.microsoft.com/en-us/library/ms190731.aspx.