Re: General SQL Performance Question

Have you looked at your performance signature with the Performance Data Investigator? This can give some helpful clues by showing the breakdown of CPU time versus various wait times. Disk time will show up as a large component if disk response times are truly the problem. But there could be other wait times contributing.

Use the Waits Overview chart over a full day (e.g., yesterday’s collection).

Dawn

On Nov 2, 2023, at 12:52 PM, Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Charles,

We are a small shop. Power 9 9009-41g with one disk controller and 6 236gb internal disk units in RAID configuration (1461gb total, 56% used). Everything is 100% IBM.

IBM is telling me they could see slow response times before our 7.5 upgrade (but the worst was 10ms). They think we are overrunning the disk controller.
They had me reduce the max size of our plan cache (it was huge on *AUTO), which brought down our temporary storage used (it was over 10%). Now it's around 74gb.

Performance is hit so hard (sporadically) that simple green screen commands (wrkactjob, wrksplf, etc.) take 5-8 seconds to complete.

So based on what you and Birgitta have said, we're looking in the wrong place. That SQL view has been in place for several years (back to our Power7+.

What do you mean by "hardware/TIMI"?

Thx,
Greg

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Thursday, November 2, 2023 12:37 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: General SQL Performance Question

On Thu, Nov 2, 2023 at 9:57 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

IBM has taken numerous traces, dumps, etc. and the issue appears to be
directly related to disk I/O - response times are very poor (+60 ms).

I'd quit looking at SQL "solutions" and press IBM on hardware/TIMI
solutions for this.

60+ ms for disk I/O is ridiculous.

Internal disk? External? IBM approved I hope?

We once got bit by a bug in our IBM SAN firmware that was causing increased
response times.
It was actually a known bug, with an existing fix, but it took quite a
while for IBM to realize it.

Charles
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.