Filtered Index Not Begin used

Question

Filtered Index Not Begin used

Djcarpen11

Ten Centuries

Points: 1069
More actions
July 23, 2012 at 10:44 am

#392807

I've created an index similar the one below
CREATE NONCLUSTERED INDEX [Ixn_Mytable_F]
ON [dbo].[MyTable] ([ColA])
INCLUDE ([ColB],[ColC],[ColD])
where ColA = 122
If I run the below code against it, I'd have thought that the index would have been used but it isn't. Instead a clustered index scan takes place.
DECLARE @P INT
SET @P= 1;
select [ColA],[ColB],[ColC],[ColDfrom MyTable
where cola =@p
Statistics IO results Table ‘Mytable. Scan count 9, logical reads 1571009, physical reads 23345, read-ahead reads 1414382, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If I take the parameter out and explicitly set the value the index is used and the query runs a lot quicker and isn’t as resource intensive.
select [ColA],[ColB],[ColC],[ColDfrom MyTable
where cola =1
Table mytable. Scan count 1, logical reads 9703, physical reads 1, read-ahead reads 4166, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Djcarpen11 (7/23/2012)
I've created an index similar the one below
CREATE NONCLUSTERED INDEX [Ixn_Mytable_F]
ON [dbo].[MyTable] ([ColA])
INCLUDE ([ColB],[ColC],[ColD])
where ColA = 122
If I run the below code against it, I'd have thought that the index would have been used but it isn't. Instead a clustered index scan takes place.
DECLARE @P INT
SET @P= 1;
select [ColA],[ColB],[ColC],[ColDfrom MyTable
where cola =@p
Statistics IO results Table ‘Mytable. Scan count 9, logical reads 1571009, physical reads 23345, read-ahead reads 1414382, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If I take the parameter out and explicitly set the value the index is used and the query runs a lot quicker and isn’t as resource intensive.
select [ColA],[ColB],[ColC],[ColDfrom MyTable
where cola =1
Table mytable. Scan count 1, logical reads 9703, physical reads 1, read-ahead reads 4166, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I read this correctly:

CREATE NONCLUSTERED INDEX [Ixn_Mytable_F]

ON [dbo].[MyTable] ([ColA])

INCLUDE ([ColB],[ColC],[ColD])

where ColA = 122

The only time this index will be used is if ColA = 122.

Am I missing something?

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 2

Well spotted, sorry it's my mistake it should be COLA =1.

Can't figure out why it would as a literal value but not as a parameter.

Dave

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 3

Djcarpen11 (7/23/2012)
Well spotted, sorry it's my mistake it should be COLA =1.
Can't figure out why it would as a literal value but not as a parameter.
Dave

What is data type of COLA?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Because as a parameter SQL can't guarantee the value will remain the same and hence can't generate a plan that uses the filtered index because the plan using the filtered index would only be correct if one specific value is passed.

Consider that query with a parameter of 1 passed, SQL compiles a plan using the filtered index, then the query is run again with the parameter value of 2 and the plan gets reused. That will return incorrect results (or fail outright)

Discussed here: http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

Djcarpen11 (7/23/2012)
DECLARE @P INT
SET @P= 1;
select [ColA],[ColB],[ColC],[ColDfrom MyTable
where cola =@p

In cases where a filtered index is available, you can force SQL to recompile the statement, which should pick up the index when applicable:

select [ColA],[ColB],[ColC],[ColDfrom MyTable

where cola =@p

option ( recompile )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".