This article explains a simple tuning process involving SQL Server settings that can be used to optimize and troubleshoot SQL Server performance.
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:
If the initial size of the log file is less than the actual file size, then do one of the following:
Set the Autogrowth properties as follows:
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:
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.