Turn index scan to an index seek

Question

I've added an index to a table to stop a table scan. Now it scans the index instead of the table, why doesn't it perform an index seek?

CREATE PROCEDURE [dbo].[GetPeople]
(
    @PlaceIDs [dbo].[Integers] READONLY,
    @RoleGroupIDs [dbo].[Integers] READONLY
)

AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        PTS.PersonToSiteID,
        PTS.SiteID,
        PTS.PersonID,
        P.PersonID,
        P.GivenName,
        P.FamilyName,
        P.Email,
        P.LandlineNumber,
        P.MobileNumber,
        R.RoleTypeID
    FROM 
        [dbo].[PeopleToPlaces] PTS
        INNER JOIN (Select DISTINCT Identifier FROM @PlaceIDs) Pl ON PTS.PlaceID = Pl.Identifier
        INNER JOIN [dbo].[People] P ON P.PeopleID = PTS.PeopleID 
        INNER JOIN [dbo].[Role] R ON R.RoleID = P.RoleID
        INNER JOIN (Select DISTINCT Identifier FROM @RoleGroupIDs) RG ON R.RoleGroupID = RG.Identifier
END

I have a covering index on the People table and have added the Distinct subqueries whilst testing. There is an index covering the join onto the PTS table and the Identifier field sin the UDTs are both ints matching the type they are joining to. I've also tried a SELECT IN compared to a JOIN and can't find a way to avoid the index scan

Used this as a resource so far - https://www.red-gate.com/simple-talk/sql/performance/identifying-and-solving-index-scan-problems/

Unless your index includes all the columns you're using from the table, it's quite possible SQL Server will determine that it's still more efficient to do a table scan than to use the index. — David784, Mar 11 '21 at 14:55
you will need to provide your actual executionplan (see pastetheplan), it can depend on various factors such as cardinality, estimates etc — Stu, Mar 11 '21 at 14:58
"Index Scan" is not a problem. It may well be the best possible option for your query, specially if we consider your query has no filtering. — The Impaler, Mar 11 '21 at 16:00

score 0 · Accepted Answer · answered Mar 12 '21 at 11:41

Your query doesn't have a WHERE filtering clause; it asks for all records in the resultset of that big join.

The query planner would choose index seek over index scan if you wanted just one, or a very small number, of rows in your resultset. index scan is a two-step process: first it seeks to the first row it needs (which could be the first row in the index). It then scans the index sequentially to get the rest of the rows it needs.

Scanning a covering index is an efficient way to get a multi-row resultset: the query can be satisfied from the index, so the query planner doesn't have to bounce back and forth between the index and the table data itself.

All is well here.

Turn index scan to an index seek

1 Answers1