TheSQLGuru (6/2/2009)
alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)
AS
select TransactionID
from [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])
where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here!
AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)
GO
Exec Test4 @ProdID = 790
4.16 cost with forced index, 11 IO
0.711 cost without forced index (CI scan), 792 IO
due to the mathematics of the optimizer (i.e. the MUCH higher cost associated with the known-to-be-not-sequential-io index seek/bookmark lookup the query plan cost of seeking/lookup 2 rows is MUCH higher than scaning the entire table despite significantly fewer total IOs.
Gail, I wonder if your larger table would still be more efficient doing the scan than with the forced seek?
It is. The cost of the forced index is way higher and the IOs are slightly higher.
CI Scan
Table 'TransactionHistory'. Scan count 3, logical reads 7367, physical reads 0
Cost 7.19
Forced index
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'TransactionHistory'. Scan count 3, logical reads 8109, physical reads 0 -- 2641 key lookups.
Cost 35.02
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