Why am I getting index seek?
I am running below query and as it’s returning almost all records from the table, it should be using index scan not seek. Anyone, please explain why it’s using seek not a scan.
DROP TABLE tblPlanDiff GO CREATE TABLE tblPlanDiff(Sno int identity,Col_1 int,Col_2 int) GO DECLARE @i int=1 WHILE(@i<=200000) BEGIN BEGIN TRAN INSERT INTO tblPlanDiff values(@i*2,@i*3) COMMIT TRAN SET @i+=1
END CREATE UNIQUE CLUSTERED INDEX ix_Sno on tblplandiff(Sno ASC) GO CREATE INDEX ix_Col1_Col2 on tblplandiff(Col_1) INCLUDE(Col_2) GO SELECT sno,col_1,col_2 FROM tblPlanDiff WHERE col_1>2
2 Solutions collect form web for “Why am I getting index seek?”
As I already mentioned in my comment: why are you worried about getting an index seek?
Col_2 as an included column, and also includes
Sno from the clustered index – so it contains all three columns that you need to satisfy your query.
So in the end, the query optimizer makes a choice on how to approach this query – and it seems to have preferred an index seek – I don’t see any problem with that at all.
When running this query on my SQL Server 2008 R2 Developer edition, I have these perf values:
Table 'tblPlanDiff'. Scan count 1, logical reads 797, physical reads 3, read-ahead reads 499, SQL Server Execution Times: CPU time = 47 ms, elapsed time = 855 ms.
When I run the same query with the
WITH (FORCESCAN) query hint, I get:
Table 'tblPlanDiff'. Scan count 1, logical reads 797, physical reads 3, read-ahead reads 499, SQL Server Execution Times: CPU time = 78 ms, elapsed time = 852 ms.
So quite obviously, there’s hardly any difference between the two – and there might have been a tiny detail that made the query optimizer prefer the index seek over the scan. Not sure why – but I don’t seen any issue or problem with that. Do you?
Because you have an index on
Col_1, it is able to Seek within that index to a point where
Col_1 values are greater than two. Just because it’s doing a Seek doesn’t mean that it’s not seeking MANY rows.
If you see a Scan, that means it is starting at the beginning of the index and scanning from there. In a sense, an Index Seek may still “scan”; it’s just starting from a precise location in the index.
Either way, why are you desiring a Scan over a Seek?