Sybase ASE
pre-12.5.0.3 12.5.0.3+ 15.x
Last updated: 25 November 2012
Changes and Enhancements to MDA tables since ASE 12.5.0.3
(updated for 15.7 ESD#2)
 

The MDA tables were first introduced in ASE 12.5.0.3. This page lists the enhancements to the MDA tables in subsequent versions of ASE. I'll try to keep this list up-to-date.

ASE 12.5.1 IR -- 5 new columns:
  • 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


ASE 12.5.2 IR -- 2 new columns:
  • 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


ASE 12.5.3 IR -- 4 new columns:
  • 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
In addition, as of 12.5.3, monOpenObjectActivity contains details about tables and indexes only. Prior to 12.5.3, this table could contain rowsa row for an executed stored procedure, but these details (like the Operations column) were not reliable.


ASE 12.5.3 ESD#2 -- 4 new columns:
  • 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


ASE 15.0 -- 2 new tables and various new/changed columns:
  • 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
New columns in monEngine:
  • 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
New columns in monCachedObject:
  • PartitionID, PartitionName - partition name and ID
  • TotalSizeKB - the total size of the object (table or index)
New columns in monOpenObjectActivity:
  • DBName - the databasename corresponding to DBID
New/changed columns in monProcessObject:
  • PartitionID, PartitionName - partition name and ID
  • TableSize has been changed to PartitionSize - this reflects the size of the partition for the object

ASE 15.0 ESD#2 -- 5 new columns

Perhaps the most important enhancement in ASE 15.0 ESD#2 is the new 'materialized' option with which the MDA proxy tables are created. In 15.0 ESD#2, the MDA tables no longer use the 'backdoor' connection back into to the server itself and consequently, the 'loopback' server name alias is no longer needed either. This new feature reduces some of the overhead of querying the MDA tables. There's nothing you have to do to benefit from this new feature other than running the 'installmontables' script that comes with 15.0 ESD#2.

New columns in monLocks:
  • 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
New columns in monSysStatement:
  • RowsAffected - the number of rows affected by the statement, similar to @@rowcount
  • ErrorStatus - the SQL return status of the statement, similar to @@error
New column in monProcessStatement:
  • RowsAffected - the number of rows affected by the statement, similar to @@rowcount


ASE 15.0.1 Cluster Edition -- various changes, 15 new tables (updated: 28 December 2008)
The following changes are for ASE Cluster Edition only -- up to ASE 15.0.3 (the classic, non-cluster-edition flavour of ASE, that is), none of the following applies.
  • A new column InstanceId has been added to various MDA tables.
15 new MDA tables were added in ASE CE:
  • 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.


ASE 15.0.2 -- 2 new tables, 11 new columns

First, the definition of the MDA tables is moved into the installmaster script, so that they're automatically installed/updated when installmaster is run (you *do* run this after installing an EBF , right?). Note that installmontables is still available as a separate script, but this is intended as a template for special cases like setting up the MDA tables in a different server or database.

New tables:
  • 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'.
New columns in monSysStatement:
  • 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
New columns in monOpenObjectActivity and monOpenPartitionActivity:
  • HkgcRequests, HkgcPending, HkgcOverflows - information about the Housekeeper's garbage collection activity for an object or partition
New columns in monLocks and monDeadLock (these columns contain diagnostic information about a lock, for support purposes only):
  • SourceCodeID was added to monLocks
  • HeldSourceCodeID and WaitSourceCodeID were added to monDeadLock
Among various bugfixes in 15.0.2, one that is worth mentioning is the number of table scans or index scans on a table can now be reliably derived from monOpenObjectActivity.UsedCount for rows with IndexID = 0. Previously, this value was not correct as it included accesses via a clustered index as well.

ASE 15.0.2 #esd5 -- 12 new columns

New column in monEngine:
  • MaxOutstandingIOs - the max.# of I/Os pending for each engine
New column in monProcessNetIO:
  • NetworkEngineNumber - engine handling the network IO for this SPID
New column in monProcessProcedures:
  • StatementNumber - the statement in the stored procedure currently being executed
New columns in monOpenDatabases:
  • 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
New column DBName was added to the following tables:
  • monLocks
  • monProcessStatement
  • monSysStatement
  • monSysPlanText
  • monCachedStatement
New column ServerUserID was added to the following tables:
  • monProcess
  • monProcessWaits

ASE 15.0.3 -- 5 new tables, 2 new columns, 1 column removed (updated: 28 December 2008)

New tables:
  • 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.
New columns in monSysStatement:
  • 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.
One column, TableCount, was removed from monCachedStatement.
ASE 15.0.3 esd#3 -- 1 new column:

New column in monState:
  • Transactions - the number of transactions executed in the server (including internal transactions).

ASE 15.5 (both SMP & Cluster Edition) -- 13 new tables and many new columns:

New tables:
  • 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.
New columns:
  • 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 .

ASE 15.5 ESD#1 -- various new columns:

New columns:
  • 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.

ASE 15.7 -- 13 new tables (there are so many new columns in existing tables that I've stopped listing them from this point onwards):
  • monRepLogActivity, monRepScanners, monRepScannersTotalTime, monRepSenders contain details about the RepAgents in ASE. In 15.7, these have been completley redesigned and are multi-threaded and support multi-path replication.
  • monLockTimeout contains details about lock timeouts (by default in ASE, these tables are useless since locks never time out, but this can be changed with the lock timeout settings).
  • monDeviceSpaceUsage contain details about all database devices, as well as information on size free space (the latter for file system devices only).
  • monTableCompression contains detials on data compressiion in ASE (if used).
  • monIOController has details on I/O controllers.
  • monServiceTask has details about service tasks (system tasks like housekeeper, checkpoint task, repagents, etc.) that are bound to an execution class.
  • monTask has details, such as the thread pool, about each task (meaningful only in threaded kernel mode).
  • monThread has details about each thread (meaningful only in threaded kernel mode).
  • monThreadPool has details about each thread pool (meaningful only in threaded kernel mode).
  • monWorkQueue has details about work queues for thread pools (meaningful only in threaded kernel mode).

ASE 15.7 ESD#2 -- 1 new table:
  • monSpinlockActivity has details on all spinlocks in ASE.

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