Filtered indexes not compatible plan reuse??

Posted by on Sunday, March 16, 2008 to SQL Server 2008, query optimizer, Indexes, CTP6, Procedure Cache
823 Views | 0 Comments |

So I was messing around with filtered indexes tonight. Yes, beta software is my idea of a wild Saturday night. Hey, I played some Smash Bros first. ;) I was trying to figure out what happens if a plan is created using a covering but filtered index and try to reuse it with a parameter that is covered vertically but not horizontally. Let's look at this example:

create database dbtest01;

--Force parameterization

alter database dbtest01 set parameterization forced;

use dbtest01

--create test table 1

create table t1

(id int identity(1,1),

c1 int,

c2 int)

--insert dummy data

declare @ctr int = 2000000

while @ctr > 0

begin

insert into t1(c1, c2) values (@ctr, @ctr)

select @ctr=@ctr-1

end

--create clustered indexes

create clustered index CI on t1(id)

--Let create a covering indexes that is pretty extreme.

create index ix01 on t1(c1) where c1 = 1990001

--Test queries

--ctrl + m

dbcc freeproccache

set statistics io on

--Does a CI scan.

select c1 from t1

where c1 = 1990001

--Nope, index hints returns an error

--Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

select c1 from t1 with (index=ix01)

where c1 = 1990001

--Ahh, recompile works. Now it uses the filtered index

select c1 from t1

where c1 = 1990001

option(recompile)

--What about simple parameterization and a stored proc?

alter database dbtest01 set parameterization simple;

create proc proc1

@p1 int

select c1 from t1

where c1 = @p1