Summary information |
|
|---|---|
Default value |
500 |
Range of values |
0–2147483 |
Status |
Dynamic |
Display level |
Comprehensive |
Required role |
System Administrator |
deadlock checking period specifies the minimum amount of time (in milliseconds) before Adaptive Server initiates a deadlock check for a process that is waiting on a lock to be released. Deadlock checking is time-consuming overhead for applications that experience no deadlocks or very few, and the overhead grows as the percentage of lock requests that must wait for a lock also increases.
If you set this value to a nonzero value (n), Adaptive Server initiates a deadlock check after a process waits at least n milliseconds. For example, you can make a process wait at least 700 milliseconds for a lock before each deadlock check as follows:
sp_configure "deadlock checking period", 700
If you set this parameter to 0, Adaptive Server initiates deadlock checking when each process begins to wait for a lock. Any value less than the number of milliseconds in a clock tick is treated as 0. See “sql server clock tick length” for more information.
Configuring deadlock checking period to a higher value produces longer delays before deadlocks are detected. However, since Adaptive Server grants most lock requests before this time elapses, the deadlock checking overhead is avoided for those lock requests. If your applications deadlock infrequently, set deadlock checking period to a higher value to avoid the overhead of deadlock checking for most processes. Otherwise, the default value of 500 should suffice.
Use sp_sysmon to determine the frequency of deadlocks in your system and the best setting for deadlock checking period. See the Performance and Tuning Guide for more information.
