The following list of performance counters is considered a base set of counters when you monitor the resource usage on the RDS workload. Log the performance counters to a local, raw (blg) performance counter log. It is less expensive to collect all instances (‘*’ wide character) and then extract particular instances while post-processing by using Relog.exe:
. To view providers on the system, run logman.exe query -ets.
Use Logman.exe to collect performance counter log data instead of using Perfmon.exe, which enables logging providers and increases CPU usage.
Performance Tuning for SAP Sales and Distribution Two-Tier Workload
SAP AG has developed several standard application benchmarks. The Sales and Distribution (SD) workload represents one of the important classes of workloads that are used for benchmarking SAP enterprise resource planning (ERP) installations. For more information on obtaining the benchmark kit, see the link to the SAP web page in “Resources” later in this guide.
SAP updated the SAP SD workload in January, 2009. The updates include added requirements such as subsecond response time and a Unicode codepage. For more information, see the link to the SAP web page in “Resources” later in this guide.
You can perform multidimensional tuning of the operating system level, application server, database server, network, and storage to achieve optimal throughput and good response times as the number of concurrent SD users increases before capping out because of resource limitations.
The following sections provide guidelines that can benefit the two-tier setup specifically for SAP ERP SD benchmarks on Windows Server 2008 R2. Some of these recommendations might not apply to the same degree for production systems.
Operating System Tunings on the Server
Navigate to Control Panel > System > Advanced System Settings > Advanced tab and configure the following:
Navigate to Performance Settings > Advanced > Virtual memory and set one or more fixed-size pagefiles (InitialSize equal to Maximum Size). The pagefile size should meet the total virtual memory requirements of the workload. Make sure that no system-managed pagefiles are in the Virtual memory on the Application Server.
Navigate to Performance Settings > Visual Effects and select the Adjust for best performance check box.
From the Group Policy MMC snap-in (Gpedit.msc), navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment. Double-click Lock pages in memory and add the accounts that have credentials to run Sqlservr.exe and SAP services.
Disable User Account Control.
Navigate to Start > All Programs > Administrative Tools > System Configuration > Tools tab, select Disable UAC, and then reboot the system. This setting can be used for benchmarking environments, but enabling UAC might be a security compliance requirement in production environments.
When the database server is SQL Server®, consider setting the following SQL Server configuration options with sp_configure. For detailed information on the sp_configurestored procedure, see the information about setting server configuration options in "Resources" later in this guide.
Apply CPU affinity for the SQL Server process: Set an affinity mask to partition the SQL process on specific cores. If required, use the affinity64 mask to set the affinity on more than 32 cores. Starting with SQL Server 2008 R2, you can apply equivalent settings for configuring CPU affinity on as many as 256 logical processors by using the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY Data Definition Language (DDL) TSQL statement as the sp_configure affinity mask options are announced for deprecation. For more information on DDL, see “Resources” later in this guide. For the current two-tier SAP SD benchmarks, it is typically sufficient to run SQL Server on one-eighth or fewer of the existing cores.
Set a fixed amount of memory that the SQL Server process will use. For example, set the max server memory and min server memory equal and large enough to satisfy the workload (2500 MB is a good starting value).
On NUMA-class hardware, you can do the following:
To further subdivide the CPUs in a hardware NUMA node to more CPU nodes (known as Soft-NUMA), see the information about configuring SQL Server to use Soft-NUMA in "Resources" later in this guide.
To provide NUMA node locality for SQL Server, set preferred NUMA node hints (applies to Windows Server 2008 R2 and later). For the commands below, use the service name. The [server] parameter is optional, the other parameters are required:
Use the following command to set the preferred NUMA node:
%windir%\system32\sc.exe [server] preferrednode
You need administrator permissions to set the preferred node. Use %windir%\system32\sc.exe preferrednode to display help text.
Use the following command to query the setting:
%windir%\system32\sc.exe [server] qpreferrednode This command fails if the service has no preferred node settings. Use %windir%\system32\sc.exe qpreferrednode to display help text.
Use the following command to remove the setting:
%windir%\system32\sc.exe [server] preferrednode -1
On a two-tier ERP SAP setup, consider enabling and using only the Named Pipes protocol and disabling the rest of the available protocols from the SQL Server Configuration Manager for the local SQL connections.