monOpenObjectActivity

Description

Provides statistics for all open tables and indexes.

Enable the enable monitoring, object lockwait timing, and per object statistics active configuration parameters for this monitoring table to collect data.

Columns

The columns for monOpenObjectActivity are:

Name	Datatype	Attributes	Description
DBID	int		Unique identifier for the database.
ObjectID	int		Unique identifier for the object.
IndexID	int		Unique identifier for the index..
InstanceID	int		Specific to cluster environments – unique identifier for an instance.
DBName	varchar(30)	Null	Name of the database in which the object resides
ObjectName	varchar(30)	Null	Name of the object.
LogicalReads	int	Counter, null	Total number of times a buffer for this object has been retrieved from a buffer cache without requiring a read from disk.
PhysicalReads	int	Counter, null	Number of buffers read from disk.
APFReads	int	Counter, null	Number of APF buffers read from disk.
PagesRead	int	Counter, null	Total number of pages read.
PhysicalWrites	int	Counter, null	Total number of buffers written to disk.
PagesWritten	int	Counter, null	Total number of pages written to disk.
RowsInserted	int	Counter, null	Number of rows inserted.
RowsDeleted	int	Counter, null	Number of rows deleted.
RowsUpdated	int	Counter, null	Number of updates.
Operations	int	Counter, null	Number of times the object was accessed.
LockRequests	int	Counter, null	Number of requests for a lock on the object.
LockWaits	int	Counter, null	Number of times a task waited for an object lock.
OptSelectCount	int	Counter, null	Number of times the optimizer selected this index to be used in a query plan.
LastOptSelectDate	datetime	Null	Last date the index was selected for a plan during compilation.
UsedCount	int	Counter, null	Number of times the object was used in a plan during execution.
LastUsedDate	datetime	Null	Last date the index was used in a plan during execution.
HkgcRequests			Total number of events queued for an object. A large value implies the system is generating large amounts of garbage for the specified object.
HkgcPending			The number of pending events for an object. A large value implies that a lot of garbage is yet to be collected, although the housekeeper will clean it up. If you reboot Adaptive Server, all entries in the housekeeper queue are lost, and the garbage from those pages is not collected when you restart Adaptive Server.
HkgcOverflows			The number of overflow object events. A large value implies the housekeeper queues are filling up. Generated garbage will not then be cleaned up because the housekeeper cannot schedule the job.
PhysicalLocks	int		Specific to cluster environments – number of physical locks requested per object.
PhsycialLocksRetained	int		Specific to cluster environments – number of physical locks retained. Use to identify the lock hit ratio for each object. Good hit ratios imply balanced partitioning for this object.
PhysicalLocksRetainWaited	int4		Specific to cluster environments – number of physical lock requests waiting before a lock is retained.
PhysicalLocksDeadlocks	int		Specific to cluster environments – number of times a requested physical lock returned a deadlock. The Cluster Physical Locks subsection of sp_sysmon uses this counter to report deadlocks while acquiring physical locks for each object.
PhysicalLocksWaited	int		Specific to cluster environments – number of times an instance waited for a physical lock request.
PhysicalLocksPageTransfer	int		Specific to cluster environments – number of page transfers that occurred when an instance requested a physical lock. The Cluster Physical Locks subsection of sp_sysmon uses this counter to report the node-to-node transfer and physical-lock acquisition as a node affinity ratio for this object
TransferReqWaited	int4		Specific to cluster environments – number of times physical lock requests waiting before receiving page transfers.
AvgPhysicalLocksWaitTime	int4		Specific to cluster environments – average amount of time clients spend before the physical lock is granted.
AvgTransferReqWaitTime	int4		Specific to cluster environments – average amount of time physical lock requests wait before receiving page transfers.
TotalServiceRequests	int4		Specific to cluster environments – number of physical lock requests serviced by the cluster cache manager of an instance.
PhysicalLocksDowngraded	int4		Specific to cluster environments – number of physical lock downgrade requests serviced by the cluster cache manager of an instance.
PagesTransferred	int4		Specific to cluster environments – Number of pages transferred at an instance by the cluster cache manager.
ClusterPageWrites	int4		Specific to cluster environments – number of pages written to disk by the cluster cache manager of an instance.
AvgServiceTime	int4		Specific to cluster environments – average amount of service time spent by the cluster cache manager of an instance.
AvgTimeWaitedOnLocalUsers	int4		Specific to cluster environments – average amount of service time an instance’s cluster cache manager waits due to page use by users on this instance.
AvgTransferSendWaitTime	int4		Specific to cluster environments – average amount of service time an instance’s cluster cache manager spends for page transfer.
AvgIOServiceTime	int4		Specific to cluster environments – average amount of service time used by an instance’s cluster cache manager for page transfer.
AvgDowngradeServiceTime	int4		Specific to cluster environments – average amount of service time the cluster cache manager uses to downgrade physical locks.

Because you can use the plan for a stored procedure or trigger multiple times, the value of the OptSelectCount column may be less than the value of UsedCount. In addition, because Adaptive Server may decide not to execute certain portions of a query plan during execution, the UsedCount may be less than the OptSelectCount.