My Basic Knowledge

UID and GID on Linux/ Unix

2012-10-25T14:35:00.004-04:00

The user ID (or UID) is a unique number that differentiates a user from any other user. The group ID (GID) determines the primary category of user that the user is. Groups may be things like ‘staff’, ‘student’, ‘engineering’, ‘research’, or any other descriptive term that can be used to partition users in a meaningful way.

/etc/passwd

One of the most critical of all files in Linux is /etc/passwd. All user accounts are stored here, with identifying information like so:

username:password:userID:groupID:realname:homedirectory:shell

For example, a user “mybasicknowledge” who was added as user 300 and group 200 has an entry that might look like this:

mybasicknowledge:x:300:200:Fred Smith:/home/fred:/bin/bash

To find a specific user’s UID, goto the terminal and type the following (replace the username with the actual username) -

id -u username

To find a user’s GID, at the Unix prompt -

id -g username

If you wish to find out all the groups a user belongs to, type in -

id -G username

If you wish to see the UID and all groups associated with a user, enter id without any options -

id username

Syntax

id [-a] [-G] [-g] [-u] [user]

-a Reports user name, use ID and all the groups to which the user belongs.

-G Output all different group IDs (effective, real and supplementary) only, using the format "%u\n". If there is more than one distinct group affiliation, output each such affiliation, using the format " %u", before the new line character is output.

-g Output only the effective group ID, using the format "%u\n".

-n Output the name in the format "%s" instead of the numeric ID using the format "%u".

-r Output the real ID instead of the effective ID.

-u Output only the effective user ID, using the format "%u\n".

user The user (login) name for which information is to be written.

PeopleSoft Application Search

2012-10-03T15:17:00.004-04:00

PeopleSoft Application Search is a group of features that offers an improved means of searching for application content, business objects and business process steps within a PeopleSoft application.

These capabilities provide users with access to more powerful, keyword-based search components that present a familiar, more intuitive search experience.

Note. PeopleSoft Application Search features depend on the Search Framework being enabled and implemented for your system. Implementing this feature for your PeopleSoft application may require the uptake of PeopleSoft Feature Packs.

Similar to how users might initiate navigation across the internet by first searching for a set of keywords, PeopleSoft Application Search allows application users to initiate steps of a business task with a simple, intuitive keyword search. This capability may reduce or even eliminate the need for users to navigate PeopleSoft applications via the existing application menu system.

• The new search experience functions similarly to the internet, providing a look and feel that users will find both familiar and intuitive.

• Application Search is available at any point in an application and can be configured to span multiple PeopleSoft applications, enabling users to initiate transactions or navigate to key information without using the PeopleSoft application menu or returning to the home page.

• This search framework includes the ability to use filters and facets to narrow result sets, further enabling users to identify and navigate to specific application content.

• Search results are displayed in prioritized order by relevance to the provided search terms.

• Related Action menus are embedded directly in the search results, allowing users to navigate straight to specific related transactions – pre-populated with the selected search results data.

• Component Search is also enhanced with keyword capabilities, extending the versatility of virtually all existing search pages.

This new means of searching provides a global search mechanism that is available to users across PeopleSoft applications. It enables more efficient searching for users and fundamentally improves navigation in PeopleSoft applications. Instead of browsing through menu hierarchies to find an
application data or transaction, users will be able to use PeopleSoft Application Search to directly navigate to desired application data or transactions from search results.

The new search results page also enables continued refinement of results using facets, which enable users to locate and navigate to desired content easily. Users can drill into search result entries and even navigate to related information from search results. Persistent search result sets enable users to
easily return to important locations that they frequently visit.

The component search mechanism used in previous releases (now called classic search) is still available, but is enhanced with the availability of PeopleSoft Application Search’s keyword capabilities.

Read PeopleSoft Search Framework
http://www.mybasicknowledge.blogspot.com/2012/10/peoplesoft-search-framework.html

PeopleSoft Search Framework

2012-10-03T15:15:00.002-04:00

The PeopleSoft Search Framework provides a standard, declarative method for creating, deploying, and maintaining search indexes for all of your PeopleSoft applications. Oracle Secure Enterprise Search (SES) is the search engine on which the PeopleSoft Search Framework relies.

Note. The PeopleSoft Search Framework does not support the Verity search engine. It can only be configured to run with SES.

The PeopleSoft Search Framework is comprised of numerous PeopleTools technologies working together to provide design, administrative, and integration capabilities, including:

• PeopleSoft Query. You use PeopleSoft Query and Connected Query to define the data that will eventually populate the search index. You can use existing queries or create new queries for the PeopleSoft Search Framework, using this familiar interface.

• Search Framework Designer. You use the Search Framework Designer to create search definitions, which are based on a single PeopleSoft Query or Connected Query definition. The search definition enables you to apply various PeopleSoft metadata to the query, such as security attributes, mapping search result display fields to query fields, and creating search categories for related search definitions.

• PeopleSoft Integration Broker. You use Integration Broker to facilitate the integration between your PeopleSoft system and SES. Integration Broker must be configured correctly for the PeopleSoft system to connect to SES and for SES to access the PeopleSoft system for crawling.

• Search Framework Administration. You use the Search Framework Administration interface to define the connection to a particular SES instance, deploy search definitions and search categories, and manage search index build schedules. When deployed to SES, PeopleSoft search definitions and search categories become “sources” and “source categories”, respectively, on the SES system.

• PeopleSoft Application Engine. An Application Engine program set to run on a recurring basis extracts the results of the query defined in PeopleSoft Query. The results of that query populate the search index on SES.

• Feed Publishing Framework. After the initial search index is built, the Search Framework uses the Feed Publishing Framework to transmit incremental updates for the existing search index, rather than rebuilding an entire index to capture recent changes.

Read PeopleSoft Application Search
http://www.mybasicknowledge.blogspot.com/2012/10/peoplesoft-application-search.html

Windows Movie Maker cannot start

2012-10-02T20:17:00.003-04:00

“Windows movie maker cannot start because your video card does not support the requirements level of hardware?”

This either means your graphics card(if you have one) does not suffice for Windows Movie Maker's requirements (maybe it’s too old), or your graphics card driver is not the latest (sometimes updating to the latest may help).

Simply by going to properties on the graphics card in Device Manager and updating the driver.

Application Engine Concepts

2012-10-02T09:45:00.003-04:00

Different types of variables in the Application Engine people code

Local variable – these are available for the duration of the program in which they are declared.

Global / Component – These variables are available while the Application Engine program is running. They are saved at commits and checkpoints, So they can be used for restarts. Component variables are same as Global incase of the AE.

Different ways pass data between the steps

1) State records – One row can be passed and can have many state records.

2) Component/Global People code variables

3) Temporary records – Multiple rows of data can be passed.

Prerequisite for the State record

1) Should be either Derived work record Or SQL Table.

2) Name of the record must end in AET; this is how the system identifies the record as a state record.

3) PROCESS_INSTANCE must be a key.

4) Any data types except character or numeric must require not be fields.

Note: - No People code fires on an Application Engine state record.

No Validation of translate values or Prompt tables are done.

Read the runctrl parameters in AE

1) SQL: - %Select (EMPLID) Select EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE = %PROCESS_INSTANCE and ORPID = %oprid

2) People code: - &SQL = “Select EMPLID from PS_AERUNCONTROL Where PROCESS_INSTACNE =” | PS_TEST_AET.PROCESS_INSTANCE | “and OPRID =” | %oprid.

%PROCESS_INSTANCE or %Bind (PROCESS_INSTANCE) can be used.

%PROCESS_INSTANCE is more efficient and faster

Execute Application Engine through Push Button

1) CreateProcessRequest () , Schedule ()

2) CallAppEngine.

CallAppEngine Syntax

CallAppEngine (applid [, state record]);

3 trace parameters you can pass to your psae.exe?

- TRACE

- TOOLSTRACESQL

- TOOLSTRACEPC

Best Trace option

- TRACE 384 – mostly used trace value.

Difference between a Trace value and Trace parameters

A Trace parameter determines which type of trace is turned on.

A Trace value determines what type of data is recorded in your trace files (s).

Where does the trace file is written to?

The trace file is written to the server directory when you’ve specified the trace on the client. If the client has %Temp% set to a drive or directory that does not exist on the server, PeopleSoft Application Engine does not generate a trace file.

For an Application Engine program running on a server, PeopleTools writes the generic PeopleTools trace for SQL and PeopleCode trace files to the same directories as the AET traces. The prefix of the trace file name is also the same, and the suffix is trace. On the Windows workstation, the trace is written to the “People Tools Trace File” specified in the Trace folder of PeopleSoft Configuration Manager.

3 common ways to pass a trace parameter and value to your program psae.exe

Configuration manager, Process Definition, Command prompt

Different types of Application Engine?

· Standard: Which is a Standard or normal entry-point program.

· Upgrade Only: Used by PeopleSoft Upgrade utilities only.

· Import Only: Used by PeopleSoft Import utilities only

· Daemon Only: Use for daemon type programs.

· Transform Only: Support for Extensible Stylesheet Language Transformations (XSLT).

PSDAEMON process

One PSDAEMON process can run for each row in the PS_SERVERDEFN table. The PS_SERVERDEFN.DAEMONENABLED field must be set to 1.

System automatically modifies all static references in other programs to the renamed program

The system automatically modifies all static references in other programs to the renamed program. For instance, if you call the renamed program from another Application Engine program, the Call Section action in the calling program is modified to reflect the new program name. All sections and steps are saved under the new name. Only one occurrence of a program name can exist for a given database.

PeopleSoft Application Engine recompiles a reused statement

%Clear Cursor function

Restart AE program

Program Level

· State Record

One of the state record needs to SQL Table, Since All Derived work record will be re-initializing on commit.

· Program Properties

On the Advanced tab in the program properties dialog box, make sure that disable restart is not checked.

· Configuration manager

In the configuration manager, sure that Disable restart is not selected on the process scheduler tab.

Section Level

· Section type

The option for section type are prepare only and critical updates

If the section is preparing data i,e select data, Populating temporary tables, or updating temporary tables then the section should be prepare only.

If the section is updating the permanent application tables in the database, you should select critical update.

Step Level

· Add an order by clause

%Select Field Select Field1 from PS_SOME_RECORD Where FIELD1 > %Bind (FIELD1) Order by FIELD1.

· Add a switch to the selected table

· Delete processed rows.

The only restriction for batch runs occurs when you have restart enabled, and you are inside a Do Select that is of the Select/Fetch type (instead of "Re-select" or "Restartable"). with select/Fetch, all commits inside the loop are ignored, including the commit frequency if it's set.

Why Sections are reordered alphabetically by name?

Sections are always reordered alphabetically by name at save time to make it easier to locate a given section. However, order of execution is dependent on internal call section references, and is therefore independent of the order that sections are inserted and displayed.

What does On Error property do?

The On Error property does not apply to compile errors (for example, specifying erroneous SQL statements). It checks only for execution-type errors. If your program has a syntax error, the program terminates.

What is to be intend to include multiple SQL statements within a single action?

Use the meta-SQL construct %EXECUTE. The previous sample SQL statement sample contains bind variables from a previous Application Engine action.

Entries in the AEREQUESTTBL table do not have any effect on Application Engine programs called from PeopleCode using the CallAppEngine function.

Tables Used in the Process Request Page

The Application Engine process request page inserts values into the following tables:

· AEREQUESTTBL.

Contains all of the values that appear on the page except those in the Parameters group.

· AEREQUESTPARM.

Includes only initial state record values specified in the Parameters group, if needed.

Invoke an Application Engine program through the command line

· Restarting.

When a program abends, a system administrator might restart the program using the command line. If needed, you can locate all of the specific program and process information from Process Monitor on the Process Request Detail dialog box. Normally, users (or system administrators) perform a restart from Process Monitor.

· Development or Testing.

Many developers include the command line in a batch file to launch a program they are developing or testing. This way, they can quickly execute the batch file as needed. This also enables separation of development of the application program from its associated pages.

· Debugging.

To debug a program running on the server, you can sign into the server (using telnet, for example) and invoke the program from the command line.

Bulk Inserts

Bulk Insert is also ignored when all three of the following conditions are true: the database platform is Oracle, the record contains and EFFDT field (effective date), and the record contains a mobile trigger. This is required because an Oracle database does not allow the reading of mutating tables in a row trigger.

Populate PS_MESSAGE_LOG instead of Log Message action

You can also use MessageBox PeopleCode to populate PS_MESSAGE_LOG instead of using the Log Message action. This enables you to easily record errors encountered within Application Engine PeopleCode programs.

Application Engine program is not set up for restart ability

When an Application Engine program is not set up for restart ability, then commits are not controlled, monitored, or restricted by PeopleSoft Application Engine. When Restart is disabled, commits are controlled by the program.

PeopleSoft Application Engine does not commit a step containing a Do Select action with the Select/Fetch option enabled until the entire step completes successfully, regardless of the other options you have selected.

Set Based Processing

Set Processing uses SQL to process groups, or sets, of rows at one time rather than processing each row individually. With row-by-row processing you following a repetitive loop that selects a row, determines if it meets a given criteria, if so, apply rule x to row Update row, commit. With set processing, you only select those rows that meet the filtering criteria and then run the rule once again all the affected rows.

Advantages of Set Processing

Improved Performance: Our internal testing has revealed that, in an overwhelming majority of cases, set processing performs significantly better than it is -by-row counterpart for “reasonable” batch processing volumes.

Minimized SQL Overhead: It tends to use fewer Application Engine SQL statements that each processed more data than the statements executed in row-by-row processing.

Easy Maintenance: - If need to make a fix or add an enhancement to SQL, it’s just a matter of modifying the SQL or inserting the new “Chunk”.

Leveraging the RDBMS: With Set – based processing, you take advantage of the SQL processing engine on the database rather than placing the processing burden and overhead on the application executable.

Testing and debugging application engine

Application Engine Trace file-You can track the step execution of your application execution.

Application Engine Interactive Debugger – First click on the trace tab and turn off the statement timings. Select Profile -> Edit Profile-> Process Scheduler Tab -> Application section Select Debug check box.

(You can dynamically modify the state record run application engine step by step

People Code Debugger for the Application Engine.)

AE Trace files: - AE__.AET

Without the process instance: - AE__.AET

Order and flow of action types

1) Do When

2) Do While

3) Do Select

4) Peoplecode

5) SQL

6) Call Section

7) Message Log

8) XSLT

9) Do until

How running AE program as Batch differs from running it online

Application Engine programs are designed for two types of execution and each has its own pool of Temporary Tables

Online:

· Invoked by CallAppEngine from People code

· Run quickly, synchronously, and at random times.

· Potential for simultaneous executions

· Uses the online Temporary Table pool.

· Not restart able.

· Psae.exe randomly assigns an instance number from the number range on your online temp tables.

· If the instance number is in use psae.exe puts the program in Queue until the assigned instance becomes free.

· Unlock on completion, on Crash free from Manage Abends.

Batch:

· Invoked through the Process Scheduler.

· Run for longer amounts of time, asynchronously, and at scheduled times.

· Can be designed for parallel execution for performance.

· Uses the Batch/Dedicated Temporary table.

· Restart able, If not Re-startable on Program completion.

· It allocates instance number based on the availability on a record by record basis and psae.exe begins with the lowest instance numbers. If the properties are set continue - Base table is used with Process instance as key.

· If Re-starable – Locked across Restarts until completes successfully.

Access property in the Section

Basically they have two options, public and private if section declared as public then it be access from other program. Private we cannot call from other program.

Which actions are mutually exclusive and why they are mutually exclusive?

Sql and call Section.

Error handling or Exception handling in Application Engine

Step properties

On Error

Abort – Write message to message log and terminate.

Ignore - Write message to message log and continue.

Suppress – No message will be written, but program will continue.

People code Action

On return options can used to handle run time errors.

Abort: - Exits immediately – Not recommended.

Break:- Exits the current step and section and control returns to the calling step.

Skip Step:- The program exits the current step, and continues processing at the next step in the section. If this is the last step in the section, the calling step resumes control of the processing.

SQL Action properties

No Rows:- When the Sql doesn’t return any rows, you can tell what application engine program should do.

Abort: - Program terminates

Section Break: - Application Engine exits the current section immediately, and control returns to the calling step.

Continue: - The program continues processing

Skip Step: - Application Engine exits the current step immediately and moves on to the next step. When using skip step keep the following in mind:

1) Application Engine ignores the commit for the current step at runtime

2) If the current step contains only one Action, only use skip step at by-pass the commit.

What are different types Do Select

1) Select/Fetch

2) Reselect

3) Restartable

· Select/Fetch

Opens the cursor only at the first time and retrieve rows one at loop.

Commits inside the step (commits in the Called Section) are ignored if AE is Restart enabled.

· Reselect

It opens the cursor and closes the cursor on each iteration of the loop.

It will reselect the same row of data.

Logic in Actions of the step should be such that it will be changing the status of the rows in the table the do select is selecting.

Commits are not ignored and will be committed in a reselect loop when the restart is enabled.

· Restartable

similar to select/Fetch but it WILL COMMIT inside the loop thus allowing the checkpoint to the PS_AERUNCONTROL table.

Difference between exit (0), exit (1) when we are using this functions in AE

Exit (1) causes immediate termination of a PeopleCode program. Use this parameter to rollback database changes.

Exit (0) caused immediate termination of a Peoplecode Program but don’t make rollback in the database.

What is the Mandatory property of App.engine?

Main-->Step-->Action
Is the Mandatory property of App.engine

· Main is the required section in Application Engine.

· There can be multiple steps in single application engine but at least one step should be part of AE.

· Similarly you can have multiple actions in AE but you should have minimum 1 action part of step.

· Save as AE

Temp Tables
They are used to store the immediate values during the processing of the AE.
They help in batch processing by creating multiple instances and there by facilitating parallel processing. If used properly they can greatly improve the performance.

State Records
They are used to pass on values from one section/step/prog to the other.
There can be a max of 200 state records that can be used in a single AE but only one of them can be default. state record name must end with _AET.

Differences between State and temporary records

1. State record at any point of time has only single line information as it's driven by the Process_Instance as key field. While temp table can have multiple rows.
2. State record had only single instance while the temp table can have maximum of 99 instance.
3. State record is used for restart logic.
4. State record can be used for dynamically calling AE section by adding AE_SECTION AE_APPLID as fields. Temp table cannot be used.

Why temporary records are needed?

Defines the record definition as a temporary table. Temporary images of the table can be created specified by the PeopleTools Options Page. Temporary tables are used for running Application Engine batch processes. Temporary tables can store specific data to update without risking your main application table.

Prerequisite for the Temporary record

1) Should have process Instance as key

2) Name should end with _TAO

Different types of Temporary tables

· Dedicated

· Shared

Parallel Processing

Potential performance improvements by splitting the data to be processed into groups and simultaneously running multiple instance of your program to deal with different groups of data.

If you have a one program that uses a temporary table and is invoked multiple times, that single temporary table could be used concurrently in multiple executions of the code. This could create unpredictable results since the different instances of the code would be issuing delete, Inserts and/or updates unsynchronized with each other.

You could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of your program would have to allocate an unused temporary table,mark it as ‘in use’ , use it and release it back to the pool when you through with it (for each Application Engine program you write).

What are the important steps for implementing the parallel processing

1. Define you Temporary Tables.

2. Set the Temporary Tables Online pool.

3. Assign Temporary Tables to your Application Engine program in it program.

4. Set Temporary Table Batch Pool – Instance count in the AE.

5. Build / Rebuild your Temporary Table record.

6. Code %Table Meta – SQL as reference to Temporary Tables in your Application Engine program, so that Application Engine can table references to the assigned Temporary Table instance dynamically at runtime.

What happens when all the instance of the temporary table are in use?

It behavior can control by AE developer. If the runtime options are set to continue “People Tools will insert rows into the base table using the PROCESS_INSTANCE as a key”. If temp table doesn't contain PROCESS_INSTANCE as a key field in a Temporary table, you should change the Temp table runtime options to “Abort” in the appropriate Application Engine programs.

Divide the data to be processed by different instance of the program to perform parallel program

Run control parameter passed to each instance of the AE program enable it to identify which input rows “belong” to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table.

Difference between Application Engine Do-while, Do-When and Do-select

In DoSelect we don't need to increase the counter. Pointer to the next row in the selected data will be moved automatically.

In DoWhen we need to write the select query to skip the processed row.

Do-while keeps running till the condition runs true.. And do-select runs
for each row and moves to the next row.

like if you have statement:
SELECT EMPLID from PS_EMPLOYEMENT and PS_ EMPLOYEMENT has 2000 rows then do-while keeps running forever and selecting the same row every time but do-select will run for 2000 times fetching the next EMPLID each time.

Difference in using SQL directly and calling SQL from PeopleCode in Application Engine

If you use SQL directly then it will go to database directly to fetch rows of data. But if u write SQL'S in PeopleCode then the trip will be from application server to database because PeopleCode resides in app server.

One should avoid writing SQL'S in App engine PeopleCode as it would degrade the performance.

Run an Application Engine program in different ways

1. Running from Application Designer.

2. By calling People Code function.

3. Running from DOS Environment (Debugging).

4. Running from Application Engine People Tool.

5. Running from PeopleSoft Application.

Why can’t the SQL and call Section Actions be present simultaneously in a single step?

Call section and SQL actions are mutually exclusive so they cannot be called at a time. There is chance to get into an infinite loop if they are appeared in the same step.

Test an application engine program

One can test Application in two ways:

1. Running Application engine from 2-Tier

2. Running Application engine from 3-Tier.

1. Running Application Engine from 2-Tier: Open Application Designer Then open desired Application engine. Then on top middle tool bar you can see 'Run Program button' click on to that button give vales to parameter like Process Instance Run Cntrl id etc and click. This will run application engine and generate a trace file to your local system. After completion of Application engine you can see if you got desired results or not.

2. Running Application engine from 3-Tier: Open 3-Tier link Go to peopleTools-> Process Schedular -> Process Add you Application engine process here in 'Process Defination Options' give 'PRCSMULTI' in COMPONENT and 'TLSALL' in Process Group and then Save the page.

After defining Application engine Go to peopleTools-> Process Schedular -> System Process Request then add a RUN CONTROL ID After adding run control id click on the RUN button on the page this will lead you to page where all the process will be listed Select you process name (Application engine Name) and clik on ok. This will run you Application engine.

Application Engine event in PeopleCode

Application engine PeopleCode event is On Execute.

Debug your Application Engine

Go to configuration manager of 2 tier (App Designer). Then navigate thru Profile > Edit > Process Scheduler > Application Engine check the Debug option.
2. Open the App Engine program needs to debug and click the traffic light run program button.
3. Enter the run control id & debug the AE.

Difference between AE and SQR

1. AE is very systematic as it is structured as Section step and Action. While SQR is not systematic.
2. AE flow is easy to read and understand while SQR is not that easy.
3. AE is easy to debug as you can do line by line debugging. With SQR it’s not easily possible.
4. By using SQR you can create and maintain different reports which are not possible in AE's.

Difference between sql statements and metasql statements

SQL statements:

The SQL statements are used to fetch/retrieve update insert delete data from the database. In PeopleSoft we use the sql stmts using sqlexec or createsql. Differnet sql statements are: Select Insert Update Delete.

Meta Sqls:

Different RDBMS have difference date and time formats. Though the component processor takes care of the automatic conversion but at the time of using the sqlexec the automatic conversion does not take place. So in order to understand this the meta sql concept has come. Which we can use in diff sql stmts. for example datetimein date time currentdatetime, datetimeout etc.

dateIn: This is used in the where clause of Select and update

dateout: This is used while selecting the data.

Inbound and outbound Process

Import data

by using a file layout requires the following sequence of activities:

1. Provide the import data in a properly formatted flat file.

Each record in the file must correspond to a record with the same name in the PeopleSoft database, and its fields must have the same names and data formats as the database record’s fields. Each record in the file must end with a new line character.

2. Create a file layout definition to match the record and field structure of the data.

Insert the appropriate record definitions into the file layout, then reposition the file records and file fields to match the record and field positions in the file.

3. Preview and troubleshoot the input data format and content.

4. Generate the data import Application Engine program and PeopleCode.

5. Run the Application Engine program to import the data.

Export Data

To export data to a flat file, you create a file layout definition, and then write PeopleCode to transfer the data to a file. The PeopleCode can be initiated from Application Engine or from any event. It should populate text strings, rowset objects, or record objects, and it should apply the WriteRecord or WriteRowset method from the File class to transfer the data to the file by using the file layout definition to position the records and fields as required by the target application.

http://www.mybasicknowledge.blogspot.com/2012/06/peoplesoft-application-engine-insight.html

Adding Custom Fields or Tables to PeopleSoft Template-Based Hire / Smart Hire Template

2012-10-02T08:16:00.002-04:00

If custom fields are added to the Personal Data or Job Data component, they can to be added to Template-Based Hire as well.

For example, let us suppose that the field MAIDENNAME (for English name format only) is added to the NAMES table, to the NAME_DFT_SEC secondary page in the PERSONAL_DATA component and to the CI_PERSONAL_DATA component interface.

The GetPersonName and SetPersonName services will need to be updated, as well as the supporting PersonNameType application class and the underlying HCR_PERSONAL_DATA_SRV component interface.

To add MAIDENNAME to Template-Based Hire, the following must be done:

1. Add MAIDENNAME to all instances of the NAMES record in the Template Record/Field Setup component.

2. Add MAIDENNAME to the delivered name section NAME_01_ENG (or create a new name section and add it to that).

3. Update all existing templates (if any, as the creation of templates should only be initiated after all customized fields have been added to sections) containing the NAME_01_ENG name section by first deleting the section then adding it back, so the new field will be included. All section configurations will need to be redone.

Note: If a field is added to a section but existing templates are not modified in this way, they will not work correctly when used.

If an entire table is added to the Personal Data or Job Data component, the process is exactly the same as above, except that it would be better to create new Get/Set services for the new table and modify the existing Get/SetPerson services to include the new table.

This article credit goes to Coppin Eagle

PeopleSoft Component Interface from EXCELTOCI or Application Engine

2012-10-02T08:06:00.001-04:00

Excel to Component Interface (ExceltoCI) is used when

a. There is less number of records to in the component.
b. When the data being populated does not need require too much modification.
c. You may encounter Excel rows limitation while using ExceltoCI.
d. it would be very useful for ad hoc request to load data in PeopleSoft.

For example you may want to load setup tables into PeopleSoft

Use App Engine to call Component Interface

a. This method uses File Layout to populate the data in general. (This can also be overridden to increase the performance of the application engine).
b. The data which is populated can have modification.
c. There is no limitation on the number of records to be populated.

For example you may want to convert all employee data from legacy system to PeopleSoft.

There are number of other consideration to choose ExceltoCI or AE to call Component Interface, but we usually use ExceltoCI for adHoc request like one time upload or functional user wants to do by own.

Application Engine gives you flexibility to modify and confirm your business logic before loading data but usually require development effort.

How to make all grids within PeopleSoft scrollable in one-fell swoop

2012-10-01T12:38:00.000-04:00

Before making decision think twice, do you really need this at database level, the best way is to use Application Designer but if you use this approach, then test, test. test, this article will give you broad idea of scroll, grid and table

Pretty much all Definitions that you can open up in Application Designer are stored in the own PeopleSoft tables so in theory you can one fell swoop just about anything with SQL and enough privileges.

The Fields on a Panel are stored in the Following table PSPNLFIELD, that said it would appear the Grid fields are special and stored in PSPNLCNTRLDATA

From what I see Grids are FIELDTYPE = 19, Note PSPNLCNTRLDATA has more than just Grids so you likely want to focus on FieldType = 19.

So the following SQL should return all the necessary rows:

SELECT A.PNLNAME, A.PNLFLDID, A.PTGRDLAYOUT, A.GRIDROWS

FROM PSPNLCNTRLDATA A

, PSPNLFIELD B

WHERE A.PNLNAME = B.PNLNAME

AND A.PNLFLDID = B.PNLFLDID

AND B.FIELDTYPE = 19

AND A.PTGRDLAYOUT = 0

-- Where PNLNAME is the Page in question and PNLFLDID is the ID the grid is in when you look at the Page on the Order Tab.

-- PTGRDLAYOUT is the field you want where 0 is the original and 1 is Scrollable. FYI - GRIDROWS is the corresponding "Rows to show in Scrollable layout"

;

-- Therefore the Following Update SQL should change all grids currently set to the Original Layout to the Scrollable Layout

UPDATE PSPNLCNTRLDATA A

SET PTGRDLAYOUT = 1

--, GRIDROWS = SOME_NUMBER -- In case you want to

WHERE PTGRDLAYOUT = 0

AND EXISTS (SELECT 'X' FROM PSPNLFIELD B

WHERE B.PNLNAME = A.PNLNAME

AND B.PNLFLDID = A.PNLFLDID

AND B.FIELDTYPE = 19)

;

This article credit goes to Justin:

Terminated Employee(s) and Peoplesoft North American (NA) Payroll

2012-09-21T12:40:00.000-04:00

I summarize possible actions when you work with terminated employee(s) and Peoplesoft North American (NA) Payroll

Paying Terminated Employees on Peoplesoft North American (NA) Payroll

Peoplesoft Peoplesoft North American (NA) Payroll requires either of pay statuses A ,P (Paid Leave) ,Q (Retired with pay) or U (terminated with pay) only when it creates on-cycle paysheets. If you need to pay a one-time payment to a terminated employee the employee status does not matter, but you will have to create a paysheet manually. If you need to set up a recurring payment you might want to consider empl.status U - Terminated with pay. Then the system will create on-cycle paysheets for you until further action is taken and employee is moved to status T.

Benefit or Deduction for Terminated Employees on Peoplesoft North American (NA) Payroll

If you want deductions to come out of the final paycheck issued for a terminated employee

Option 1:
Deduction level setup should not use Stop at Termination.

In the case of an employee who is Terminated with Pay, you can insert a termination row into the base benefit record so deductions will not continue to be withheld from the continued pay.

Option 2:
Leave the benefits active and terminate after the payroll has been confirmed.

Error Importing SSL Certificate into PeopleSoft

2012-09-17T13:13:00.005-04:00

You may get below errors when Trying to import new certificate against existing key alias (i.e. myalias) with pskeymanager.cmd/sh

"pskeymanager -import": Error "keytool error: java.lang.Exception: Public keys in reply and keystore don't match

Import failed. Verify that the Certificate Authority that signed 'www.mybasicknowledge.blogspot.com'
has been loaded into your keystore 'keystore\pskey'

keytool error: java.lang.Exception: Certificate reply does not contain public key for

Import failed. Verify that the Certificate Authority that signed Concat.cer
has been loaded into your keystore /peopletools/webserv/peoplesoft/keystore/pskey

The error indicates that the server certificate (public key), does not match the key entry (private key) in the keystore with the specified alias (i.e. myalias).

Its mismatch between the issued certificate and the certificate request file resulted in the error message

The certificate file was not generated by the CSR file, it was generated by a different CSR file. Simply create a new certificate and signed by server certificate again Or used last generated CSR request to get server certificate (public key) again

How to create CSR request

pskeymanager.cmd/sh –create

How to import CSR response

pskeymanager.cmd/sh -import

How to see List

pskeymanager.cmd/sh -list

Note: pskeymanager.cmd/sh located under <%PS_HOME%>/webserv/<Domain>/piabin

and pskey located under <%PS_HOME%>/webserv/<Domain>/piaconfig/keystore

Recommendation:

It always have a good practice to save pskey file before starting work, so you can restore if error occur.

PSMAIL Command in SQR with PeopleSoft Application

2012-09-16T18:16:00.000-04:00

PSMAIL.EXE looks for the environment variable PS_SERVER_CFG to retrieve its SMTP Settings. That variable is usually set on the Application Server and Process Scheduler on boot up. On the Application Server, it usually is set to
%PS_HOME%\appserv\<domain name>\psappsrv.cfg. On Process Scheduler, it usually points to %PS_HOME%\appserv\prcs\<database name>\psprcs.cfg.

The possible command line arguments are:

TO = the recipients of the email
CC = carbon copy list of recipients
BCC = blind carbon copy list of recipients
FROM = the sender of the email
SUBJECT = the subject heading of the email
BODY = the email body
FILE = list of file attachments to send with email.  File location is relative to where PSMAIL.EXE is run from.
INPUT=points to a config file that contains the above seven arguments.  This was to bypass the maximum number of characters allowable on a command line and carriage return/line feeds.

The tokens - or / can be used in front of the above arguments.  The values for each argument must be encapsulated in double quotes.  Look at the following examples.

PSMAIL -TO"email1@mybasicknowledge.blogspot.com" -FROM"SystemTestRpts@mybasicknowledge.blogspot.com" -SUBJECT"Test Subject

PSMAIL /TO" email1 @mybasicknowledge.blogspot.com" /FROM"support@mybasicknowledge.blogspot.com" /SUBJECT"Test Subject" /BODY"This is test email"

PSMAIL /INPUT"myfile.txt"
If you are using -INPUT or /INPUT then you need to have the input file in UNICODE format.

(on NT, you can do this by saving the file in notepad as "unicode format" &
on Unix you can run the following PeopleSoft binary file
PSUNICONV ASCII <inputfile> UCS-2 <outputfile>)

DEBUG SQR Code Within The PeopleSoft Application

2012-09-16T18:12:00.000-04:00

There are two debugging related SQR flags used to help troubleshoot problematic SQR programs. When these flags are added to the command line, more detailed information is added to the current SQR program log file.

SQR flags used for debugging:

-DEBUG

Setting the -DEBUG flag on the SQR command line will turn on all code found within the current SQR program run where the program line starts with a DEBUG compiler directive. This means that if the SQR program was submitted with the -DEBUG flag that matches the DEBUG code then the code will be compiled to be executed for this instance of the program run. If unsure which debug code is being used for a particular SQR program, open the SQR program code (within a text editor of your choice) and search for all occurrences of the word "DEBUG" (note: some environments, search may be case-sensitive).

SQR Code Examples:

#DEBUG <some command> <-- in case of DEBUG flag do some command
#DEBUGA <some command> <-- in case of DEBUGA flag do some command
#DEBUGB <some command> <-- in case of DEBUGB flag do some command
Command Line DEBUG Flag Examples:

-DEBUG <-- execute DEBUG code
-DEBUGA <-- execute DEBUG and DEBUGA code
-DEBUGB <-- execute DEBUG and DEBUGB code
-DEBUGAB <-- execute DEBUG, DEBUGA and DEBUGB code

-S

Setting the -S flag on the SQR command line will turn on SQL cursor status logging within the current SQR program run. This means that cursor and cursor status information will be appended to the SQR log file for this instance of the program run.

STEPS for setting -DEBUG and -S flags

To set the "-S" or "-DEBUG" flags within the Oracle PeopleSoft application for troubleshooting a particular process:

1) Open the related Process Definition (navigation: PeopleTools > Process Scheduler > Processes) and go to the "Override Options" tab.
2) From here you can append either flag (or both flags separated by a space) to the parameter list by entering them in the "Parameter List" field and setting the Parameter List drop-down to "Append".
3) Save the Process Definition.

Note: Once this has been done, any time a new process request for this process is submitted, the flags will be appended and extra information added to the SQR log file for further analysis. For the flags to fully take effect for existing process requests, due to cache related scenarios, affected Process Scheduler domains may need to be recycled, (and possibly cache cleared), before the cache is refreshed with the new Process Definition parameters.

Read More
http://www.mybasicknowledge.blogspot.com/2012/08/peoplesoft-configuration-settings-for.html

Using Windows System Monitor For SQL SERVER

2012-09-06T08:24:00.004-04:00

The following tables summarize some of the useful System Monitor counters that help to optimize PeopleSoft Application and Database Performance.

Controlling SQL SERVER Locking Behaviour with PeopleSoft Application

2012-09-06T08:22:00.001-04:00

SQL Server uses isolation levels and locking as its method of achieving data consistency (atomicity). SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. SQL Server uses dynamic locking – which means that little or no configuration is needed in order for SQL Server to achieve isolation and concurrency.

Choosing an isolation level does not affect the locks that are acquired to protect data consistency. Regardless of the isolation level, transactions will always hold an exclusive lock on the data being modified until the transaction completes; however, the isolation level does define how a concurrent transaction will be affected, trying to read the same data.

The important aspects of SQL Server locking that particularly affect performance include isolation levels, lock granularity, and lock escalations.

Isolation Levels

Transaction isolation levels control the following in a SQL Server database:

• Whether locks are acquired and the type of locks acquired when data is read.

• Duration of the read locks.

• Whether a read operation referencing rows modified by another transaction:

• Blocks until the exclusive lock on the row is freed.

• Retrieves the committed version of the row that existed at the time the statement or transaction started.

• Reads the uncommitted data modification.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

The recommended isolation level for PeopleSoft applications is the read-committed snapshot isolation level.

http://www.mybasicknowledge.blogspot.com/2012/06/rcsi-read-committed-snapshot-isolation.html

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.

Under this isolation level, blocking and deadlocking issues due to lock contention are greatly reduced. Read operations only acquire an Sch-s lock at the table. No page or row S locks are acquired and, therefore, do not block transactions that are modifying data.

Lock Granularity

SQL Server supports the following basic locking grains (levels):

• Table

• Page

• Key

• Key Range

• Row (or RID)

These locking grains represent the initial locking grain as determined by SQL Server’s dynamic locking mechanism. When the lock grain is higher (table or page), it reduces the amount of CPU and memory resources spent on maintaining the locks.

However, it reduces concurrency. When lock grain is lower (key or row), the reverse is true.

In SQL Server , the ALTER INDEX statement with the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options can be used to customize the initial lock grain for an index or an entire table, including indexes. These options will allow (or disallow) row or page locks on the specified object. The default for these options is ON, that is, row and page locks are allowed.

Note. Row locks on non-clustered indexes refer to the key or row locator entries in the index’s leaf pages.

By disallowing page locks, you can increase write concurrency, and can reduce writer – writer deadlocks. For example:

ALTER INDEX PS_BO ON PS_BO

SET (ALLOW_PAGE_LOCKS = OFF) ;

Note. In SQL Server, when using the read-committed snapshot isolation level, lock contention or blocking issues due to writers blocking readers are eliminated. Therefore, the need to use the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options for controlling locking to avoid blocking and improving concurrency is greatly reduced for writer – reader blocking scenarios.

If an index (or table) is dropped and re-created, ALTER INDEX will need to be re-executed to reestablish the customized locking for the table or index.

Note. In versions prior to SQL Server, the sp_indexoption stored procedure was used to allow or disallow page and row locks. This feature will be removed in future versions of SQL Server. Avoid using this stored procedure; instead use ALTER INDEX.

Lock Escalations

Lock escalation is a technique to lower the number of locks taken by a transaction, and to manage the total amount of memory used for locks in control. SQL Server automatically escalates row, key, and page locks to coarser table locks as appropriate.

Lock escalation converts many individual rows or page locks to table locks.

Lock Escalation Triggers and Mechanism

Lock escalation is triggered when the lock count for one transaction exceeds 5000, or the lock count for one index or table exceeds 765. The lock manager determines how much memory is allocated for locks. If more than 40 percent of the memory pool is used for locks, SQL Server attempts to escalate multiple page, key, or RID locks to table locks. SQL Server tries to find a table that is partially locked by the transaction, and holds the largest number of locks for which no escalation has already been performed and is capable of escalation. If any other process holds a contradictory lock on any rows, pages, or keys on the table, lock escalation cannot be done on that table.

When the threshold is reached (that is, lock memory is greater than 40 percent of SQL Server memory), SQL Server attempts to escalate locks to control the amount of memory used for locks. It identifies a table within the transaction that holds the maximum amount of locks as a good candidate for lock escalation. However, if it finds any incompatible locks held on the table, it skips that table.

If the table lock requested cannot be granted, escalation is not blocked; the transaction continues and escalation will be requested when the next multiple of 1250 locks have been acquired by the transaction.

Lock

Escalation Hierarchy

Lock escalation never converts row locks to page locks, but always converts them to table locks. The escalation is always directly to a table lock.

Lock Escalation and Performance

Though lock escalation may result in blocking and deadlocks, they are not the only cause of blocking and deadlocks. Often blocking and deadlocks happen because of the application and the nature of the usage even without any lock escalation.

If lock escalation is causing performance issues through excessive blocking or deadlocking, lock escalation can be prevented.

Use the following techniques to prevent lock escalation:

• Use SQL Server Profiler and monitor lock escalations to find out how frequently lock escalation occurs and on what tables.

• Ensure that SQL Server has sufficient memory. Use the SQL Server Performance Monitor to monitor Total Server Memory (KB) and Lock Memory.

• Determine if the transaction provides a way to control the commit frequency. If yes, increase the commit frequency. A good example is Global Payroll – PAYCALC.

• Disable lock escalation with one of the following two methods (DBCC TRACEON (1121) or UPDLOCK HOLDLOCK).

Method 1

Use this SQL Server command DBCC TRACEON (1211) to disable lock escalation on the entire database instance. Although this eliminates lock escalations, it also defeats the purpose of lock escalations and is why this command is undocumented. When this command is enabled, your system can potentially allocate its whole memory for the locks, which affects the performance of every user. In addition SQL Server introduces a new trace flag 1224 which is very similar to trace flag 1211, but will only disable lock escalation until the lock structures consume 40% of the memory used by SQL Server in the low 2GB (i.e. excluding the AWE memory). If both trace flags 1224 and 1211 are specified, trace flag 1224 takes precedence over 1211.

Note. Do not try the above setting in your production environment.

Method 2

With this method you can disable lock escalation for a specific table.

Warning! Only experienced database administrators should use this method, and with caution.

From SQL Server Profiler you know that lock escalation mostly happens on one table. You want to eliminate the lock escalation on that table, for example PS_BO_CM.

1. Open a query window in Management Studio and connect to the database as dbowner.

2. Enter the following to start a transaction:

begin tran

SELECT * FROM PS_BO_CM WITH (UPDLOCK HOLDLOCK) WHERE 1 =2

This selects 0 records because the condition 1 =2 is always false. However, with the hint WITH (HOLDLOCK), the transaction places an Intent Shared Lock on the table. This prevents any SQL statements from taking an X- Table lock on this table, including the escalation.

3. At the end of the period or the day, you can simply come back to the query window and enter the following to release your Intent Shared Lock on the table:

Note. This approach is useful if you want to ensure no lock escalation on a particular table.

In SQL Server, the read-committed snapshot isolation level is the recommended setting for PeopleSoft applications. This isolation level has no direct effect on lock escalation; however, it does alleviate lock contention or blocking problems caused by lock escalation. For instance, if an UPDATE statement causes lock escalation and the entire table is locked, under readcommitted snapshot isolation level, a concurrent read transaction on the table would not be blocked.

http://www.mybasicknowledge.blogspot.com/2012/06/rcsi-read-committed-snapshot-isolation.html

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.

Locking Trace Flags

In very specific cases, trace flag 1211 can be used to suppress the request for table locks. This also disables lock escalation due to memory pressure. Use of this trace flag is not advisable for PeopleSoft applications.

As an alternative, use trace flag 1224 in SQL Server to suppress lock escalation rooted in the estimated number of locks, but allow lock escalation due to memory pressure. Trace flag 1224 is new in SQL Server and does not exist in earlier versions of SQL Server. For PeopleSoft applications, trace flag 1224 is better suited to suppress lock escalation than trace flag 1211.

Deadlocks

In SQL Server , with the introduction of the new read-committed snapshot isolation level, lock contention or blocking problems are greatly reduced. Read-committed snapshot isolation eliminates writers blocking readers and readers blocking writers. This, in turn, would also eliminate the read – write deadlock scenarios, where an UPDATE and a SELECT transaction deadlock. However, deadlocks caused by two concurrent UPDATE transactions and other writer – writer scenarios still may exist. The information that follows will help you analyze and debug these deadlocks.

A deadlock occurs when two processes are waiting for a resource and neither process can advance because the other process prevents it from getting the resource.

Knowing the following information is a starting point to resolve a deadlock:

• Processes that caused the deadlock.

• Deadlock trace.

• SQL statements that caused the deadlock.

• SQL statements within the transaction, where the deadlock happened.

• Execution plan on each SQL statement that resulted in a deadlock.

Deadlocks can be monitored in one of three ways: using trace flag 1204, using trace flag 1222, or using SQL Server Profiler.

Using Trace Flag 1204 or Trace Flag 1222

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes, and then by resources in an XML format. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

SQL Server can be started with trace flag 1204 or 1222 enabled. To do so, open SQL Server Enterprise Manager, right-click on your server instance, and select Properties. In the General tab, click Startup Parameters. Add the following line:

-T1204 -T3605

The output of the deadlock trace will be logged into the SQL Server error log specified by the -e parameter in Startup Parameters. The 3605 flag causes the output to go to the error log rather than the screen. This is set as the default in SQL Server.

Alternatively, deadlock tracing can be enabled with the following command:

DBCC TRACEON (1204, 3605,-1)

Here is sample output from DBCC TRACEON for 1204:

The output contains the following information:

• The object involved in the deadlock. In this example, it is 10:2045302396:2, where 10 is the database ID, 2045302396 is the object ID, and the final 2 is the index ID.

Entering the following gives you the name of the database where the deadlock occurred:

SELECT DB_NAME(captured_database_id)

From the deadlocked database, entering the following shows the table involved:

SELECT OBJECT_NAME(captured_object_id)

Entering the following shows the index involved:

SELECT NAME FROM sys.indexes WHERE object_id = captured_object_id AND indid = captured_index_id

• The statement type shows what kind of statement it is (for example, INSERT or UPDATE).

• ‘Input buf:’ (input buffer) shows the actual statement. However, in a PeopleSoft environment you see either sp_prepexec or sp_cursorexec. This is not very useful in identifying the SQL statement.

Using SQL Server Profiler

A better alternate is to enable SQL Server Profiler.

To use this profiler output to determine the cause of a deadlock:

1. Save the output into a trace table. From the File menu, select Save As, and choose Trace Table.

2. Use the following SQL statement to find the list of Deadlock Chain events.

SELECT * FROM DLTRACE1 WHERE EventClass =59

DLTRACE1 is the trace table and EventClass 59 is for deadlocks.

From the output you can determine which SPID is involved in the deadlock and note down the row number for this Deadlock Chain event.

3. Substitute the values in the following query and you will find all the SQL statements used by that process as part of the deadlocked transaction.

DECLARE @LastCommitPoint int, @DLSpid int, @DLChainRowNumber int

/* Set the Deadlock SPID and the Deadlock Chain’s rownumber */

SET @DLSpid = 134

SET @DLChainRowNumber = 159501

SELECT @LastCommitPoint = max(RowNumber) FROM DLTRACE1

WHERE SPID = @DLSpid

AND RowNumber < @DLChainRowNumber

AND EventClass = 41 -- SQL:StmtCompleted

AND TextData like 'COMMIT TRAN%'

SELECT * FROM DLTRACE1

WHERE SPID = @DLSpid

AND RowNumber < @DLChainRowNumber

AND RowNumber > @LastCommitPoint

AND EventClass = 45 -- SP:StmtCompleted

4. Repeat the previous steps for the other Deadlock Chain events. These SQL statements will present a clear picture of how the deadlock happened.

The following EventClass classes and their corresponding IDs are relevant to the PeopleSoft environment.

RPC:Completed - 10

Show Plan Text -96

Execution Plan - 68

RPC:Starting - 11

Lock:Escalation - 60

Lock:Deadlock - 25

Lock:Deadlock Chain - 59

SP:StmtStarting - 44

SP:StmtCompleted – 45

SQL:StmtStarting - 40

SQL:StmtCompleted - 41 (COMMIT TRAN)

This document’s appendix includes an example of a procedure that automates the process. You can use this procedure as a model and modify it for your purposes.

Resolving a Deadlock

The following actions can help when resolving a deadlock

• Determine whether read-committed snapshot isolation is enabled. (Only applicable to those versions of PeopleTools that support read-committed snapshot isolation level.)

• Determine whether the table contains recent statistics.

• Use the INDEXPROPERTY statement to determine whether page locks are disallowed on a table.

For example:

SELECT INDEXPROPERTY(OBJECT_ID('PS_BO'), 'PS0BO', 'IsPageLockDisallowed')

A return value of 0 means that page locks are allowed; a value of 1 means page locks are disallowed.

You can use ALTER INDEX to disallow page locks.

ALTER INDEX PS_BO ON PS_BO

SET (ALLOW_PAGE_LOCKS = OFF) ;

• Create any additional indexes that could help resolve the deadlock. Review the execution plans of the SQL statements that caused the deadlock and determine if they do an index scan. If they do, see if creating an additional index changes the access path for the SQL statement from index scan to index seek.

For example, examine the following SQL statement:

SELECT DISTINCT EOEW_MAP_OBJ

FROM PS_EOEW_RUN_PROC

WHERE RUN_CNTL_ID LIKE :1 %CONCAT '.%'

The SQL statement does a clustered index scan because the leading key of the existing index is OPRID, but the SQL statement does not use OPRID as part of the WHERE clause.

The solution is to add another index with RUN_CNTL_ID as a leading key:

Note. PeopleSoft applications are delivered with the necessary indexes that are required for an application and its performance for typical usage. They are not delivered with all the possible indexes because an index creates unnecessary overhead (on INSERT, UPDATE, and DELETE) if it is not useful for an implementation. Your implementation (data and business processes) may warrant some additional indexes.

• Adding an index cover for a non-clustered index to cover the query could help resolve the deadlock. In the previous example, the SQL statement would use the new index first, but to get the EOEW_MAP_OBJ, it has to go to the table.

It would use the available clustered index to perform this task. If EOEW_MAP_OBJ is also added to the new nonclustered index, the query becomes a covered query. In other words, SQL Server could build the result set entirely by reading the index. If the column you are trying to add to a non-clustered index is part of clustered index, there is no need to add that column to the non-clustered index for the purpose of index cover.

• Pay attention to lock escalations.

PeopleSoft Database Maintenance Part2

2012-09-06T08:09:00.003-04:00

Disk Space Considerations

Additional temporary disk space is required for online operations. If a clustered index is created, rebuilt, or dropped online, a temporary non-clustered index is created to map old bookmarks to new bookmarks.

If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. The temporary mapping index contains one record for each row in the table, and its content is the union of the old and new bookmark columns, including uniqueifiers and record identifiers, and including only a single copy of any column used in both bookmarks.

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb.

Performance Considerations

Online index operations are typically slower than equivalent offline index operations due to the extra steps required to allow online operations. Heavy update activity may further impede the online index operation as well. Online index operations are fully logged, potentially leading to decreased performance compared to bulk-logged operations.

Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double the amount of usage. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation.

Online index operations may use multiple processors on a multiprocessor system with SQL Server Enterprise Edition. You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. In this way, you can balance the resources that are used by index operation with those of the concurrent users.

Transaction Log Considerations

Online index operations require a large amount of transaction space. The space required depends on the size of the index. In addition to the index operations, concurrent online operations also consume log space. From a performance perspective, it is important to consider the extra transaction log space usage and pre-size the log file accordingly to avoid expansion during index or user operations.

For PeopleSoft applications that have high uptime requirements, online index operations are recommended. However, they should be scheduled during times of low user or batch activity to avoid performance degradation.

Statistics

Statistics are details about the uniqueness (or density) of the data values, including a histogram consisting of an even sampling of the values for the index key (or the first column of the key for a composite index) based on the current data. It also includes the number of pages in the table or index. SQL Server uses a cost-based optimizer, which means that if the statistics are not relatively current, it is misleading to the optimizer and can result in poor execution plans.

AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS Options

For PeopleSoft applications it is recommended that you enable the AUTO_CREATE_ STATISTICS and AUTO_UPDATE_STATISTICS database options at the database level. When these options are enabled, SQL Server automatically updates statistics when the query optimizer determines that they are out of date. The AUTO_UPDATE_STATISTICS option will automatically update the statistics for a table when a specific change threshold has been reached. The sysindexes.rowmodctr column maintains a running total of all relevant modifications to a table. This counter is updated each time any of the following events occurs:

• A row is inserted into the table.

• A row is deleted from the table.

• An indexed column is updated.

Every database is created with the database options AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS set to TRUE. For PeopleSoft applications, it is recommended that you leave these options set unless there is a compelling reason to disable them. If the options must be disabled in exceptional cases, they should only be disabled at a table level so that they do not affect the entire database.

Disabling AUTO_UPDATE_STATISTICS at the Table Level

If you do not want statistics to be automatically updated during the normal operational hours for a specific table, you can disable the option at the table or index level. You then take the responsibility of maintaining statistics for that table or index by explicitly updating statistics. At a table level, the AUTO_UPDATE_STATISTICS option can be disabled using either the sp_autostats stored procedure or the UPDATE STATISTICS statement with the WITH NORECOMPUTE option.

Use the sp_autostats procedure to indicate that statistics should or should not be updated automatically for a table.

For example, to disable automatic updating of statistics for all the indexes on the table PS_BO:

sp_autostats PS_BO, 'OFF'

For example, to disable automatic updating of statistics for a specific index on the table PS_BO:

sp_autostats PS_BO, 'OFF', PSABO

Alternatively, use the UPDATE STATISTICS statement with the WITH NORECOMPUTE option. This indicates that statistics should not be automatically recomputed in the future. Running UPDATE STATISTICS again without the WITH NORECOMPUTE option enables automatic updates again. For example:

UPDATE STATISTICS PS_BO WITH NORECOMPUTE

Note. Setting the AUTO_UPDATE_STATISTICS database option to FALSE overrides any individual table settings.

User-Created Statistics

If a particular column in a table is not a leading column (the first column) in any indexes of that table, histograms will not be available on that column by default. If the AUTO_CREATE_STATISTICS database option is set to ON for the database or table, SQL Server may create statistics and histogram for that column as needed. Users can also explicitly create statistics on a table column. This creates a histogram on the first supplied column and associated density groups (collections) over the supplied column or set of columns, as the following example demonstrates:

CREATE STATISTICS BO_FIRST_NAME ON PS_BO_NAME (FIRST_NAME)

Statistics are used by the query optimizer to estimate the selectivity of expressions, and thus the size of intermediate and final query results. Good statistics allow the optimizer to accurately assess the cost of different query plans and choose a high-quality plan.

User-created statistics are required for very few advanced performance tuning scenarios. The statistics created by SQL Server are usually sufficient for the optimizer to produce efficient execution plans.

Updating Statistics

Statistics can be manually updated any time using the UPDATE STATISTICS statement. This statement updates information about the distribution of key values for one or more statistics groups (collections) in the specified table.

The following example updates statistics for all the indexes on PS_BO table, using a default sampling:

UPDATE STATISTICS PS_BO

Usually a default sampling is good enough. However, there were few occasions during tuning and benchmarking of a PeopleSoft application that the SQL Server optimizer failed to produce the best execution plan for some SQL statements.

Further testing showed that updating statistics with FULLSCAN improved the situation.

The following example updates statistics for all the indexes on PS_BO table, using a specific sampling:

UPDATE STATISTICS PS_BO WITH SAMPLE 40 PERCENT

The following example updates statistics for all the indexes on PS_BO table, using all the rows:

UPDATE STATISTICS PS_BO WITH FULLSCAN

Note. Use UPDATE STATISTICS with FULLSCAN as an exceptional situation, if you believe the optimizer is not picking up the best execution plan based on the existing statistics.

Statistics can also be updated on all user-defined tables in the current database, using the sp_updatestats stored procedure. This may take a very long time to complete. For example:

USE PSFTDB

EXEC sp_updatestats

Viewing Statistics

The DBCC SHOW_STATISTICS command reports the distribution statistics for a specified indexed or non-indexed column.

The report contains the following useful information:

• Date and time that statistics were collected.

• Number of rows in the table.

• Rows that were sampled.

• Number of steps in the histogram.

• Density for non-frequent column values.

• Average key length.

• All density.

• Distribution histogram.

The following is an example of the DBCC SHOW_STATISTICS command and its output:

http://www.mybasicknowledge.blogspot.com/2012/09/peoplesoft-database-maintenance-part1.html

PeopleSoft Database Maintenance Part1

2012-09-06T08:04:00.007-04:00

Managing Indexes

SQL Server maintains indexes automatically. However, indexes can become fragmented over time. The fragmentation can be of two types: internal and external.

Internal fragmentation occurs when large amounts of data are deleted and pages are less full. A certain amount of free space on index pages is beneficial as it allows room for future inserts into these pages without having to split a page.

When the next logical page of an index is not the next physical page, it is called external fragmentation. This may impact performance when SQL Server is doing an ordered scan of all or part of a table, or an index. The access by a range of values is no longer sequential, limiting the ability of the storage engine to issue large I/O requests.

Parallel Index Operations

For PeopleSoft applications, creating or maintaining indexes on large tables can benefit tremendously when you execute the index creation or maintenance operation on parallel CPUs.

SQL Server 2005 provides the ability to use the MAXDOP query hint to manually configure the number of processors that are used to run the index statement. In prior versions of SQL Server, the server setting of MAXDOP (and the current workload) determined the number of processors to be used by the index operation.

The MAXDOP index option overrides the max degree of parallelism configuration option for the query specifying this option.

Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

• CREATE INDEX

• ALTER INDEX REBUILD

• DROP INDEX (for clustered indexes only)

• ALTER TABLE ADD (index) CONSTRAINT

• ALTER TABLE DROP (clustered index) CONSTRAINT19

Note. Parallel index operations are available only in SQL Server 2005 Enterprise Edition.

Following is an example of using the MAXDOP index option with an ALTER INDEX statement:

ALTER INDEX PSALEDGER ON dbo.PS_LEDGER

REBUILD WITH (MAXDOP = 4);

For PeopleSoft applications, it is recommended that you set the MAXDOP server setting to 1. However, during index operations, this setting could be overridden by using the MAXDOP query hint as shown above, for better performance and CPU resource utilization.

Index-Related Dynamic Management Views

SQL Server 2005 provides the ability to query and return server and database state information to monitor the health of a server instance, diagnose problems, and tune performance through dynamic management views. The following sections describe some index-specific dynamic management views that can be used for monitoring and tuning indexes.

Identify Frequently Used Indexes

The sys.dm_db_index_usage_stats dynamic management view returns information on the usage frequency of an index. The following example query can be used to return information on the frequency of seeks, scans, and lookups by a user query on all indexes for all user tables in a specific database:

USE PSFTDB

select db_name(database_id)as 'DB Name', object_name(isu.object_id) as 'Table

Name'

, si.name as 'Index Name', user_seeks as 'Seeks', user_scans as 'Scans' , user_

lookups as 'Lookups'

from sys.dm_db_index_usage_stats isu

inner join sys.indexes si

on si.index_id = isu.index_id

and si.object_id = isu.object_id

inner join sys.objects so

on so.object_id = si.object_id

and so.type = 'U'

The sys.dm_db_index_usage_stats dynamic management view or the query using it can be used in PeopleSoft applications to retrieve information on the index usage statistics of the database. This information can help to evaluate index usage and plan for index maintenance operations as well. It is important to note that the information in this dynamic management view is reset or deleted when the SQL Server service is started.

Identify Missing Indexes

The SQL Server 2005 query optimizer has the ability to identify missing indexes for queries. When the query optimizer generates a query plan, it analyzes the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a query plan based on available indexes, but stores information about the desired missing indexes.

This information can be retrieved from the dynamic management views and analyzed to improve the indexing and query performance.

The dynamic management views that identify and store the missing index information consist of the following:

• sys.dm_db_missing_index_group_stats: Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.

• sys.dm_db_missing_index_groups: Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.

• sys.dm_db_missing_index_details: Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.

• sys.dm_db_missing_index_columns: Returns information about the database table columns that are missing an index. The following example query can be used to identify missing index information for PeopleSoft applications.

USE PSFTDB

select d.*

, s.avg_total_user_cost

, s.avg_user_impact

, s.last_user_seek

,s.unique_compiles

from sys.dm_db_missing_index_group_stats s

,sys.dm_db_missing_index_groups g

,sys.dm_db_missing_index_details d

where s.group_handle = g.index_group_handle

and d.index_handle = g.index_handle

order by s.avg_user_impact desc

--- suggested index columns & usage

declare @handle int

select @handle = d.index_handle

from sys.dm_db_missing_index_group_stats s

,sys.dm_db_missing_index_groups g

,sys.dm_db_missing_index_details d

where s.group_handle = g.index_group_handle

and d.index_handle = g.index_handle

select *

from sys.dm_db_missing_index_columns(@handle)

order by column_id

It is highly recommended for PeopleSoft applications that you do a thorough analysis of the missing index data before adding any indexes. Adding indexes may improve query performance; however, adding indexes may add overhead for update and insert operations.

Note. Use only PeopleTools to add indexes.

Note. The information in this dynamic management view is reset or deleted when SQL Server service is started.

Identify Indexes Not Used to a Point in Time

Indexes that are created in the database, but have not been used by any query until a point in time can be identified with the sys.dm_db_index_usage_stats dynamic management view.

The following example query identifies unused indexes:

use PSFTDB

select object_name(i.object_id), i.name

from sys.indexes i, sys.objects o

where i.index_id NOT IN (select s.index_id

from sys.dm_db_index_usage_stats s

where s.object_id=i.object_id and

i.index_id=s.index_id and

database_id = db_id('PSFTDB') )

and o.type = 'U'

and o.object_id = i.object_id

order by object_name(i.object_id) asc

For PeopleSoft applications, it is important to understand that some indexes could be used quite infrequently; however, they still could be quite critical for performance of some specific functionality. For example, a batch process could run monthly, quarterly, or even annually. The index identified by the previous example query may never be used, but a batch process that is scheduled to be run could be using it. Deleting such an index could have adverse effects on performance for scheduled (but unrun) batch processes. Though it may lower overhead to delete indexes that are never used, thorough analysis should be made before deleting any index.

Note. The dynamic management view used to identify the unused index information gets reset or information in it is deleted when SQL Server is restarted. So at best, the information revealed by the previous query is from the last known SQL Server restart to the point in time the dynamic management view query was executed.

Detecting Fragmentation

In SQL Server 2005, fragmentation can be detected by using the index-related dynamic management view sys.dm_db_index_ physical_stats. The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement in SQL Server 2000.

The algorithm for calculating fragmentation is more precise in SQL Server 2005 than in SQL Server 2000. As a result, the fragmentation values will appear higher.

The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. For heaps, the value represents the extent fragmentation of the heap. For indexes, the value represents the logical fragmentation of the index. Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are more accurate.

The following example query demonstrates how the dynamic management view returns fragmentation information:

USE PSFTDB

SELECT database_id,object_id, index_id, index_type_desc, index_depth, index_

level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages

FROM sys.dm_db_index_physical_stats

(DB_ID(N'PSFTDB'), OBJECT_ID(N'PS_LEDGER'), NULL, NULL , NULL);

For PeopleSoft applications, the value for avg_fragmentation_in_percent should ideally be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable.

Reducing Fragmentation

In SQL Server 2005, there are three ways to reduce fragmentation:

• Drop and re-create the index

• Reorganize the index

• Rebuild the index

The CREATE INDEX .. DROP EXISTING = ON statement can be used to drop and re-create the index.

The following example demonstrates using the CREATE INDEX statement to drop and re-create an index:

CREATE CLUSTERED INDEX PS_LEDGER

ON PS_LEDGER(BUSINESS_UNIT, OPERATING_UNIT, FISCAL_YEAR, ACCOUNTING_

PERIOD, LEDGER, ACCOUNT, ALTACCT, DEPTID, PRODUCT, PROJECT_ID, AFFILIATE,

CURRENCY_CD, STATISTICS_CODE, FUND_CODE, CLASS_FLD, MSSCONCATCOL)

WITH (DROP_EXISTING = ON);

Use the DROP_EXISTING option to change the characteristics of an index or to rebuild indexes without having to drop the index and re-create it. The benefit of using the DROP_EXISTING option is that you can modify indexes created with PRIMARY KEY or UNIQUE constraints. This option performs the following:

• Removes all fragmentation.

• Reestablishes FILLFACTOR/PAD_INDEX.

• Recalculates index statistics.

The second way to reduce fragmentation is to reorganize the index. To reorganize the index, use the ALTER INDEX REORGANIZE statement. It is the replacement for DBCC INDEXDEFRAG, and it will reorder the leaf-level pages of the index in a logical order. Use this option to perform online logical index defragmentation.

It is possible that an interruption may happen to the defragmentation process. The operation can be interrupted without losing work already completed.

The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation and it does not update statistics.

The following example demonstrates the ALTER INDEX REORGANIZE statement:

ALTER INDEX PS_LEDGER ON PS_LEDGER

REORGANIZE;

The third way to reduce fragmentation is to rebuild the index. To do so, use the ALTER INDEX REBUILD statement. It is the replacement for DBCC DBREINDEX and it will rebuild the index online or offline. Use this option to:

• Remove heavy defragmentation.

• Rebuild the physical index online or offline.

The ALTER INDEX REBUILD statement requires a statistics update.

The following example demonstrates the ALTER INDEX REBUILD statement:

ALTER INDEX PS_LEDGER ON PS_LEDGER

REBUILD;

In general, when the avg_fragmentation_in_percent value is between 5 and 30 percent, the ALTER INDEX REORGANIZE statement can be used to remove fragmentation. For heavy fragmentation (more than 30 percent) the ALTER INDEX REBUILD or CREATE INDEX DROP EXISTING statements can be used.

Use the following guidelines to decide between the two options.

* A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition. This operation must be performed with the ONLINE option set to OFF. Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.

** If the index is re-created by using the same name, columns and sort order, the sort operation may be omitted. The rebuild operation checks that the rows are sorted while building the index.

Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect on performance. It is also not recommended to remove fragmentation for fragmentation of 5 percent or less. Depending on the index size, the cost may outweigh the benefit.

Online Index Reorganization

For most PeopleSoft applications, high application uptime and availability are expected. Database maintenance operations can sometimes affect the uptime requirement, because the database may have to be taken offline for database maintenance.

SQL Server 2005 provides capabilities for database maintenance, specifically index reorganizations to be done online without affecting application uptime and availability.

Online Operations

In SQL Server 2005 you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated non-clustered indexes during these index operations. When the indexes are being built with the ONLINE option, concurrent user access to query and modify the underlying table data is permitted.

The ONLINE option is available in the following Transact-SQL statements.

• CREATE INDEX

• ALTER INDEX

• DROP INDEX

• ALTER TABLE (To add or drop UNIQUE or PRIMARY KEY constraints with CLUSTERED index option)

The following example demonstrates an online index rebuild operation:

ALTER INDEX PS_LEDGER ON PS_LEDGER

REBUILD WITH (ONLINE = ON);

In the following example, all indexes on the PS_LEDGER table are rebuilt online.

ALTER INDEX ALL ON PS_LEDGER

REBUILD WITH (ONLINE = ON);

When you perform online index operations, the following guidelines apply:

• Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) datatypes: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.

• Non-unique non-clustered indexes can be created online when the table contains LOB data types, but none of these columns are used in the index definition as either key or non-key (included) columns. Non-clustered indexes defined with LOB data type columns must be created or rebuilt offline.

• Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

• The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

You can perform concurrent online index operations on the same table only when doing the following:

• Creating multiple non-clustered indexes.

• Reorganizing different indexes on the same table.

• Reorganizing different indexes while rebuilding non-overlapping indexes on the same table.

All other online index operations performed at the same time fail. For example, you cannot rebuild two or more indexes on the same table concurrently, or create a new index while rebuilding an existing index on the same table.

http://www.mybasicknowledge.com/2012/09/peoplesoft-database-maintenance-part2.html

SQL SERVER Administration Optimizations with PeopleSoft Application

2012-09-06T07:57:00.002-04:00

Dedicated Administrator Connection (DAC)

SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. The DAC is available through the sqlcmd utility and SQL Server Management Studio.

To connect to a server using the DAC

1. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

2. In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named HRSrvr\HRProd, type ADMIN: HRSrvr\HRProd.

3. Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

http://www.mybasicknowledge.com/2012/06/dedicated-administrator-connection-dac.html

I/O Optimizations

Instant File Initialization

For large PeopleSoft installations, the newly introduced SQL Server feature, instant file initialization, can be leveraged to avoid long waits when the data file expands.

Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

• Create a database.

• Add files, log or data, to an existing database.

• Increase the size of an existing file (including autogrow operations).

• Restore a database or filegroup.

The initialization of the data or log file with zeros usually leads to wait time and blackouts, if a large expansion is required.

In SQL Server, data files can be initialized instantaneously for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

Instant file initialization is available only if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

The SE_MANAGE_VOLUME_NAME privilege is granted by default and no specific action is required to use this feature.

Security Considerations

Because the deleted disk content is overwritten only as new data is written to the files, the deleted content might be accessed by an unauthorized principal. While the database file is attached to the instance of SQL Server, this information disclosure threat is reduced by the discretionary access control list (DACL) on the file. This DACL allows file access only to the SQL Server service account and the local administrator. However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME. A similar threat exists when the database is backed up. The deleted content can become available to an unauthorized user or service if the backup file is not protected with an appropriate DACL.

If the potential for disclosing deleted content is a concern, you should do one or both of the following:

• Always make sure that any detached data files and backup files have restrictive DACLs.

• Disable instant file initialization for the instance of SQL Server by revoking SE_MANAGE_VOLUME_NAME from the SQL Server service account.

Note. Disabling instant file initialization only affects files that are created or increased in size after the user right is revoked.

For PeopleSoft applications, instant file initialization is recommended from a performance perspective. However, evaluate the performance gain against the possible security risk. If the security policy does not allow for this possible risk, do not use instant file initialization. You can disable it by revoking SE_MANAGE_VOLUME_NAME from the SQL Server service account.

Long I/O Requests

In SQL Server, the buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. This helps the system administrator distinguish between SQL Server problems and I/O subsystem problems. Error message 833 is reported and appears in the SQL Server error log as follows:

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d

seconds to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p.

The offset of the latest long I/O is: %#016I64x.

A long I/O may be either a read or a write; it is not currently indicated in the message. Long I/O messages are warnings, not errors.

Note. Long I/O warning messages are related to functional disk errors only. For performance tuning of I/O issues, use the I/O related dynamic management views and System Monitor counters.

For PeopleSoft applications, I/O error message 833 can be very useful from a reactive I/O maintenance and monitoring perspective. It is recommended that you monitor the SQL Server error log for these messages. However, for I/O performance tuning, it is recommended that you use the I/O-related dynamic management views and System Monitor counters.

PeopleSoft Table and Index Management

2012-09-06T07:54:00.002-04:00

For large PeopleSoft installations, the table and index size can, over time, become unmanageable and easily cross over to tens of millions of rows. To manage these large tables and indexes and to provide some performance enhancements, SQL Server provides new table and index management enhancements, as described in the section that follows.

Table and Index Partitioning

SQL Server introduces the concept of table and index partitioning. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. You can choose an appropriate partitioning scheme to make large tables and indexes more manageable and scalable.

For PeopleSoft applications, table and index partitioning can reduce time for management and maintenance and improve scalability and performance.

Maintenance operations, such as index reorganizations and rebuilds, can be performed on a specific partition only, thus optimizing the maintenance process. With an efficient partitioning design, maintenance operations can be significantly reduced.

In this situation, the partitioning design would most likely create partitions based on the static (data that does not change or seldom changes) and dynamic (data that is affected or changed very frequently) nature of data. All static data is stored in specific partition(s) and dynamic data is stored in other partition(s). Maintenance operations are only performed against the dynamic data partition, affecting only a very small portion of the data in the table. Backups can also be performed for the dynamic partition data only.

Note. You cannot directly back up a partition; however, you can place a partition on a specific filegroup and back up that filegroup only

Performance and Scalability

Queries with an equi-join on two or more partitioned tables can show improvements in performance if their partitioning columns are the same as the columns on which the tables are joined. The SQL Server query optimizer can process the join faster, because the partitions themselves can be joined. It is important to note that the partitioning function should be the same for the tables as well.

Performance can also be improved for queries that are executed on a specific partition of the table only. The assumption is that the entire data required to process the query is contained within the same partition. Because the index size and the index tree depth are smaller as compared to a similar size non-partitioned table, the query execution will be more efficient.

What follows is an example of how to partition a table or index in PeopleSoft applications. It is important to note that the example explains the steps required to create partitions. The choice of tables and the columns to partition on will depend on your application and specific scenario.

Step 1. Create a Partition Function

A partition function specifies how the table or index is partitioned. The function maps the domain into a set of partitions.

The following example maps the rows of a table or index into partitions based on the values of a specified column:

CREATE PARTITION FUNCTION AcctRangePF1 (char(10))

AS RANGE LEFT FOR VALUES ( '1000', '2000', '3000', '4000');

Based on this function, the table to which this function is applied will be divided into five partitions as shown below:

Step 2. Create a Partition Scheme

A partition scheme maps the partitions produced by a partition function to a set of filegroups that you define.

The following example creates a partition scheme that specifies the filegroups to hold each one of the five partitions. This example assumes the filegroups already exist in the database.

CREATE PARTITION SCHEME AcctRangePS1

AS PARTITION AcctRangePF1

TO (HR1fg, HR2fg, HR3fg, HR4fg, HR5fg);

Step 3. Create a Table or Index Using the Partition Scheme

The example below creates the PS_LEDGER table using the partition scheme defined in Step 2.

CREATE TABLE PS_LEDGER (

[BUSINESS_UNIT] [char](5) COLLATE Latin1_General_BIN NOT NULL,

[LEDGER] [char](10) COLLATE Latin1_General_BIN NOT NULL,

[ACCOUNT] [char](10) COLLATE Latin1_General_BIN NOT NULL,

[ALTACCT] [char](10) COLLATE Latin1_General_BIN NOT NULL,

…

)

ON AcctRangePS1 (ACCOUNT) ;

The PS_LEDGER table will be created on the five partitions based on the partitioning function and the scheme created in Steps 1 and 2, respectively.

The following table shows the partitions for PS_LEDGER based on the previous examples:

It is strongly recommended that based on your specific PeopleSoft application scenario and requirements, the choice to partition or not, and what tables to partition, should be evaluated. Partitioning for most scenarios is most beneficial for management and maintenance. For some specific scenarios, partitioning can yield some performance improvement as well. If the tables involved in a query are not joined on the partitioning key and are not partitioned by the same partitioning function, or for a single table query, if all the data required for the query is not colocated on the same partition, performance may be negatively impacted.

Disabling Indexes

For maintenance and performance troubleshooting, SQL Server provides the functionality to disable a non-clustered or clustered index.

Disabling an index prevents user access to the index, and for clustered indexes it prevents access to the underlying table data. The index definition remains in metadata and index statistics are kept on non-clustered indexes.

Disabling a non-clustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.

For PeopleSoft applications, you can disable indexes for the following reasons:

• To correct I/O errors and then rebuild an index.

• To temporarily remove the index for performance troubleshooting purposes.

• To optimize space while rebuilding non-clustered indexes.

Disabling a non-clustered index physically deletes the index data. The disk space made available when data is deleted can be used for subsequent index rebuilds or other operations.

When a non-clustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index.

The following example shows how to disable an index:

ALTER INDEX PSCLEDGER ON dbo.PS_LEDGER

DISABLE ;

To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING.

Make sure to evaluate each index carefully before disabling it. Some indexes may be required for monthly, quarterly, or yearend processes. Disabling infrequently used indexes could cause performance issues for those processes.

PeopleSoft ODBC API Server Cursor

2012-09-06T07:51:00.002-04:00

PeopleSoft applications use ODBC for database connectivity to the SQL Server database. The ODBC API server cursors are used extensively throughout the PeopleSoft application suite for result set processing.

In previous versions of SQL Server, there were several known issues with ODBC API server cursors.

In SQL Server 2000, the ODBC API server cursors could change the cursor type requested by the application to another cursor type based on a well-defined list of conditions; such as whether the query contained references to text, ntext, or image columns, and numerous other documented conditions. This is defined as cursor conversion, also known as cursor degradation.

Typically, when these conversions occurred, the cursor type degraded to a more expensive cursor type. Generally, a fast forward–only cursor performs best, followed by dynamic cursor, keyset-driven cursor, and finally, the static cursor is the lowest performing cursor.

Sometimes these conversions caused performance problems particularly when the resulting cursor type was static or keysetdriven.

This is because these two cursor types required that the entire result set (static) or keys be populated in a work table before the first row could be returned to the application. Whether this is problematic is directly related to the number of rows the cursor must gather. When the cursor created a very large result set, the application slowed when retrieving the initial set of rows. This can be problematic for applications that tend to join many tables with many target rows but only plan to use a small number of rows from the beginning of the result set.

To improve some of the above-mentioned issues and to further enhance the API server cursors, two enhancements have been made to the API server cursor model in SQL Server : implicit cursor conversion and real-time cursor tracking with dynamic management views.

Implicit Cursor Conversion

In SQL Server , for most of the cases, the requested cursor type will be the resulting cursor type. Cursor degradation is now very limited and will happen in very few cases, as documented in SQL Server Books Online under the topic “Implicit Cursor Conversions (ODBC).” However, the fact that the majority of cases that caused cursor degradation to occur have been eliminated should result in a more consistent application experience when using ODBC API server cursors in SQL Server .

Because this is an optimizer improvement in SQL Server , no manual steps are required by the developer to leverage it.

Real-Time Cursor Tracking with Dynamic Management Views

Because PeopleSoft applications widely use ODBC API server cursors, dynamic management views in SQL Server can be effectively used for cursor tracking and monitoring.

The sys.dm_exec_cursors dynamic management object is a specific cursor-related dynamic management view. Querying this dynamic management object will return information about the open cursors in the database, such as:

• Cursor name

• Properties for declaration interface, cursor type, cursor concurrency, cursor scope, cursor nesting level

• Sql_handle – handle to the text of the cursor that could be used with the sys.dm_exec_sql_text(sql_handle) view to return the exact cursor text

• Creation time

• Reads or writes performed by the cursor

• Fetch buffer size

• Others as documented in the SQL Server Books Online topic, “sys.dm_exec_cursors.”

In the following example, the sys.dm_exec_cursors dynamic management view can be joined with the sys.dm_exec_sql_text to find the session_id, properties, reads, writes, creation time, and the exact cursor code executing currently, for all cursors:

select c.session_id, c.properties, c.reads, c.writes, c.creation_time,

substring(qt.text,c.statement_start_offset/2,

(case when c.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else c.statement_end_offset end - c.statement_start_

offset)/2)

as cursor_text

from sys.dm_exec_cursors (0) c

cross apply sys.dm_exec_sql_text(c.sql_handle) qt

To find information about a specific cursor, replace the 0 with the session_id (SPID) of the session that holds the cursor as the input parameter for the sys.dm_exec_cursors dynamic management view.

By using the sys.dm_exec_cursors dynamic management view you have significantly improved capabilities to diagnose cursorbased applications over previous versions of SQL Server. For example, you can determine whether the cursors are truly the cursor type that application requested. You can also see if a keyset or static cursor is currently being asynchronously populated, and so on.

PeopleSoft Batch / Process Scheduler Server Placement

2012-09-06T07:48:00.004-04:00

PeopleSoft Process Scheduler executes PeopleSoft batch processes. Process Scheduler (Batch Server) can be configured on the batch server (either on the same server as the application server or on a separate server) or on the database server.

There are three potential locations for the batch server:

• Dedicated batch scheduler. This is the preferred configuration. One or more dedicated servers assigned to run the process scheduler service provides the best overall scalability for batch processing and the isolation needed to effectively tune the various tiers of the PeopleSoft infrastructure.

• Database server. In this scenario, PeopleSoft Process Scheduler runs directly on the database server. The drawback with this configuration is that it consumes costly database server resources that could otherwise be dedicated to Database.

• Application servers. PeopleSoft Process Scheduler can be collocated with the Tuxedo instances on one or more of the application servers. This is not an ideal configuration because the application servers are memory-intensive processes and it is best to dedicate the servers running the application server to that purpose only.

Note. If the process scheduler is installed on a separate batch server and not on the database server, use a high bandwidth connection such as 1 Gbps between the batch server and database server. If a particular batch process uses extensive row-by row processing, having the process scheduler on the database server may offer increased performance.

PeopleSoft Application Engine Re-Use Capability

2012-09-06T07:47:00.004-04:00

Statement Compilation

When an SQL statement is issued, SQL Server (technically SQL Manager) checks if the statement (an execution plan for the query) is already available in the SQL Server cache. If not, the query must be compiled fully. During the compilation phase SQL Server must check the statement syntactically and semantically, prepare a sequence tree, optimize based on statistics and other criteria, and generate an execution plan. This is referred to as a compile, which is expensive in terms of CPU usage.

Compilation happens when SQL Server parses a query and cannot find an exact match for the query in the procedure cache.

This occurs due to the inefficient sharing of SQL statements, and can be improved by using bind variables (parameters) instead of literals in queries. The number of hard parses can be identified with an Application Engine trace (128).

Use of Bind Variables

The number of compiles can be reduced to one per multiple executions of the same SQL statement by constructing the statement with bind variables instead of literals. Most of the PeopleSoft programs written in Application Engine, SQR, and COBOL have taken care to address this issue.

Application Engine ReUse Option

Application Engine programs use bind variables in their SQL statements, but these variables are specific to PeopleSoft. When a statement is passed to the database, Application Engine sends the statement with literal values. To indicate to the Application Engine program to send the bind variables, enable the ReUse option in the Application Engine step containing the statement

that needs to use the bind variable.

Restrictions on Enabling the ReUse Option

It is acceptable to enable ReUse if %Bind is used to supply a value to a column in a WHERE predicate, SET clause, or INSERT VALUES list.

For example:

UPDATE PS_PF_DL_GRP_EXEC

SET PF_ODS_STATUS = 'C',

PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

WHERE PF_DL_GRP_ID = %Bind(PF_DL_GRP_ID)

AND PF_DL_ROW_NUM = %Bind(PF_DL_ROW_NUM)

Do not enable ReUse if %Bind is used to supply a column name or portion of a table name.

For example:

SELECT DISTINCT KPI_ID

, CALC_ID

, ' '

,KP_CALC_SW

,KP_OFFCYCLE_CALC

FROM PS_%Bind(KP_CALC_AET.KP_KPI_LST1,NOQUOTES)

%Bind(EPM_CORE_AET.FACT_TABLE_APPEND ,NOQUOTES)

WHERE LOOP_CNT = %Bind(KP_CALC_AET.LOOP_CNT)

AND LOOP_PROGRESSION='B'

Do not enable ReUse if %Bind appears in the SELECT list.

For example:

SELECT DISTINCT

%Bind(EPM_CORE_AET.PROCESS_INSTANCE)

, %Bind(EPM_CORE_AET.ENGINE_ID)

, %CurrentDateTimeIn

, 10623

, 31

, 'GL_ACCOUNT'

, ' '

, A.MAP_GL_ACCOUNT

, ' '

, 'LEDMAP_SEQ'

FROM …………

Do not enable ReUse if %Bind is being used to resolve to a value other than a standard Bind value and the contents of the Bind will change each time the statement executes.

For example:

%Bind(GC_EQTZ_AET.GC_SQL_STRING,NOQUOTES)

In this case, the SQL is different each time (at least from the database perspective) and therefore cannot be “reused.”

If NOQUOTES modifier is being used inside %Bind, there is an implied STATIC. For dynamic SQL substitution, the %Bind has a CHAR field and NOQUOTES to insert SQL rather than a literal value. If you enable ReUse, the value of the CHAR field is substituted inline, instead of using a Bind marker (as in :1, :2, and so on). The next time the same Application Engine action executes, the SQL that it executes will be the same as before, even if the value of a static bind has changed.

For example:

INSERT INTO PS_PF_ENGMSGD_S

%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)

(PROCESS_INSTANCE

, ENGINE_ID

, MESSAGE_DTTM

, MESSAGE_SET_NBR

, MESSAGE_NBR

, FIELDNAME1

, FIELDNAME2

, FIELDNAME3

, FIELDNAME4

, FIELDNAME5

, FIELDVAL1

, FIELDVAL2

, FIELDVAL3

, FIELDVAL4

, FIELDVAL5

, SOURCE_TABLE)

……………

Use the %ClearCursor function to recompile a reused statement and reset any STATIC %Bind variables.

Application Engine Bulk Insert Option

By buffering rows to be inserted, specifying a ReUse statement value of Bulk Insert can provide considerable performance boost. PeopleSoft Application Engine offers this non-standard SQL enhancement for Microsoft SQL Server. This feature improves performance only when an SQL INSERT statement is called multiple times in the absence of intervening COMMIT statements.

PeopleSoft Application Engine ignores the Bulk Insert option in the following situations:

• The SQL is not an INSERT statement.

• The SQL is other than an INSERT/VALUES statement that inserts one row at a time. For example, the following statements are ignored: INSERT/SELECT, UPDATE, and DELETE.

• The SQL does not have a VALUES clause.

• The SQL does not have a field list before the VALUES clause.

In these situations, PeopleSoft Application Engine still executes the SQL; it just does not take advantage of the performance boost associated with Bulk Insert

PeopleSoft Dedicated Temporary Tables

2012-09-06T07:38:00.002-04:00

One of the ways to improve scalability and reduce the time taken for processing batch programs is to run multiple instances of the same program on subsets of the data in parallel. For example, instead of processing orders 1 to 1,000,000 in a single instance, run five concurrent instances of the same batch program for 200,000 orders each. When running more than one instance of the program concurrently, the most serious potential problem is data contention, which can result in lock waits and deadlocks.

To reduce contention, PeopleSoft Application Engine provides dedicated temporary tables. These temporary tables are permanent with respect to the Application Engine program definition. Only the data residing in these tables is temporary.

Temporary tables also improve performance when a subset of data from a huge table is referenced multiple times within the program. Temporary tables can:

• Store intermediate data during the process.

• Minimize contention when running in parallel.

• Ensure that each process uses its own temporary table instance.

It is important to allocate the correct number of dedicated temporary tables for your environment. If the temporary tables are under-allocated, the total available instances of a particular temporary table are less than the total number of programs running at one time that use the temporary table. When this condition occurs, the program either uses the base temporary table and continues to run, or it aborts with an error, depending on whether the program property setting If non-shared tables cannot be assigned is set to Continue or Abort, respectively. These options may both be undesirable.

Following are the drawbacks if the program uses the base (non-shared) temporary table:

• Multiple instances may read or write into the same table, causing contention.

• Selecting becomes dependent on ProcessInstance as a leading qualifier.

• DELETE is performed instead of TRUNCATE TABLE. DELETE is far slower than TRUNCATE TABLE.

Important reasons for PeopleSoft to choose regular tables as temporary tables instead of using SQL Server’s temporary tables (#tables) include the following:

• A number of Application Engine programs are restartable. This means that if a program abends in the middle of the run, data stored in the temporary tables is preserved. This enables the program to restart from the last commit point. This is not possible if it uses #tables instead, because they will not be available after the session terminates.

• In SQL Server, this regular form of temporary table offers the same performance as the #tables when they are allocated correctly.

Statistics at Runtime for Temporary Tables

PeopleSoft uses shared temporary tables or dedicated temporary tables in the batch processes. These temporary tables will have few or no rows in the beginning of the process and again few or no rows at the end of the process. Temporary tables are populated during the process, and are deleted or truncated at the beginning or end of the process. Because data in these tables changes so radically, accurate statistics on these tables may help the SQL statements significantly.

Beginning with PeopleSoft 8, if the process is written in PeopleSoft Application Engine, then %UpdateStats meta-SQL can be used in the program after the rows are populated. This ensures that the statistics are updated before the selection happens from that table.

Note. COMMIT is required prior to executing this statement. Make sure to use the COMMIT statement immediately following the previous step. If you do not do so, then this statement will be skipped by Application Engine and will not be executed.

For example, suppose you have the following meta-SQL command in an SQL step of an Application Engine program:

%UpdateStats(INTFC_BI_HTMP)

This meta-SQL issues the following command to the database at runtime:

UPDATE STATISTICS PS_INTFC_BI_HTMP

Make sure the temporary table statistics have been handled as shown above. If you find that statistics created by AUTO UPDATESTATS is sufficient, you can disable %UpdateStats in the program.

Disabling Update Statistics

Update Statistics (%UpdateStats) can be disabled in two ways.

• Program level: Identify the steps that issue %UpdateStats and deactivate them. These steps can be identified by the Application Engine trace (for example, set the trace flag TraceAE=3). This is a program-specific setting.

• Installation level: If there is a compelling reason to disable update statistics for all batch programs, then the installation level setting can be applied to disable %UpdateStats. The following parameter should be set in the Process Scheduler configuration file psprcs.cfg to achieve this:

;-------------------------------------------------------------------------

; DbFlags Bitfield

;

; Bit Flag

; --- ----

; 1 - Ignore metaSQL to update database statistics (shared with COBOL)

DbFlags=1

Note. Carefully consider all the positive and negative effects before setting this flag because it can adversely affect performance.

http://www.mybasicknowledge.com/2012/08/statistics-at-runtime-for-peoplesoft.html

SQL SERVER Network Protocols and Pagefile and PeopleSoft Application Recommendation

2012-09-06T07:37:00.002-04:00

SQL Server 2005 supports the following network protocols:

• TCP/IP

• Named Pipes

• Virtual Interface Adapter (VIA)

• Shared Memory

Configuring the correct network protocol is critical from a performance and stability perspective. The following section discusses recommendations for configuring network protocols for PeopleSoft applications.

For TCP/IP, data transmissions are more streamlined and have less overhead than Named Pipes. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms, such as windowing, delayed acknowledgements, and so on. This can be very helpful in high network traffic scenarios. For PeopleSoft applications, such performance differences can be significant. For best performance, install TCP/IP on the server and configure SQL Server TCP/IP to communicate with clients.

The Named Pipes network protocol can be used only when the application server or process scheduler is running on the same physical computer as the database engine. For the application to use Named Pipes as the first choice network protocol, make sure that in the Client Configuration section of SQL Server Configuration Manager that Named Pipes has a higher order than TCP/IP. Ensure that SQL Server uses Named Pipes in addition to TCP/IP. For this to work, you must also configure native ODBC connections to use Named Pipes.

The VIA (Virtual Interface Adapter) protocol works only with specialized VIA hardware. If you are using VIA hardware, enable this protocol. If you do not have VIA hardware, keep this protocol disabled. The default is disabled.

Shared Memory is a non-routable protocol and is not useful for PeopleSoft applications.

SQL Native Client

SQL Server 2005 introduces a new data access technology called SQL Native Client (SNAC). SQL Native Client contains the SQL OLE DB provider and SQL ODBC driver in one native dynamic link library (DLL) supporting applications using native code APIs (ODBC, OLE DB, and ADO) to Microsoft SQL Server. It is recommended to use the SQL Native Client rather than MDAC for data access to SQL Server 2005 for PeopleSoft applications.

SQL Native Client provides access to new features of SQL Server and also provides full backward compatibility to ODBC and OLE DB.

For PeopleSoft applications, SQL Native Client provides access to new features such as database mirroring and also provides some performance optimizations.

To configure SQL Native Client, navigate to the ODBC Data Source Administrator in the Control Panel. Select SQL Native Client as the new data source driver and configure the data source.

SQL Server Configurations and PeopleSoft Application Recommendation

2012-09-06T07:35:00.002-04:00

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.

SQL SERVER Database Options and PeopleSoft Application Recommendation

2012-09-06T07:24:00.005-04:00

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 = <YourDatabaseName>

A value of 1 for is_auto_create_stats_on indicates that the auto create statistics option is enabled.