Why is there a scan on my clustered index?

Question

SQL 2000
The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID
The SIGN table has a foreign key to the NED table SIGN.SignID to NED.SignID
The RowID and SignID are clustered primary keys that are GUIDs (not my choice)
The WHERE clause is:

FROM
    [SIGN] A   
    INNER JOIN NED N ON A.SIGNID = N.SIGNID  
    INNER JOIN Wizard S ON A.WizardID = S.WizardID   
    INNER JOIN [Level] SL ON N.LevelID = SL.LevelID  
    LEFT JOIN Driver DSL ON SL.LevelID = DSL.LevelID  
        AND DSL.fsDeptID = @fsDeptID  
    INNER JOIN [Character] ET ON S.CharacterID = ET.CharacterID  
    INNER JOIN Town DS ON A.TownID = DS.TownID   
WHERE  
    (A.DeptID = @DeptID OR   
    S.DeptID = @DeptID  
    AND   
    A.[EndTime] > @StartDateTime AND A.[StartTime] < @EndDateTime  
    AND   
    A.NEDStatusID = 2    

Why is there an INDEX SCAN on the SIGN table for this query? What would cause an index scan on a clustered index? Thanks

Answer 1

A clustered index scan is how SQL Server designates a full table scan on a table with a clustered index. This is because you don't have enough indexes on the SIGN table to satisfy the WHERE clause, or because it decided that the SIGN table is small enough (or the indexes not selective enough) that a table scan would be more efficient.

Just by examining the query, you'd probably have to index the DeptID column as well as some combination of StartTime, EndTime and NEDStatusID to avoid the table scan. If the reason you're asking is because you're having performance problems, you can also run the Index Tuning Wizard (now the Database Engine Tuning Advisor in the SQL2005+ client tools) and have it give some advice on which indexes to create to speed up your query.

Answer 2

Because your WHERE clause isn't against indexed columns.

Answer 3

Here's a good blog post about when SQL Server reaches the "tipping point" and switches from an index seek to an index/table scan:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

You may want to look at the way your queries are filtering, as the tipping point is often much fewer rows than people expect.

Answer 4

You have several restrictions on the SIGN A table, if I read this correctly:

WHERE  
        (A.DeptID = @DeptID OR   
        S.DeptID = @DeptID  
        AND   
        A.[EndTime] > @StartDateTime AND A.[StartTime] < @EndDateTime  
        AND   
        A.NEDStatusID = 2

Are any of those restrictions (like DeptID, StartTime, EndTime, NEDStatusID) indexed? How well are those field selecting from your set of data?

If you have 10 mio. rows and NEDStatusID has only 10 possible values, then any restriction on that field would always yield approx. 1 mio. rows - in that case, it might just be easier (and less costly) for SQL Server to do a full table scan (clustered index scan), especially if it also needs to check additional WHERE clauses on the same table that aren't indexed, either (StartTime, EndTIme etc.).

Marc