SQL Server Performance Forum – Threads Archive

Forcing Index Scan > Index Seek

Sorry, this may be akin to a FAQ, but … In the Query Execution Plan, I see that SQL Server is going to do a Clustered Index Seek when a high number of rows have a certain value, and a Clustered Index Scan when there are fewer matching rows. The seek, of course, gives me much better performance (in fact, with the scan, it always times out (>30 sec) in the ASP script). How can I *force* it to do a Clustered Index SEEK? I’ve included the phrase:
with (index(ID) (where ID is the clustered index)
for the relevant table, but it doesn’t make any difference. Using the info fromhttp://www.sql-server-performance.com/rd_table_hints.asp, I also tried INDEX(0) or INDEX(1), but it still goes for the scan, not the seek (when the number of matching rows is too low). BTW, I know it’s a rotten solution, but right now I’m forcing a SEEK by creating a query where the column value matches either what I’m really looking for or just some fake but highly frequent value (thus giving the necessary number of matching rows for SQL Server to choose a SEEK). Then I just delete from the temp table the rows that don’t really match what I want. There’s got to be a real way to do this, though. Thanks in advance.

---

Hi,
Rerer thread @http://www.dbforums.com/archive/index.php/t-321192.html which has similar discussion. Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

---

Can you post your query and the indexes involved?
Roji. P. Thomas
http://toponewithties.blogspot.com

---

]]>