The
following database options may have performance implications on the PeopleSoft
application. The database options are discussed below with the recommended
setting for optimal performance for PeopleSoft applications.
Read
Committed Snapshot Isolation
Read-committed
snapshot isolation level is a new isolation level introduced in SQL Server
2005. The performance of a typical PeopleSoft workload can benefit from this
isolation level. Under this isolation level, blocking and deadlocking issues
due to lock contention are greatly reduced. Read operations only acquire an
Sch-s (schema stability) lock at the table. No page or row S (shared) locks are
acquired and therefore do not block transactions that are modifying data. Every
time a row is modified by a specific transaction, the instance of the Database
Engine stores a version of the previously committed image of the row in tempdb.
The
read-committed snapshot isolation level provides the following benefits:
• Read
operations retrieve a consistent snapshot of the database.
• SELECT
statements do not lock data during a read operation (readers do not block
writers, and vice versa). Blocking is significantly reduced.
• SELECT
statements can access the last committed value of the row, while other
transactions are updating the row without getting blocked.
• The
number of blocks and deadlocks is reduced.
• The
number of locks required by a transaction is reduced, which reduces the system
overhead required to manage locks.
• Fewer
lock escalations take place.
The
read-committed snapshot isolation level is not the default isolation level. It has
to be explicitly enabled with the Enable_rcsi.sql script included with
PeopleTools 8.48.
Use the
following query to identify whether a database is currently set to use the
read- committed snapshot isolation level:
select
name, is_read_committed_snapshot_on from sys.databases
where name
= <YourDatabaseName>
A value of
1 in the is_read_committed_snapshot_on column indicates that the read-
committed snapshot isolation level is set.
For
PeopleSoft applications, the recommendation is to enable the read-committed
snapshot isolation level. PeopleSoft workloads typically have concurrent online
and batch processing activities. There are possible blocking and deadlocking
issues due to lock contention from the online and batch activity. This usually
manifests as performance degradation issues due to lock contention. The
read-committed snapshot isolation level will alleviate most of the lock
contention and blocking issues.
Warning!
Ensure that the
version of PeopleTools you are using supports the read-committed snapshot
isolation level. You can only use it if it is supported by PeopleTools.
Asynchronous
Statistics Update
The
asynchronous statistics update is a new database level option introduced in SQL
Server 2005. When this option is enabled (set to ON), queries that trigger
out-of-date statistics update execute without being blocked for the statistics
update to complete.
By default,
the AUTO_UPDATE_STATISTICS_ASYNC option is OFF.
For a
typical PeopleSoft workload, which has a good mix of transactional and batch
activities, it is recommended that you use the default setting of OFF for this
option. A PeopleSoft workload may have a large batch process run on the
database. The batch process can potentially cause significant changes to the
data distribution through updates and inserts.
Running a
SELECT/UPDATE transactional query through the PeopleSoft online screen
immediately following the batch process on the same data set may initiate the
out-of-date statistics update. With this option set to OFF (default setting),
the query will be on hold until the statistics are updated. However, this
assures the latest statistical information for the query optimizer to use to
create the execution plan. This mechanism may provide better performance for
most scenarios, with the tradeoff of waiting for the statistics update.
Because the
option is OFF by default, no action is required. To check for the current
setting, use the following command:
select
name, is_auto_update_stats_async_on from sys.databases
where name
= <YourDatabaseName>
A value of
0 indicates AUTO_UPDATE_STATISTICS_ASYNC is OFF, the desired setting for
PeopleSoft applications.
Parameterization
This SQL
Server 2005 database option, when set to FORCED, parameterizes any literal
value that appears in a SELECT, INSERT, UPDATE, or DELETE statement submitted
in any form. The exception is when a query hint of RECOMPILE or OPTIMIZE FOR is
used in the query.
Use the
following ALTER DATABASE statement to enable forced parameterization:
ALTER
DATABASE <YourDatabaseName>
SET
PARAMETERIZATION FORCED ;
To
determine the current setting of this option, examine the is_parameterization_forced
column in the sys.databases catalog view as follows:
select
name, is_parameterization_forced from sys.databases
where name
= <YourDatabaseName>
A value of
1 for is_parameterization_forced indicates that forced parameterization
is set.
For a
PeopleSoft workload, it is recommended that you set this parameter to 1
(forced). Some PeopleSoft application queries pass in literals instead of
parameters. For such workloads you may want to experiment with enabling the
forced parameterization option and seeing if it has a positive effect on the
workload by way of a reduced number of query compilations and reduced processor
utilization. An example query from the PeopleSoft Financials online workload
follows:
Note: Test Twice after setting this
option.
SELECT 'x'
FROM PS_DEPT_TBL
WHERE SETID
= 'SETID'
AND DEPTID
= '1001'
In this
example, the literal value 1001 is passed to the query. When the forced
parameterization option is enabled, the hard-coded literal is automatically
substituted with a parameter during the query compilation. The query plan would
be cached and reused when this query is submitted again, with a different
literal value for CUST_ID. Because the plan could be reused, the compilation
overhead is eliminated, thereby reducing processing requirements.
However,
note that in some cases forced parameterization may cause a suboptimal plan to
be reused, thus degrading performance. If the parameter value in the query
changes significantly, when it warrants a different execution plan for better
performance, the older plan would be reused from the cache, which may not be
the most optimal from a performance perspective. It may also choose suboptimal
plans for queries posed on partitioned tables.
Auto
Update Statistics
When this
option is set, any missing or out-of-date statistics required by a query for
optimization are automatically built during query optimization. For optimal
performance in PeopleSoft applications, it is recommended that you leave the
auto update statistics option enabled.
Use the
following ALTER DATABASE statement to set this option:
ALTER
DATABASE <YourDatabaseName>
SET
AUTO_UPDATE_STATISTICS ON;
To
determine the current setting of this option, examine the is_auto_update_stats_on
column in the sys.databases catalog view as follows:
select
name, is_auto_update_stats_on from sys.databases
where name
=
A value of
1 for is_auto_update_stats_on indicates that auto update statistics is
enabled.
Auto
Create Statistics
This
database option automatically creates missing statistics on columns used in
query predicates. For optimal performance in PeopleSoft applications, it is
recommended that you leave the auto create statistics option enabled.
Use the
following ALTER DATABASE statement to set this option:
ALTER
DATABASE <YourDatabaseName>
SET
AUTO_CREATE_STATISTICS ON;
To
determine the current setting of this option, examine the is_auto_create_stats_on
column in the sys.databases catalog view as follows:
select
name, is_auto_create_stats_on from sys.databases
where name
=
A value of
1 for is_auto_create_stats_on indicates that the auto create statistics
option is enabled.
