SQL Observer: Catching the Queries That Drain Your Resources

Every Power i system runs SQL statements in the background – thousands, sometimes hundreds of thousands, every single hour.

Most of them do what they’re supposed to do.
Some of them, however, don’t.

They use more resources than necessary, or they run far too often. And because they’re hidden in the noise of normal activity, they go unnoticed.

This is exactly why I built the SQL Observer feature in GiAPA.

What does SQL Observer do?

It tracks every SQL statement that runs during the data collection period – not just what ran, but how often, and how much CPU each one used.

When something stands out, GiAPA logs it and lets you dig deeper.

• Which queries are running too often?
• Are they being used correctly?
• Do they need optimization or indexing?
• Could a redesign reduce the load?

SQL Observer doesn’t just tell you what’s happening – it helps you take action.

How does it work in practice?

In one system we reviewed, a developer had added a query that was meant to run once per batch. Instead, it was being called inside a loop – executing thousands of times every run.

It worked.
But it cost 20 minutes of unnecessary processing time every night.

Once we found it, the fix was simple. The saving was immediate.

And yes – you can see the full statement.

SQL Observer lets you view the actual statement text (if collection settings allow it), so you’re not guessing. You see what’s being executed, how often, and what it’s doing to your system.

You can also sort the output to find:

• Most CPU-intensive statements
• Most frequently run statements
• Statements triggered by specific jobs or users

Why this matters

I’ve seen SQL statements silently drain performance in otherwise well-behaved systems.

They’re not broken.
They’re just not efficient.

SQL Observer helps you find and fix them – before they become a problem.

‍