Hyper-Threading
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.
