allow backward scans

allow backward scans controls how the optimizer performs select queries that contain the order by...desc command.

Summary Information

Default value

1 (on)

Valid values

0 (off), 1 (on)



Display level


Required role

System administrator

Configuration group

Query Tuning

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. Alternatively, you can use sp_sysmon to check for deadlocking. See the Performance and Tuning Series: Locking and Concurrency Control.