I have an intermittent issue explained below which I’m hoping someone in the community can advise on:
SAS Application: SAS Marketing Automation 9.3 (Customer Intelligence Studio 5.41)
DBMS: Teradata 14.00.04.07
Occasionally Marketing Automation applications (specifically CI Studio as this is the most heavily used on site) will freeze when running DB queries, upon investigation the root cause is found to be excessive (Idle) database sessions attributed to the SAS user. The number of database sessions (spawned by SAS) encroaches on the limit of our Teradata environment impacting processing times severely. The issue is resolved by killing the idle sessions (DBA task) or restarting the SAS application servers.
A SAS track has been raised, and my own investigations are focusing on the nature of the queries/sessionscreated. The SAS environment is used primarily to run scheduled CI campaigns; both campaigns and the associated schedule/batch change very little and run daily without problems, but this issue will crop up intermittently. This leads me to think that the issue may lie with how the application is managing sessions/DB queries rather than the queries themselves. I’d be grateful if you could offer any advice on the following:
Which SAS process/processes manage opening/closing of DB session and how to track this on the application servers/Management Console or similar?
Settings or configuration elements worth reviewing?