SQL Best practices to enhance the performance of CCS

Article:HOWTO60732  |  Created: 2011-10-28  |  Updated: 2012-01-30  |  Article URL http://www.symantec.com/docs/HOWTO60732
Article Type
How To



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 file group.

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 maximum avaiable memory for SQL should be less (i.e. 40-50%).

Note: To check for the 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.

 



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


Terms of use for this information are found in Legal Notices