1

Identify the inefficient SQL Statement

Use the Troubleshooting-AR-System-performance-issues-by-using-AR-Log-Analyzer topic or database tools to identify inefficient SQLs that are impacting performance.

Identify what the expected times are and what the actual response was found to be.

Note that some SQLs, such as running complex reports, are expected to take time to complete. Focus your analysis on SQL statements that are expected to return quickly but cause a delay.

2

Is there an obvious problem with the query?

Look for obvious problems with the query, including the following:

1. No Where clause or a Where clause that only includes non-field values, such as where 1=1 or where ' '=' '.
2. The Where clause includes only statements that would not use an index, such as <>, != , IS NULL, IS NOT NULL.
3. The Where clause includes only qualifications that are Not Selective. In other words, would not match more than 10% of the rows in the table. For example, Where C7>=0, or Where C536870924 LIKE '%A'.
4. The Where clause includes only qualifications with leading wildcards, such as Where C536870924 LIKE '%A'.
5. The Where clause only includes any of the qualifications listed above. Note that all of these examples are fine as long as there are other components in the Where clause that would use an index.

If any of these obvious problems exist with the SQL in question, the problem is going to be one of the following:

• A user performed an inefficient query and should be educated on creating more efficient queries.
• Custom workflow or qualifications (such as table fields) caused the poor qualification. The customization should be modified to generate more efficient SQL. For example, add a more specific qualification to the query such as Where C7=2 OR C7=3
• Out-of-the-Box workflow or qualifications are causing the problem. Continue to the next step to create a support case.

3

Determine if an index could be used but does not exist

Check to see if the SQL statement includes qualifications that are likely to use an index, such as:

• C7=1
• C7>9
• C536870925="Bob"
• C536870925 LIKE "Bob%"
• In some cases, IS NULL will use an index if a small number of records match the qualification, such as where C18095 IS NULL

If there a qualification that you suspect should use an index but appears not to be, check the form properties to ensure that an index exists.

In most cases, in order to use the index, it should be present in the first column/field of a composite index.

If you believe that an index does not exist but should exist, create a support case.

4

Determine the origination of the SQL

SQLs are often dynamically generated by the AR System Server based on workflow, permissions, database type, and field population.

Using API/SQL/Filter logging with AR Log Analyzer, identify if the SQL was generated based on a specific piece of workflow or if it was a result of a simple user query.

Create a support case if it appears that the SQL was generated by the Out-of-the-box application.

5

Determine if User Behavior can improve the situation

If the SQL resulted from a customer query, identify if the problem can be resolved with user behavior. For example, a user looking for 'All Open' cases may not be able to get a fast result because an index is not used. Narrow the scope of the query such as searching for 'All My Open' cases.

6

Identify workflow that caused the inefficient SQL

If you can identify the workflow, table field, or other reason for the query, create a support case so that BMC can provide a short and a long-term solution.

7

Find a solution

a. Use the following table to troubleshoot specific problems with slow SQL statements.

b. If the cause has not been found or no solution is available, proceed to the next step to gather logs and create a support case.

8

Collect relevant logs

If you're still experiencing issues after following the steps or if you're unable to complete any of the preceding steps, run the log zipper utility to collect the API, SQL, and Filter logs relevant to the problem. These logs will be requested from the BMC Support when submitting a case on performance issues. You will need to run it on each server that you enabled the logs on. Rename the zipped file to contain the server name.

9

Create a Support Ticket

Collect and send output of AR Log Analyzer and detailed information when creating a case with BMC Support:

1. Provide the following information as part of your case:
   • Users that experienced the problem.
   • Approximate Timestamp that the slowness SQL was noticed.
   • Provide the SQL statement in question.
   • Any recent changes to the Application, database, or other components of the Remedy environment.
2. Attach any materials or reports provided by the DBA
   This includes AWR, ADDM, or ASH reports, execution plans, observations, etc.
3. Attach the Log Zipper zip file to your case (up to 2 GB) or transfer the files to BMC using FTP. See Steps to send logs, files, screenshots, etc to BMC Support for a Remedy Product related case

Index is missing

Developer Studio

Add an Index

Defining Indexes

Missing indexes for the field "Parent Request ID" (1000002706) on the form AST:CMDB Associations

Developer Studio

Add an Index

Missing indexes for the field "Parent Request ID"

When you create entries on a form and create a web service request to fetch data for that form, the return query includes all columns, which might cause a performance issue.

AR System Server, Mid Tier, Developer Studio

1. Upgrade your AR System Server and Mid Tier to version 20.02 patch 005.
2. Upgrade your Developer Studio to the latest available version.

20-02-05-Patch-5-for-version-20-02