Waits
Waits occur when system resources are busy processing other tasks, and new tasks are placed into a holding queue until resources free up. Locks on data are the only reason a task will encounter a wait. Bottlenecks and hardware resources are also common contributors to a task having to wait before proceeding.
A dynamic management view called sys.dm_os_wait_stats will output the total time the system has spent waiting, broken down by the root cause. This is helpful for tracking down performance issues.
SELECT * from sys.dm_os_wait_stats
Deadlock
The far more serious reason a task might not finish occurs when there is a deadlock between two or more processes. This occurs when a transaction places a lock on a group of records and then holds that lock while it goes off to get a lock on other bundles of records. Meanwhile, another transaction has placed a lock on the records the first transaction is trying to fetch, while waiting for the lock to be lifted on the records that the first transaction is holding on to. In essence the first transaction is being held up by the second, and the second is waiting for the first, resulting in a deadlock. Without intervention, both processes would get held up forever.
SQL Server monitors for this situation, and after about five seconds, will pick one of the transactions to call the victim and kills it off so that the other transaction can finally proceed.
SQL Server provides two trace flags that are useful for tracking down causes of deadlocks, and will place entries into the error log for review. Trace flag 1204 reports deadlock information for each involved node. And Trace flag 1222 formats deadlock information by process and then by resource. Enabling both at the same time will provide different looks at the situation that led to the deadlock.
Sources:
Comments