为什么我的群集索引上有扫描?

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:

NED表的外键指向NED符号表。RowID签署。罗维德:标志台有内德标志的外键。SignID内德。表示RowID和表示是集群的主键，它们是GUIDs(不是我的选择)WHERE子句是:

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

为什么在这个查询的符号表上有索引扫描?什么会导致集群索引上的索引扫描?谢谢

4 个解决方案

#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:

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

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.

您可能想看看查询的过滤方式，因为临界点通常比人们预期的要少得多。

#2

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.

集群索引扫描是SQL Server在具有集群索引的表上指定完整表扫描的方式。这是因为符号表上没有足够的索引来满足WHERE子句，或者因为它认为符号表足够小(或者索引不够选择性)，所以表扫描会更有效。

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.

通过检查查询，您可能需要索引DeptID列，以及StartTime、EndTime和nedsid的一些组合，以避免表扫描。如果您要问的原因是您有性能问题，那么您还可以运行索引调优向导(现在是SQL2005+客户端工具中的数据库引擎优化顾问)，并提供一些建议，以便创建哪些索引来加速查询。

#3

Because your WHERE clause isn't against indexed columns.

因为WHERE子句不是针对索引列的。

#4

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

如果我没看错的话，在A表格上有几个限制:

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?

这些限制(比如DeptID、StartTime、EndTime、ned雕塑)是否被索引?这些字段从您的数据集中选择的效果如何?

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.).

如果你有10个mio。行和ned雕塑sid只有10个可能的值，那么对该字段的任何限制总是会产生大约值。1绪。行——在这种情况下，SQL Server进行完整的表扫描(集群索引扫描)可能更容易(而且成本也更低)，特别是如果它还需要检查同一表上没有索引的WHERE子句(StartTime、EndTIme等)。

Marc

马克

#1