Occurs when the number of deadlocks per second exceeds theNumber Deadlocks Per Second upper boundthreshold. Adeadlockoccurs when two processes, each having locked a resource, request access to put a lock on the other’s locked resource. Since each process is waiting for the other to release their lock, the processes wait indefinitely until one of the processes is killed. Microsoft SQL Server automatically resolves deadlocks by rolling back one of the processes to release the lock. The process that is rolled back is the ″victim″since the system kills it to resolve the deadlock.
The resource model monitors the number of deadlocks that have occurred during the current monitoring interval. This indication focuses on the number of lock requests per second that resulted in a deadlock. Use this indication to alert you to a high number of deadlocks occurring, which ensures that you do not lose any transactions.
Note: Microsoft SQL Server error 1205 is a deadlock error. Because any process can be chosen as the deadlock victim, use an error handler to trap error message 1205. If the error is not captured, proccesses can proceed unaware that the transaction was rolled back. This can result in errors. You can use theNT Event Loggingtask to log this error in the Windows NT event log. You can
also use the“Microsoft SQL Server Deadlock Alert indication” on page 45 in theErrorsresource model to monitor occurrences of deadlocks.
A traditional wait for a lock is not a deadlock. Typically, when a process that is holding a lock completes a transaction, it releases the lock. The next process waiting for the resource recieves the lock. This type of wait is normal and required for multiple-use systems.
Consider doing one of the following to minimize the number of deadlocks:
v Stagger competing processes.
v Alter the order in which competing processes run. v Rewrite queries that cause deadlocks.
v Set the deadlock priority. Encourage SQL Server to choose specific processes to
kill by setting the DEADLOCK_PRIORITY of a session to LOW, using the SET statement. If a session’s setting is LOW, that session is the preferred victim when involved in a deadlock situation.
You can also code applications to resubmit processes that are killed as a result of a deadlock.
When the indication occurs often enough to trigger an event, the event delivers a message to the Tivoli Enterprise Console in the following format:
<application_label>: Microsoft SQL Server <MSSQLServerName> -
<MSSQLResourceType>:Number Deadlocks Per Second is <MSSQLNumberDeadlocksPerSec>, which exceeds threshold of <UpperBound>.
If you have Tivoli Business Systems Manager configured for your system, Tivoli Enterprise Console forwards the message to Tivoli Business Systems Manager. You can check the health of this resource model in the IBM Tivoli Monitoring Web Health Console. For more information, see the IBM Tivoli Monitoring Web Health Console documentation.
The indication has the following attributes: application_class
The registered object’s Tivoli Management Environment (TME) class. application_label
The registered object’s Tivoli Management Environment (TME) label. application_oid
The registered object’s Tivoli Management Environment (TME) object identifier.
application_version
The managed resource’s version; for example, v7, or 2k. MSSQLNumberDeadlocksPerSec
The number of lock requests per second that resulted in a deadlock. A deadlock occurs when two processes, each having locked a resource, request access to put a lock on the other’s locked resource.
MSSQLResourceType
The type of resource with which the lock is associated (Page, Table, etc.). MSSQLServerName
UpperBound
The value set as the maximum allowable for the specified threshold. This indication has the following threshold:
v Number Deadlocks Per Second upper bound
For more information about this threshold, see “Thresholds” on page 71. The following table describes the default settings for this indication.
Setting Default value
Send indications to Tivoli Enterprise Console Yes Send indications to Tivoli Business Systems Manager
No
Occurrences 1
Holes 0
Associated tasks and built-in actions None
Note: If you have Tivoli Business Systems Manager configured for your systems, Tivoli Enterprise Console automatically forwards events to Tivoli Business Systems Manager. Do not change the configuration of the indication to send events to Tivoli Business Systems Manager.
Thresholds
The following table lists the thresholds that can be set for the Locks resource model. For each threshold it shows the name, a short description, and the default value:
Threshold Description Default
value
Lock Wait Time (ms) Per Second upper bound
AMicrosoft SQL Server Lock Wait Time Per Second too highindication occurs if the numnber of milliseconds of lock wait time per second exceeds this value. The threshold is expressed as milliseconds per second.
10
Number Deadlocks Per Second upper bound
AMicrosoft SQL Server Number Deadlocks Per Second too highindication occurs if the number of deadlocks per second exceeds this value.
50
Parameters
NoneTasks and built-in actions
NoneLogging
You can log data for the properties of the managed resource listed in the following table. The table shows the context of the managed resource and the properties that the resource model logs for the IBM Tivoli Monitoring Web Health Console.
Managed resource
Context Properties
MSSQLServer Locks
Performance MSSQLServer.MSSQLServerName*
The instance name of Microsoft SQL Server.
MSSQL_Lock_Requests
The number of new locks per second requested from the lock manager.
MSSQL_Lock_Timeouts
The number of lock requests per second that timed out before the lock was granted.
MSSQL_Lock_Wait_Time
The total wait time in milliseconds that a process spends waiting for another process to release a lock.
MSSQL_Number_Deadlocks
The number of lock requests per second that resulted in a deadlock. A deadlock occurs when two processes, each having locked a resource, request access to put a lock on the other’s locked resource.
MSSQLVersion
The version number, v7 or 2k, of the installed Microsoft SQL Server.
MSSQLResourceType
The type of resource with which the lock is associated (Page, Table, etc.).
MSSQLServer TableLock
Escalations MSSQLServer.MSSQLServerName*
The instance name of Microsoft SQL Server.
MSSQL_Table_Lock_Escalations
The number of Table Lock Escalations occurring per second.
MSSQLVersion
The version number, v7 or 2k, of the installed Microsoft SQL Server.
Note: An asterisk (*) denotes a key property.
Return codes
Table 2 on page 7 contains a listing of resource model return codes, their
description, and what action you can take to resolve each code. The return code number is displayed in the IBM Tivoli Monitoring Web Health Console status field, or by using the wdmlsengcommand.
CLI example
wdmeditprf -P $Prof1 -edit Microsoft_SQL_Server_Locks \ -t High_MSSQL_LockWaitTimePerSec 0.000000 \
-t High_MSSQL_NumberDeadlocksPerSec 0.000000 \ -e Microsoft_SQL_Server_High_LockWaitTimePerSec \
-o 1 -h 0 -severity CRITICAL -SendTBSM -"$MyTec" \ -e Microsoft_SQL_Server_High_NumberDeadlocksPerSec \