WHY Index Scan Instead Of Seek While Joining Big Table With #Temp Table

Question

In my sql server 2016 database, I have a table 'BigData' having around 50 million rows and a Temporary table #TempID which contains 600 rows.

CREATE TABLE BigData (ID INT IDENTITY(1,1) PRIMARY KEY, OtherID INT, Comment nvarchar(max), ........................................................................)

CREATE TABLE #TempID (ID INT PRIMARY KEY)

I have also a NonClustered index on OtherID column of BigData table:

CREATE NONCLUSTERED INDEX IX_BigData_OtherID ON BigData (OtherID) INCLUDES (Comment, .............................................)

I am executing the given query in a procedure:

SELECT A.ID, A.Comment

FROM BigTable A

JOIN #TempID B ON A.OtherID=B.ID

ORDER BY A.ID

The query was taking lots of time (more than 5 minutes). When i check the execution plan in activity monitor, it is showing Index Scan which is taking time. Here, my question is, why there is Index Scan instead of Index Seek even if we have only 600 records in #TempID table???

Answer 1

why there is Index Scan instead of Index Seek

Index Scan on which table/column?

You have no WHERE clause in your query, why do you expect something different then a FULL SCAN?

Answer 2

Hi @Vikrant Sirohi

why there is Index Scan instead of Index Seek even if we have only 600 records in #TempID table???

The statistics for the involved columns might be outdated or inaccurate. Try update statistics on the involved tables and indexes.

Also, check for index fragmentation and rebuild/reorganize indexes if necessary.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".