BUG: Optimizer Picks Table Scan for IF EXISTS Subquery with BETWEEN and Local Variable (223031)

SYMPTOMS

A specific type of query may, under narrow conditions, cause the optimizer to pick an access plan that is not optimal. The symptoms of this problem are excessive I/O (if data is not cached) or excessive CPU usage (if data is cached). For this problem to occur, the following conditions must be present:

You have an IF EXISTS subquery with a local variable in the WHERE clause.

-and-
There are column statistics on a second poor-cardinality column in the WHERE clause.

The following is an example of a query that demonstrates this problem:

DECLARE @nextid int
SELECT  @nextid = 6011050
IF EXISTS (SELECT col1 FROM table WHERE col2 = 1
                   AND col1 BETWEEN @nextid AND 6011051)
print 'hello'

WORKAROUND

To work around this problem, try one of the following:

Drop column statistics on the indicated column. It may be necessary to disable auto create statistics in the database to avoid re-creating column statistics on that column.
Rewrite the query to use a constant rather than a variable in the WHERE clause.

Rewrite query to not use IF EXISTS. For example, instead of:

   IF EXISTS( SELECT * FROM table WHERE column BETWEEN @v1 AND @v2 )

Write the query as:

   SELECT @r = (SELECT COUNT(*) FROM table WHERE column BETWEEN @v1 AND @v2)
   IF @r > 0 ....and so on.

BUG: Optimizer Picks Table Scan for IF EXISTS Subquery with BETWEEN and Local Variable (223031)

SYMPTOMS

WORKAROUND

STATUS