The Cluster Edition adds these catagories for sp_sysmon.
Task Management per sec per xact count % of total -------------------- -------- -------- ------ ---------- ... Text Context Switches Due To: ... Physical Lock Transition 208.9 0.4 26108 4.6% Logical Lock Transition 2209.3 4.5 276160 48.5% ... Interconnect Message Sleeps 612.2 1.2 76527 13.5%
Physical Lock Transition – identify context switches related to acquiring physical locks.
Logical Lock Transition – identify context switches related to acquiring cluster logical locks
Object Lock Transition – identify context switches related to acquiring object locks
For these three rows, % total represents
a collective number of context switches caused by cluster messaging.
Higher values for % total for
this context indicates high cluster message activity. Compare this
number with Number of Cluster Lock Requests to
determine the number of cluster messages per lock request. Query
the monSysWaits monitoring table for more information
about context switches.
Assess your application partitioning if a lock requests creates too many context switches. A higher number of context switches indicates additional requirements for the cluster message buffer. monCIPC may have high values for ReceiveCount and TransmitCount if there is a shortage of CIPC message buffers. Increase the size of the CIPC message buffer to resolve this issue.
Lock Summary per sec per xact count % of total -------------------- -------- -------- ------ ---------- Cluster Locks Retained 428.6 2000.3 6001 99.4 %
Cluster Locks Retained – Indicates the number of cluster locks retained. A high number indicates a well-partitioned application and that most of the cluster locks are already granted to the current instance. A lower value may indicate that you should reassess the object’s schema and workload pattern to reduce conflicting data access across multiple instances.
monCLMobjectActivity includes values for cluster-level lock activity for each object. These values are helpful for making locking decisions at the object level.
Cluster Lock Summary per sec per xact count % of total ------------------------- ----------- ----------- --------- ---------- Lock Garbage Collection 0 0 0 n/a Targeted lock garbage collected n/a n/a 0 0.0% Physical Lock Summary per sec per xact count % of total ------------------------- ----------- ----------- --------- ---------- Cluster Lock Requests 17.6 82.3 247 100.0 % Local Master 17.6 82.3 247 100.0 % Lock Granted 17.6 82.3 247 100.0 % Lock Waited 0.0 0.0 0 0.0 % Downgrade Req Recv 0.1 n/a 2 n/a Logical Lock Summary per sec per xact count % of total ------------------------- ----------- ----------- --------- ---------- Cluster Lock Requests 50.0 233.3 700 100.0 % Local Master 49.0 228.7 686 98.0 % Lock Granted 50.0 233.3 700 100.0 % Lock Waited 0.0 0.0 0 0.0 % Downgrade Req Recv 0.0 n/a 0 n/a Object Lock Summary per sec per xact count % of total ------------------------- ----------- ----------- --------- ---------- Cluster Lock Requests 188.3 878.7 2636 100.0 % Local Master 34.9 163.0 489 18.6 % Lock Granted 188.3 878.7 2636 100.0 % Lock Waited 0.0 0.0 0 0.0 % Downgrade Req Recv 8.2 n/a 115 n/a
sp_sysmon reports on the following activities for cluster locks:
Lock Garbage Collection – number of times lock garbage collection ran. A large number of garbage collections per second indicates the cluster has an insufficient number of locks.
Targeted Collection Success – number of times lock garbage collection was able to successfully reclaim the targeted number of locks. The Cluster Edition determines the target number for locks internally, and is not a configurable parameter. A low number of garbage collections per second indicates the cluster has an insufficient number of locks or insufficient amount of CIPC message space available.
Cluster Lock Requests – number of user physical, logical, or object lock requests that cannot be granted because of retention and require cluster communication. A high value indicates a lot of cluster traffic.
Local Master – number of physical, logical, or object locks that have a local master. A higher value indicates ideal lock, possibly indicating smaller overhead while serving cluster lock requests.
If the Cluster Edition cannot find the local master:
You may need to reassess your application partitioning.
A single instance has too great a load, which is leading to load balancing of master to remote instance. Consider changing your workload pattern and schema (such as partitioning your data).
Lock Granted – number of cluster lock requests
that wait for a remote instance that owns the cluster lock with
no conflicting task ownership. A high value for Lock
Granted indicates conflicting lock requests
from multiple instances in cluster. Reassess your application partitioning.
Lock Waited – number of cluster lock requests
that wait for a remote instance that owns the cluster lock with
conflicting task ownership. A high value for Lock
Waited indicates lock level contention: two
tasks on different instances are attempting to acquire the same
lock and are in conflict. To rectify, reassess your application
partitioning.
Downgrade Req Recv – displays the number of downgrade requests sent by a remote instance when it asks for a lock that is owned by the local instance.
