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.

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

6 posts in General Discussion Last posting was on 2009-10-23 00:26:43.0Z
Dean Jones Posted on 2009-10-22 10:51:51.0Z
From: Dean Jones <deanjones7@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: How to use the MDA tables to find who's table scanning
Date: Thu, 22 Oct 2009 03:51:51 -0700 (PDT)
Organization: http://groups.google.com
Lines: 18
Message-ID: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com>
NNTP-Posting-Host: 121.44.139.195
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1256208711 25155 127.0.0.1 (22 Oct 2009 10:51:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 22 Oct 2009 10:51:51 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a37g2000prf.googlegroups.com; posting-host=121.44.139.195; posting-account=lLyz5QoAAADq4IjOnKVmCNalqhtDHo7E
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!newsfeed.kpn.net!pfeed09.wxs.nl!xlned.com!feeder3.xlned.com!feeder.news-service.com!postnews.google.com!a37g2000prf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28522
Article PK: 77766

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] Posted on 2009-10-22 12:12:47.0Z
From: "mpeppler@peppler.org [Team Sybase]" <michael.peppler@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to use the MDA tables to find who's table scanning
Date: Thu, 22 Oct 2009 05:12:47 -0700 (PDT)
Organization: http://groups.google.com
Lines: 32
Message-ID: <e1ac7c29-6842-4ad9-b480-e69f3478dd14@k26g2000vbp.googlegroups.com>
References: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com>
NNTP-Posting-Host: 170.148.198.157
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1256213567 10087 127.0.0.1 (22 Oct 2009 12:12:47 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 22 Oct 2009 12:12:47 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: k26g2000vbp.googlegroups.com; posting-host=170.148.198.157; posting-account=9rHMzAoAAADtzToS8d2WKVGlkISAvPdk
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!k26g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28523
Article PK: 77764


On Oct 22, 12:51 pm, Dean Jones <deanjon...@gmail.com> wrote:
> 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.

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] Posted on 2009-10-22 15:27:09.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com>
Subject: Re: How to use the MDA tables to find who's table scanning
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae079cd$1@forums-1-dub>
Date: 22 Oct 2009 08:27:09 -0700
X-Trace: forums-1-dub 1256225229 10.22.241.152 (22 Oct 2009 08:27:09 -0700)
X-Original-Trace: 22 Oct 2009 08:27:09 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28524
Article PK: 77765

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" <deanjones7@gmail.com> wrote in message
news:7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com...
> 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.


Sherlock, Kevin [TeamSybase] Posted on 2009-10-22 15:30:57.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com> <4ae079cd$1@forums-1-dub>
Subject: Re: How to use the MDA tables to find who's table scanning
Lines: 136
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae07ab1$1@forums-1-dub>
Date: 22 Oct 2009 08:30:57 -0700
X-Trace: forums-1-dub 1256225457 10.22.241.152 (22 Oct 2009 08:30:57 -0700)
X-Original-Trace: 22 Oct 2009 08:30:57 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28525
Article PK: 77767

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

1> exec sp_monitor 'help'

Usage: sp_monitor [ 'enable' | 'disable' | 'connection' | 'procedure' |
'statement' | 'event' | 'deadlock' | 'procstack' | 'archive'

| 'report' [, ...] ]
Usage: sp_monitor help [, 'enable' | 'disable' | 'connection' | 'procedure'
| 'statement' | 'event' | 'deadlock' | 'procstack' |

'archive' | 'report']
Usage: sp_monitor help, 'all'

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.sherlock@teamsybase.com> wrote in
message news:4ae079cd$1@forums-1-dub...
> 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" <deanjones7@gmail.com> wrote in message
> news:7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com...
>> 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.
>
>


Sherlock, Kevin [TeamSybase] Posted on 2009-10-22 15:34:24.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com> <4ae079cd$1@forums-1-dub> <4ae07ab1$1@forums-1-dub>
Subject: Re: How to use the MDA tables to find who's table scanning
Lines: 146
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae07b80$1@forums-1-dub>
Date: 22 Oct 2009 08:34:24 -0700
X-Trace: forums-1-dub 1256225664 10.22.241.152 (22 Oct 2009 08:34:24 -0700)
X-Original-Trace: 22 Oct 2009 08:34:24 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28526
Article PK: 77769

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.sherlock@teamsybase.com> wrote in
message news:4ae07ab1$1@forums-1-dub...
> you might also want to look into "sp_monitor". Maybe "exec sp_monitor
> 'statement' "
>
> 1> exec sp_monitor 'help'
>
> Usage: sp_monitor [ 'enable' | 'disable' | 'connection' | 'procedure' |
> 'statement' | 'event' | 'deadlock' | 'procstack' | 'archive'
> | 'report' [, ...] ]
> Usage: sp_monitor help [, 'enable' | 'disable' | 'connection' |
> 'procedure' | 'statement' | 'event' | 'deadlock' | 'procstack' |
> 'archive' | 'report']
> Usage: sp_monitor help, 'all'
>
> 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.sherlock@teamsybase.com> wrote in
> message news:4ae079cd$1@forums-1-dub...
>> 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" <deanjones7@gmail.com> wrote in message
>> news:7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com...
>>> 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.
>>
>>
>
>


Dean Jones Posted on 2009-10-23 00:26:43.0Z
From: Dean Jones <deanjones7@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to use the MDA tables to find who's table scanning
Date: Thu, 22 Oct 2009 17:26:43 -0700 (PDT)
Organization: http://groups.google.com
Lines: 79
Message-ID: <e49be22f-500b-4262-8f24-161ea776a2c3@m33g2000pri.googlegroups.com>
References: <7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com> <4ae079cd$1@forums-1-dub>
NNTP-Posting-Host: 121.44.240.212
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1256257604 6502 127.0.0.1 (23 Oct 2009 00:26:44 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 23 Oct 2009 00:26:44 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m33g2000pri.googlegroups.com; posting-host=121.44.240.212; posting-account=lLyz5QoAAADq4IjOnKVmCNalqhtDHo7E
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m33g2000pri.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28535
Article PK: 77776

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]"

<kevin.sherl...@teamsybase.com> wrote:
> 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" <deanjon...@gmail.com> wrote in message
>
> news:7b8e0ed7-700a-49c3-8772-0f832a160595@a37g2000prf.googlegroups.com...
>
> > 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.