Why is there a scan on my clustered index?
clustered-index, sql, sql-server
Solution
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.
Problem
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