我是否需要重建SQL服务器数据库索引？(Do I need to rebuild my SQL server database indexes?)

我是否需要重建SQL服务器数据库索引？(Do I need to rebuild my SQL server database indexes?)

我使用了内置的性能调优工具并为我的数据库构建了索引。 从那时起，我将一些查询从“select top 10 ...”更改为“select top 50”。

我的问题是我需要重建我的SQL服务器数据库索引吗？

I used the built-in performance tuning tool and built indexes for my database. Since then I changed some of the queries from "select top 10..." to "select top 50".

My question is do I need to rebuild my SQL server database indexes?

最满意答案

如果你所有的变化都是排名前50的前十名，则不需要重建索引。查询在执行计划方面完全相同（因此将从现有索引中受益），唯一的区别是你正在检索一些额外的结果集中的行。

如果您注意到在此更改后性能大幅下降，则问题必定在其他地方。 例如，我发现在按照与索引的自然顺序不同的顺序添加数据之后，出于性能原因需要重建聚簇索引。 但这是因为新数据，而不是因为前10名变为前50名。

编辑：Sam Saffron评论说执行计划可以在前10个和前50个查询之间进行更改，以确保您不会检查两个执行计划并查看它们是否有所不同。

You don't need to rebuild your indexes if all you've changed is top 10 for top 50. The queries are exactly equal regarding execution plan (and thus will benefit from existing indexes), the only difference is that you are retrieving some extra rows from the resultset.

If you are noticing a big performance drop after that change, the problem must lie elsewhere. For example, I've seen the need to rebuild a clustered index for performance reasons after adding data in an order different to the natural order of the index. But this is because of the new data, not because a top 10 changed to top 50.

EDIT: Sam Saffron comments that execution plans can change between top 10 and top 50 queries, to make sure this isn't happening to you check both execution plans and see if they vary.