I have a purging SQL which deletes records from a single table prior to a certain number of days: delete t1 where status in ("ERR","COMPL") and datediff(dd,LastProcessDate,"datetime variable derived from getdate()") > 103 now I have run update stats on this table and have a non clustered index on both status & LastProcessDate. The problem i am getting is when i do showplan: This query is doing a tablescan. What i found is the following: QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. FROM TABLE t1 Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 16 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. Total estimated I/O cost for statement 1 (at line 1): 244910. but when i again ran this with a force index on the Status & Process date index , i got : STEP 1 The type of query is SELECT. FROM TABLE t1 Nested iteration. Index : StatusCode Forward scan. Positioning at index start. Using I/O Size 2 Kbytes for index leaf pages. With LRU Buffer Replacement Strategy for index leaf pages. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. Total estimated I/O cost for statement 1 (at line 1): 1025126. Anyone have any idea regarding the optimiser behaviour in this case. Appreciate any help received.