Blog

Created on Friday, 16 September 2005 14:45

Written by Larry Grey

As I was setting up a meeting with a large PeopleSoft customer (for Open World), we talked about issues they're having with their solution for handling rogue PS/Queries. For customers running PS/Queries using the internet architecture, this can be a problem (but isn't unique to that release). Unfortunately, the solution they were using was also affecting their Crystal and nVision reports that used queries.

What are Rogue Queries?

Rogue Queries are queries that are started, but never seem to complete. Because each process in PIA takes up an app server process (whether PSAPPSRV or PSQRYSRV), this locks up valuable server resources (because PeopleTools isn't threadsafe, the way to scale up is to have many app server processes running). It also takes resources from the database server.

Rogue Queries are most often created by users who kick off a long-running PS/Query and then close the browser window. The query continues to run, even though there's nothing waiting for the results any more.

How do you handle Rogue Queries?

Prior to PeopleTools 8.44, there wasn't a clean way to handle them. Many customers would use the PSQRYSRV (which is a specialized PeopleSoft application server that can be used to handle queries), and then kill the process periodically if it runs too long. Although this seems to address the problem, it can cause other issues:

1. There can still be a thread running on the database server related to the SQL and returning the data, even though there's no app server process to receive it (therefore, the roque query is still there on the database server).
2. If you're running nVision reports or Crystal reports in 3-tier (i.e. using PSQED or Crystal designer on the client connecting through the application server instead of directly to the database server), those reports can get killed inadvertently.

Another approach for preventing rogue queries is to use the process scheduler to run queries versus running them online. With the "Run to Window" functionality in PeopleTools 8.42, you can make it seem like the queries are being run on the app server, even though they are being scheduled to run "right now" on the process scheduler server. Processes are much easier to identify and kill on the process scheduler, and a rogue query running on the process scheduler server will not lock any of the app server processes.

The best solution, however, is to use PeopleTools 8.44. In this release, a lot of thought was put into this issue, and PeopleSoft provided a solution to this problem at two levels:

1. The query permissions list was extended with the ability to restrict the amount of time (or number of rows) a query can run or return.
2. The query monitor was enhanced and renamed to the Query Administrator. It allows an administrator to identify rogue queries currently running, kill them, and disable queries from being run in the future.

The limits in the permission list will cause any rogue queries to automatically be killed, regardless of what is running them (except for 2-tier running of queries, i.e. PSQED or PSNVS). The killing of the queries will kill the thread on the database as well as the app server. For the people with nVision design access connecting through an application server, one can bump up these time limits (or make them unlimited). If you create a rogue query when running nVision reports, you can use the query administrator to find the query (it has your user ID, process name, and amount of running time listed), and kill it from there.

Labels: Performance, Query