monOpenPartitionActivity

Description

Provides information about the use of each open partition on the server.

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

Columns

The columns for monOpenPartitionActivity 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.
PartitionID	int		Unique identifier for the partition.
InstanceID	int		ID of an instance in a shared-disk cluster.
DBName	varchar(30)	Null	Name of the database in which the object resides.
ObjectName	varchar(30)	Null	Name of the object.
PartitionName	varchar(30)	Null	Name of the partition.
LogicalReads	int	Counter, null	Total number of buffers read.
PhysicalReads	int	Counter, null	Number of buffers read from disk.
APFReads	int	Counter, null	Number of asynchronous prefetch (APF) buffers read.
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.
OptSelectCount	int	Counter, null	Number of times object was selected for plan during compilation.
LastOptSelectDate	datetime	Null	Last date the index was selected for 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 a partition. A large value implies the system is generating large amounts of garbage for the specified partition.
HkgcPending			The number of pending events for a partition. 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 partition 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		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 physical lock requested 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.
AvgPhysicalLockWaitTime	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 the Adaptive Server may decide not to execute certain portions of a query plan during execution, the UsedCount may be less than the OptSelectCount.