Get Live Help
A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server, that is, each task has a lock on a resource which the other tasks are trying to lock.
When an instance of the Microsoft SQL Server Database Engine detects a deadlock, it chooses a transaction as a deadlock victim, terminates the current batch, rolls back the transaction and returns an error message to the application.
Deadlocks cannot be completely avoided; however, following certain coding conventions can minimize the occurrence of deadlocks.
Disk queue length tracks the average number of I/O requests that are queued and waiting for a disk during a sampled interval. This figure may include I/O activity generated by processes other than SQL Server. Values that exceed the threshold set in this metric indicate a bottleneck.
Reading or writing data requires a disk to access the disk sector where the requested data resides. After this sector was accessed, the amount of time required for a disk to read or write data from or to a storage media is referred to as disk transfer time.
Transfer time, usually expressed in milliseconds, is part of the disk access time, that is, the total time required for the computer to process the data request from the processor and then retrieve the needed data from a storage device.
The percentage of elapsed time that a disk is busy servicing I/O requests.