Skip to Content

Since ASE1252 CR52524, SAP ASE instroduced a new dbcc command ‘dbcc stackused’ to provide an estimate of maximum amount of stack memory used since ASE server last boot.

Currently, it is only used to report max stack size which is not currently used by spid,

and there is one CR620774 -“FR: Extend DBCC STACKUSED to report on current spid’s stack usage”,

Which was planned to add this new feature in ASE16 SP01 !!

At this time, I think that is a helpful tool for DBA to monitor stack size usage to estimate if he/she need to adjust ‘stack size’ before really getting ‘stack overflow’ error !!

I did a simple test as following for your reference ! HTH !! Thanks !!

Steps:

1. recycle ASE:

2. collect first ‘dbcc stackused’ report:

/data/ase1570/ASE-15_0/install > isql -Usa -Psybase -SASE1570
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

1> dbcc stackused
2> go

Configured stack size (including the guard area) : 135168 bytes
Configured guard area size : 4096 bytes

Max stack used by a db process since startup : 70112 bytes
        by current kpid 2818070
        by current spid 16

DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

3. execute sp_sysmon script :

/data/ase1570/ASE-15_0/install > isql -Usa -Psybase -SASE1570 -isp_sysmon.sql -osp_sysmon.log

4. collect second ‘dbcc stackused’ report :

/data/ase1570/ASE-15_0/install > isql -Usa -Psybase -SASE1570
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc stackused
2> go

Configured stack size (including the guard area) : 135168 bytes
Configured guard area size : 4096 bytes

Max stack used by a db process since startup : 78064 bytes
        by a currently free db process

DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1>

We can see the value of ‘Max stack used by a db process since startup’ changed from 70112 bytes to 78064 bytes after ‘sp_sysmon’ executed !!

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply