Foglight for PeopleSoft 5.6.11 - User and Reference Guide

Setting the PS_MSSQL Server Filter Parameters

Use the SQL Server SQL Criteria properties to set the SQL Server Filter properties.

Go to the SQL Server SQL Criteria set of properties.

Select where this PS_MSSQL Agent is being set up from on the Select server to run on list. The agent can be set up either on a server where PeopleSoft application server domain(s) are configured or on the MSSQL Server 2000 database server. If the application server domain and database are on the same box, choose AppServer.

Click the Enable SQL Criteria Filters radio button, True or False, to turn on the data filtering. When this field is set to true, only data satisfying the filter criteria are collected in the UserData table. This criteria can be used to identify and collect I/O intensive SQL statements that are executed by the PeopleSoft application users.

Enter values for the CPU (%) Thresholds and I/O (%) Thresholds in percentage.

If the Enable SQL Criteria Filters is set to True, enter a value for each to determine an I/O-intensive SQL statement. Only PeopleSoft database users executing a SQL statement or stored procedure exceeding the above threshold values are collected for the collection interval.

The default value for both thresholds is zero percent.

Click the Log SQL Output to Log File radio button, True or False, to output the SQL statement or stored procedure to a log file for review.

If the Log SQL Output to Log File option is set to True, enter the path and log file name to which you want the SQL statement or stored procedure written in the Log File Name (full path) text box.

Click Save.

Investigating Database Usage and PeopleSoft User Intensive SQL Statements

The PS_MSSQL Agent provides database usage statistics by measuring the number of active and inactive database users and identifies resource intensive SQL statements or stored procedures executed by PeopleSoft users.

The PS_MSSQL Agent also monitors SQL activity for PeopleSoft users who execute a SQL statement or stored procedure during a sampling period. These statistics let you isolate problem SQL statements or stored procedures based on CPU consumed, I/O consumed, buffer gets, disk reads, or rows processed. The PS_MSSQL Agent also lets you output the problem SQL statement or stored procedure to a log file for any further analysis of PeopleSoft user activities in the database. Using the Data Browser, you can also review SQL activity for each PeopleSoft user.

To start your investigation with the PS_MSSQL_PID_Overview graph view:

•

Go to the PS_MSSQL_PID_Overview graph view which displays the Percentages of Total CPU or Total I/O by the PeopleSoft application server process PID (system Process ID). This graph provides an indication of which PIDs are the most resource intensive.

To view more detailed information:

•

Drill down on any bar to view the details about the Microsoft SQL Server session corresponding to the PeopleSoft application server process PID, including the associated PeopleSoft Application user and SQL metrics, with the PS_MSSQL_PID_Detail table view.

PS_OracleSQL Agent

The PS_OracleSQL Agent provides a breakdown of database user activity and the statistics necessary to identify usage trends for the PeopleSoft database users, as well as active and inactive PeopleSoft application users. The PS_OracleSQL Agent also monitors any poorly performing PeopleSoft-related SQL activity. The PS_OracleSQL Agent identifies the PeopleSoft application users and links them to the SQL statement that they are issuing through the application server process. It also identifies important database metrics for the SQL statements as well as PeopleSoft application server domain metrics.

PS_OracleSQL Agent Properties

When an agent connects to the Foglight Management Server, it is provided with sets of properties that it uses to configure its correct running state.

Each agent is provided with a combination of two types of properties: agent properties and shareable properties.

Default versions of these properties are installed with the Foglight for PeopleSoft. However, you can edit the default shareable and agent properties, configure agent properties that apply only to a specific agent instance, and create edited clones of shareable properties that are used by a subset of the agents of a certain type.

For more information about working with agent properties, see the Foglight Administration and Configuration Guide.


	NOTE: EnableDBMonitoring should be set to one in the PeopleSoft application server domains in order to gather complete information for the PS_OracleSQL UserData table.