LabKey Server monitors the queries that it runs against the underlying database. For performance and memory usage reasons, it does not retain every query that has ever run, but it attempts to hold on to the most recently executed queries, the longest-running queries, and the most frequently run queries. In the vast majority of cases, all of the queries of interest are retained and tracked.
This information can be very useful for tracking down performance problems caused by slow-running database queries. You can also use the query profiler to see details about any queries running on your server.
View the Query Log
To view the query log:
- Select (Admin) > Site > Admin Console.
- Under Diagnostics, click Queries.
You will see two summary sections and data.
- Queries Executed within HTTP Requests: Including Query Count, Query Time, Queries per Request, Query Time per Request, Request Count.
- Queries Executed Within Background Threads: Including Query Count and Query Time.
- Total Unique Queries
- Server Uptime
All statistics here can be reset by clicking
Reset All Statistics. Export the information with
Export.
Below the statistics, you'll see a listing of the top 270
unique queries with the highest number of invocations. Sort this list of queries by clicking the column headers:
| Column Name | Description |
|---|
| Count | The number of times that the server has executed the query since it started, or statistics were reset. |
| Total | The aggregate time of all of the invocations of the query, in milliseconds. |
| Avg | The average execution time, in milliseconds. |
| Max | The longest execution time for the query, in milliseconds. |
| Last | The last time that the query was executed. |
| Traces | This column will show the number of different call stacks from the application code that have invoked the query. If capture of stack traces was enabled on your server at the time of the query, clicking the link shows the actual stack traces, which can be useful for developers to track down any issues. |
| SQL | The query itself. Note that this is the actual text of the query that was passed to the database via the JDBC driver. It may contain substitution syntax. |
Troubleshoot Performance
To troubleshoot performance of an action, script, or application, particularly if you are not sure which query might be causing a slowdown, you can
Reset All Statistics here, then return to execute the action(s) of interest. When you return to this page, the statistics will report a more focused view of the recent actions.
Click the
Max column header to see the queries that took the most time. You can also examine any
traces to find out more about particular query invocations.
Traces
In addition to performance issues, query profiling can help identify possible problems with many actions and queries. To trace an action, have two browser windows open:
- In one, Reset All Statistics on the query page.
- In the other, execute only the action you want to trace.
- Back in the first browser, refresh and examine the list of queries that just occurred.
Clicking on a link in the
Traces column will show a details page. It includes the raw text of the query, as well as one example of actual parameter values that were passed. Note that other invocations of the query may have used other parameter values, and that different parameter values can have significant impact on the runtime performance.
Below the trace details, you can click
Show Execution Plan to get the execution plan of the query as reported by the database itself.
Related Topics
