why this is an index scan and not an index seek

Question

A clustered index has been created on both dw_assesment_details and dw_assesment_details_id tables

/* 6 minutes */
CREATE CLUSTERED INDEX [Ix_DW_ASSESSMENT_DETAILS_qid_QNO_TmpverName] 
ON [dbo].[DW_ASSESSMENT_DETAILS_QUESTION_ID]
(
    [TEMPLATENAME] ASC,
    [TEMPLATEVERSION] ASC,
    [QUESTION_NO] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

/* 9 minutes */
CREATE CLUSTERED INDEX [Ix_DW_ASSESSMENT_DETAILS_QNO_TmpverName] 
ON [dbo].[DW_ASSESSMENT_DETAILS]
(
    [TEMPLATENAME] ASC,
    [TEMPLATEVERSION] ASC,
    [QUESTION_NO] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

SELECT 
    [GETQUESTIONID], 
    dw.[TEMPLATENAME], dw.[TEMPLATEVERSION],
    dw.[QUESTION_NO]
FROM 
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS] dw 
INNER JOIN
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS_QUESTION_ID] id ON dw.TEMPLATENAME = id.TEMPLATENAME 
                                                          AND dw.TEMPLATEVERSION = id.TEMPLATEVERSION 
                                                          AND dw.QUESTION_NO = id.QUESTION_NO

But the above select query is using an index scan - not index seek. What to do to use an index seek instead?

Any suggestion by performance tuning expert?

You are reading all the data and the two tables have indexes that present the data in the same order. You **don't** want an index seek — Caleth, Aug 17 '17 at 09:15
You might want to read [RBarry Young's Answer on this question](https://stackoverflow.com/questions/6528906/why-is-this-an-index-scan-and-not-a-index-seek) — Ulug Toprak, Aug 17 '17 at 09:16

Dan Guzman · Answer 1 · 2017-08-18T10:46:51.883

What to do to use an index seek instead?

You could specify a LOOP JOIN query hint if you want to try and outsmart the SQL Server optimizer. I would expect the plan with the MERGE JOIN to perform much better with many rows.

Note that the storage engine may also perform asynchronous read-aheads during large scans to prefetch data into memory so that it's available to the query without waiting to be read from storage. Read-aheads won't occur with seeks that return few rows.

Try the queries below to see if that is the case in your environment.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT 
    [GETQUESTIONID], 
    dw.[TEMPLATENAME], dw.[TEMPLATEVERSION],
    dw.[QUESTION_NO]
FROM 
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS] dw 
INNER JOIN
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS_QUESTION_ID] id ON dw.TEMPLATENAME = id.TEMPLATENAME 
                                                          AND dw.TEMPLATEVERSION = id.TEMPLATEVERSION 
                                                          AND dw.QUESTION_NO = id.QUESTION_NO;
GO

SELECT 
    [GETQUESTIONID], 
    dw.[TEMPLATENAME], dw.[TEMPLATEVERSION],
    dw.[QUESTION_NO]
FROM 
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS] dw 
INNER JOIN
    [QIS_DW].[dbo].[DW_ASSESSMENT_DETAILS_QUESTION_ID] id ON dw.TEMPLATENAME = id.TEMPLATENAME 
                                                          AND dw.TEMPLATEVERSION = id.TEMPLATEVERSION 
                                                          AND dw.QUESTION_NO = id.QUESTION_NO
OPTION(LOOP JOIN);
GO

score 1 · Answer 2 · answered Aug 17 '17 at 09:30

In SQL Server, Clustered or Non-Clustered index table search change index scan till we did not add condition in Where clause.

Where clause will make Index seek as execution plan. Without Where clause it act as Index Scan to search whole table data. That's why index scan is always slow.

why this is an index scan and not an index seek

2 Answers2