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.
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.
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:
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:
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.