• My Techwave 2003 presentation about MDA table basics (190Kb).

• The more advanced Techwave 2004 presentation about MDA tables (by Peter Dorfman and Rob Verschoor) (428Kb).

• The Techwave 2005 presentation about MDA tables (by Peter Dorfman, Jeff Tallman and Michael Wallace) (750Kb).

• 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.
  Update 28-Nov-2010: this stored proc now reports rowcount and size for every table. Get the latest version.

• 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.

• 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).

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

-- 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. 

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

-- 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!)
use master
go
grant role mon_role to sa
-- grant to other logins or roles here, as needed
go

-- 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.
--
sp_configure "sql text pipe active", 1
go
sp_configure "sql text pipe max messages", 100
go
sp_configure "statement pipe active", 1
go
sp_configure "statement pipe max messages", 100
go
sp_configure "errorlog pipe active", 1
go
sp_configure "errorlog pipe max messages", 100
go
sp_configure "deadlock pipe active", 1
go
sp_configure "deadlock pipe max messages", 100
go
sp_configure "wait event timing", 1
go
sp_configure "process wait events", 1
go
sp_configure "object lockwait timing", 1
go
sp_configure "SQL batch capture", 1
go
sp_configure "statement statistics active", 1
go
sp_configure "per object statistics active", 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:

-- sp_configure "plan text pipe active", 1
go
sp_configure "plan text pipe max messages", 100
go


-- 
-- As of ASE 15.0.2, also run the following one:
--
sp_configure "enable stmt cache 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 must be enabled only when 
-- using DBXRay, so it is not relevant when only 
-- using the MDA tables directly. It is mainly
-- included here for completeness and to pre-empt
-- your questions...
sp_configure "performance monitoring option", 1
go

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

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 "enable stmt cache monitoring", 0, 'default'
go
sp_configure "max SQL text monitored", 0, 'default', 
go
sp_configure "performance monitoring option", 0, 'default'
go

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