TheSQLGuru (6/2/2009)Perhaps because they found that their poorly written code (IS NULL OR constructs) required it to perform acceptably?!?

Still not sure about that being the issue though. Can you restore a backup of your database (probably pretty darn big given the table sizes you listed) and then set forced parameterization off and see if you get the same query plans?? Or maybe just change production for a few minutes off hours (if doable obviously)?

Jeffrey Williams (6/2/2009)

---

Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct and include the with recompile option, and the actual execution plans for different criteria all use index seeks where appropriate.

Jeffrey Williams (6/2/2009)

---

Nope - SQL Server 2005 SP3 and had the same experience on 2005 SP2 CU7.

Dave Ballantyne (6/3/2009)

---

Jeffrey ,
Can you post a full XML query plan of one of your queries that Seek ?.
If you could try to make the query as simple as possible though, just one column / variable.
We may be able to gleam a vital piece of information from that.

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?

Dave Ballantyne (6/3/2009)

---

Jeffrey ,

Can you post a full XML query plan of one of your queries that Seek ?.
If you could try to make the query as simple as possible though, just one column / variable.
We may be able to gleam a vital piece of information from that.