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

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.