July 23, 2012 at 10:44 am
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.
July 23, 2012 at 10:47 am
Djcarpen11 (7/23/2012)
I've created an index similar the one belowCREATE 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?
July 23, 2012 at 10:50 am
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
July 23, 2012 at 11:13 am
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?
July 23, 2012 at 11:22 am
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
July 23, 2012 at 12:51 pm
Djcarpen11 (7/23/2012)
DECLARE @P INTSET @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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply