SQL Server Recommendations for CWSerenade

Purpose: MICROS recommends the following SQL Server settings for CWSerenade.

Recommendation	See:
Verify the SQL Server database compatibility level:
Change the CWSerenade database level so that it matches your installed SQL edition.	Verify the CWSerenade Database Compatibility Level
To address performance issues with SQL Server 2012:
If you experience performance issues related to high CPU usage (over 90%) with SQL Server 2012: • Enable the SQL Server trace flag 8032 to configure SQL Server to use additional memory for its internal processing and reduce overall CPU usage. • If you enable this flag, make sure that the SQL Server machine is not memory constrained and, if needed, add additional memory.
Follow these recommendations, regardless of the version of SQL Server you use:
Configure the database server so that data and log files are on a disk array separate from your operating system.	Disk Configuration
Disable any power saving features on the server that reduces processor frequency.	Power Saving Settings
Change the Degree of Parallelism (DOP) setting. Set this number to less than 1/4 of the total number of processor cores available.	Degree of Parallelism Setting Verify this change with your database administrator or IT staff prior to changing this setting.
Create multiple files for the temporary (tempdb) database. For maximum efficiency, you should create one file for each CPU core. Example: If you have a 2-way quad core machine, you should create 8 files for the temporary database. By default, the system creates 1 file for the temporary database, so in this example, you would need to create 7 additional files. On newer systems that have a greater core count, such as 32 or more, you can create one file per 2 cores.	Configure Temporary Database Files SQL Server uses the temporary database (tempdb) for internal processing. Creating multiple files for the temporary database alleviates any waits and locks on the database.
Adjust the total amount of memory that SQL Server should use to make sure the operating system has enough memory available to it. Typically, this is 4G less than the total available memory. On SQL 2008 R2 or newer, this is not as critical as it once was; this can be set at the preference of your SQL administrator.	Configure Memory Settings in SQL Server
Make sure your system is up-to-date on SQL Server and Windows patches.
The connection information used to connect to the CWSerenade database is: 1.db.url = jdbc:sqlserver://SERVERNAME:1433; DatabaseName=CWSerenade;selectMethod=direct;sendStringParametersAsUnicode=false where: • SERVERNAME = The name or IP address of the database server. • 1433 = The port used to connect to the database server. • CWSerenade = The name of the CWSerenade database.	You define connection information in the DBConfig Properties File and tomee.xml File

Disk Configuration

MICROS recommends that you configure the database server so that data files are on a disk array separate from your operating system.

If you are using three disk arrays:

• Disk Array 1: Operating System and Temporary Database

• Disk Array 2: Data Files

• Disk Array 3: Log Files

If you are using four disk arrays:

• Disk Array 1: Operating System

• Disk Array 2: Data Files

• Disk Array 3: Log Files

• Disk Array 4: Temporary Database

Disk arrays should all have battery backup write caching enabled.

Power Saving Settings

Many servers come with a power saving setting that tries to conserve power by adjusting the speed of the processors. This can cause problems with SQL Server performance. MICROS recommends that you disable any power saving features on the server that reduces processor frequency. If your server has a power saver option, it is usually located in the system BIOS and can be set to full performance mode. Refer to your specific hardware specifications for more details.

Setting the CWSerenade Database Compatibility Level to Match the Installed SQL Version

Use the following steps to change the CWSerenade database compatibility level.

To check your current database level:

1. On the database server that contains the CWSerenade database, open SQL Server Management Studio.

2. In the left-hand pane, advance to the CWSerenade database. Right-click on the database name and select Properties to advance to the Database Properties window.

3. On the Database Properties window, select Options in the left-hand pane and verify the setting of the Compatibility level field.

Before you begin: Before you begin, you must stop the SERENADE service and shut down all CWSerenade application servers.

To change the database level: Use the following steps to update the Compatibility level field for the CWSerenade database to the same level as the installed SQL Version (usually the highest available).

1. On the database server that contains the CWSerenade database, open SQL Server Management Studio.

2. Open a Query window and execute the following query, where CWSerenade is the name of the CWSerenade database and 90 is the SQL version (90 for SQL 2005, 100 for SQL 2008, 110 for SQL 2012): EXEC sp_dbcmptlevel [CWSerenade], 90;

3. Once the query completes successfully, restart the SERENADE service and all CWSerenade application servers.

Degree of Parallelism Setting

The Degree of Parallelism setting controls how SQL Server distributes heavy queries over CPUs. If this value is set incorrectly, SQL Server will spend too much time trying to break queries over the available CPUs, potentially causing a bottleneck.

For CWSerenade, our recommendation is to set the Degree of Parallelism (DOP) from the default of 0 to 1/4 or less of the total available cores. Check with your database administrator before making this configuration change.

Instructions: To review and update the Degree of parallelism setting:

1.	Select Start > Programs > Microsoft SQL Server > SQL Server Management Studio to open SQL Server Management Studio.
2.	Right-click on the server name and select Properties.
3.	At the Server Properties window, select Advanced in the left-hand pane to display the advanced settings for the server in the right-hand pane.
4.	In the right-hand pane, the Max Degree of Parallelism setting is the second to last entry. Change this setting and select OK to apply the change.

Configure Temporary Database Files

SQL Server uses the temporary database (tempdb) for internal processing. To alleviate waits and locks on the temporary database, you should create multiple files for this database. For maximum efficiency, you should create one file for each CPU core. For example, if you have a 2-way quad core machine, you should create 8 files for the temporary database. By default, the system creates 1 file for the temporary database, so in the example above, you would need to create 7 additional files. On newer systems that have a greater core count, such as 32 or more, you can create one file per 2 cores.

Instructions: To configure temporary database files:

1.	Select Start > Programs > Microsoft SQL Server > SQL Server Management Studio to open SQL Server Management Studio.
2.	In the left-hand pane, expand the server folder to advance to Databases > System Databases > tempdb.
3.	Right-click on tempdb and select Properties to advance to the Database Properties window. In the left-hand pane, select Files to display the database files in the right-hand pane.
4.	Select Add to add a new database file. The system adds a new row to the database files:
5.	In the new row, enter a logical file name for the new database file. Select Add again to add additional database files until you have created amount based on total processor cores. Note: All new files should be the same size as the first primary file and have the same Autogrowth settings.
6.	Select OK to add the files to the system. Note: You will need to restart the server before your changes take effect.

Configure Memory Settings in SQL Server

You should adjust the total amount of memory that SQL Server should use to make sure the operating system has enough memory available to it. Typically, this is 4G less than the total available memory. On SQL 2008 R2 or newer, this is not as critical as it once was; this can be set at the preference of your SQL administrator.

Instructions: To configure memory settings:

1.	Select Start > Programs > Microsoft SQL Server > SQL Server Management Studio to open SQL Server Management Studio.
2.	Right-click on the server name and select Properties.
3.	At the Server Properties window, select Memory in the left-hand pane to display memory settings in the right-hand pane.
4.	In the right-hand pane, set the Maximum server memory to 4GB less than the total available on the machine. Select OK to save the change.