关于缺少索引的SQL Server索引创建的思考 [英] Thoughts on index creation for SQL Server for missing indexes

问题描述

我正在进行性能调优我的SQL Server 2008数据库，并使用各种DMV的输出来识别缺失的索引，未使用的索引等等。

I'm working on performance tuning my SQL Server 2008 database and am using the output from various DMVs to identify missing indexes, indexes that aren't being used, etc.

我主要使用依赖于SQL Server提供的DMV数据的这3个脚本（来自SQLServerCentral.com）：

I am mainly using these 3 scripts (from SQLServerCentral.com) that rely on DMV data that SQL Server provides:

终极缺失索引查找器

终极重复索引查找器

Ultimate Index Usage Reporter

在尝试创建新索引时，我知道我需要聪明并尝试将许多建议组合成尽可能少的索引，方法是将它们创建得恰到好处以便它们可以做一个十二月提供一些不同查询的工作与为每个查询创建单独的索引时，唯一的区别可能是包含的列等。

When trying to create new indexes, I know that I need to be smart and try to group many of the suggestions into as few indexes as possible by creating them just right so that they can do a decent job of serving a few different queries vs. creating a separate index for each query when the only difference may be the included columns, etc.

所以我想得到社区的想法，看看人们是否可以回答有关阅读DMV结果和构建新索引的几个问题。

So I wanted to get the community's thoughts and see if people can answer a few questions about reading the DMV results and structuring the new indexes.

索引中包含列的顺序是否正确问题？

采取以下两个索引建议。你会做什么试图使1适合两者？

object_name equality_columns                    inequality_columns              included_columns
    Appointment [FranchiseId], [AppointmentTypeId]  [CustomerId], [ApptDateTime]    NULL
    Appointment [FranchiseId], [AppointmentTypeId]  [ApptDateTime]                  [CustomerId]

如果我有很多具有相同的等式和不等式字段但包含不同字段的索引建议，是否更好地包含更多字段或使用较少包含的字段？同样，目标是创建1个索引与3个（如果3个包含不同的列）。

最后，如果有任何好的资源如果可以提供这些链接，我可以提供良好的经验法则或一般指导方针。

Lastly, if there are any good resources out there that can offer up good rules of thumb or general guidelines for things like this I would appreciate it if those links could be provided.

推荐答案

订单对索引列很重要，但不包括列。这是因为只有键列用于查找。

Order matters for indexed columns, but not included columns. This is because only key columns are used for lookups.

你必须阅读大多数文档之间的界限，但这就是他们在中所暗示的内容这篇BOL文章

You have to "read between the lines" of most of the documentation, but that's what they're implying in this BOL article

这个讨论更明确地说顺序并不重要。

This discussion is a bit more explicit in saying order isn't important.

几乎仅关注包含索引中的ded列是空间 - 如果包含大量列或非常大的列，索引可能会变得非常大。但这意味着您肯定不想做两个单独的索引，每个索引具有相同的键列但包含不同的键列。然后你只是复合你的空间问题。

Pretty much the only concern with included columns in indexes is space--the indexes can get quite large if you include lots of columns, or very large columns. BUT this means you definitely don't want to do two separate indexes, each with the same key columns but different included ones. Then you're just compounding your space problem.

这篇关于关于缺少索引的SQL Server索引创建的思考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！