Symptoms

Issue:
The client experienced very poor performance and timeout errors when opening their forms.
 

Diagnoses

Diagnosis:

We had a look at running processes in the server.procinst table no processes where in the running state for more than couple of seconds.
We select from the server.ipcasync table but they have no ipc processes as well as async table and we only found 9 entries in the server. Async Table.
We ran a F12 network trace on the form that the clients get the time out on we saw there are ajax calls that are executing extremely slow.
We checked the task manager and found that the server differentiates between 15 and 60% CPU usage and k2 memory usage is 1 GB.
Tested the forms speed by opening the simplest form on the k2 blackpearl server Checked response or load time the load time was bit excessive and no data was returned for the most used forms.
Opened workspace and found it opens fast and responsive.
We then open from the management console workflow processes and checked the timing which seemed normal checked the amount of instances on one process. The returned amount was 6 pages from the 5 dec 2016 onward which did not seem excessive.
We went back to the forms.
We found that all of the forms in question is using custom database with custom stored procedures.
Ran SQL traces on the custom DB and found that there are some expensive queries being executed constantly attached is a SQL trace of the custom Database.
We Accessed the Runtime/Runtime site and loaded in milliseconds.
We Accessed the manage issuer form and did take quite some time to load 20 seconds
We ran the third party Stored procedure and it took 9:50 to execute.
Accessed the administration page and then navigated to form type registration and loaded about 15 sec.
We then open user management and found it loaded quickly.
Asked the client if all data is coming from the custom database and he confirmed.

The Stored procedure was more responsive bit later on round about lunch time and when this happened all the forms became more responsive
 

Resolution

Resolution:

The client altered the stored procedure in question to return 0 rows and found that the forms were working as expected again and no timeout errors occurred