Skip navigation

Developer Community

3 Posts authored by: Iris Geist Employee

POSTMAN is an extension app that can be downloaded and installed from the Chrome Web Store. It is used to test REST APIs, check the input and the output for REST methods, and view the Code used to run the request. This post is the extension of Testing REST web services through Firefox RESTClient or POSTMAN. I will describe how to use POSTMAN, and as requested will add information on how to post an attachment.

 

The look and feel is very similar to the Firefox RESTClient:

POSTMAN.jpg

Learn more about POSTMAN for sending requests

 

How to retrieve data from a ServiceNow instance

One common test we run, is using GET to retrieve data from ServiceNow. You will run GET methods to retrieve data programmatically from ServiceNow into another tool, where it can be used for processing or reporting. The URL for the GET method can most easily be retrieved from the REST api explorer in the instance:

https://<instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1

 

Each request will have a Header, where, for example authorization and content formatting information can be found, and a body, where the individual attributes will be filled.

 

Once the URL is entered, the authorization headers have to be filled:

postman authorize.jpg

Typically, we use Basic Auth, which is using userID and password, but the OAuth authentication method is also supported by  ServiceNow. I save the request, once I have the basics set up, to make it easier to tweak and retest.

 

When clicking the Send button, we can view the request

https://instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1&sysparm_offset=49

 

With all the additional header information:

Cache-control →no-cache,no-store,must-revalidate,max-age=-1

Content-Encoding →gzip

Content-Type →application/json;charset=UTF-8

Date →Sun, 28 May 2017 17:10:18 GMT

Expires →0

Link →<https://<instance>.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1&sysparm_offset=0>;rel="first",<https://instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1&sysparm_offset=-1>;rel="prev",<https://instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1&sysparm_offset=1>;rel="next",<https://instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1&sysparm_offset=49>;rel="last"

Pragma →no-store,no-cache

Server →ServiceNow

Strict-Transport-Security →max-age=63072000; includeSubDomains

Transfer-Encoding →chunked

X-Is-Logged-In →true

X-Total-Count →50

 

POSTMAN header response result

postman get.jpg

 

Other basic POSTMAN features

  1. When clicking on Code on the upper right of the application, you get a sample code string:

    GET /api/now/table/incident?sysparm_query=active%3Dtrue&amp;sysparm_display_value=true&amp;sysparm_limit=1 HTTP/1.1

    Host: instance.service-now.com

    Authorization: Basic YWRtaW46QWtsYXJhMjAxMi8v

    Cache-Control: no-cache

    Postman-Token: 0d9471c9-26f8-715a-2b81-c01c113087f0

     

    That shows you detail on how POSTMAN executed the GET command.

  2. When clicking on Params to the right of the URL, you can enter the URL parameters as key value pairs in a table, or check the parameters currently set:

    sysparm.jpg

  3. POSTMAN also makes troubleshooting easier with the copy to clipboard and search functions, with which you can quickly find a key value pair that you want to analyze deeper.

 

Create a new Incident with POSTMAN

Now that we have the basics covered, let's use postman to create a new incident in your ServiceNow instance. To create an incident, we will use the POST method.

 

  1. First, you will change the method to POST, and enter the URL similar to this:

    https://instance.service-now.com/api/now/table/incident?sysparm_display_value=true

  2. Then, you will have to set up the Authorization again, same as you did for the GET method.
  3. Next, set the correct content-type:

    authorization.jpg

  4. Finally, the fields to fill in for your new ticket are entered in the Body tab using Key - Value pair notation in raw format:

    json.jpg

  5. Once you have all fields filled in that you want to fill with values other than default values, you can (first save and then) send the request:

    {

      "result": {

        "parent": "",

        "made_sla": "true",

        "caused_by": "",

        "watch_list": "",

        "upon_reject": "Cancel all future Tasks",

        "sys_updated_on": "28.05.2017 07:37:53 PM",

        "child_incidents": "0",

        "approval_history": "",

        "skills": "",

        "number": "INC0010091",

        "resolved_by": "",

        "sys_updated_by": "admin",

        "opened_by": {

          "display_value": "System Administrator",

          "link": "https://instance.service-now.com/api/now/table/sys_user/6816f79cc0a8016401c5a33be04be441"

        },

        "user_input": "",

        "sys_created_on": "28.05.2017 07:37:53 PM",

        "sys_domain": {

          "display_value": "TOP/MSP/Default",

          "link": "https://instance.service-now.com/api/now/table/domain/09ff3d105f231000b12e3572f2b4775d"

        },

        "state": "Active",

        "task_for": "",

        "sys_created_by": "admin",

        "knowledge": "false",

        "order": "",

        "calendar_stc": "",

        "closed_at": "",

        "delivery_plan": "",

        "impact": "3 - Low",

        "active": "true",

        "work_notes_list": "",

        "business_service": "",

        "priority": "5 - Planning",

        "sys_domain_path": "!!!/!!#/!!!/",

        "rfc": "",

        "time_worked": "",

        "expected_start": "",

        "opened_at": "28.05.2017 07:37:53 PM",

        "business_duration": "",

        "group_list": "",

        "work_end": "",

        "caller_id": "",

        "resolved_at": "",

        "approval_set": "",

        "subcategory": null,

        "work_notes": "",

        "short_description": "This is a test ticket",

        "close_code": null,

        "correlation_display": "",

        "delivery_task": "",

        "work_start": "",

        "assignment_group": "",

        "additional_assignee_list": "",

        "business_stc": "",

        "description": "",

        "calendar_duration": "",

        "close_notes": "",

        "notify": "Do Not Notify",

        "sys_class_name": "Incident",

        "closed_by": "",

        "follow_up": "",

        "parent_incident": "",

        "sys_id": "29c05d89134fb200ec5450f32244b0b4",

        "contact_type": null,

        "incident_state": "New",

        "urgency": "3 - Low",

        "problem_id": "",

        "company": "",

        "reassignment_count": "0",

        "activity_due": "UNKNOWN",

        "u_some_ne_wfield": "",

        "assigned_to": {

          "display_value": "Beth Anglin",

          "link": "https://instance.service-now.com/api/now/table/sys_user/46d44a23a9fe19810012d100cca80666"

        },

        "severity": "3 - Low",

        "comments": "",

        "approval": "Not Yet Requested",

        "sla_due": "UNKNOWN",

        "comments_and_work_notes": "",

        "due_date": "",

        "sys_mod_count": "1",

        "reopen_count": "0",

        "sys_tags": "",

        "escalation": "Normal",

        "upon_approval": "Proceed to Next Task",

        "correlation_id": "",

        "location": "",

        "category": "Inquiry / Help"

      }

    }

 

Create an new Incident with attachment using POSTMAN

Now that we have all the simple steps figured out, let's send a new ticket with an attachment. Often times an attachment helps the viewer of the incident with screen shots or specific examples, enabling a faster resolution of the issue.

 

I first checked, how an attachment is sent with REST API explorer: Attachment API

 

Then I translated that information into how to do it with POSTMAN:

  1. The URL to use:

    https://instance.service-now.com/api/now/attachment/file?table_name=<ServiceNow dictionary name>&table_sys_id=<SysID of the record that should hold the attachment>&file_name=<name to give the file once loaded>

    • Example Headers:

      postman header.jpg

      Files need to be of content-type multipart/form-data

    • Example Body:

      postman body.jpg

      As you can see, POSTMAN allows for two types of form-data: Text for any clear text you want to pass, and File for attachments. Once you choose File, you can upload any file through the Choose Files button.

  2. Save and Send the request and the attachment will be created.

    postman servicenow.jpg

    Several images for each attachment I tried for this blog post is attached to this post below - one for each "Send" I performed.

Attachment handling is not done through the incident (or other table) API, but in a separate step. If you are writing code to create an incident that should contain an attachment, you will first create the incident, then return the sys_id of this incident to call the attachment API with that information.

 

I hope this information helps - please let me know if you are interested in more detail, and we can expand as needed.

When testing APIs (Application Interfaces), it is always very helpful to get an independent view of the interfacing application. For example, if you are interfacing two ServiceNow instances, and it is not working as expected - how will you know where the error is - the source or the target of the integration? In this case, you will employ a 3rd party tool, such as the Firefox RESTClient, or Chrome's POSTMAN to test the REST integration. You can build tests to verify functionality such as POSTMAN.

 

For SOAP integrations, we typically use SOAP UI, which is already documented very well in Mini-Lab: Web Services – Part 1: Using SoapUI to Test ServiceNow WSDL. I will give you details on how to use RESTClient and POSTMAN for REST integrations with ServiceNow.

 

Let's start with the RESTClient, that can be downloaded into your Firefox browser as a plugin. For REST integrations, you will have a URL and a method. The standard methods are automatically available in the REST client including: GET, POST, DELETE, OPTIONS,HEAD, TRACE, and CONNECT.

rest integrations1.jpg

You can read up on these methods (also called HTTP verbs) here: Hypertext Transfer Protocol - Wikipedia

 

How to retrieve a record from your Instance

To retrieve a record from your ServiceNow instance, you now have to do the following:

  1. Enter your URL (hint: look up the URL in your instance's REST API Explorer):

    rest api explorer1.jpg

  2. You can click for example on cURL to get the URL:

    curl "https://instance.service-now.com/api/now/table/incident?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1" \

    --request GET \

    --header "Accept:application/json" \

    --user 'user':'password'

  3. In RESTClient, this will translate to:

    restclient1.jpg

The URL can be copied from the curl URL above.

 

The Content-Type header is added through the menu item Headers > Custom Headers and then filled as shown above, the Authorization Header is added through the menu item Authentication > Basic Authentication (add user name and password).

 

Other possible header values are found here: Supported REST API headers  and other possible Authorization methods can be found here: REST API security

 

The response is in the JSON (Key-Value-Pair) format.

{

"result":

[

{

"parent": "",

"made_sla": "false",

"caused_by": "",

"watch_list": "",

"u_mobilecategory": null,

"u_alternate_phone": "",

"upon_reject": null,

"sys_updated_on": "2016-12-08 08:03",

"child_incidents": "",

"approval_history": "",

"skills": "",

"number": "INC0000002",

"resolved_by": "",

"sys_updated_by": "system",

"opened_by":

{

"display_value": "Joe Employee",

"link": "https://instance.service-now.com/api/now/table/sys_user/681ccaf9c0a8016400b98a06818d57c7"

},

"user_input": "",

"sys_created_on": "2011-03-25 15:30",

"sys_domain":

{

"display_value": "global",

"link": "https://instance.service-now.com/api/now/table/sys_user_group/global"

},

"state": "New",

"sys_created_by": "pat",

"knowledge": "false",

"order": "",

"u_cicat": "",

"calendar_stc": "",

"closed_at": "",

"cmdb_ci": "",

"delivery_plan": "",

"contract": "",

"impact": "1 - High",

"active": "true",

"work_notes_list": "",

"business_service": "",

"priority": "1 - Critical",

"rfc": "",

"time_worked": "",

"expected_start": "",

"rejection_goto": "",

"opened_at": "2012-08-28 16:07",

"u_eriktest": "",

"business_duration": "",

"group_list": "",

"work_end": "",

"caller_id": "",

"resolved_at": "",

"approval_set": "",

"subcategory": "Operating System",

"wf_activity": "",

"work_notes": "2013-04-04 08:00 - Service-now: Person [maint,admin,itil] (Work notes) test  2013-04-04 07:57 -

"short_description": "Can't get to network file shares",

"close_code": null,

"correlation_display": "",

"delivery_task": "",

"work_start": "",

"assignment_group":

{

"display_value": "Software",

"link": "https://instance.service-now.com/api/now/table/sys_user_group/8a4dde73c6112278017a6a4baf547aa7"

},

"u_eriktest2": null,

"additional_assignee_list": "",

"business_stc": "",

"description": "User can't get to any of his files on the file server.",

"calendar_duration": "",

"u_eriktest3": "",

"close_notes": "",

"notify": "Do Not Notify",

"sys_class_name": "Incident",

"closed_by": "",

"follow_up": "",

"parent_incident": "",

"sys_id": "9d385017c611228701d22104cc95c371",

"u_cisubcategory": "",

"contact_type": "Phone",

"incident_state": "Awaiting Problem",

"urgency": "1 - High",

"problem_id":

{

"display_value": "PRB0000007",

"link": "https://instance.service-now.com/api/now/table/problem/9d3a266ac6112287004e37fb2ceb0133"

},

"company": "",

"reassignment_count": "2",

"activity_due": "2016-12-08 10:03",

"assigned_to":

{

"display_value": "Howard Johnson",

"link": "https://instance.service-now.com/api/now/table/sys_user/46ca0887a9fe19810191e08e51927ebf"

},

"severity": "1 - High",


            {

                "display_value": "Salem OR",

                "link": "https://empigeist2.service-now.com/api/now/table/cmn_location/108486c7c611227500b093211aa88dcc"

            },

            "category": "software"

        }

    ]

}

 

Now that we know how to select a record or set of records, next we will create a new record in ServiceNow using the POST method:

POST method1.jpg

 

The URL is the simple REST URL as documented, the Content Type and Authorization are the same as used in the GET method. The difference is in the Body - here we will fill the fields - in key value pairs defined by the JSON format. For example:

{"active":"true","category":"software","caller_id":"Abel Tuter","short_description":"REST POST DEMO"}

Will fill in the active, category, caller, and short description for the new incident.

 

The response shows the fields within the newly created record:

{

    "result":

    {

        "parent": "",

        "made_sla": "true",

        "caused_by": "",

        "watch_list": "",

        "u_mobilecategory": "",

        "u_alternate_phone": "",

        "upon_reject": "cancel",

        "sys_updated_on": "2017-03-23 19:12:16",

        "child_incidents": "0",

        "approval_history": "",

        "skills": "",

        "number": "INC0010098",

        "resolved_by": "",

        "sys_updated_by": "admin",

        "opened_by":

        {

            "link": "https://instance.service-now.com/api/now/table/sys_user/6816f79cc0a8016401c5a33be04be441",

            "value": "6816f79cc0a8016401c5a33be04be441"

        },

        "user_input": "",

        "sys_created_on": "2017-03-23 19:12:16",

        "sys_domain":

        {

            "link": "https://instance.service-now.com/api/now/table/sys_user_group/global",

            "value": "global"

        },

        "state": "1",

        "sys_created_by": "admin",

        "knowledge": "false",

        "order": "",

        "u_cicat": "",

        "calendar_stc": "",

        "closed_at": "",

        "cmdb_ci": "",

        "delivery_plan": "",

        "contract": "",

        "impact": "3",

        "active": "true",

        "work_notes_list": "",

        "business_service": "",

        "priority": "5",

        "rfc": "",

        "time_worked": "",

        "expected_start": "",

        "rejection_goto": "",

        "opened_at": "2017-03-23 19:12:00",

        "u_eriktest": "",

        "business_duration": "",

        "group_list": "",

        "work_end": "",

        "caller_id":

        {

            "link": "https://instance.service-now.com/api/now/table/sys_user/62826bf03710200044e0bfc8bcbe5df1",

            "value": "62826bf03710200044e0bfc8bcbe5df1"

        },

        "resolved_at": "",

        "approval_set": "",

        "subcategory": "",

        "wf_activity": "",

        "work_notes": "",

        "short_description": "REST POST DEMO",

        "close_code": "",

        "correlation_display": "",

        "delivery_task": "",

        "work_start": "",

        "assignment_group":

        {

            "link": "https://instance.service-now.com/api/now/table/sys_user_group/287ee6fea9fe198100ada7950d0b1b73",

            "value": "287ee6fea9fe198100ada7950d0b1b73"

        },

        "u_eriktest2": "",

        "additional_assignee_list": "",

        "business_stc": "",

        "description": "",

        "calendar_duration": "",

        "u_eriktest3": "",

        "close_notes": "",

        "notify": "1",

        "sys_class_name": "incident",

        "closed_by": "",

        "follow_up": "",

        "parent_incident": "",

        "sys_id": "207d25b313a9b200d57c50f32244b07b",

        "u_cisubcategory": "",

        "contact_type": "phone",

        "incident_state": "1",

        "urgency": "3",

        "problem_id": "",

        "company":

        {

            "link": "https://instance.service-now.com/api/now/table/core_company/227cdfb03710200044e0bfc8bcbe5d6b",

            "value": "227cdfb03710200044e0bfc8bcbe5d6b"

        },

        "reassignment_count": "0",

        "activity_due": "",

        "assigned_to": "",

        "severity": "3",

        "comments": "",

        "u_categorytier3": "",

        "u_sla_start_time": "",

        "approval": "not requested",

        "sla_due": "",

        "comments_and_work_notes": "",

        "due_date": "",

        "sys_mod_count": "0",

        "reopen_count": "0",

        "sys_tags": "",

        "escalation": "0",

        "upon_approval": "proceed",

        "correlation_id": "",

        "location": "",

        "category": "Software"

    }

}

 

The caller, which we entered by its display name of "Abel Tuter" is stored as a reference with the user record's sys_id, and default values that were filled via dictionary or business rules are also filled in.

 

Using external tools to validate your REST request and response is extremely helpful when troubleshooting REST integrations. You can compare your request and the response you receive within the external tool, to the results you expect in ServiceNow. These tools allow you to independently test both applications within an integration to ensure they are producing the expected results.

 

So, in a nutshell, that is how to use the RESTclient plugin. I am looking forward to providing a deep dive into the other frequently used external tool - POSTMAN (coming soon).

Since Fuji, administrators can now add indexes to their own instances as needed. Fellow support agent billt wrote in detail how you can Improve your ServiceNow instance performance by creating database indexes via the User Interface. To expand on this function, I want to focus on what you can do if you have a slow query in the slow query log and want to know how to make this specific query faster.

 

A note of caution though - adding too many indexes, even if they work well for a query - can be detrimental to the table's overall performance. Always feel free to consult with your support engineers to determine if adding the new index will be beneficial.

 

 

Example of a slow query log

First you need to analyze the Slow Queries log under System Diagnostics and to do that, you will need to be able to interpret all the information given in every log entry. I have used my instance as an example on how and in which order to read the slow query information:

slow log.jpg

 

First, I sort my list descending on Total execution time - this is where I can make the biggest difference, because these slow queries are executed most often. I usually don't worry about queries that are in the single or double digits, but everything higher than that for average execution time, I will analyze more closely. Once I have finished my analysis on those findings, I move on to the queries with the highest average execution time.

 

Let us look at a single query that needs improvement and analyze it:

Slow Queries Example:

SELECTFROM (sys_dictionary sys_dictionary0  INNER JOIN sys_metadata sys_metadata0 ON sys_dictionary0.`sys_id` = sys_metadata0.`sys_id` )  WHERE sys_dictionary0.`choice_table` IS NOT NULL

 

Total execution time: 50 Minutes

Execution count: 40.091

Average execution time (ms): 75,72

 

Example stack trace:

glide.scheduler.worker.0

  com.glide.db.query_stats.QueryPattern.setStackTrace(QueryPattern.java:59)

  com.glide.db.query_stats.QueryStats.handleMetaData(QueryStats.java:111)

  com.glide.db.query_stats.QueryStats.addToNursery(QueryStats.java:86)

  com.glide.db.query_stats.QueryStats.recordQuery(QueryStats.java:74)

  com.glide.db.query_stats.QueryStats.recordQuery(QueryStats.java:63)

  com.glide.db.DBI.saveStats(DBI.java:1035)

  com.glide.db.DBI.sqlVerbosity(DBI.java:1029)

  com.glide.db.DBI.executeStatement(DBI.java:785)

  com.glide.db.DBI.executeStatement(DBI.java:766)

  com.glide.db.DBQuery.executeAsResultSet0(DBQuery.java:290)

  com.glide.db.DBQuery.executeAsResultSet0(DBQuery.java:265)

  com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:231)

  com.glide.db.DBAction.executeNormal(DBAction.java:197)

  com.glide.db.DBAction.executeAndReturnException(DBAction.java:166)

  com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:46)

  com.glide.db.DBQuery.execute(DBQuery.java:1772)

  com.glide.choice.ChoiceDependency.<init>(ChoiceDependency.java:39)

  com.glide.choice.ChoiceDependency.get(ChoiceDependency.java:28)

  com.glide.choice.ChoiceChangeListener.expandMe(ChoiceChangeListener.java:49)

  com.glide.choice.ChoiceChangeListener.flushChanges(ChoiceChangeListener.java:37)

  com.glide.db.AChangeListener.onExecute(AChangeListener.java:43)

  com.glide.db.DBAction.processListeners(DBAction.java:143)

  com.glide.db.DBAction.executeAndReturnException(DBAction.java:171)

  com.glide.script.GlideRecordITable.delete(GlideRecordITable.java:177)

  com.glide.script.GlideRecord.delete(GlideRecord.java:4913)

  com.glide.script.system.GlideSystemUtilDB.tableClearByTable(GlideSystemUtilDB.java:523)

  com.glide.script.system.GlideSystemUtilDB.cleanupTableReferences(GlideSystemUtilDB.java:504)

  com.glide.script.system.GlideSystemUtilDB.dropTable(GlideSystemUtilDB.java:741)

  com.glide.script.system.GlideSystemUtilDB.js_dropTable(GlideSystemUtilDB.java:428)

  ...

  org.mozilla.javascript.gen.c5175.call(sys_script_include.77e30b080a00052638e7274157b9b97a:30)

  org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1227)

  org.mozilla.javascript.gen.c3087.call(sys_script_include.bb1996e80a0a0b0f00499e4a748ddfb7:58)

  org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1227)

  org.mozilla.javascript.gen.c3088.call(sys_script_include.bb1996e80a0a0b0f00499e4a748ddfb7:67)

  org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1227)

  org.mozilla.javascript.gen.c5173.call(sysevent_script_action.2227b84a0a0a0b950004868f9ece6509:3)

  org.mozilla.javascript.gen.c5173.exec(sysevent_script_action.2227b84a0a0a0b950004868f9ece6509)

  com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:233)

...

  com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:192)

  com.glide.policy.ScriptActionHandler.executeScriptInScope(ScriptActionHandler.java:144)

  com.glide.policy.ScriptActionHandler.process0(ScriptActionHandler.java:62)

  com.glide.policy.ScriptActionHandler.process(ScriptActionHandler.java:39)

  com.glide.policy.EventProcessor.processEventDuringNormalOperation(EventProcessor.java:164)

...

  com.glide.policy.EventManager._process(EventManager.java:160)

  com.glide.policy.EventManager.process(EventManager.java:142)

...

  org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:597)

  org.mozilla.javascript.FunctionObject.call(FunctionObject.java:504)

  org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1227)

...

  com.glide.schedule.JobExecutor.execute(JobExecutor.java:79)

  com.glide.schedule.GlideScheduleWorker.executeJob(GlideScheduleWorker.java:177)

  com.glide.schedule.GlideScheduleWorker.process(GlideScheduleWorker.java:124)

  com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:56)

 

Example URL: events process

First sighting: 2015-09-22 17:10:33

 

The Explain Plan

OrderSelect TypeTableTypePossible KeysKeyKey lengthRefRowsExtraID0SIMPLEsys_dictionary0ALLPRIMARY25.629Using where11SIMPLEsys_metadata0eq_refPRIMARYPRIMARY96empigeist2_2.sys_dictionary0.sys_id11

 

The Database Indexes

TableReference TableIndex
sys_scopesys_scopesys_metadata
referencereferencesys_dictionary
element, nameelementsys_dictionary
sys_idPRIMARYsys_metadata
sys_idPRIMARYsys_dictionary
sys_packagesys_packagesys_metadata
name, elementnamesys_dictionary
sys_update_name, sys_class_namesys_update_namesys_metadata

 

 

This is all the information available to you in the Slow Queries log. I will explain them in the order in which they make most sense to me - from the time the query was issued to the additional information that helps us understand what is going on.

 

Interpreting the slow query log information

Query Source

Through the example URL we know that this query was issued by the events process. Another common example URL might be /home.do - meaning the slow query is on a user's home page. The stack trace confirms that this query comes from a worker:  com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:56).

 

Following the stack further, I see org.mozilla.javascript.gen.c5175.call(sys_script_include.77e30b080a00052638e7274157b9b97a:30), which refers to a script include called TableDrop, which in line 30 gs.dropTable(tableName); which includes - of course - querying the dictionary record for which table to drop.

 

Query run times

Now that we know the background of this query - how often did it run, how long did it take? The answers to this can be found here:

Total execution time: 50 Minutes

Execution count: 40.091

Average execution time (ms): 75,72

 

Then the question becomes - why does it take so long for a simple query?

 

Query analysis

The query is:

SELECTFROM (sys_dictionary sys_dictionary0  INNER JOIN sys_metadata sys_metadata0 ON sys_dictionary0.`sys_id` = sys_metadata0.`sys_id` )  WHERE sys_dictionary0.`choice_table` IS NOT NULL

 

What does this query do?

It first queries the sys_dictionary table for all records where choice_table is not null. Then it joins the result of that with the information in the sys_metadata table for all records where the sys_ids match.

 

Interpreting an explain plan

Why does this take so long? To find that out, we look at the explain plan:

OrderSelect TypeTableTypePossible KeysKeyKey lenRefRowsExtraID
0SIMPLEsys_dictionary0ALLPRIMARY25.629Using where1
1SIMPLEsys_metadata0eq_refPRIMARYPRIMARY96empigeist2_2.sys_dictionary0.sys_id1Extra1

 

 

This is the real interesting part - what does all this mean?

The more common Select Types are:

  • SIMPLE; no UNION or subquery
  • PRIMARY; outermost SELECT
  • UNION; second or later SELECT in a UNION
  • SUBQUERY; first SELECT in subquery
  • DERIVED; derived table (subquery in WHERE clause)

 

The most commonly seen types are - in order of efficiency:

NameDescription
constAt most, one matching value, treated as a constant.  I.e. active = 1
eq_refJoining or looking up unique index values.  JOIN uses a unique index or key prefix.  I.e. joining two tables on the PRIMARY key.
refJoining or looking up non-unique index values.  JOIN uses a non-unique index or key prefix.  Indexed fields compared with ‘=‘, ‘!=‘. Best data access strategy for non-unique values.
index_mergePerform several index searches using different keys from same table  and merge the results.
rangeRange index scan. The key column is compared to a constant using operators like BETWEEN, IN, >, >=. I.e. sys_created_on > “2015-09-23 00:00:00”.
indexFull Index scan. The entire index tree is scanned.
ALLFull Table scan.  The Entire table is scanned.

 

Now that we have all this information, what does our explain mean:

OrderSelect TypeTableTypePossible KeysKeyKey lenRefRowsExtraID
0SIMPLEsys_dictionary0ALLPRIMARY25.629Using where1
1SIMPLEsys_metadata0eq_refPRIMARYPRIMARY96empigeist2_2.sys_dictionary0.sys_id11

 

We are querying two tables, sys_dictionary first, and then sys_metadata. In the case of sys_dictionary, we use a full table scan through the primary key (sys_id) and have to scan through 25.629 records, that we then filter out from there through the where clause. On the sys_metadata table, we merge the results of the first query record by record based on the primary key, sys_id.

 

Why did it use the primary key and a full table scan on the dictionary query? The answer is simple - there is no index on choice_table, so it had no matching index to go with. If we were to add an index (non-unique, since we do not create unique indexes for query tuning) on choice_table, our explain would then look like something like this:

 

OrderSelect TypeTableTypePossible KeysKeyKey lenRefRowsExtraID
0SIMPLEsys_dictionary0refPRIMARY, choice_tablechoice_table405.427Using index; using where1
1SIMPLEsys_metadata0eq_refPRIMARYPRIMARY96empigeist2_2.sys_dictionary0.sys_id11

 

As you can tell, the extra now tells us an index was found and used. The number of rows we had to comb through to get to our records is significantly smaller, and it returns way faster than before.

 

Talking about the extras, here is a sampling of what they can be:

  • Using filesort; filtered rows are gone through again for sorting
  • Using index; all data is retrieved from a single index (covering index)
  • Using index; using where; index is also used for all key lookups
  • Using temporary; a temporary table is required to process the query
  • Using where; a WHERE clause is used
  • Using join buffer; rows are re-used from the join buffer

 

Finding the best index for your query

How do you know if an index will be good and taken or not - looking at the query is the first step, but there is also this thing called "cardinality." At one point, the database will decide that taking the index has no benefit and will revert to a full table scan. You will look at the explain plan very confused and say "I gave it an index, why would it not take it???"

 

Imagine the following situation: You are searching for all users named Jim that work in the Finance department. Your company has a total of 30000 users, out of those, 500 are named Jim, there are nearly a thousand different names in your list of users, and you have 15 departments in your company (meaning about 2000 users per department).

 

The story of cardinality is - how can I narrow down my results fastest, so I don't have to search through too much information. In our search for Jim in Finance - are we better off searching for Jim first, or for the Finance Department?

 

If we search (and index) for Jim first, we will go through an index tree that has nearly a thousand sorted nodes (all the different first names). If we search (and index) for departments first, we will go through only 15 nodes. But if you look at how many records it would find within the record, you would have 500 for the name, and around 2000 for the department, meaning searching and indexing for name first will narrow our results down fastest and furthest.

 

Different situation - you want to find all closed incidents in your system. 99% of all your incidents are closed. Will an index on state help? Most likely not, since there are so many closed incidents compared to the complete set of data, that it very likely will revert to a full table scan at that point.

 

Sometimes you need to change they query, not the index

And of course there are situations where an index cannot be used at all. We all love the "contains" searches that ServiceNow offers. In SQL speak, they translate to a LIKE '%xxx%', or a double wildcard. Databases cannot use an index on that type of query at all - always ending up with a full table scan (unless there is a saving grace of another part of the query that can be indexed)

 

Another example are OR queries against different fields: WHERE first_name="Jim" OR last_name="Smith" - in this case, it will first search all Jims, then all Smith's and merge the results together - it cannot use one index for both. We always recommend making those two queries and merge the results through other methods.

 

One more common example - if you group by one thing and order by another, the same situation occurs - what to take? Then I won't take any... : GROUP BY first_name, ORDER BY last_name

 

---

 

Special thanks to our fantastic Performance Engineer Scott Nemes at ServiceNow who reviewed and provided some of those great tables that I included in here. Scott, you are the BEST!

Filter Blog

By date: By tag: