RE: Performance impact of SQL monitors

["Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>]

Because SSD, I doubt disk was the issue.
How much CPU, full core, or less?
How much memory, maybe faulting.
Or possibly another issue, seize waits?

Was collection services running?
Is MPG installed, check the various graphs, should point to the culprit?

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, April 24, 2015 9:33 AM
To: Midrange Systems Technical Discussion
Subject: Re: Performance impact of SQL monitors

Coworker used SQL Performance Monitor Wizard from IBM Navigator.
Three boxes were checked:
Job name (and job name was supplied)
Job user (and job user was supplied)
Statements that access these objects (and one view in one schema was supplied).

Basically running this may double already significant run times of certain batch jobs.
Job         User        Number   Start    End           Duration
ERPCSTROL1  DARREN      626786  15:20:18        16:04:45        00:44:27 
With SQL monitor running
ERPCSTROL1  DARREN      626550  14:52:26        15:12:39        00:20:13 
Without
ERPCSTROL1  DARREN      624719  10:46:49        11:12:47        00:25:58 
Without? (disk addition and rebalancing also)
ERPCSTROL1  DARREN      624563  10:20:31        10:42:08        00:21:37 
With?
ERPCSTROL1  DARREN      623692  08:24:58        09:09:16        00:44:18 
With?
ERPCSTROL1  DARREN      623423  07:59:43        08:19:43        00:20:00 
Without?

The disk balancing was I added another storage space to this guest lpar and told it to 'add and balance'.  Hosting lpar is all SSD.  Add and balance really didn't take too awful long but disks jumped up to 50+% busy on this guest.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to:  2505 Dekko Drive
          Garrett, IN 46738
Ship to:  Dock 108
          6928N 400E
          Kendallville, IN 46755
http://www.dekko.com





From:   Charles Wilt <charles.wilt@xxxxxxxxx>
To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:   04/24/2015 08:52 AM
Subject:        Re: Performance impact of SQL monitors
Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



What kind of monitor?

You can start a monitor that looks at every job...

Or you can start one that only watches one job.

As you can guess, there's a bit more overhead with the former.

Charles

On Fri, Apr 24, 2015 at 8:21 AM, <rob@xxxxxxxxx> wrote:

> Recently got a contact from a coworker.  Their SQL was running much 
slower
> on an lpar.  They keep detailed track of start/stop times of certain 
jobs
> going back awhile.  Their hypotheses was that someone was running an SQL
> monitor.  Yes, there was someone.
> They duplicated the data on another lpar.
> Ran SQL without any monitors running on system.  Good performance.
> Ran it again with an sql monitor running on something unrelated.  Poor
> performance.
> Ran SQL again without any monitors running on system.  Good performance.
> Ran it again with an sql monitor running on something unrelated.  Poor
> performance.
> These were all running in batch so it's not like "the first time this 
job
> ran a certain process...".
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.