Summary information |
|
|---|---|
Default value |
1 (on) |
Valid values |
0 (off), 1 (on) |
Status |
Dynamic |
Display level |
Intermediate |
Required role |
System administrator |
Configuration group |
Query Tuning |
allow backward scans controls how the optimizer performs select queries that contain the order by...desc command:
When the value is set to 1, the optimizer can access the index or table rows by following the page chain in descending index order.
When the value is set to 0, the optimizer selects the rows into a worktable by following the index page pointers in ascending order, and then sorts the worktable in descending order.
The first method—performing backward scans—can speed access to tables that need results ordered by descending column values. Some applications, however, may experience deadlocks due to backward scans. In particular, look for increased deadlocking if you have delete or update queries that scan forward using the same index. There may also be deadlocks due to page splits in the index.
Use print deadlock information to send messages about deadlocks to the error log. See “print deadlock information”. Alternatively, you can use sp_sysmon to check for deadlocking. See the Performance and Tuning Series: Locking and Concurrency Control.
