SQL Server Configurations and PeopleSoft Application Recommendation


Hyper-threading is Intel’s implementation of simultaneous multithreading technology on the Pentium 4 micro-architecture. The performance benefits of using hyper-threading are dependent upon workload. For PeopleSoft applications, it is recommended that you disable hyper-threading for the database server via the BIOS.

Memory Tuning
Memory tuning can be critical to the performance of your PeopleSoft application. This section discusses memory tuning on both 32-bit and 64-bit systems and the various options available for each.

32-Bit Memory Tuning

/3GB Switch

By default, all 32-bit operating systems can linearly address only up to 4 GB of virtual memory. Out of this 4GB, a single application can directly address a maximum of 2 GB of memory. The 32-bit versions of Microsoft Windows Server™ 2003 and Windows 2000 permit applications to access a 3 GB flat virtual address space, when the /3GB switch is specified in the boot. ini file. The  /3GB  switch allows SQL Server to use 3 GB of virtual address space. This switch is relevant for 32-bit operating systems only.

Note. When the physical RAM in the system exceeds 16 GB and the  /3GB  switch is used, the operating system will ignore the additional RAM until the  /3GB  switch is removed. This is because of the increased size of the kernel required to support more page table entries. The assumption is made that the administrator would rather not lose the  /3GB  functionality silently and automatically; therefore, the administrator must explicitly change this setting.

Physical Address Extension (/PAE)
PAE is an Intel®-provided memory address extension that enables support of up to 64 GB of physical memory for applications running on most 32-bit (IA-32) Intel Pentium Pro and later platforms. Support for PAE is provided on Windows 2000, and later versions of the Advanced Server and Datacenter Server operating systems.

PAE enables most processors to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits through support in the host operating system for applications using the Address Windowing Extensions (AWE) API. PAE is enabled by specifying the /PAE switch in the boot.ini file.

AWE Memory
SQL Server 2005 can use as much memory as Windows Server allows. To use AWE memory, you must run the SQL Server 2005 Database Engine under a Windows account on which the Windows policy Lock Pages in Memory option has been enabled.

SQL Server Setup will automatically grant the SQL Server (MSSQLServer) service account permission to use the Lock Pages in Memory option. To enable the use of AWE memory by an instance of SQL Server 2005, use the sp_configure option awe enabled.

PeopleSoft applications consume relatively large amounts of memory, so many deployments will benefit from using a combination of /3GB and AWE memory.

The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be dynamically mapped to and unmapped from SQL Server’s virtual address space and take full advantage of memory allocated through AWE. AWE does not directly help supporting additional users, threads, databases, queries, and other objects that permanently reside in the virtual address space.

It is recommended to set the max server memory option when using AWE memory. Instances of SQL Server 2005 that are running on Microsoft Windows 2000 use static AWE memory allocation, and instances that are running on Microsoft Windows Server 2003 use dynamic AWE memory allocation. Under static allocation, instances of SQL Server 2005 that run on Windows 2000 lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. Under dynamic memory allocation on SQL Server 2005 and Windows Server 2003, memory is locked only as needed.

To set memory options:

1. If your installation of Windows Server 2003 or Windows 2000 has more than 4 GB of memory but less than 16 GB of memory, add the /3GB switch to boot.ini.
2. To enable Physical Address Extension, add the /PAE switch to boot.ini.
3. Use sp_configure to enable AWE with sp_configure ‘awe enabled’, 1.
4. Enable the configuration using RECONFIGURE WITH OVERRIDE.
5. Set the maximum amount of memory SQL Server can use with sp_configure ‘max server memory’.
6. Enable the configuration using RECONFIGURE WITH OVERRIDE.
7. Shut down and restart the server.

Note. Some services such as antivirus software have caused instability when used on systems that have /3GB enabled, and servers are constrained to no more than 16 GB if both /3GB and /PAE are enabled.

64-Bit Memory Tuning
For 64-bit servers running 64-bit Windows operating systems and 64-bit editions of SQL Server 2005, special memory settings such as AWE, /3GB, or /PAE are not required.

Depending on the server and the Windows operating system used, memory in the range of terabytes can be addressed directly.

SQL Server 2005 64-bit editions can take full advantage of the large memory address space. The buffer pool and all other memory structures of SQL Server can fully utilize the memory, thus eliminating the 4 GB virtual space limit imposed by 32-bit systems. The 64-bit systems bring linear memory addressability to SQL Server, implying that no internal memory mapping is needed for large memory access.

For large PeopleSoft applications with high user concurrency in the range of thousands of users and a large database size, 64-bit systems can provide scalability and high performance. Such complex and highly concurrent PeopleSoft applications typically make heavy use of memory and can benefit from 64-bit systems in the following areas:

• Plan cache – The ad hoc and dynamic SQL from PeopleSoft applications can fully utilize the large memory space. The plan generated can stay in memory longer thus promoting more reuse and fewer compilations.
• Workspace memory – Index builds and complex concurrent hash joins can be done in memory.
• Connection memory – Large numbers of concurrent connections can be easily handled.
• Thread memory – High concurrency load can be easily handled.
• Lock memory – Concurrent PeopleSoft workloads can utilize large amounts of lock memory.

For PeopleSoft applications with large scalability and memory requirements the 64-bit platform is highly recommended.

For existing applications on 32-bit platforms with no large scalability requirements or memory requirements, it may not be beneficial to migrate to a 64-bit platform. If the 32-bit platform is under memory pressure and memory is proving to be a bottleneck, migration to a 64-bit platform may help. Any 32-bit workloads which are processor bound may not benefit from migrating to 64-bit platforms.

Lock Pages in Memory
In 32- and 64-bit computing environments, assign the SQL Server 2005 service account the Windows policy Lock Pages in Memory option.

Sometimes, enabling PAE, AWE, and /3GB fails to acquire memory greater than 4 GB. The most common reason for this is not enabling the  Lock Pages in Memory  option. For 32-bit operating systems,  Lock Pages in Memory  permission must be granted before AWE is configured for SQL Server.

This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. The Lock Pages in Memory  option is set to OFF by default in SQL Server 2005. If you
have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.

To enable Lock Pages in Memory:
1. On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
Although it is not required, it is recommended that you set the Lock Pages in Memory option when using 64-bit operating systems. This keeps data in physical memory, preventing the system from paging the data to virtual memory on disk.

Important sp_configure Parameters
The following table presents some of the important sp_configure parameters along with their recommended values.

affinity mask
Limits SQL Server execution to only a certain set of processors defined by the bit mask. It is useful for reserving processors for other applications running on the database server.

The default value is 0 – execute on all processors.

There is no need to alter this setting if your server is a dedicated database server.

lightweight pooling
Controls fiber mode scheduling. It primarily helps large multiprocessor servers that are experiencing a high volume of context switching and high processor utilization.

The default value is OFF.

For PeopleSoft applications, set this option to OFF (default value).

priority boost
Boosts the priority at which SQL Server runs.
For PeopleSoft applications, set this option to OFF (default value).

max degree of parallelism
Limits the number of processors considered for parallel plan execution to a specified value.

The default value is 0 – all processors. This default setting may help some complex SQL statements, but it can take away CPU cycles from other users during high online usage periods.

Set this parameter to 1 during peak OLTP periods. Increase the value of this parameter during periods of low OLTP and high batch processing, reporting, and query activity.

Note. Index creation and re-creation can take advantage of parallelism, so it is advisable to enable parallelism through this setting when planning to build or rebuild indexes. The OPTION hint in the index creation or rebuild statements can also be used to set max degree of parallelism.

Performance tests on some of the batch processes showed that parallelism could result in very good performance. If you do not want to toggle this value based on the type of load, you can set the value to 1 to disable this setting. However, you may want to explore some middle ground by setting this option to 2, which may help some complex batch jobs as well as online performance.

cost threshold for parallelism
Specifies the cost threshold in seconds that needs to be met before a query is eligible to be executed with a parallel query execution plan.
The default value is 5.

Most of the PeopleSoft online SQL statements are simple in nature and do not require parallel query execution plans. Consider increasing the value to 60, so only true complex queries will be evaluated for parallel query execution plans.

awe enabled
Enable this parameter to take advantage of memory above 4 GB. This is applicable only for 32-bit operating systems.

max server memory
Specifies the maximum memory in megabytes allocated to a SQL Server instance.

The default value is 2,147,483,647 MB.

Observe standard best practices for this setting. If you are enabling AWE, remember that AWE memory is statically allocated for Windows 2000 and non-pageable. AWE memory is dynamically allocated for Windows Server 2003. For a dedicated database server, plan to leave at least 1 GB for the operating system and other ancillary services on the database server. For example, if the database server has 16 GB, set max server memory to 15 GB. Monitor the memory: available bytes to determine if max server memory should be reduced or increased.