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?
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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???
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?
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".