ServiceNow has a plethora of debugging tools available on the instance which helps ServiceNow admins and technical support engineers troubleshoot performance issues and narrow down the cause. Out of all the options ServiceNow offers, the SQL Debugger is the most powerful and helps admins find out all queries involved in a particular transaction. It can be used for both MySQL as well as Oracle based queries which are the relational database query languages available for ServiceNow instances. It empowers you to find the slowness based on the queries and using the explain plan tool create the appropriate indexes to help improve the performance of the application.
There are two types of of SQL Debugging tools:
- Debug SQL
- Debug SQL (Detailed)
Debug SQL vs. Debug SQL (Detailed)
The Debug SQL option lists all the queries involved but doesn't give the stack trace from where the query is originating as opposed to Debug SQL (Detailed) which does give you the Java stack trace with a "+" symbol. This primarily is the only difference between the two.
| SQL DETAILED |
|---|
 |
The MySQL debugger is only valid till your session exists. If you logout it automatically turns the debugger off. It is not necessary that you need to logout or wait for the session to timeout. Once done if you want to continue using the application it can be manually turned by navigating to System Security and clicking on Stop Debugging. If one of your users is experience unusual slowness, admins can turn on the SQL Debugger and then impersonate the user to investigate the queries involved in the transaction for that particular user transaction.
Impersonating a user to investigate queries for a particular user transaction
We impersonate a user experiencing performance issues to see find the specific form, incident or transaction that could be causing slowness. In the Application
navigation filter type in Debug SQL. Click on either of the options to turn the MySQL Debugger on. You can impersonate any user after this and navigate to a form or list to the see a list of queries being executed.
|
For example: On trying to open the incident list, it took more then 180 seconds to render. This is reproducible every time and no other list is affected. To determine if the slowness lies on the database, we need to find the slow database query(s) or the source from where it's originating from. There were approximately 3,800 insert queries that were loading with the list. Upon clicking the SQL Debugger (detail) to find the source, we see it is the the business rule. These queries were running against the incident table which was recursively adding gs.log() statements for every record being pulled, causing the slowness. |
If a long running database query is responsible for the slowness, then you can use the explain plan to determine if there is a way to optimize it using an index. Cases where performance degradation is the underlying source (script includes and business rules) the queries can be identified and the appropriate steps or plan of action can be taken to rectify it to improve the performance of the application. The SQL debugger is a great must-use-tool recommended for troubleshooting performance of many ServiceNow applications. There are certain applications which do not use the SQL debugger such as the Visual Task Boards (VTB), ServiceNow support can help you out with that.
Thank you john.gonzalez for the idea to write this post!
