How to use the MDA tables to find who's table scanning

Dean Jones

unread,

Oct 22, 2009, 6:51:51 AM10/22/09

to

Hi,

We're using ASE 15.0.3. Since we upgraded we've had some performance
problems. Using sp_sysmon I noticed that the 16K pool in the default
data cache is rejecting statements on a regular basis because its not
big enough (its 500MB).

To me this looks like some queries are confusing the optimizer and its
trying to use large i/o. We've got some very large tables.

I'm trying to find the queries causing this via the MDA tables but I'm
not having much luck.

I figure anything doing a lot of i/o is a candidate.

Can anyone give me some recommendations to find the likely queries.

Thanks.

mpeppler@peppler.org [Team Sybase]

unread,

Oct 22, 2009, 8:12:47 AM10/22/09

to

You can find which *tables* are involved in table scans via
monOpenObjectActivity. A table scan is identified by IndId = 0, and
UsedCount > 0.

To find which queries are table scanning it can be a little trickier -
you'd have to capture the statements and the plans (via
monSysStatements, monSysPlanText and monSysSQLText), and this requires
a bit of thought (you need to configure the "pipe" for each of these
to be large enough, and you'll have to query the tables every X
seconds and insert the data in a work database.

Michael

Sherlock, Kevin [TeamSybase]

unread,

Oct 22, 2009, 11:27:09 AM10/22/09

to

Dean,
Getting the specific line of SQL code from MDA isn't an exact science using
sql. But, here is a snippet of code that lists the top 10 queries by
logical_io. In the output is included the line number of the batch
consuming the io, and the _first_ 40 characters of the batch. You would
have to use this information to know what the batch of SQL looks like, and
then find the corresponding line number in that batch. If the batch is a
stored procedure execution, you could use:

exec sp_showtext <procname>, <linenumber>, <number_of_lines>, 'showsql'

There isn't really an equivalent way when the batch is not a proc call, but
rather a series of statements and lines sent using language commands. Other
information in monSysStatement such as StatementNumber et al can help you
pinpoint the exact source in the batch consuming the IO.

Of course, you have to have all of the necessary MDA configurations (pipes,
max messages, etc) enabled for this to work.

=================
select *
into #SQLText
from master..monSysSQLText

select *
into #Stmnt
from master..monSysStatement

select top 10
st.KPID
, st.LineNumber
, st.LogicalReads
, datediff(ms, st.StartTime, st.EndTime) MMSecs
, st.WaitTime
, st.CpuTime
, convert(varchar(40),txt.SQLText) SQLText
from #Stmnt st INNER JOIN #SQLText txt
ON st.KPID = txt.KPID
and st.BatchID = txt.BatchID
and txt.SequenceInBatch = 1
where st.KPID != (select sp.kpid
from master..sysprocesses sp
where sp.spid = @@spid)
order by st.LogicalReads desc,st.KPID,st.BatchID,st.LineNumber
go
drop table #SQLText
,#Stmnt
go

"Dean Jones" <deanj...@gmail.com> wrote in message
news:7b8e0ed7-700a-49c3...@a37g2000prf.googlegroups.com...

Sherlock, Kevin [TeamSybase]

unread,

Oct 22, 2009, 11:30:57 AM10/22/09

to

you might also want to look into "sp_monitor". Maybe "exec sp_monitor
'statement' "

1> exec sp_monitor 'help'

Usage: sp_monitor [ connection, [ cpu | diskio | elapsed time]]
Examples: sp_monitor 'connection', 'elapsed time'

Usage: sp_monitor [ event, [spid]]
Examples: sp_monitor 'event', '5'

Usage: sp_monitor [ procedure, [ dbname , [ procname, [, summary |
detail]]]]
Examples: sp_monitor 'procedure'
Examples: sp_monitor 'procedure', 'employee_db', 'sp_get_salary'
Examples: sp_monitor 'procedure','detail'
Examples: sp_monitor 'procedure', 'employee_db', 'sp_get_employee_id',
'detail'

Usage: sp_monitor [ enable ]
Examples: sp_monitor 'enable'

Usage: sp_monitor [ disable ]
Examples: sp_monitor 'disable'

Usage: sp_monitor [ statement, [ cpu | diskio | elapsed time]]
Examples: sp_monitor 'statement', 'elapsed time'

Usage: sp_monitor 'deadlock' [ [, '@filters' ] [, '@output_modes' ] ]
Usage: sp_monitor 'deadlock'
[ [, '<deadlockID>' | '<for Date>' ]
[, { 'verbose' | 'pagediag' }
|{ 'count by date'
| 'count by application'
| 'count by object'
| 'count by date, object'
} ]
]

Usage: sp_monitor 'procstack' [, '<spid>'] [, '<contextblock>' ]

Usage: sp_monitor 'archive [using prefix=<string>]' {, '<monitoring_type>' }
Archiving is currently supported for only these monitoring types:
'deadlock'.

Usage: sp_monitor 'report [using prefix=<string>]' , '<monitoring_type>'
[ <options supported for monitoring_type> ]
Reporting from an archive is currently supported for only these monitoring
types: 'deadlock'.

"Sherlock, Kevin [TeamSybase]" <kevin.s...@teamsybase.com> wrote in
message news:4ae079cd$1@forums-1-dub...

Sherlock, Kevin [TeamSybase]

unread,

Oct 22, 2009, 11:34:24 AM10/22/09

to

also another possibility is sysquerymetrics.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc34982_1500/html/mig_gde/mig_gde150.htm

"Sherlock, Kevin [TeamSybase]" <kevin.s...@teamsybase.com> wrote in

message news:4ae07ab1$1@forums-1-dub...

Dean Jones

unread,

Oct 22, 2009, 8:26:43 PM10/22/09

to

Hi Kevin,

Thanks. I'll try a few of the suggestions you and Michael have made.

On Oct 23, 2:27 am, "Sherlock, Kevin [TeamSybase]"

> "Dean Jones" <deanjon...@gmail.com> wrote in message