Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Improve usefulness of sysprocesses.physical_io

12 posts in Product Futures Discussion Last posting was on 2004-02-17 21:58:54.0Z
Frank_Hamersley Posted on 2003-10-22 01:07:11.0Z
Reply-To: "Frank_Hamersley" <terabite@at.bigpond.com>
From: "Frank_Hamersley" <terabite@bigpond.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Improve usefulness of sysprocesses.physical_io
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
NNTP-Posting-Host: ess-p-144-138-5-118.mega.tmns.net.au
X-Original-NNTP-Posting-Host: ess-p-144-138-5-118.mega.tmns.net.au
Message-ID: <3f95d83f@forums-1-dub>
Date: 21 Oct 2003 18:07:11 -0700
X-Trace: forums-1-dub 1066784831 144.138.5.118 (21 Oct 2003 18:07:11 -0700)
X-Original-Trace: 21 Oct 2003 18:07:11 -0700, ess-p-144-138-5-118.mega.tmns.net.au
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1433
Article PK: 96440

At the moment the physical_io column in sysprocesses only returns the amount
of IO for the currently executing command.

This means a spid can never find out how much pio it performed in its own
right because when it consults sysprocesses it is executing a new command
and therefore physical_io is always 0. For example...

SELECT * INTO #ttt FROM VeryLargeTable
SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
physical_io
-----------
0
(1 row affected)

Without changing ASE current behaviour can we please have ...
a) @@pio = Cumulative amount of PIO performed by @@spid
b) master..sysprocesses.pio = Cumulative PIO for process in reads/writes.
c) @@physical_io = Amount of PIO performed by the previous command for
@@spid

NB Option (c) is not likely to be of much use because you can synthesise it
easily by bracketing the statement(s) you are interested in with something
like...

SELECT @pio_was = @@pio
UPDATE BigTable WHERE LotsOfMatches = 1
SELECT @pio_used = @@pio - @pio_was
-- previous statement probably requires overflow logic for very long running
spids

However (a) would be _very_ useful in profiling more complex ASE application
systems where occasional performance problems arise and are not always
observed by sys admins (unless they happen to be scanning sysprocesses on a
very frequent basis). Then the application could accurately log its PIO for
later review by the system designers rather than a sys admin trying to do
showplans etc when the system is apparently distressed.

I would expect (a) could be delivered in an ESD given its an addition to
functionality rather than a change whereas (b) may well be better linked to
a point release given there is an increase to the column count for
sysprocesses.

Thoughts?

Frank Hamersley.


Grant Posted on 2003-10-22 03:37:11.0Z
From: "Grant" <gqueenin-no@hot-spamatall-mail.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f95d83f@forums-1-dub>
Subject: Re: Improve usefulness of sysprocesses.physical_io
Lines: 84
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rover-gwh.btal.com.au
X-Original-NNTP-Posting-Host: rover-gwh.btal.com.au
Message-ID: <3f95fb67@forums-1-dub>
Date: 21 Oct 2003 20:37:11 -0700
X-Trace: forums-1-dub 1066793831 203.10.111.39 (21 Oct 2003 20:37:11 -0700)
X-Original-Trace: 21 Oct 2003 20:37:11 -0700, rover-gwh.btal.com.au
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1434
Article PK: 96441

Yes, and more:
It is my undrestanding that worker threads pio is not accumulated by the
family owner, which therefore implies that pio by a command is not
accurately reported once the workers are discarded.
If this is correct, physical io should be captured for statement level pio
more accurately. Also, a new session cumulative field should exist, that
represents all physical io instigated by the spid during the session. It may
be nice to be able to reset this with a grantable function call.

logical io should also be exposed via sysprocesses. Stmt & cumulative.

While we are on this subject; it would also be useful to know the relative
physical and logical IO associated with the query versus worktables etc...
imagine looking at sysprocesses and seeing who and what is being nasty!

The cost here is a little code + some small-modest amount of memory for
sysproceses per spid for storage. Although some of this is available through
mda tables; they are, like monitor server, a little too costly
(resource-wise) for pervasive use. Hence I advocate exposing this data from
in sysprocesses; some of this may already be hanging around in the pss
anyway.

thoughts, comments?
Grant Queenin

"Frank_Hamersley" <terabite@bigpond.com> wrote in message
news:3f95d83f@forums-1-dub...
> At the moment the physical_io column in sysprocesses only returns the
amount
> of IO for the currently executing command.
>
> This means a spid can never find out how much pio it performed in its own
> right because when it consults sysprocesses it is executing a new command
> and therefore physical_io is always 0. For example...
>
> SELECT * INTO #ttt FROM VeryLargeTable
> SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> physical_io
> -----------
> 0
> (1 row affected)
>
> Without changing ASE current behaviour can we please have ...
> a) @@pio = Cumulative amount of PIO performed by @@spid
> b) master..sysprocesses.pio = Cumulative PIO for process in
reads/writes.
> c) @@physical_io = Amount of PIO performed by the previous command for
> @@spid
>
> NB Option (c) is not likely to be of much use because you can synthesise
it
> easily by bracketing the statement(s) you are interested in with something
> like...
>
> SELECT @pio_was = @@pio
> UPDATE BigTable WHERE LotsOfMatches = 1
> SELECT @pio_used = @@pio - @pio_was
> -- previous statement probably requires overflow logic for very long
running
> spids
>
> However (a) would be _very_ useful in profiling more complex ASE
application
> systems where occasional performance problems arise and are not always
> observed by sys admins (unless they happen to be scanning sysprocesses on
a
> very frequent basis). Then the application could accurately log its PIO
for
> later review by the system designers rather than a sys admin trying to do
> showplans etc when the system is apparently distressed.
>
> I would expect (a) could be delivered in an ESD given its an addition to
> functionality rather than a change whereas (b) may well be better linked
to
> a point release given there is an increase to the column count for
> sysprocesses.
>
> Thoughts?
>
> Frank Hamersley.
>
>


Pablo Sanchez Posted on 2003-10-22 04:05:15.0Z
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Improve usefulness of sysprocesses.physical_io
From: Pablo Sanchez <honeypot@blueoakdb.com>
References: <3f95d83f@forums-1-dub>
Organization: Blueoak Database Engineering, Inc
Message-ID: <Xns941BF3D7EE0F2pingottpingottbah@192.138.151.106>
User-Agent: Xnews/06.07.17
X-Original-NNTP-Posting-Host: mtl58-12-171-139.dialup.sprint-canada.net
X-Original-Trace: 21 Oct 2003 21:09:20 -0700, mtl58-12-171-139.dialup.sprint-canada.net
Lines: 13
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Oct 2003 20:58:16 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Oct 2003 21:05:15 -0700
X-Trace: forums-1-dub 1066795515 10.22.108.75 (21 Oct 2003 21:05:15 -0700)
X-Original-Trace: 21 Oct 2003 21:05:15 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1435
Article PK: 96442

"Frank_Hamersley" <terabite@bigpond.com> wrote in
news:3f95d83f@forums-1-dub:

>
> Thoughts?
>

Hey Frank,

Do the new monitor tables help?
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com


Frank_Hamersley Posted on 2003-10-22 04:32:00.0Z
Reply-To: "Frank_Hamersley" <terabite@at.bigpond.com>
From: "Frank_Hamersley" <terabite@bigpond.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f95d83f@forums-1-dub>
Subject: Re: Improve usefulness of sysprocesses.physical_io
Lines: 88
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
NNTP-Posting-Host: ess-p-144-138-5-118.mega.tmns.net.au
X-Original-NNTP-Posting-Host: ess-p-144-138-5-118.mega.tmns.net.au
Message-ID: <3f960840@forums-1-dub>
Date: 21 Oct 2003 21:32:00 -0700
X-Trace: forums-1-dub 1066797120 144.138.5.118 (21 Oct 2003 21:32:00 -0700)
X-Original-Trace: 21 Oct 2003 21:32:00 -0700, ess-p-144-138-5-118.mega.tmns.net.au
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1436
Article PK: 96443

Pablo,

It may well be available in the monitor tables...I haven't had time to check
in detail but will shortly.

However, my main interest (which is somewhat aligned with Grants I believe)
is to have a lightweight hook that can be embedded in the application
systems logging and then deployed unchanged into the Production environment.

This will assist in unifying log analysis for developers rather than forcing
them to correlate the progress of the problem in multiple disparate
histories. Further when those nasty intermittant issues arise in production
then hopefully some useful information can be obtained without having to
spend time trying to reproduce the problem in a "trussed" test bed.

The app that I am engaged on at the moment has a number of daemon type
processes hammering the database. In order to get prior warning of sprocs
that are worthy of review (poor performers) I find I need to take a
statistical view of all the work they are doing and without PIO there is not
much to go on. I can use CPU and elapsed times for an initial pointer, but
excessive PIO is the real performance killer for ASE hence my first post.

As an aside what is your view about the cost/risk of leaving Monitor engaged
on a very active production server?

Cheers,
Frank.

"Frank_Hamersley" <terabite@bigpond.com> wrote in message
news:3f95d83f@forums-1-dub...
> At the moment the physical_io column in sysprocesses only returns the
amount
> of IO for the currently executing command.
>
> This means a spid can never find out how much pio it performed in its own
> right because when it consults sysprocesses it is executing a new command
> and therefore physical_io is always 0. For example...
>
> SELECT * INTO #ttt FROM VeryLargeTable
> SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> physical_io
> -----------
> 0
> (1 row affected)
>
> Without changing ASE current behaviour can we please have ...
> a) @@pio = Cumulative amount of PIO performed by @@spid
> b) master..sysprocesses.pio = Cumulative PIO for process in
reads/writes.
> c) @@physical_io = Amount of PIO performed by the previous command for
> @@spid
>
> NB Option (c) is not likely to be of much use because you can synthesise
it
> easily by bracketing the statement(s) you are interested in with something
> like...
>
> SELECT @pio_was = @@pio
> UPDATE BigTable WHERE LotsOfMatches = 1
> SELECT @pio_used = @@pio - @pio_was
> -- previous statement probably requires overflow logic for very long
running
> spids
>
> However (a) would be _very_ useful in profiling more complex ASE
application
> systems where occasional performance problems arise and are not always
> observed by sys admins (unless they happen to be scanning sysprocesses on
a
> very frequent basis). Then the application could accurately log its PIO
for
> later review by the system designers rather than a sys admin trying to do
> showplans etc when the system is apparently distressed.
>
> I would expect (a) could be delivered in an ESD given its an addition to
> functionality rather than a change whereas (b) may well be better linked
to
> a point release given there is an increase to the column count for
> sysprocesses.
>
> Thoughts?
>
> Frank Hamersley.
>
>


Pablo Sanchez Posted on 2003-10-22 14:02:25.0Z
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Improve usefulness of sysprocesses.physical_io
From: Pablo Sanchez <honeypot@blueoakdb.com>
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub>
Organization: Blueoak Database Engineering, Inc
Message-ID: <Xns941C64F40CCD7pingottpingottbah@192.138.151.106>
User-Agent: Xnews/06.07.17
X-Original-NNTP-Posting-Host: mtl58-12-171-139.dialup.sprint-canada.net
X-Original-Trace: 22 Oct 2003 07:06:36 -0700, mtl58-12-171-139.dialup.sprint-canada.net
Lines: 26
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Oct 2003 06:55:28 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Oct 2003 07:02:25 -0700
X-Trace: forums-1-dub 1066831345 10.22.108.75 (22 Oct 2003 07:02:25 -0700)
X-Original-Trace: 22 Oct 2003 07:02:25 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1437
Article PK: 96444

"Frank_Hamersley" <terabite@bigpond.com> wrote in
news:3f960840@forums-1-dub:

> As an aside what is your view about the cost/risk of leaving
> Monitor engaged on a very active production server?

Hi Frank,

Thank you for elaborating your intentions. In short, I agree with
the process you've put forth. Getting a baseline is always
important in any performance tuning effort.

I don't have any metrics on leaving monitoring running
however I would suspect that it would be minimal and would be worth
its cost (assuming it's less than, say 7%) to root out the very
problems you described.

I think a simple cost-analysis would show that even if the 'hit' was
something like 10%, it'd be far cheaper to buy another processor and
leave monitoring on than it is to switch it off and instrument the
environment and collect forensics later, etc etc.

Thx!
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com


Frank_Hamersley Posted on 2003-10-23 01:08:54.0Z
Reply-To: "Frank_Hamersley" <terabite@at.bigpond.com>
From: "Frank_Hamersley" <terabite@bigpond.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub> <Xns941C64F40CCD7pingottpingottbah@192.138.151.106>
Subject: Re: Improve usefulness of sysprocesses.physical_io
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
NNTP-Posting-Host: ess-p-144-138-5-138.mega.tmns.net.au
X-Original-NNTP-Posting-Host: ess-p-144-138-5-138.mega.tmns.net.au
Message-ID: <3f972a26@forums-1-dub>
Date: 22 Oct 2003 18:08:54 -0700
X-Trace: forums-1-dub 1066871334 144.138.5.138 (22 Oct 2003 18:08:54 -0700)
X-Original-Trace: 22 Oct 2003 18:08:54 -0700, ess-p-144-138-5-138.mega.tmns.net.au
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1439
Article PK: 96446


"Pablo Sanchez" wrote
> I don't have any metrics on leaving monitoring running
> however I would suspect that it would be minimal and would be worth
> its cost (assuming it's less than, say 7%) to root out the very
> problems you described.
> I think a simple cost-analysis would show that even if the 'hit' was
> something like 10%, it'd be far cheaper to buy another processor and
> leave monitoring on than it is to switch it off and instrument the
> environment and collect forensics later, etc etc.
>

Percentages of that magnitude would be hard for us to cop - we are not yet
24x5 (luckily weekends can be used for reorgs) but we do have some tight
times for interaction with the corporations GL and STP links to payment
systems etc.

Admittedly more CPU/RAM could offset this cost, but the business has to fund
it and their interest is as ever spasmodic (ie. when a system failure
impacts revenue you gets lots of focus) so they tend to want to devote the
grunt to things other than baseline monitoring. Besides they would argue
they have already "over provisioned" in the supply of identically configured
Acceptance, Test and Development servers - so it seems reasonable for them
to ask why can't they be used?

I will check out Kevin's suggestion - but am wondering if it might be a
sledgehammer/walnut situation. I expect Monitor is feature rich but have
found somestimes that ultra capable tools (like I expect it to be) are also
problem in that you have to drastically degrade the volumes of info it
produces lest you drown in the noise!

Basically I strongly adhere to the KISS principle - thus my original request
for a simple @@pio outcome!

Will report back later on how the "cat skinning" goes!

Cheers
Frank.


Sherlock, Kevin Posted on 2003-10-22 15:06:16.0Z
Message-ID: <3F969BF4.6F40CF2F@qwest.com.nospam>
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Improve usefulness of sysprocesses.physical_io
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 199.184.176.10
X-Original-NNTP-Posting-Host: 199.184.176.10
Date: 22 Oct 2003 08:06:16 -0700
X-Trace: forums-1-dub 1066835176 199.184.176.10 (22 Oct 2003 08:06:16 -0700)
X-Original-Trace: 22 Oct 2003 08:06:16 -0700, 199.184.176.10
Lines: 125
X-Authenticated-User: teamps
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1438
Article PK: 96445

Actually, Historical Server/ASE Monitor server can do this quite well.
Here is a Historical view which you could setup an alarm for which fires
off a perl script to do anything you want.

hs_create_view spid_obj_io,
"Login Name", "Value for Sample",
"Process ID", "Value for Sample",
"Kernel Process ID", "Value for Sample",
"Database Name", "Value for Sample",
"Database ID", "Value for Sample",
"Object Name", "Value for Sample",
"Object ID", "Value for Sample",
"Object Type", "Value for Sample",
"Page Hit Percent", "Value for Sample",
"Page I/O", "Rate for Sample",
"Logical Page Reads", "Rate for Sample",
"Index Logical Reads", "Rate for Sample",
"Physical Page Reads", "Rate for Sample",
"Index Physical Reads", "Rate for Sample",
"Page Writes", "Rate for Sample"
go
hs_create_alarm spid_obj_io, "Physical Page Reads", "Rate for Sample",
execute, "/home/sybase/hs_alarm.pl",100
go

Frank_Hamersley wrote:
>
> Pablo,
>
> It may well be available in the monitor tables...I haven't had time to check
> in detail but will shortly.
>
> However, my main interest (which is somewhat aligned with Grants I believe)
> is to have a lightweight hook that can be embedded in the application
> systems logging and then deployed unchanged into the Production environment.
>
> This will assist in unifying log analysis for developers rather than forcing
> them to correlate the progress of the problem in multiple disparate
> histories. Further when those nasty intermittant issues arise in production
> then hopefully some useful information can be obtained without having to
> spend time trying to reproduce the problem in a "trussed" test bed.
>
> The app that I am engaged on at the moment has a number of daemon type
> processes hammering the database. In order to get prior warning of sprocs
> that are worthy of review (poor performers) I find I need to take a
> statistical view of all the work they are doing and without PIO there is not
> much to go on. I can use CPU and elapsed times for an initial pointer, but
> excessive PIO is the real performance killer for ASE hence my first post.
>
> As an aside what is your view about the cost/risk of leaving Monitor engaged
> on a very active production server?
>
> Cheers,
> Frank.
>
> "Frank_Hamersley" <terabite@bigpond.com> wrote in message
> news:3f95d83f@forums-1-dub...
> > At the moment the physical_io column in sysprocesses only returns the
> amount
> > of IO for the currently executing command.
> >
> > This means a spid can never find out how much pio it performed in its own
> > right because when it consults sysprocesses it is executing a new command
> > and therefore physical_io is always 0. For example...
> >
> > SELECT * INTO #ttt FROM VeryLargeTable
> > SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> > physical_io
> > -----------
> > 0
> > (1 row affected)
> >
> > Without changing ASE current behaviour can we please have ...
> > a) @@pio = Cumulative amount of PIO performed by @@spid
> > b) master..sysprocesses.pio = Cumulative PIO for process in
> reads/writes.
> > c) @@physical_io = Amount of PIO performed by the previous command for
> > @@spid
> >
> > NB Option (c) is not likely to be of much use because you can synthesise
> it
> > easily by bracketing the statement(s) you are interested in with something
> > like...
> >
> > SELECT @pio_was = @@pio
> > UPDATE BigTable WHERE LotsOfMatches = 1
> > SELECT @pio_used = @@pio - @pio_was
> > -- previous statement probably requires overflow logic for very long
> running
> > spids
> >
> > However (a) would be _very_ useful in profiling more complex ASE
> application
> > systems where occasional performance problems arise and are not always
> > observed by sys admins (unless they happen to be scanning sysprocesses on
> a
> > very frequent basis). Then the application could accurately log its PIO
> for
> > later review by the system designers rather than a sys admin trying to do
> > showplans etc when the system is apparently distressed.
> >
> > I would expect (a) could be delivered in an ESD given its an addition to
> > functionality rather than a change whereas (b) may well be better linked
> to
> > a point release given there is an increase to the column count for
> > sysprocesses.
> >
> > Thoughts?
> >
> > Frank Hamersley.
> >
> >

--
Kevin Sherlock
Staff Info Systems Analyst
Omaha, NE
Dex Media
http://www.qwestdex.com
[TeamSybase]
http://teamsybase.com/kevin.sherlock
Take a look at CodeXchange:
http://www.codexchange.sybase.com
ISUG Enhancement Requests
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement


Grant Posted on 2003-10-23 03:05:23.0Z
From: "Grant" <gqueenin-no@hot-spamatall-mail.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub> <3F969BF4.6F40CF2F@qwest.com.nospam>
Subject: Re: Improve usefulness of sysprocesses.physical_io
Lines: 214
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: vpn-sydney.btfinancialgroup.com
X-Original-NNTP-Posting-Host: vpn-sydney.btfinancialgroup.com
Message-ID: <3f974573@forums-1-dub>
Date: 22 Oct 2003 20:05:23 -0700
X-Trace: forums-1-dub 1066878323 203.10.111.39 (22 Oct 2003 20:05:23 -0700)
X-Original-Trace: 22 Oct 2003 20:05:23 -0700, vpn-sydney.btfinancialgroup.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1440
Article PK: 96447

There is no question most of the data is there or can be reasonably
interpolated - either from MDA or Historical/Monitor server... BUT using
these comes at a cost. Percentages of 5-10%/engine have been and are oft
quoted - in an 8 engine ASE - more than 1/2 of one CPU is doing monitoring
for us... + maintenance costs, license cost, support costs.... it is not
just about CPU and memory. Low cost effective monitoring hooks are
essential.

However:
1. These are mutually exclusive - You choose if you use MDA or Mon/Hist
server.
2. As the # of engines/cpu's increase so does the cost...
3. Due to memory mapping these must co-exist with the system under "load".
It would be better if these could be more loosely coupled with a memory
mapped remote io's so that the load imposed is much lower... this would
likely be considered a vulnerability though; so I can understand why this
has not been shipped.
4. Yes, the counters being monitored can be reduced to lower the load/cost
imposed by monitoring.

The start of a counter argument usually goes "If you can't afford 10% of CPU
headroom for monitoring to find out when poor code gets into the wild and to
trend the system....".

Of these #2 is my biggest inhibitor to using Mon/Hist server in prod
environments. If I am doing CP/tuning work, I don't want my system under
test/load to suffer measurably from the introspection tool. Having to allow
caveat around the artifacts of monitoring is just not a great starting
point - if you have a choice. Most every performance report shows that the
monitoring with Hist/Mon server (and now presumably mda) is in the top 10-15
tasks; try writing the summary and dodging that!

When monitoring costs more than 2-3% it is hard to justify it to people
funding the systems as a core process. Especially as systems become more
stressed later in their lifecycle. When a server heads toward whatever level
of utilisation you deem to be distress, non-core things start to be
rationalised. The argument is - "Is monitoring as core as database
responsiveness?" and the usual answer is "it depends..." which mostly
translates to "Turn it off! I'm not paying for that.".

I would like Sybase to consider exposing more of the pss structure:

Either as:
1. Readable columns in sysprocesses which is only materialised on demand, as
I understand it.
2. A binary pointer col to the PSS and document the structure to allow
external processing away from the ASE instance in a client process.
3. Maintain session level io counters in sysprocesses.
4. At slightly more expense, readonly session variables is another good
approach.
5. Account for family cpu/io etc at the family owner spid level as workers
are destroyed.
6. Should Sybase allow TSQL UDF's we can then create any number of
pseudo-session variables on published data in approach 1 or 2.

It is my expectation that 1 - 2 in particular should not increase cost
dramatically over a regular select from sysprocesses, in either memory or
CPU terms, and still this would be less than using mda or Mon/hist server to
collect the same data.

Thoughts/Comments?
Cheers
Grant Queenin

"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3F969BF4.6F40CF2F@qwest.com.nospam...
> Actually, Historical Server/ASE Monitor server can do this quite well.
> Here is a Historical view which you could setup an alarm for which fires
> off a perl script to do anything you want.
>
> hs_create_view spid_obj_io,
> "Login Name", "Value for Sample",
> "Process ID", "Value for Sample",
> "Kernel Process ID", "Value for Sample",
> "Database Name", "Value for Sample",
> "Database ID", "Value for Sample",
> "Object Name", "Value for Sample",
> "Object ID", "Value for Sample",
> "Object Type", "Value for Sample",
> "Page Hit Percent", "Value for Sample",
> "Page I/O", "Rate for Sample",
> "Logical Page Reads", "Rate for Sample",
> "Index Logical Reads", "Rate for Sample",
> "Physical Page Reads", "Rate for Sample",
> "Index Physical Reads", "Rate for Sample",
> "Page Writes", "Rate for Sample"
> go
> hs_create_alarm spid_obj_io, "Physical Page Reads", "Rate for Sample",
> execute, "/home/sybase/hs_alarm.pl",100
> go
>
> Frank_Hamersley wrote:
> >
> > Pablo,
> >
> > It may well be available in the monitor tables...I haven't had time to
check
> > in detail but will shortly.
> >
> > However, my main interest (which is somewhat aligned with Grants I
believe)
> > is to have a lightweight hook that can be embedded in the application
> > systems logging and then deployed unchanged into the Production
environment.
> >
> > This will assist in unifying log analysis for developers rather than
forcing
> > them to correlate the progress of the problem in multiple disparate
> > histories. Further when those nasty intermittant issues arise in
production
> > then hopefully some useful information can be obtained without having to
> > spend time trying to reproduce the problem in a "trussed" test bed.
> >
> > The app that I am engaged on at the moment has a number of daemon type
> > processes hammering the database. In order to get prior warning of
sprocs
> > that are worthy of review (poor performers) I find I need to take a
> > statistical view of all the work they are doing and without PIO there is
not
> > much to go on. I can use CPU and elapsed times for an initial pointer,
but
> > excessive PIO is the real performance killer for ASE hence my first
post.
> >
> > As an aside what is your view about the cost/risk of leaving Monitor
engaged
> > on a very active production server?
> >
> > Cheers,
> > Frank.
> >
> > "Frank_Hamersley" <terabite@bigpond.com> wrote in message
> > news:3f95d83f@forums-1-dub...
> > > At the moment the physical_io column in sysprocesses only returns the
> > amount
> > > of IO for the currently executing command.
> > >
> > > This means a spid can never find out how much pio it performed in its
own
> > > right because when it consults sysprocesses it is executing a new
command
> > > and therefore physical_io is always 0. For example...
> > >
> > > SELECT * INTO #ttt FROM VeryLargeTable
> > > SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> > > physical_io
> > > -----------
> > > 0
> > > (1 row affected)
> > >
> > > Without changing ASE current behaviour can we please have ...
> > > a) @@pio = Cumulative amount of PIO performed by @@spid
> > > b) master..sysprocesses.pio = Cumulative PIO for process in
> > reads/writes.
> > > c) @@physical_io = Amount of PIO performed by the previous command
for
> > > @@spid
> > >
> > > NB Option (c) is not likely to be of much use because you can
synthesise
> > it
> > > easily by bracketing the statement(s) you are interested in with
something
> > > like...
> > >
> > > SELECT @pio_was = @@pio
> > > UPDATE BigTable WHERE LotsOfMatches = 1
> > > SELECT @pio_used = @@pio - @pio_was
> > > -- previous statement probably requires overflow logic for very long
> > running
> > > spids
> > >
> > > However (a) would be _very_ useful in profiling more complex ASE
> > application
> > > systems where occasional performance problems arise and are not always
> > > observed by sys admins (unless they happen to be scanning sysprocesses
on
> > a
> > > very frequent basis). Then the application could accurately log its
PIO
> > for
> > > later review by the system designers rather than a sys admin trying to
do
> > > showplans etc when the system is apparently distressed.
> > >
> > > I would expect (a) could be delivered in an ESD given its an addition
to
> > > functionality rather than a change whereas (b) may well be better
linked
> > to
> > > a point release given there is an increase to the column count for
> > > sysprocesses.
> > >
> > > Thoughts?
> > >
> > > Frank Hamersley.
> > >
> > >
>
> --
> Kevin Sherlock
> Staff Info Systems Analyst
> Omaha, NE
> Dex Media
> http://www.qwestdex.com
> [TeamSybase]
> http://teamsybase.com/kevin.sherlock
> Take a look at CodeXchange:
> http://www.codexchange.sybase.com
> ISUG Enhancement Requests
> http://www.isug.com/cgi-bin/ISUG2/submit_enhancement


Frank_Hamersley Posted on 2003-10-23 08:09:50.0Z
Reply-To: "Frank_Hamersley" <terabite@at.bigpond.com>
From: "Frank_Hamersley" <terabite@bigpond.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub> <3F969BF4.6F40CF2F@qwest.com.nospam> <3f974573@forums-1-dub>
Subject: Re: Improve usefulness of sysprocesses.physical_io
Lines: 252
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
X-Original-NNTP-Posting-Host: ess-p-144-138-5-8.mega.tmns.net.au
Message-ID: <3f978dcc@forums-2-dub>
X-Original-Trace: 23 Oct 2003 01:14:04 -0700, ess-p-144-138-5-8.mega.tmns.net.au
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Oct 2003 01:02:49 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Oct 2003 01:09:50 -0700
X-Trace: forums-1-dub 1066896590 10.22.108.75 (23 Oct 2003 01:09:50 -0700)
X-Original-Trace: 23 Oct 2003 01:09:50 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1441
Article PK: 96451

Well said Gunga Din!

An ounce (or a gram) of well directed lightweight debug that corrals an "in
the wild" problem to a narrow section of an application is far more useful
than a tonne of heavyweight tools generating copious output of every burp
the system produces - at least until you figure what is wheat and what is
chaff.

Usually I find that if you can get close to the underlying cause in the very
first encounter, you stand a much better chance of reproducing the problem
(or of at least deducing its nature) on the Test system where you can afford
to deploy all the big debug tools to nail it.

Would "pretty please" be enought to get @@pio in 12.5.1 ESD#1 and/or
12.5.0.3 ESD#5?

Cheers,
Frank.

"Grant" wrote
> There is no question most of the data is there or can be reasonably
> interpolated - either from MDA or Historical/Monitor server... BUT using
> these comes at a cost. Percentages of 5-10%/engine have been and are oft
> quoted - in an 8 engine ASE - more than 1/2 of one CPU is doing monitoring
> for us... + maintenance costs, license cost, support costs.... it is not
> just about CPU and memory. Low cost effective monitoring hooks are
> essential.
>
> However:
> 1. These are mutually exclusive - You choose if you use MDA or Mon/Hist
> server.
> 2. As the # of engines/cpu's increase so does the cost...
> 3. Due to memory mapping these must co-exist with the system under "load".
> It would be better if these could be more loosely coupled with a memory
> mapped remote io's so that the load imposed is much lower... this would
> likely be considered a vulnerability though; so I can understand why this
> has not been shipped.
> 4. Yes, the counters being monitored can be reduced to lower the load/cost
> imposed by monitoring.
>
> The start of a counter argument usually goes "If you can't afford 10% of
CPU
> headroom for monitoring to find out when poor code gets into the wild and
to
> trend the system....".
>
> Of these #2 is my biggest inhibitor to using Mon/Hist server in prod
> environments. If I am doing CP/tuning work, I don't want my system under
> test/load to suffer measurably from the introspection tool. Having to
allow
> caveat around the artifacts of monitoring is just not a great starting
> point - if you have a choice. Most every performance report shows that the
> monitoring with Hist/Mon server (and now presumably mda) is in the top
10-15
> tasks; try writing the summary and dodging that!
>
> When monitoring costs more than 2-3% it is hard to justify it to people
> funding the systems as a core process. Especially as systems become more
> stressed later in their lifecycle. When a server heads toward whatever
level
> of utilisation you deem to be distress, non-core things start to be
> rationalised. The argument is - "Is monitoring as core as database
> responsiveness?" and the usual answer is "it depends..." which mostly
> translates to "Turn it off! I'm not paying for that.".
>
> I would like Sybase to consider exposing more of the pss structure:
>
> Either as:
> 1. Readable columns in sysprocesses which is only materialised on demand,
as
> I understand it.
> 2. A binary pointer col to the PSS and document the structure to allow
> external processing away from the ASE instance in a client process.
> 3. Maintain session level io counters in sysprocesses.
> 4. At slightly more expense, readonly session variables is another good
> approach.
> 5. Account for family cpu/io etc at the family owner spid level as workers
> are destroyed.
> 6. Should Sybase allow TSQL UDF's we can then create any number of
> pseudo-session variables on published data in approach 1 or 2.
>
> It is my expectation that 1 - 2 in particular should not increase cost
> dramatically over a regular select from sysprocesses, in either memory or
> CPU terms, and still this would be less than using mda or Mon/hist server
to
> collect the same data.
>
> Thoughts/Comments?
> Cheers
> Grant Queenin
>
> "Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
> news:3F969BF4.6F40CF2F@qwest.com.nospam...
> > Actually, Historical Server/ASE Monitor server can do this quite well.
> > Here is a Historical view which you could setup an alarm for which fires
> > off a perl script to do anything you want.
> >
> > hs_create_view spid_obj_io,
> > "Login Name", "Value for Sample",
> > "Process ID", "Value for Sample",
> > "Kernel Process ID", "Value for Sample",
> > "Database Name", "Value for Sample",
> > "Database ID", "Value for Sample",
> > "Object Name", "Value for Sample",
> > "Object ID", "Value for Sample",
> > "Object Type", "Value for Sample",
> > "Page Hit Percent", "Value for Sample",
> > "Page I/O", "Rate for Sample",
> > "Logical Page Reads", "Rate for Sample",
> > "Index Logical Reads", "Rate for Sample",
> > "Physical Page Reads", "Rate for Sample",
> > "Index Physical Reads", "Rate for Sample",
> > "Page Writes", "Rate for Sample"
> > go
> > hs_create_alarm spid_obj_io, "Physical Page Reads", "Rate for Sample",
> > execute, "/home/sybase/hs_alarm.pl",100
> > go
> >
> > Frank_Hamersley wrote:
> > >
> > > Pablo,
> > >
> > > It may well be available in the monitor tables...I haven't had time to
> check
> > > in detail but will shortly.
> > >
> > > However, my main interest (which is somewhat aligned with Grants I
> believe)
> > > is to have a lightweight hook that can be embedded in the application
> > > systems logging and then deployed unchanged into the Production
> environment.
> > >
> > > This will assist in unifying log analysis for developers rather than
> forcing
> > > them to correlate the progress of the problem in multiple disparate
> > > histories. Further when those nasty intermittant issues arise in
> production
> > > then hopefully some useful information can be obtained without having
to
> > > spend time trying to reproduce the problem in a "trussed" test bed.
> > >
> > > The app that I am engaged on at the moment has a number of daemon type
> > > processes hammering the database. In order to get prior warning of
> sprocs
> > > that are worthy of review (poor performers) I find I need to take a
> > > statistical view of all the work they are doing and without PIO there
is
> not
> > > much to go on. I can use CPU and elapsed times for an initial
pointer,
> but
> > > excessive PIO is the real performance killer for ASE hence my first
> post.
> > >
> > > As an aside what is your view about the cost/risk of leaving Monitor
> engaged
> > > on a very active production server?
> > >
> > > Cheers,
> > > Frank.
> > >
> > > "Frank_Hamersley" <terabite@bigpond.com> wrote in message
> > > news:3f95d83f@forums-1-dub...
> > > > At the moment the physical_io column in sysprocesses only returns
the
> > > amount
> > > > of IO for the currently executing command.
> > > >
> > > > This means a spid can never find out how much pio it performed in
its
> own
> > > > right because when it consults sysprocesses it is executing a new
> command
> > > > and therefore physical_io is always 0. For example...
> > > >
> > > > SELECT * INTO #ttt FROM VeryLargeTable
> > > > SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> > > > physical_io
> > > > -----------
> > > > 0
> > > > (1 row affected)
> > > >
> > > > Without changing ASE current behaviour can we please have ...
> > > > a) @@pio = Cumulative amount of PIO performed by @@spid
> > > > b) master..sysprocesses.pio = Cumulative PIO for process in
> > > reads/writes.
> > > > c) @@physical_io = Amount of PIO performed by the previous
command
> for
> > > > @@spid
> > > >
> > > > NB Option (c) is not likely to be of much use because you can
> synthesise
> > > it
> > > > easily by bracketing the statement(s) you are interested in with
> something
> > > > like...
> > > >
> > > > SELECT @pio_was = @@pio
> > > > UPDATE BigTable WHERE LotsOfMatches = 1
> > > > SELECT @pio_used = @@pio - @pio_was
> > > > -- previous statement probably requires overflow logic for very long
> > > running
> > > > spids
> > > >
> > > > However (a) would be _very_ useful in profiling more complex ASE
> > > application
> > > > systems where occasional performance problems arise and are not
always
> > > > observed by sys admins (unless they happen to be scanning
sysprocesses
> on
> > > a
> > > > very frequent basis). Then the application could accurately log its
> PIO
> > > for
> > > > later review by the system designers rather than a sys admin trying
to
> do
> > > > showplans etc when the system is apparently distressed.
> > > >
> > > > I would expect (a) could be delivered in an ESD given its an
addition
> to
> > > > functionality rather than a change whereas (b) may well be better
> linked
> > > to
> > > > a point release given there is an increase to the column count for
> > > > sysprocesses.
> > > >
> > > > Thoughts?
> > > >
> > > > Frank Hamersley.
> > > >
> > > >
> >
> > --
> > Kevin Sherlock
> > Staff Info Systems Analyst
> > Omaha, NE
> > Dex Media
> > http://www.qwestdex.com
> > [TeamSybase]
> > http://teamsybase.com/kevin.sherlock
> > Take a look at CodeXchange:
> > http://www.codexchange.sybase.com
> > ISUG Enhancement Requests
> > http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
>
>


Bret Halford Posted on 2003-10-23 15:23:50.0Z
Message-ID: <3F97F0E0.FFDFF309@sybase.com>
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en,ja
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Improve usefulness of sysprocesses.physical_io
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub> <3F969BF4.6F40CF2F@qwest.com.nospam> <3f974573@forums-1-dub> <3f978dcc@forums-2-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vpn-dub-116.sybase.com
X-Original-NNTP-Posting-Host: vpn-dub-116.sybase.com
Date: 23 Oct 2003 08:23:50 -0700
X-Trace: forums-1-dub 1066922630 10.22.120.116 (23 Oct 2003 08:23:50 -0700)
X-Original-Trace: 23 Oct 2003 08:23:50 -0700, vpn-dub-116.sybase.com
Lines: 21
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1442
Article PK: 96448


Frank_Hamersley wrote:

> Would "pretty please" be enought to get @@pio in 12.5.1 ESD#1 and/or
> 12.5.0.3 ESD#5?

Well, that I can answer. Absolutely not, because 12.5.1 ESD #1 hit
code-freeze
some time ago and is due to be released very soon. There are no further
12.5.0.x
ESDs in the works, 12.5.0.3 ESD #4 was the last.

In general, new features are only put in for IR releases, such as a
12.5.1.1,
12.5.2, or the future "Galaxy / 15.0" release. You are probably more
likely to
be able to get enhancements to the MDA monitor tables than new global
variables.

-bret


Jason L. Froebe [TeamSybase] Posted on 2003-10-23 20:03:29.0Z
From: "Jason L. Froebe [TeamSybase]" <jason@froebe.net>
Newsgroups: sybase.public.ase.product_futures_discussion
Organization: TeamSybase[151.191.175.195]
References: <3f95d83f@forums-1-dub> <3f960840@forums-1-dub> <3F969BF4.6F40CF2F@qwest.com.nospam> <3f974573@forums-1-dub> <3f978dcc@forums-2-dub> <3F97F0E0.FFDFF309@sybase.com>
X-Newsreader: AspNNTP 1.50 (Tri Hoang)
Subject: Re: Improve usefulness of sysprocesses.physical_io
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: transinc-win02.netoptex.com
X-Original-NNTP-Posting-Host: transinc-win02.netoptex.com
Message-ID: <3f983411$1@forums-1-dub>
Date: 23 Oct 2003 13:03:29 -0700
X-Trace: forums-1-dub 1066939409 206.169.167.22 (23 Oct 2003 13:03:29 -0700)
X-Original-Trace: 23 Oct 2003 13:03:29 -0700, transinc-win02.netoptex.com
Lines: 33
X-Authenticated-User: teamps
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1443
Article PK: 96449

On 23 Oct 2003 08:23:50 -0700,
in sybase.public.ase.product_futures_discussion

Bret Halford <bret@sybase.com> wrote:
>Frank_Hamersley wrote:
>
>> Would "pretty please" be enought to get @@pio in 12.5.1 ESD#1 and/or
>> 12.5.0.3 ESD#5?
>
>Well, that I can answer. Absolutely not, because 12.5.1 ESD #1 hit
>code-freeze
>some time ago and is due to be released very soon. There are no further
>12.5.0.x
>ESDs in the works, 12.5.0.3 ESD #4 was the last.
>
>In general, new features are only put in for IR releases, such as a
>12.5.1.1,
>12.5.2, or the future "Galaxy / 15.0" release. You are probably more
>likely to
>be able to get enhancements to the MDA monitor tables than new global
>variables.
>
>-bret

Just to add to what Bret said... create the feature request (enhancement
request) on the ISUG web page as they have more weight than just a normal
feature request filed on Sybase's website.

Jason L. Froebe
TeamSybase (http://www.teamsybase.com)
ISUG member (http://www.isug.com)
Chicago Sybase Tools User Group (http://www.cpbug.com)
HealthyPals (http://www.healthypals.com)


Peter Dorfman Posted on 2004-02-17 21:58:54.0Z
Message-ID: <40328A45.CB5CE7C2@sybase.com>
From: Peter Dorfman <peter.dorfman@sybase.com>
Organization: SMMD
X-Mailer: Mozilla 4.78 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
To: Frank_Hamersley <terabite@at.bigpond.com>
Subject: Re: Improve usefulness of sysprocesses.physical_io
References: <3f95d83f@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: jura.sybase.com
X-Original-NNTP-Posting-Host: jura.sybase.com
Date: 17 Feb 2004 13:58:54 -0800
X-Trace: forums-1-dub 1077055134 10.18.60.144 (17 Feb 2004 13:58:54 -0800)
X-Original-Trace: 17 Feb 2004 13:58:54 -0800, jura.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1511
Article PK: 96510

I'm sorry to come to this discussion (very) late. But I would first suggest
trying the MDA table monProcessActivty to see whether this a) provides the
information needed or b) degrades performance measurably. I believe the
performance impact will be slight, but this depends on a number of factors
including the frequency with which the table is queried, the existing load on
the server, # of engines, etc.

The monProcessActivty table contains columns for cumulative logical and physical
IO, CPU usage, wait time, lock usage, and a number of other metrics describing
process level resource usage. There are other tables that break this
information down at the statement level.

If the MDA tables do not provide the information needed or performance impact is
found to be too great, please post this informant and, as Jason indicated,
submit enhancement request for any missing features.

Peter Dorfman
Sybase Engineering

Frank_Hamersley wrote:

> At the moment the physical_io column in sysprocesses only returns the amount
> of IO for the currently executing command.
>
> This means a spid can never find out how much pio it performed in its own
> right because when it consults sysprocesses it is executing a new command
> and therefore physical_io is always 0. For example...
>
> SELECT * INTO #ttt FROM VeryLargeTable
> SELECT physical_io FROM master..sysprocesses WHERE spid = @@spid
> physical_io
> -----------
> 0
> (1 row affected)
>
> Without changing ASE current behaviour can we please have ...
> a) @@pio = Cumulative amount of PIO performed by @@spid
> b) master..sysprocesses.pio = Cumulative PIO for process in reads/writes.
> c) @@physical_io = Amount of PIO performed by the previous command for
> @@spid
>
> NB Option (c) is not likely to be of much use because you can synthesise it
> easily by bracketing the statement(s) you are interested in with something
> like...
>
> SELECT @pio_was = @@pio
> UPDATE BigTable WHERE LotsOfMatches = 1
> SELECT @pio_used = @@pio - @pio_was
> -- previous statement probably requires overflow logic for very long running
> spids
>
> However (a) would be _very_ useful in profiling more complex ASE application
> systems where occasional performance problems arise and are not always
> observed by sys admins (unless they happen to be scanning sysprocesses on a
> very frequent basis). Then the application could accurately log its PIO for
> later review by the system designers rather than a sys admin trying to do
> showplans etc when the system is apparently distressed.
>
> I would expect (a) could be delivered in an ESD given its an addition to
> functionality rather than a change whereas (b) may well be better linked to
> a point release given there is an increase to the column count for
> sysprocesses.
>
> Thoughts?
>
> Frank Hamersley.