Introduction
Presentations
Tools
Installation steps
Changes and Enhancements to MDA tables since ASE 12.5.0.3 (updated for 15.5 ESD#1)
Some MDA query examples

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

• I wrote some handy stored procedures for easy access to some of the MDA tables.

Quick usage info:
• 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.

For on-line usage information, execute these procedures with '?' as the first parameter.

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

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


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


-- 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 "enable monitoring", 1
go
sp_configure "sql text pipe active", 1
go
sp_configure "sql text pipe max messages", 100
go
-- keep this disabled if you don't need query plan info
-- and you want to limit performance impact
--sp_configure "plan text pipe active", 1
go
sp_configure "plan 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

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

• monErrorLog.State - 'state' of an error
• monOpenDatabases.QuiesceTag - the tag specified with 'quiesce database' (if any)
• monOpenDatabases.SuspendedProcesses - number of currently suspended processes due to log-full condition in this database
• monProcessWorkerThread.FamilyID - for parallel queries, the spid of parent process
• monProcessWorkerThread.ParallelQueries - total # parallel queries attempted

• monProcessObject.TableSize - table size in Kbyte
• monProcessActivity.WorkTables - total number of work tables created by the process
• Note: the uninitialized milliseconds in monSysStatement.StartTime / EndTime have been fixed in 12.5.2

• A column ServerUserID has been added to monProcessActivity, monProcessSQLText and monSysSQLText; this column is the login's 'suid'.
• monProcessProcedures.LineNumber - the line in the procedure currently being executed

• monEngine.Yields - #times this engine yielded to the Operating System
• monEngine.DiskIOChecks - #times this engine checked for asynchronous disk I/O
• monEngine.DiskIOPolled - #times this engine polled for completion of outstanding asynchronous disk I/O.
• monEngine.DiskIOCompleted - #asynchronous disk I/Os that were completed when this engine polled

• The new table monOpenPartitionActivity reports monitoring statistics at partitition level
• The new table monLicense shows the details for the license keys that are active in this server

• HkgcMaxQSize - maximum #items that can be queued for HK garbage collection in this engine
• HkgcPendingItems - #items yet to be garbage-collected by the HK in this engine
• HkgcHWMItems - maximum #pending items queued for HK garbage collection at any instance of time since server restarted
• HkgcOverflows - #items that could not be queued for HK garbage collection due to queue overflows

• PartitionID, PartitionName - partition name and ID
• TotalSizeKB - the total size of the object (table or index)

• DBName - the databasename corresponding to DBID

• PartitionID, PartitionName - partition name and ID
• TableSize has been changed to PartitionSize - this reflects the size of the partition for the object

• BlockedState - identifies whether a lock is being blocked or is blocking others
• BlockedBy - for blocked locks, identifies the session this lock is being blocked by

• RowsAffected - the number of rows affected by the statement, similar to @@rowcount
• ErrorStatus - the SQL return status of the statement, similar to @@error

• RowsAffected - the number of rows affected by the statement, similar to @@rowcount

• A new column InstanceId has been added to various MDA tables.

• monCIPC, monCIPCEndpoints, monCIPCMesh, monCIPCLinks contain statistics about communication over the cluster interconnect (CIPC)
• monLogicalCluster, monLogicalClusterAction, monLogicalClusterInstance, monLogicalClusterRoute, monSysLoad, monWorkload, monWorkloadPreview, monWorkloadProfile, monWorkloadRaw contain information about the logical cluster configuration and the workload manager features.
• monTempdbActivity contains information about activity in instance-specific local temporary databases (i.e. not in global temporary databases like tempdb).
• monClusterCacheManager is for internal diagnostics only.

• The new tables monCachedStatement and monStatementCache report monitoring statistics about the statement cache. These tables are controlled by the new configuration parameter 'enable stmt cache monitoring'.

• Ssqlid - a unique identifier of a SQL statement, maps to monCachedStatement.SSQLID
• HashKey - the hash key value for the SQL text of a SQL statement, maps to monCachedStatement.HashKey

• HkgcRequests, HkgcPending, HkgcOverflows - information about the Housekeeper's garbage collection activity for an object or partition

• SourceCodeID was added to monLocks
• HeldSourceCodeID and WaitSourceCodeID were added to monDeadLock

• MaxOutstandingIOs - the max.# of I/Os pending for each engine

• NetworkEngineNumber - engine handling the network IO for this SPID

• StatementNumber - the statement in the stored procedure currently being executed

• LastCheckpointTime - date/time of the start of the last checkpoint for this database
• LastTranLogDumpTime - date/time of the start of the last log dump for this database

• monLocks
• monProcessStatement
• monSysStatement
• monSysPlanText
• monCachedStatement

• monProcess
• monProcessWaits

• The new tables monSQLRepActivity and monSQLRepMisses report statistics about SQL Statement replication (note that this new feature requires Replication Server 15.2 as well).
• Three new (and so far, undocumented) tables monPCIBridge, monPCISlots, monPCIEngine have been added. These tables are related to new internals in 15.0.3, and for the time being, these are likely meaningful for TechSupport only.

• ProcNestLevel - the nesting level on which the statement executed
• StatementNumber - a sequence number, starting at 0, for each statement in a batch or stored procedure, in the order of their actual execution.

• Transactions - the number of transactions executed in the server (including internal transactions).

• The new table monDBRecovery contains details about the recovery of each database in the ASE server (for example, how long it took to recover). The new table monDBRecoveryLRTypes reports on the number of log records of each log record type encountered curing database recovery.
• The new table monTableTransfer contains details about transfer table commands executed.
• The new tables monPCIEngine, monPCISlots, monPCIBridge contain details about the so-called PCI bridge infrastructure which is used for the new Java VM that was added in ASE 15.0.3.
• The new (and so far, undocumented) table monInmemoryStorage contains information about the special caches for in-memory databases in ASE 15.5. This is used internally; there is no useful information for customers in this MDA table. Customers should use sp_helpcache instead.
• The table monTempdbActivity, that was previously only available in ASE Cluster Edition (CE), is now also available in ASE SMP. In fact, all MDA tables for ASE CE are now installed in ASE SMP as well as of version 15.5; however, most CE-specific MDA tables are meaningless in ASE SMP. monTempdbActivity is an exception as it also applies to ASE SMP.
• The new tables monProcessMigration, monCLMObjectActivity, monPCM, monCMSFailover, monClusterCacheManager, and monFailoverRecovery are specific for ASE Cluster Edition.

• A new column InstanceId has been added to various MDA tables. This column is relevant only in ASE Cluster Edition, but now exists also in ASE SMP (SMP = ASE non-Cluster Edition; "ASE classic").
• In monEngine, a new column IOCPUTime has been added. This contains the time (in seconds) the engine has been waiting for the issued IOs to finish.
• In monCachedProcedures, 3 new columns were added: RequestCnt contains the number of times a stored procedure was executed since the plan was compiled; TempdbRemapCnt and AvgTempdbRemapTime contain the number of times an existing stored proc plan was remapped due to being executed in a different temporary database, and the average time (in milliseconds) this remapping took, respectively.
• In both monOpenObjectActivity and monOpenPartitionActivity, 18 columns have been added. These are all specific to ASE Cluster Edition and are meaningless in ASE SMP. The columns are PhysicalLocks, PhysicalLocksRetained, PhysicalLocksRetainWaited, PhysicalLocksDeadlocks, PhysicalLocksWaited, PhysicalLocksPageTransfer, TransferReqWaited, AvgPhysicalLockWaitTime, AvgTransferReqWaitTime, TotalServiceRequests, PhysicalLocksDowngraded, PagesTransferred, ClusterPageWrites, AvgServiceTime, AvgTimeWaitedOnLocalUsers, AvgTransferSendWaitTime, AvgIOServiceTime, and AvgDowngradeServiceTime .

• In both monOpenObjectActivity and monOpenPartitionActivity, 18 columns have been added. These are all specific to ASE Cluster Edition and are meaningless in ASE SMP. The columns are MaxPhysicalLockWaitTime, MaxTransferReqWaitTime, MaxServiceTime, AvgQueueWaitTime, MaxQueueWaitTime, MaxTimeWaitedOnLocalUsers, MaxTransferSendWaitTime, MaxIOServiceTime, MaxDowngradeServiceTime, MaxPhysicalLockWaitTime, MaxTransferReqWaitTime, MaxServiceTime, AvgQueueWaitTime, MaxQueueWaitTime, MaxTimeWaitedOnLocalUsers, MaxTransferSendWaitTime, MaxIOServiceTime, and MaxDowngradeServiceTime.
• In monCIPCLinks, the order of the columns has been rearranged.

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

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

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