SQL Server：在一个小表上进行表扫描怎么会如此昂贵？
我正在查看一个麻烦查询的执行计划。
我可以看到 45% 的计划用于对包含七 (7) 行数据的表进行表扫描。
我即将在一个有七行的表上放置一个聚集索引来覆盖查询中的列，但感觉......错误。鉴于表如此之小，我的查询的这一部分如何占用如此多的计划？
我正在阅读此处，感觉这可能只是因为非连续数据——相关表上根本没有索引。总的来说，我们的数据库很大（7GB）并且很繁忙。
我很想知道其他人的想法 - 谢谢！
编辑：
查询运行非常频繁，并且陷入死锁（并被选为受害者）。目前，运行时间在 300 毫秒到 500 毫秒之间，但当数据库更繁忙时，需要的时间会更长。
查询：
select l.team1Score, l.team2Score, ls.team1ExternalID, ls.team2ExternalID, et.eventCategoryID, e.eventID, ls.statusCode
from livescoretracking l(nolock)
inner join liveScores ls (nolock) on l.liveScoreID = ls.liveScoreID
inner join db1.dbo.events e on e.gameid = ls.gameid
inner join db1.dbo.eventtype et (nolock) on e.eventTypeID = et.eventTypeID
inner join eventCategoryPayTypeMappings ecb (nolock) on ( et.eventCategoryID = ecb.eventCategoryID and e.payTypeID = ecb.payTypeID and ecb.mainEvent = 1 )
where ls.gameID = 286711 order by l.dateinserted
问题表是 eventCategoryPayTypeMappings 表 - 谢谢！
I'm looking at an execution plan from a troublesome query.
I can see that 45% of the plan is taken up doing a table scan on a table with seven (7) rows of data.
I am about to put a clustered index to cover the columns in my query on a table with seven rows and it feels...wrong. How can this part of my query take up so much of the plan given the table is so tiny?
I was reading up here and it feel it might just be becuase of non-contiguous data - there are no indexes at all on the table in question. Overall though our database is large-ish (7GB) and busy.
I'd love to know what others think - thanks!
EDIT:
The query is run very frequently and was involved in deadlock (and chosen as the victim). Right now it's taking between 300ms and 500ms to run, but will take longer when the database is busier.
The query:
select l.team1Score, l.team2Score, ls.team1ExternalID, ls.team2ExternalID, et.eventCategoryID, e.eventID, ls.statusCode
from livescoretracking l(nolock)
inner join liveScores ls (nolock) on l.liveScoreID = ls.liveScoreID
inner join db1.dbo.events e on e.gameid = ls.gameid
inner join db1.dbo.eventtype et (nolock) on e.eventTypeID = et.eventTypeID
inner join eventCategoryPayTypeMappings ecb (nolock) on ( et.eventCategoryID = ecb.eventCategoryID and e.payTypeID = ecb.payTypeID and ecb.mainEvent = 1 )
where ls.gameID = 286711 order by l.dateinserted
The problem table is the eventCategoryPayTypeMappings table - thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
对小表进行表扫描并不是一件坏事 - 如果它适合对缓存的单次读取，优化器将计算出表扫描的成本低于读取索引链的成本。
如果您想帮助确保内容“倾向于”以这种方式排序（尽管您需要明确的顺序来保证这一点），我只会推荐聚集索引。
A table scan on a small table is not a bad thing - If it fits in a single read into the cache the optimizer will calculate that a table scan costs less than reading through an index chain.
I would only recommend a clustered index if you want to help insure that the contents will 'tend' to be sorted that way (though you will need an explicit order by to guarantee that).
这实际上取决于查询从开始到结束需要多长时间。如果查询只花费 10 毫秒，45% 并不意味着花费很长时间。它真正说的是大部分时间都花在表扫描上，这是可以理解的。
当表增长时，拥有索引可能会有所帮助，并且可能不是一个坏主意，除非您知道该表不会增长。然而，您会发现向包含 7 条记录的表添加索引对性能几乎没有影响。
It really depends how long the query takes from start to finish. 45% doesn't mean its taking a long time if the query is only taking say 10ms. All it really says is most of the time is spent doing the table scan which is understandable.
Having an index may help when the table grows and is probably not a bad idea unless you know this table is not going to grow. However you will find that adding an index to a table with 7 records makes little to no difference to performance.
死锁通常更能说明资源访问顺序问题，而不是具体的查询设计问题。我会查看死锁中的其他参与者，并查看每个事务锁定了其他参与者所需的哪些对象。如果您可以重新排序以确保一致的访问顺序，您也许能够完全避免争用问题。
Deadlocks are usually more indicative of a resource access ordering issue than a problem with query design in particular. I would look at the other participant(s) in the deadlock and take a look at what objects each transaction had locked that were required by the other(s). If you can reorder to ensure consistent access order you may be able to avoid contention issues entirely.
如果表上没有索引，查询引擎将总是必须进行表扫描。没有其他方法可以处理数据。
许多 RDBMS 平台都会对这么小的表进行表扫描，即使有索引也是如此。 （我不确定 SQL Server 具体情况。）
我会更关心查询计划中的实际数字。
If there are no indexes on the table, the query engine will always have to do a table scan. There's no other way it can process the data.
Many RDBMS platforms will do a table scan on a table that small even if there are indexes. (I'm not sure about SQL Server specifically.)
I would be more concerned about the actual numbers in the query plan.
对七行表进行表扫描并不昂贵。除非有查询提示，否则无论存在什么索引，查询引擎都会对这么小的表使用表扫描。您能否向我们展示有关相关查询和执行计划问题的更多信息？
A table scan on a seven row table is not expensive. Barring query hints, the query engine will use a table scan on such a small table no matter what indexes exist. Can you show us more about the query in question and the problem with the execution plan?
如果不知道实际的总成本，百分比成本就没有意义。例如，如果查询需要 1 毫秒来执行表扫描，则 45% 的成本为 0.45 毫秒，则不值得尝试优化；如果查询需要 10 秒来执行，则 45% 的成本就很大并且值得优化。
A percentage cost is meaningless without knowing the total cost in real terms. e.g. if the query takes 1 ms to execute a 45% cost for a table scan is .45 of a milisecond which is not worth trying to optimise, if the query takes 10 seconds to execute then the 45% cost is significant and worth optimising.