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
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.
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.
Because your WHERE clause isn't against indexed columns.
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
