Sybase ASE
pre-12.5.0.3 12.5.0.3+ 15.x
Last updated: 18 March 2013
MDA/Monitoring tables in ASE
 
Contents


Introduction
Starting in ASE 12.5.0.3, a new feature called 'MDA tables' is available to ASE users. These MDA tables provide access to low-level monitoring information in ASE; since the MDA tables can be accessed with regular SQL select statements, they're much easier to use than products like Monitor Server/Historical Server.

The MDA tables are proxy tables, located in the master database. All MDA tables are named master..monXXX, where XXX indicates the type of information in the table.
One example is master..monCachedObject, which contains details about the current data cache usage for different tables and indexes.

'MDA' is short for 'Monitoring Data Access', 'Monitoring and Diagnostics for ASE', 'Monitoring and Diagnostic Agent' or 'Monitoring and Diagnostic Access', depending on who you ask. As 'monitoring' seems to be a common denominator, the MDA tables are also referred to as 'monitoring tables' (although they're not normal tables, but in fact proxy tables mapped to native RPCs inside ASE).

A poster of the MDA table, showing the relationships, can be found in the ASE documentation at sybooks.sybase.com, for example here.

Presentations
Here are some useful presentations related to MDA tables:
Tools (+download)
I wrote some handy stored procedures for easy access to some of the MDA tables. Download here (requires 15.0.2+).
ASE 12.5.x users: Download here for 12.5.x (NB: the 12.5 version will probably not be maintained or enhanced any further).

Quick usage info (for on-line usage information, execute these procedures with '?' as the first parameter):
  • sp_mda_help - provides a quick way of searching for specific MDA tables or columns, based on a substring. Examples:
    -- List all cache-related MDA tables:
    sp_mda_help cach
    
    -- List all columns & parameters for a specific table: 
    sp_mda_help monObjectActivity
    
    -- List all columns having something to do with SQL text:
    sp_mda_help null, sql
    
  • sp_mda_io - monitors logical (and physical) I/O usage by T-SQL statements. Examples:
    -- Reports I/O usage since previous call 
    sp_mda_io
    
    -- Reports I/O for a particular T-SQL batch:
    sp_mda_io "...T-SQL statement(s)..."
    
  • sp_mda_wait - displays detailed information about wait events during a certain interval (default=10 seconds) Examples:
    -- displays wait information for the entire ASE server
    sp_mda_wait 'server'
    
    -- displays wait information for a specific process
    sp_mda_wait '<spid_no>'
    
    -- displays wait information for all processes along with 
    -- additional process details (such as the SQL currently 
    -- being executed)
    sp_mda_wait 'all'
    
  • sp_mda_monOpenObjectActivity - calculates delta values for monOpenObjectActivity since the previous call in this session. This information will identify the tables where the largest amount of I/O has been spent during this interval, and within each table, show how often a table scan or a particular index was used. More information is here.
  • sp_mda_diskIO - calculates delta values for monOpenObjectActivity since the previous call in this session. This information will identify the tables where the largest amount of I/O has been spent during this interval, and within each table, show how often a table scan or a particular index was used. More information is here.
    Update 18-Mar-2013: an integer-overflow bug was fixed. Get the latest version.

Some additional MDA tools:
  • Here's a simple 'collector' procedure to continuously extract data from the MDA history tables and store it in a permanent table. See the header of the script for further instructions (you'll need to create a separate database, for instance).
  • David Wein wrote a great procedure named sp__idleReaper to automatically kill idle user processes in ASE. This procedure can be found on CodeXchange (a local copy is here).

Some MDA query examples
Below are some examples of queries against the MDA tables -- adjust as needed.
Please note that this is not aiming for completeness, but merely an attempt to get you started. For more examples, see the various presentations available above.
--====================================================================

-- find out what queries are running right now 
select * from master..monProcessSQLText

-- ... and the corresponding resource usage 
select * from master..monProcessStatement

-- keep in mind that the information in these tables changes 
-- very rapidly, so the tables may not always match!

--====================================================================

-- when tempdb is full and you cannot select from sysprocesses, syslocks 
-- etc. anymore, still find out what's running :

select * from master..monProcess

--====================================================================

-- show recent queries  that ran longer than 100 millisec 
-- or took more than 1000 I/Os
drop table #tsql, #ts
go
declare @kpid int
select @kpid = kpid from master..sysprocesses where spid = @@spid
select * into #tsql from master..monSysSQLText where KPID != @kpid
select * into #ts from master..monSysStatement where KPID != @kpid
select KPID, BatchID, LineNumber, LogicalReads, 
       datediff(ms, StartTime, EndTime) millisec, WaitTime from #ts 
where datediff(ms, StartTime, EndTime) > 100 
   or LogicalReads > 1000 order by 1,2,3
go
-- to find the corresponding SQL text, pick a KPID from the above 
-- query's output:
select * from #tsql where KPID= 
order by  BatchID, SequenceInBatch
go


--====================================================================

-- find seemingly unused indexes in the current database:
select "Database" = db_name(DBID), 
       "Table" = object_name(ObjectID, DBID),
       IndID = IndexID, si.name
from master..monOpenObjectActivity oa, sysindexes si
where oa.ObjectID = si.id 
  and oa.IndexID = si.indid
  and UsedCount = 0 
  and OptSelectCount = 0 
  and ObjectID > 99 
  and IndexID > 1 and IndexID != 255 
  and DBID = db_id() 
order by 1,2



--====================================================================

-- monOpenObjectActivity has been one of the most useful MDA tables
-- for me when troubleshooting customer systems
-- Previously, I had listed a long SQL batch here that calculated 
-- delta values for monOpenObjectActivity. This has now been replaced
-- by the stored procedure 'sp_mda_monOpenObjectActivity', which 
-- you can download here.
-- Usage is pretty simple: just call it repeatedly. Delta values are
-- calculated independent of other sessions calling this procedure 
-- as well.

Examples: 

-- first call; will set up a session-specific table to calculate 
-- delta values when called again
exec sp_mda_monOpenObjectActivity

-- reports only delta values for tables named '%prod%'
exec sp_mda_monOpenObjectActivity 'prod'

-- reports only delta values for tables named '%prod%', located in 
-- databases named '%sales%'
exec sp_mda_monOpenObjectActivity 'prod', 'sales'



-- The delta values are ordered by table; tables with the highest 
-- percentage of all Logical I/O (LIO) are shown first; tables with 
-- really small percentages are omitted.
-- Within a table, the amount of table scans or index access is shown,
-- again ordered by amount of LIO. 
-- The output should be reasonably self-evident...


--====================================================================

-- monDeviceIO and monIOQueue provide information about disk I/O activity
-- in the ASE server. 
-- The stored procedure 'sp_mda_diskIO' (download here)
-- presents this information in an accessible way.
-- Usage is pretty simple: just call it repeatedly. Delta values are
-- calculated independent of other sessions calling this procedure 
-- as well.

Examples: 

-- first call; will set up a session-specific table to calculate 
-- delta values when called again
exec sp_mda_diskIO

-- reports only delta values for devices named '%temp%'
exec sp_mda_diskIO 'temp'

-- Do not report devices where no disk I/O has occurred (second paramater=0)
exec sp_mda_diskIO '%', 0


-- Note that the calculated I/O times become more reliable when based 
-- on a larger number of I/Os. When based on less than, say, 10000 I/Os, 
-- the calculated I/O times are unlikely to be accurate. For this purpose, 
-- a column is added to indicate the likelihood of the I/O times being 
-- accurate. 

--====================================================================

Installation steps (ASE 15)
In ASE 15, the MDA tables are automatically installed by the installmaster script, so you do not need to install them separately anymore as in ASE 12.5. (actually, this applies in ASE 15.0 ESD#2 and later, but it seems safe to assume that all ASE 15 users are on a later version than that).
Keep in mind that whenever you install a new EBF, you must always run the installmaster script. Failure to do so may result in incorrect values or errors being returned when querying the MDA tables.
To check if you have the correct version isntalled for your EBF, run sp_version.

Before you can use the MDA tables, some configuration settings must be set:
-- First, ensure that the configuration parameter
-- 'enable cis' is set to 1 (if not, an ASE 
-- restart is needed)
sp_configure 'enable cis', 1
go

-- Assign 'mon_role' to logins allowed MDA access 
-- (this also applies to the 'sa' login!)
-- NB: In 15.7 ESD#2, mon_role is already assigned to 'sa' by default.
use master
go
grant role mon_role to sa
-- grant to other logins or roles here, as needed
go
Once installed, you need to enable certain MDA configuration parameters (proceed below)


Enable MDA configuration parameters
Once the MDA tables are installed, you need to enable some configuration parameters before you can use them (to disable them again, go here):

-- Test basic MDA configuration: 
-- (note: you may need to disconnect/reconnect first
-- to activate 'mon_role' when you just granted this
-- role to the login you're currently using)
--
select * from master..monState
go

-- "enable monitoring" is the 'master switch' for
-- most MDA functionality (although some things, 
-- like monState (above) are always enabled when the 
-- MDA tables are installed.
-- Setting "enable monitoring" to 0 will disable any 
-- MDA features that could have any performance impact 
-- on your ASE server.
--
sp_configure "enable monitoring", 1
go


-- Now enable all configuration parameters; 
-- these are all dynamic (except the last one)
-- For all 'pipe' tables, the number of
-- messages is set to 100 here, but you may want 
-- to choose a large size.
--

-- this enables monSysSQLText:
sp_configure "sql text pipe active", 1
go
sp_configure "sql text pipe max messages", 100
go

-- this enables monSysStatement:
sp_configure "statement pipe active", 1
go
sp_configure "statement pipe max messages", 100
go

-- this enables monErrorLog:
sp_configure "errorlog pipe active", 1
go
sp_configure "errorlog pipe max messages", 100
go

-- this enables monDeadLock:
sp_configure "deadlock pipe active", 1
go
sp_configure "deadlock pipe max messages", 100
go

-- this enables monProcessWaits and monSysWaits:
sp_configure "wait event timing", 1
go

-- this enables monProcessWaits:
sp_configure "process wait events", 1
go

-- this enables lock wait timing in various MDA tables:
sp_configure "object lockwait timing", 1
go

-- this enables various columns in various MDA tables:
sp_configure "statement statistics active", 1
go
sp_configure "per object statistics active", 1
go

-- this is required for the mon*SQLText tables;
sp_configure "SQL batch capture", 1
go


-- keep the following disabled if you don't need query plan 
-- info though MDA (usually, you don't), and you want to 
-- limit performance impact:
-- (this enables monSysPlanText)
--
-- sp_configure "plan text pipe active", 1
go
sp_configure "plan text pipe max messages", 100
go


-- 
-- In ASE 15.0.2 or later, also run the following:  
-- (this enables monStatementCache)
-- 
sp_configure "enable stmt cache monitoring", 1
go

-- 
-- In ASE 15.7 or later, also run the following, 
-- but only if you are interested in lock timeout 
-- information (you may not be)
-- (this enables monLockTimeout)
--
sp_configure "lock timeout pipe active", 1
go
sp_configure "lock timeout pipe max messages", 100
go

-- 
-- In ASE 15.7 or later, also run the following
-- to enable monTableCompression
--
sp_configure "capture compression statistics", 1
go

-- 
-- In ASE 15.7 ESD#2 or later, also run the following:
-- (this enables monSpinlockActivity)
--
sp_configure "enable spinlock monitoring", 1
go



-- This is the only static parameter. Set to
-- a higher value (the setting is in bytes
-- per user connection) if you're expecting
-- a lot of (or long) SQL batches
sp_configure "max SQL text monitored", 2048
go


-- The following option used to be required when 
-- using DBXRay (so it was not relevant when only 
-- using the MDA tables directly). 
-- However, DBXRay has been end-of-lifed, so it is 
-- included here only for completeness, but commented out
--
--sp_configure "performance monitoring option", 1
go

-- Now you're ready to use the MDA tables. Have fun!

Disable MDA configuration parameters
Sometimes you may need to restore all MDA-related config parameters to their defaults. If so, you can copy the following:
sp_configure "enable monitoring", 0, 'default'
go
sp_configure "sql text pipe active", 0, 'default'
go
sp_configure "sql text pipe max messages", 0, 'default'
go
sp_configure "statement pipe active", 0, 'default'
go
sp_configure "statement pipe max messages", 0, 'default'
go
sp_configure "errorlog pipe active", 0, 'default'
go
sp_configure "errorlog pipe max messages", 0, 'default'
go
sp_configure "deadlock pipe active", 0, 'default'
go
sp_configure "deadlock pipe max messages", 0, 'default'
go
sp_configure "wait event timing", 0, 'default'
go
sp_configure "process wait events", 0, 'default'
go
sp_configure "object lockwait timing", 0, 'default'
go
sp_configure "SQL batch capture", 0, 'default'
go
sp_configure "statement statistics active", 0, 'default'
go
sp_configure "per object statistics active", 0, 'default'
go
sp_configure "plan text pipe active", 0, 'default'
go
sp_configure "plan text pipe max messages", 0, 'default'
go
sp_configure "max SQL text monitored", 0, 'default', 
go
sp_configure "performance monitoring option", 0, 'default'
go

-- ASE 15.0.2 or later:
sp_configure "enable stmt cache monitoring", 0, 'default'
go

-- ASE 15.7 or later:
sp_configure "lock timeout pipe active", 0, 'default'
go
sp_configure "lock timeout pipe max messages", 0, 'default'
go
sp_configure "capture compression statistics", 0, 'default'
go

-- ASE 15.7 ESD#2 or later:
sp_configure "enable spinlock monitoring",0, 'default'
go




Installation steps (ASE 12.5)
In ASE 12.5.x, you must explicitly install the MDA tables with the installmontables script before you can use them (in ASE 15, the installation is performed 'automatically' as part of the installmaster script).
Follow these steps:
-- First, ensure that the configuration parameter
-- 'enable cis' is set to 1 (if not, an ASE 
-- restart is needed)
sp_configure 'enable cis', 1
go

-- Add 'loopback' server name alias (assuming @@servername 
-- is also defined in the interfaces file)
-- (NB: this step is no longer required in 15.0 ESD#2 or later)
use master
go
sp_addserver loopback, null, @@servername
go

-- Test this configuration: 
-- (NB: this step is no longer required in 15.0 ESD#2 or later)
set cis_rpc_handling on
go
--
-- Alternatively, run: 
--     sp_configure 'cis rpc handling', 1 
-- ...and disconnect/reconnect your session

exec loopback...sp_who  -- note: 3 dots!
go

-- Install the MDA tables. Important: do NOT run this
-- script with 'sqsh' as it'll give errors: 'sqsh' sees 
-- a '$' as the start of a sqsh variable, and this messes
-- up the native RPC names, since these start with a 
-- '$' as well.
-- Solution: either usq 'isql' as below, or run 'sqsh'
-- with the '-Lexpand=0' option to disable sqsh's 
-- variable expansion feature (thanks to Paul Harrington
-- for this tip).
-- (NB: as of ASE 15.0.2, this script is part of 'installmaster')
isql -U sa -P yourpassword -S YOURSERVER \
     -i $SYBASE/$SYBASE_ASE/scripts/installmontables
Once installed, you need to enable certain MDA configuration parameters. Go here...


 
 This document is located at www.sypron.nl/mda.html