Can you run MDA (Monitoring Tables) and sp_sysmon at the same time?

[susanmills]

Can we run MDA (Monitoring Tables) and sp_sysmon at the same
time?

We are trying to resolve memory issues we are having. We
would like to track SQL statements and transactions via MDA
and monitor cache via sp_sysmon. Can they be ran at the
same time?

Thank you

[Manish Negandhi]

Starting ASE 12.5.3 , you can run sp_sysmon with "noclear" option.
Running sysmon with this option will not clear monitor counters so you
can run MDA tables and sysmon together using "noclear" option

-HTH
Manish Negandhi
[TeamSybase Intern]

[mpep...@peppler.org]

Yes, provided you run sp_sysmon with the 'noclear'
parameter, e.g.

sp_sysmon '00:05:00', 'noclear'

to run sp_sysmon over a 5 minute period.

Michael

[susanmills]

Thank you for your quick response and feedback to my
question concerning running sp_sysmon and MDA at the same
time.

Please forgive me if I ask a dumb question for
clarification...

Does this apply if sp_sysmon was running before MDA queries?
Or does it apply in both cases (before or after sp_sysmon
runs).

For example...

--------------------------------------
Test Case / Monitoring Case 01:
--------------------------------------
1) Start sp_sysmon for the duration of 8 hours.

2) Start test and run identified scripts / sql code.

3) Run MDA queries (i.e. against monProcessSQLText and
monProcessStatement) periodically / during identified
timeframes throughout the test case.
--------------------------------------

What are your thoughts?

Thank you,

Susan

[Chris N. Brown]

On Tue, 15 Apr 2008 05:17:28 -0700, Susan Mills wrote:

[SNIP]

>
> --------------------------------------
> Test Case / Monitoring Case 01:
> --------------------------------------
> 1) Start sp_sysmon for the duration of 8 hours.
>
> 2) Start test and run identified scripts / sql code.
>
> 3) Run MDA queries (i.e. against monProcessSQLText and
> monProcessStatement) periodically / during identified
> timeframes throughout the test case.
> --------------------------------------
>

This will work just remember to run sp_sysmon with the 'noclear' option
(as stated before).

One other thing I would suggest is that in order to get anything
meaningful out of some of the MDA tables, you need to run a query to get
the 'before' value, and then run another one to get an 'after' values.
Then, just do a min / max to get a delta (and the performance metric you
are looking for).

This would be true for all of the 'pipe' tables that just hold values
that keep increasing over time (For example, monSysWaits, monIOQueue,
monDeviceIO ... )

HTH,
--Chris

[susanmills]

I think I understand now. I think the answer is...

Yes, you always have to use the "noclear" option with SYSMON
whenever you use MDA during the same timeframe. Is this
correct?

Meaning, that if I...

1) start "sp_sysmon '08:00:00'" at 9:00AM.

2) And at 11:00AM I cannot run MDA query (the below query)
if the "noclear" options was not set / used in step "1)"...

select s.SPID, s.CpuTime, t.LineNumber, t.SQLText
from monProcessStatement s, monProcessSQLText t
where s.SPID=t.SPID
order by s.CpuTime, s.SPID, t.LineNumber desc

I apologize for having to ask again for clarification. I'm
hoping to get a clear "yes, you are correct" or "no, you are
not correct becuase there are exceptions".

Sincere thanks.

[michael...@gmail.com]

On Apr 16, 1:31 pm, Susan Mills wrote:
> I think I understand now. I think the answer is...
>
> Yes, you always have to use the "noclear" option with SYSMON
> whenever you use MDA during the same timeframe. Is this
> correct?
>
> Meaning, that if I...
>
> 1) start "sp_sysmon '08:00:00'" at 9:00AM.
>
> 2) And at 11:00AM I cannot run MDA query (the below query)
> if the "noclear" options was not set / used in step "1)"...
>
> select s.SPID, s.CpuTime, t.LineNumber, t.SQLText
> from monProcessStatement s, monProcessSQLText t
> where s.SPID=t.SPID
> order by s.CpuTime, s.SPID, t.LineNumber desc
>
> I apologize for having to ask again for clarification. I'm
> hoping to get a clear "yes, you are correct" or "no, you are
> not correct becuase there are exceptions".

Actually - you are not correct. The "noclear" option is needed because
by default sp_sysmon will clear the counters when it starts and/or
ends (Chris et.al. can specify the exact behavior). If you run it for
8 hours, and want to query the MDA tables in the middle of the run
then you should be OK - querying the MDA tables will not affect
sp_sysmon.

If on the other hand you use some tool to extract MDA table data on a
regular basis then you need to make sure that sp_sysmon is run with
'noclear' so as to not affect the data in the MDA tables, and
generating inconsistencies in your MDA collection scheme.

Michael

[Derek Asirvadem]

> On 2008-04-15 22:17:28 +1000, Susan Mills said:
>
> --------------------------------------
> Test Case / Monitoring Case 01:
> --------------------------------------
> 1) Start sp_sysmon for the duration of 8 hours.
>
> 2) Start test and run identified scripts / sql code.
>
> 3) Run MDA queries (i.e. against monProcessSQLText and
> monProcessStatement) periodically / during identified
> timeframes throughout the test case.
> --------------------------------------

I am responding to your approach (others have already covered the detail).

This is a good approach: (1) covers the basic stats you need for ASE
config changes and tuning; (2) & (3) together are for point work, to
identify which queries are causing problems. (The pre-MDA method was
to run sp_sysmon for the duration [eg 2 mins] of the execution of a
known-to-be-bad query.) Essentially, use sp_sysmon for monitoring and
MDA for diagnosis; if you are look ing at diagnosis only, you do not
need sp_sysmon running as well.

When using MDA tables, you really need to study it and do it properly.
Many people think I am "for" or "against" MDA tables. I am not, I am
all for (a) using the right tool for the job [MDA is good for this type
of diagnosis in the absence of a P&T special-ist and educated code
reviews] and (b) setting accurate expectations.

1 Read Rob Veschoor's webiste entry on the subject
2 get Jeff Tallman's diagram (data model) and ensure you have the
correct joins (a lot of time wasted here).
3 Understand that some of them are very transient. If joins fail, it
is usually because the data has already been flushed. So the first
thing you have to do is make particular tables persistent, and work off
them.
4 Because you are capturing SQL text, the overhead is high: you will
need a separate tempdb (or even better, a separate Db) for (3) and a
separate named cache. If for no other reason, simpky to ensure that
their stats are not mixed in (and therefore colouring) the rest of the
stats which you are after.

Some will advise extracting the MDA tables (SQL text plus plus) onto a
separate machine, but I would say that has gone too far (you are then
spending time/resources on the diagnostic tool rather than the
diagnosis).
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright Š 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability