为什么对我的聚集索引进行扫描?

问题描述

SQL 2000
NED 表有一个到 SIGN 表的外键 NED.RowID 到 SIGN.RowID
SIGN 表有一个指向 NED 表的外键 SIGN.SignID 到 NED.SignID
RowID 和 SignID 是作为 GUID 的集群主键(不是我的选择)
WHERE 子句是:

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

为什么此查询的 SIGN 表上有一个 INDEX SCAN?什么会导致对聚集索引进行索引扫描?谢谢

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

推荐答案

这是一篇关于 SQL Server 何时达到临界点"并从索引查找切换到索引/表扫描的好博文:

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.

这篇关于为什么对我的聚集索引进行扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！