Analysing processing performance
This page covers the situation where processing completes correctly but too late. The aim is to find out where the time goes and which changes will actually shorten the run.
Occasional lateness caused by a failure is an operational matter — see Handling failed tasks. This page is about structural lateness that recurs.
Contents
Where to look
| Tool | Answers |
|---|---|
| Dashboard | Is the run on track right now? Which errors keep recurring? How does parallelism develop over the run? |
| Critical path | Which chain of tasks determines when a given task can finish? |
| Stats | How long does this scenario normally take, and is it getting slower? |
| Task statistics | How does one task's runtime behave over time? |
| Visualization | How is the task graph shaped? |
The Dashboard is only present when it is enabled for the installation.
Dashboard
The Dashboard gives the overall picture in one screen.
Status chips
The chips along the top show the task counts by status group — Error, Running, Success, Manual and Waiting. Clicking a chip filters the view to that status group.
Note that Success here aggregates both Finished and Warning. A run with many warnings will still look entirely green on this row; check the warnings separately.
Recent errors
The Recent errors panel lists error messages with the processing, the task, the message text, a count and the time of the last attempt. Clicking the task name opens it in the Monitor.
The count is the useful column. A task failing once is an incident; a task failing repeatedly across runs is a configuration problem, and the retries themselves may be a significant part of why the processing is late.
Parallelism
The Parallelism panel charts the number of concurrently running tasks over the course of a processing.
This is the most informative single view for tuning. Read it against the resource usage recorded on the database server and operating system over the same period:
- a flat plateau at a constant value means a limit is being hit — the global parallelism limit, or a group or connection limit
- a long thin tail means the run finishes on a small number of long tasks; adding capacity will not help, and the critical path is what matters
- a deep trough in the middle means the flow is serialising on a dependency
- high parallelism combined with poor throughput means overload — the tasks are competing rather than progressing
Overload is defined as a combination of concurrently running tasks completing more slowly than they would have with less parallelism. Where this is happening, reducing parallelism will make the run faster, not slower. See Resource management.
Milestones
A milestone tracks progress towards a specific deliverable. Each card shows:
- the milestone name, its date and current status
- a Reference progress bar — progress against the normal profile for this run
- an Actual progress bar — progress achieved so far
- the Deadline, the Reference finish and the Estimated finish, with the estimate highlighted when it is late
Comparing Reference against Actual shows not just that a run is late but when it fell behind. Clicking the card opens it in the Monitor; where a blocking task can be identified, the card links to it directly.
Critical path
For a scheduled task, Show critical path in the Monitor console detail panel identifies the chain of predecessor tasks that determines when that task can finish. It is enabled only where a path can be computed.
This is the tool to use when a specific output is late. Optimising a task that is not on the critical path will not move the finish time; optimising one that is will. The usual outcomes are to tune the SQL of a task on the path, or to split a long task so that its successors can start earlier.
Statistics
The Stats screen shows runtime history. For a scenario, select it and set the number of history days to view. The chart plots the trend, and the table lists each run with:
| Column | Content |
|---|---|
| Date | Processing date |
| Iteration | Iteration code, where one is used |
| Task count | Number of tasks in the processing |
| Try count | Number of execution attempts |
| Processing time | Time attributable to task execution |
| Duration | Elapsed time of the processing |
Two comparisons are worth making:
- Try count against task count. A try count well above the task count means work is being repeated. Repeated retries consume capacity that successful tasks could have used.
- Processing time against duration. A duration much larger than the processing time means the run spent its time waiting rather than working — on dependencies, on limits, or on nothing at all. That is a scheduling problem, not a SQL problem, and no amount of query tuning will fix it.
Task statistics, reached from the task detail panel in the Monitor, shows the same kind of history for a single task. Use it to confirm whether a task has genuinely become slower or simply ran late because it started late.
What to change
Once the bottleneck is identified, the available levers are:
- Weights. Where several tasks compete for the same resource, assign a weight reflecting the load each imposes, so that too many heavy tasks are not started together. Weights are set on the task and are not adjusted automatically — if a task's resource demands change, the weight must be updated by hand. See Resource management.
- Priority. Raising the priority of a task that feeds a required output brings it forward. This works only if the task is not near the end of the dependency graph; if it needs most of the processing to complete first, priority will not help. Remember that priority is inherited through task dependencies only, not through group dependencies. See Prioritization.
- Exclusive dependencies. Where two tasks regularly cause lock waits or deadlocks when run together, make the dependency between them exclusive so they cannot run concurrently. See Dependencies.
- Parallelism and load limits. Set limits at the global, group or connection level. Where a group limit and a connection limit both apply, the stricter one applies.
- Multiday modes. For catch-up runs, the multiday mode determines how much can be done concurrently. A task set to Fully processed serialises everything behind it. See Parallelism.
A note on parallelism
Raising parallelism is the intuitive response to a slow run and frequently the wrong one. In practice a level of parallelism that is too high exhausts the database engine's resources, which then produces poor execution plans, and the whole run slows down.
Change one limit at a time and compare the parallelism chart and the run duration before and after. Tuning several settings at once makes it impossible to attribute the result.
