1. First, it is needed to know which page is loading slow. It might already be known as per the Steps to Reproduce the issue, however, if not then load the page and look for the value present in the URL for id param (e.g. /sp?id=list)
2. Once the page is known, find out how much time it is taking for the page to load. This can be checked on the browser dev tool's Network tab. This will tell how much time is taken on what aspect.
In the below screenshot form a use case, notice the total time taken is 5.43 s. This is mostly taken by 'Waiting' time ~4 s.
If the 'Waiting' time is taking the most of the time, this means that the response from the server is more.
3. Once it is determined that server time is taking more, it's time to see which server functionality is taking the most. There could 3 scenarios, a. Slow running ACL, b. Slow Running Business Rules or c. Slow running SQL queries.
4. Now enable System Diagnostics > Session Debug > Debug Log first, to know which of these are taking most of the time. Based on that we can enable more session debugging.
5. When the debug log is enabled, reproduce the issue and try to get the logs on the jsdebugger window or on a ui_page.do. If this transaction has taken more time, similar to below log will appear. The keyword is 'EXCESSIVE', so it can be searched with command+F
log05:15:20 AM.771 EXCESSIVE *** End #2298398 /api/now/sp/page, user: admin, total time: 0:00:06.484, processing time: 0:00:06.484, SQL time: 0:00:05.209 (count: 887), business rule: 0:00:00.001 (count: 6), ACL time: 0:00:00.001, Cache build time: 0:00:00.007, source: 103.23.66.61 , type:rest, method:GET, api_name:now/sp, resource:now/sp/page, version:Default, user_id:admin, response_status:200
6. In this log, it can be seen, which component has taken the most time. For this use case, the total time was ~6s. This was distributed as SQL time= 5s, Business Rule= 1ms and ACL= 1 ms. So it is evident that the culprit here is the SQL queries, which is taking the most time.
7. Once the component is known, then look for the specific transaction's specific function. Remember to note the Request URL, which initiated this transaction. TThis use case it is an API call, /api/now/sp/page, the same can be seen in the network logs.
8. Enable the sessing debug of that component. In this case, enable Debug SQL (detailed). One reason to enable the detailed log is, this will show the trace of the queries. Basically it will provide the sys_id of the widget from where this slowness came.
9. As the SQL debug is enabled, reproduce the issue, and then reload the ui_page.do to get the new logs.
10. After this, search with the API name and look for the transaction name under Queries as shown in the below screenshot. This will give an explanation of how much time is taken by all the queries, per-transaction basis and which query took what percentage of the time. That way, it becomes clearer to know any time-consuming query.
11. As the time-consuming query is discovered (OOB shows the long-running one at the top), search for the query in the logs below. Copy the end part of the query to search, as the selection fields are usually hidden in the SQL statements. If it is searched, it will bring to the exact SQL entry logs, and the SQL log can be expanded to see which widget is causing this.
12. Now analyze the Widget, and based on that modify the query.
This is a situation when it is caused by the slowness on Query. If it was found that it was caused by either Business Rule Or ACL, enable the respective session debug. Those will show the respective transaction taking the most of the time. Those can then be investigated and given a solution accordingly.