Extreme lag in server response - anyone else seeing this?

[kristenankeny]

For about a week now we have been seeing extremely slow server responses in our dev (where we are developing a project) and production (where people are working) instances. We haven't seen anything in test, but mostly because almost nobody is touching it at this time because we have all hands on deck in dev. That said, I started seeing the issue in Dev working with workflows. I reported this to ServiceNow HI and received a response that there is a known issue with Orchestration plugin installed and loading workflows and a resolution timeframe is currently unknown. Once I was done working on the workflows for our project, I started focusing on debugging the project. I'm getting connection time out on our system log 9 times out of 10, especially if I attempt to filter at all. My team is seeing lags in the client side updating with the current step in a workflow (for example, they submit a form that has no approvals an moves immediately to task creation, while the workflow shows it completed the request workflow and requested item workflow (to the point of task creation) in under a minute, the server is taking 10 minutes to send us that information. Reloading the ritm does not grab the new information. I have ServiceNow HI looking at this (they've split it into two tickets despite us reporting that it's systemic). I haven't received any update on why the logs are timing out and we were told to upload the update set on this post to fix the workflow issue (though I'm confused as to why since we aren't using service portal and this seems to be focused on allow UI Portal functionality to work within Service Portal). In production, I've just received the general feedback that our help desk is frustrated with slow response times on the tickets they are working (incidents and tasks) - I have not asked for specific scenarios or tickets yet. I personally went into production to check the information on a record in the cmdb_ci_appl table and it took 20 seconds for the server to respond.

Has anyone else been having problems with server response on their instances? Or perhaps had this in the past and have a clue as to what the issue might be?

[Jon Barnes]

It is possible that this could be caused by a noisy neighbor, or another customer that shares hardware with your company, but this is unlikely. It is more likely that something in your application needs to be tuned.

I would start by looking at the following tables and troubleshooting from there:

Slow Queries. Sort descending by Average Execution time and see which queries are taking so long. If there are some smoking guns here, ask HI to help you optimize with indexes if possible. It may not be possible if they are poorly written reports or code, and in those cases, you would have to tweak the reports/code:

/sys_query_pattern_list.do%3Fsysparm_userpref_module%3D9d967a108f30300072a89cfde0f92389%26sysparm_clear_stack%3Dtrue%26sysparm_clear_stack%3Dtrue%26sysparm_clear_stack%3Dtrue%26sysparm_clear_stack%3Dtrue

This list will tell you the DB queries that are taking the longest.

Also have a look at this list. This is the transaction log. It is a big table so be patient. I would filter it out to only look at transactions taking longer than 10 seconds as a starting point. Sort descending on Response time, but be sure to also look at Semaphore Wait time and Session Wait time, because those mean they were waiting for a semaphore or another session transaction to complete.

/syslog_transaction_list.do?sysparm_query=sys_created_onONToday%40javascript%3Ags.daysAgoStart(0)%40javascript%3Ags.daysAgoEnd(0)%5Eresponse_time%3E10000

There should be clues in these tables as to what is taking so long and then you can go from there to investigate bad queries, need for new indexes, etc.