Profile SQL queries using logs in Jitterbit App Builder

Introduction

If you suspect a business object has performance issues, you can get the SQL statement from the App Builder logs and then run it through a database utility to identify areas for improvement. This page explains how to do that.

Capture and analyze slow queries

To capture and analyze a slow query, follow these steps:

1. In your App Builder app, navigate to the page where the slow business object is running.

2. Select IDE > Monitoring.

3. In the Logs panel, click the Memory Logs button.

4. Click the Edit Configuration button. The App Server Log Configuration popup window appears.

5. Click the Edit button.

6. In the Memory Log Configuration panel, set the Minimum Severity to Trace.

   Important

   Always reset this value back to Off when you are finished capturing log information to avoid impacting performance.

7. Click the Save button, then close the dialog to return to the Memory Logs page.

8. Refresh the application page that contains the slow business object to ensure the query is captured in the log.

9. Scroll through the log data to locate the SQL statement you're looking for. It will begin with SELECT. For example:

   

10. Copy the entire SQL statement.

11. Go back to the log configuration (Edit Configuration > Edit) and set the Minimum Severity for the Memory Log Configuration back to Off.

12. Launch SQL Server Management Studio (SSMS).

13. Connect to your App Builder database.

14. You can now use SSMS to analyze the query. For example, you can paste the query into a New Query window and use the built-in tools to view its execution plan or send it to the Database Engine Tuning Advisor.

    

15. To analyze the query, you can right-click in the query panel and select Analyze Query in Database Engine Tuning Advisor. You can also select Display Estimated Execution Plan from the toolbar. The execution plan maps out the path the query takes and helps pinpoint parts that may be taking significant time.