SQL Server 索引维护清单

1

Fragmentation

One of the biggest performance hits that you will see on your databases, next to not having indexes, is indexes that are very fragmented. Fragmentation causes issues where it takes SQL Server more time to traverse the index tree to find the necessary records. This can be checked using: sys.dm_db_index_physical_statssys.dm_db_index_physical_stats. Another problem with indexes that are very fragmented is that it takes up a lot more space to maintain these indexes and therefore impacting just about all aspects of your database.Take a look at these articles for more information.查看这些文章以获取更多信息。

2

Index Rebuilds or Defrags索引重建或碎片整理

Once you have identified which indexes are fragmented and need some assistance you need to determine whether you use the index rebuild or defrag operation. There are a couple of key differences between these operations:Rebuild – a complete rebuild of the index, cleanest approach, but requires the index to be offline (Enterprise Edition offers online operations)重建 – 索引的完整重建，最干净的方法，但要求索引处于脱机状态（企业版提供在线作）Defrag – only defrags part of the index, not as clean as rebuild, but this is an online operation so index is still available碎片整理 – 仅对索引的一部分进行碎片整理，不如重建干净，但这是一个联机作，因此索引仍然可用Read this tip for more information.阅读此提示以获取更多信息。

3

Placing Indexes on Separate File Groups将索引放置在单独的文件组上

Another potential performance gain would be to place your non-clustered indexes on a separate file group from your clustered index or heap table. This will allow SQL Server to hit different files and hopefully different disks to reduce any IO bottleneck that may occur. The key here is the filegroups are on different physical disks to really get the IO improvement.另一个潜在的性能提升是将非聚集索引放在与聚集索引或堆表不同的文件组上。 这将允许 SQL Server 访问不同的文件，并希望访问不同的磁盘，以减少可能发生的任何 IO 瓶颈。这里的关键是文件组位于不同的物理磁盘上，以真正获得 IO 改进。

4

Over indexed tables索引表

Indexing is a great way to ensure SQL Server finds the data as quick as possible. But on the flip side too many indexes on a table may degrade performance. For example let’s say a table has 10 indexes. Each time a new record is added, deleted or updated there is a potential that all 10 indexes need to be maintained. So too many indexes is not always helpful. Take a look at this tip to get a listing of all your indexes, so you can analyze what is out there.索引是确保 SQL Server 尽快找到数据的好方法。但另一方面，表上的索引太多可能会降低性能。例如，假设一个表有 10 个索引。每次添加、删除或更新新记录时，都可能需要维护所有 10 个索引。因此，太多的索引并不总是有帮助的。查看此提示以获取所有索引的列表，以便您可以分析现有内容。

5

Under indexed tables索引表下

The opposite is also true, too few indexes can hurt performance by having to do unnecessary table scans. The way to determine this is to use profiler to capture your longest running queries and then examine the execution plans to see where indexes could help.反之亦然，索引太少会因为必须进行不必要的表扫描而损害性能。确定这一点的方法是使用探查器捕获运行时间最长的查询，然后检查执行计划以查看索引可以提供帮助的地方。

6

Duplicate indexes重复索引

Another thing to look for is duplication of indexes. I have seen this occur where a clustered index or primary key is created and then another index is created for the exact same columns. Take the time to review your indexes and remove any duplication. This will cut down on the time it will take SQL Server to maintain these additional indexes. Take a look at this tip to get a listing of all your indexes, so you can analyze your indexes.另一件要寻找的事情是索引的重复。我已经看到这种情况发生在创建聚集索引或主键，然后为完全相同的列创建另一个索引的情况下。花时间检查您的索引并删除任何重复项。这将减少 SQL Server 维护这些附加索引所需的时间。查看此提示以获取所有索引的列表，以便您可以分析您的索引。

7

Clustered indexes or not聚集索引与否

In most cases all tables should have a clustered index. This is a good practice as well as allowing you to physically store the data according to the clustered index. This can help on queries where you need to return ranges of data. Also, clustered indexes are helpful to combat fragmentation issues that may occur with heap tables. Take a look at this tip to get a listing of all your indexes, so you can analyze your indexes.在大多数情况下，所有表都应具有聚集索引。这是一种很好的做法，还允许您根据聚集索引物理存储数据。这有助于需要返回数据范围的查询。此外，聚集索引有助于解决堆表可能出现的碎片问题。查看此提示以获取所有索引的列表，以便您可以分析您的索引。

8

Scheduling Maintenance计划维护

Above we mentioned about how to find out if there is fragmentation and the options of using rebuilds or defrags. Once you have determined this you should setup a scheduled time of when you will address these issues. The best time to run these is off hours when the load is low. Also, how frequently this is run really depends upon how fragmented your indexes become and how quickly they become fragmented. In some cases there are indexes that probably never need to be rebuilt, but often it is easier to apply maintenance across the board.上面我们提到了如何确定是否存在碎片以及使用重建或碎片整理的选项。确定这一点后，您应该设置一个解决这些问题的预定时间。 运行这些的最佳时间是负载较低的非工作时间。此外，运行频率实际上取决于索引的碎片化程度以及碎片化的速度。在某些情况下，有些索引可能永远不需要重建，但通常更容易全面应用维护。

9

Covering indexes覆盖指数

Covering indexes include more then just the column that you are searching on. Let’s say for example you want to search by LastName. You could create an index on just LastName, but you know that there is always the need to get FirstName, City and State. So based on this you could create an index that has all four columns, so when the query is executed it can get all of its data from the covering index instead of having to lookup the data from the clustered index.覆盖索引不仅包括您正在搜索的列。例如，假设您想按 LastName 进行搜索。您可以仅在 LastName 上创建索引，但您知道始终需要获取 FirstName、City 和 State。因此，基于此，您可以创建一个具有所有四列的索引，因此，在执行查询时，它可以从覆盖索引中获取其所有数据，而不必从聚集索引中查找数据。

10

Ascending and Descending indexes升序和降序索引

This article talks about ascending and descending indexes. This tip showed how it doesn’t really matter if the index is created in either ascending or descending order for one column, but how key this can be when there are multiple columns and there is a need to have one column ascending and another descending. Keep this in mind when you think about how the indexes will be used.本文讨论升序和降序索引。这个技巧展示了索引是按升序还是降序为一列创建并不重要，但当有多列并且需要一个列升序和另一个列降序时，这有多重要。当您考虑如何使用索引时，请记住这一点。

11

Indexes on columns that are not very selective选择性不强的列上的索引

Another issue that I see quite often is indexes being put on columns that are not very selective. What this means is that there are so few differences in the values that are stored in the column that SQL Server will never use the index. Some examples of this include gender codes (M,F) or something else where there are too few values for the index to be useful. These columns should not be indexed directly, but could be used as a secondary column in an index for a covering index or used as an included column.我经常看到的另一个问题是索引被放在选择性不强的列上。这意味着，存储在列中的值差异非常小，以至于 SQL Server 永远不会使用索引。这方面的一些例子包括性别代码 （M，F） 或其他值太少而索引无法使用的东西。这些列不应直接建立索引，但可以用作覆盖索引中的辅助列或用作包含的列。

12

Non used indexes未使用的索引

Do you have any indexes that are not being used? With SQL Server you can use the DMV sys.dm_db_index_usage_stats. This will give you an idea of which indexes are being used and how they are being used. Take a look at this tip How to get index usage information for more information.是否有未使用的索引？使用 SQL Server，可以使用 DMV sys.dm_db_index_usage_stats。这将使您了解正在使用哪些索引以及如何使用它们。有关详细信息，请查看此提示：如何获取索引使用信息。