SQLServer数据库索引性能监控列表

所属类别：SQL SERVER
推荐指数：★★★★
文档人气：155
本周人气：4
发布日期：2008-2-17

审核索引的使用情况不是一件容易的任务，但对于你服务器的性能来说是紧迫的
在审核SQLServer数据库里索引的使用情况的时候，有时我很受打击。例如，怎样去审核超过1500个表的数据库的索引？审核单个索引相对简单些，审核多个数据库里的成千上万个索引就不是一件容易的任务了。不管这项任务是否容易，对于优化SQLServer数据库的性能来说却是重要的。

在着手处理大量索引的审核时有两个不同的方法。一个是分成更小的更容易管理的单元，首先着眼于那些最可能影响SQLServer性能的索引。例如，你可以在你服务器最忙的数据库上启动审核，如果它有很多表，首先从最多数据的那些表开始，然后逐步到那些少一点的表。这样，你将在那些最可能有很大实际影响服务器性能的地方看到最初的成就。

另一个方法，也是我通常使用的方法（因为我有点懒），就是使用排除法。我的意思是如果看不到数据库的任何性能问题，就不必要评估数据库的每一个索引。但如果数据库显示正好存在性能问题，那么对那些不是最优的索引来说是一个好的调优机会，特别注意它们，尤其在数据库任务紧急的时候。如果有大量的索引要审核，那么先从最大的入手，因为它们最可能引起性能问题。例如，在有1500个表的数据库里，我仅仅小心的审核大约一打的表（都是很大的表），我认为它们应该受到最多的关注。

不管怎样，当你决定审核你所管理的数据库的索引的时候，你需要拿出合理的计划并系统地实现。

正如你已经看到的，我上面提供的审核列表不是很长。这是故意的。记住，这一系列关于性能监控的文章的目的是为了分辨容易和显而易见的性能问题，不是找出全部。上面列出来的将使你走很长的路去分辨和纠正容易的与索引相关的性能问题。一旦你掌握了它们，就可以更上一层楼了。例如，本网站上有很多索引相关的提示，大部分都很高级，比如下面的主题：

普通索引
聚集索引
复合索引
覆盖索引
非聚集索引
重建索引
索引调优向导
如果你还没有做过的话，你需要复习这些提示的网页。

你最近运行过索引调优向导吗？

微软在SQLServer7.0和2000里给我们最好的工具就是之一就是索引调优向导。它不是一个完美的工具，但它确实能帮助你分辨存在的索引是否正被使用，同时提供能加快查询的新索引。如果你正使用SQLServer2000，它也能推荐索引视图的使用。它使用目前你正在数据库里运行的查询，所以它的建议是基于你数据库是真正怎么使用的。它用来分析所需的查询来源于你用SQLServer事件探查器创建的跟踪。

当在一个新的SQLServer上进行性能审核时我做的第一件事就是在捕捉到的服务器活动的跟踪上运行索引调优向导。大多数情况下，它能帮助我快速的分辨出任何一个不被使用的可以被删掉的索引，分辨出为了提升数据库性能需要新建的索引。

这里有一些对于在使用索引调优向导审核SQLServer数据库索引时的提示：

当你在使用事件探查器捕捉数据时（索引调优向导用来分析性能），选择一天中数据库正常负荷的具有代表性的时段。我通常喜欢选择在上午或者下午3点，然后运行事件探查器跟踪至少一个小时以上。
一旦事件探查器跟踪完，索引调优向导可以随时运行。但是，一个好的想法是在数据库一段时间不忙的最适宜的时候运行，这是因为使用索引调优向导进行性能分析时会影响服务器的一些性能，既然不必要，对服务器性能产生负面影响就毫无意义。也要避免在产品服务器上运行分析（向导仍不得不连接到产品服务器），当执行分析的时候在另一台服务器上运行向导可以减少产品服务器的负载。
尽管要花费更多的时间去完成分析，你需要在索引调优向导的几个选项的设置期间列一个清单来帮助进行彻底的分析。这些包括：不要选择"Keep all existing indexes"（保留所有现有索引），因为你要分辨哪些索引没有用;指定你要进行"Thorough"（彻底的）分析，而不是"Fast"（快速）或者"Medium"（适中）;不要选择"Limit the number of workload queries to sample"（将要抽样的工作负荷查询的数目限制为）选项，保留"maximize columns per index"（每个索引的最大列数）设置的最大设置为16;选择所有被用来调优的表。通过选择这些选项，索引调优向导将进行彻底的工作，尽管要花费几个小时才能完成，这依赖于跟踪文件的大小和你执行分析的硬件的速度。注：这些只针对SQLServer2000，SQLServer7.0稍微有些不同。
一旦分析完成，向导也许没有任何建议，也许建议删除一个或更多的索引，或者建议添加一个或更多的索引，或者建议既添加也删除。你需要在采纳建议之前小心评估它们。例如，向导也许建议删除一个特殊的索引，但你知道该索引是真正需要的。那么当你知道这不是一个好想法可为什么向导建议删除呢？这是因为向导没有分析在跟踪文件（仅仅是一个抽样而已）里发现的每一个查询，加之你的抽样跟踪数据可能没有包括需要那个索引的查询。这种情况下，向导也许建议删除该索引，即使这不是一个好的建议。一旦你检查到一个索引是不需要的，你应该删除它。
如果向导建议添加新的索引，那么你要评估它们，也要和目前表上存在的索引比较看看它们是否有意义，会不会引起潜在的新的问题。例如，一个建议的索引或许能帮助一部分查询，但它也可能降低每小时成千上万次的INSERT操作。向导不知道这些，你必须决定哪个更重要，一些查询运行快了点而INSERT去慢了，反之亦然。
最后，即使索引调优向导没有任何新索引的建议，这也不意味着新索引是不需要的，仅仅根据跟踪数据来分析可能不会有任何建议。为了更好的帮助分辨出需要的索引。你要考虑好几天运行多个跟踪以便得到更多的抽样数据。即使那样，索引调优向导也不能找出全部需要的索引，但它将找出所有显而易见需要的索引。
一旦你完成分析并根据建议做出了更改，我建议你再次跟踪分析以便看看你的更改是否有效果。谨记索引调优向导分析不是一蹴而就的事情。随着时间的推移数据库的数据发生了潜在的变化，随着一起变化的还有查询的类型。所以，作为一个要点：定期的对服务器进行跟踪和分析以保持定期的优化。?

每个数据库的每个表都有聚集索引吗？

首要的原则是每个表都应该有聚集索引。聚集索引通常但不总是应该建在单调递增的一列上如自增列，或者其他的值是递增并唯一的列上。大多数情况下，主键是作为聚集索引理想的列。

如果你曾经调优过SQLServer6.5的性能，你也许听说在单调递增列上创建聚集索引不是一个好的方法，因为它可能由于磁盘的"hotspot"（热区）引起性能问题。那个建议在SQLServer6.5中是正确的。

在SQLServer7.0和2000中，"hotspots"通常不是问题了。只有在每秒超过1000个的事务的情况下，"hotspot"才对性能有负面的影响。事实上，"hotspot"在这些环境下是有用的，因为它消除了页拆分。

下面是原因。如果你正在向一个主键上建聚集索引的表里插入新的行，主键是单调递增的，这意味着每个INSERT将在磁盘上逐个的物理顺序出现，因此，页拆分不会发生，这本身就节省了资源。这是因为SQLServer有能力决定数据是否被插入到有单调递增序列的表里，如果是，就不会执行页拆分。

如果你正插入很多行到一个堆表（没有聚集索引的表）中，数据不会按任何特定的顺序插入到数据页，不管数据单调递增与否。这样当从磁盘上访问数据时，SQLServer会花费更多的读操作。另一方面，如果给表添加聚集索引，数据被顺序的插入到数据页上，通常在读取数据时花费更少的磁盘I/O。

如果数据被插入到一个或多或少有随机顺序的聚集索引里，数据通常是随机的插入到数据页里，就象堆表一样，会发生页拆分。

那么说回来，为了全面的提升性能，最好的建议就是在一个单调递增列（假定有一列是符合条件的）上添加聚集索引。如果表上有很多INSET、UPDATE和DELETE操作更是应该这样。但如果表的更改很少，大部分是SELECT语句，那么这个建议就不是很有用，为聚集索引考虑其他的选择。

作为索引监控的一部分，检查看看数据库里每个表是否都有索引。如果根本没有索引，认真的考虑给添加一个聚集索引，参考上面的建议。事实上给表添加一个聚集索引不会比没有聚集索引时引起性能下降。?

每个表的任一列是否被多次索引?

听上去这个建议是显而易见的，但它比你认为的要普遍得多，特别是多个DBA每人管理一段时间的数据库。SQLServer不关心你是否这样做，只要索引的名称不同它就认可了。所以当检查表目前的索引时，看看是否有列在不必要的重复索引中。删除它们不仅能节约磁盘空间，也能加快对表数据的访问和修改。?

重复索引的一个通常例子就是忘记了列上有主键，或者列是唯一的，这样列上会自动创建索引，可是又在上面以不同的索引名称创建了索引。

查询里是否有没有被使用的索引?

这里有另外一个显而易见的建议，但也是一个普遍的问题，特别是在数据库正式启用以前DBA或开发人员猜测的为数据库创建的最初的那些索引。仅仅看看表的索引不会告诉你这些索引是否有用，所以分辨没用的索引通常是不容易的。

分辨没用的索引的最好途径是使用索引调优向导，前面讨论过。

不必要的索引，象重复索引，既浪费磁盘空间又对数据的访问修改的性能没有多大的好处。?

索引是否太宽?

索引越宽，明显地就变得越大，访问或修改数据时SQLServer就不得不做更多的操作。因此，你应该避免在太宽的列上添加索引。索引越窄，性能越快。

另外，复合索引，包括两个或更多的列，也可能出现同样潜在的问题。通常要尽可能的避免复合索引。数据库使用复合索引越多，常常意味着数据库的设计有缺陷。

不可能总是避免宽索引或复合索引，但不得不用时，确信对你的选择做过仔细的评估并且没有其他的办法帮助提高性能。?

连接的表的连接列上是否有适当的索引?

基本上，为了最好的性能，表里用来连接的列上都应该建索引。这是直接了当的建议，也是相当显而易见的，但为了最优的JOIN性能监控索引却是不容易的，因为为了全面的性能监控你必须熟悉数据库里所有执行的连接。

当创建主外键关系（通常用来JOIN的）时，很多人都忘记了主键列上会自动创建索引而外键列上不会自动创建，外键列上必须手动创建。

由于经常忘记，作为监控的一部分，你要分辨出表的所有主外键关系并检查每一个外键列上是否有正确的索引。

除此之外，你也能使用索引调优向导帮助分辨出丢失的连接索引，但我发现向导不总是能为连接表分辨出丢失的索引。说穿了，除非你知道数据库里通常运行连接的类型，否则是很难分辨出索引建在哪些列上能获得帮助。

索引是否足够唯一到有用？

仅仅因为一个表有一个或更多的索引并不意味着SQLServer查询分析器会用到它们。在它们被使用之前，查询优化器不得不考虑它们是否有用。如果表的列上不是至少有95％是唯一的，那么查询优化器最可能不用这个列上的非聚集索引。因此，不要给那些没有95％唯一值的列上添加非聚集索引。例如，一个只有"yes"和"no"的列上不是至少95％都是唯一的，在这列上创建索引基本上永远不会得到使用，我们已经知道这对性能有很大的拖累。

作为监控的一部分，考虑在表上目测数据。换句话说，查看表里的数据，再看看索引的那些列。通常，列是否可选来做索引是非常显而易见的。如果你注意到数据都是男或女，是或否等等，那么这些数据不可选来做索引，并且它们上面的任何索引都将浪费空间并影响性能。?

覆盖索引是否带来了好处？

覆盖索引是复合索引的一种形式，包括查询里SELECT、JOIN、WHERE语句引用的所有的列。因此，索引包含了你要查询的数据，SQLServer不必去表里查找实际的数据了，这样减少了逻辑或物理I/O，从而提升性能。当非覆盖的复合索引不能提升性能时，覆盖索引就派上用场了，大多数情况下，它确实能提升查询的性能。

分辨出覆盖索引在什么地方最有用是很困难的。虽然索引调优向导能有所帮助，但它仍会丢失大量的找到覆盖索引有用的地方的机会。另外，唯一的方法就是小心检查你数据库里运行的所有查询，当然这几乎是不可能的，除非你真的有时间且没有其他更好的事情去做。

在这点上，你监控的目的本身不是找出新的覆盖索引，而是理解它们以便你在你的环境里遇到它们有用的地方时能从中获得好处。?

索引重建的频率是多少？

随着时间的推移，索引会出现碎片，这会引起SQLServer访问它们时降低性能。唯一的解决方法就是定期整理数据库里所有索引的碎片。有几种不同的方法来整理，怎样去整理不会在这儿讨论，这个在SQLServer的帮助文档里有，本网站以后也会介绍。

你监控的目的是找出正在监控的数据库的索引是否在定期的整理碎片。整理碎片的频率从每天每周到每月不等，依赖于修改的频率和数据库的大小。如果数据库每天要进行很多修改，那么碎片整理应该更频繁的执行。如果数据库很大，这意味着碎片整理要花更长的时间，因此由于碎片整理过程占用太多的资源从而影响用户的使用，所以不能太频繁的整理碎片。作为监控的一部分，你要评估碎片产生的频率，找到最佳的频率。

至少，如果索引目前没有重建，而它们又需要重建，作为监控的一部分，你需要确认一些适当的索引重建计划。

索引的填充因子是多少？

和索引重建最相关的是填充因子。当创建一个新索引，或重建一个存在的索引时，你可以指定一个填充因子，它是在索引创建时索引里的数据页被填充的数量。填充因子设置为100意味着每个索引页100％填满，50％意味着每个索引页50％填满。

如果你创建一个填充因子为100的聚集索引（在一个非单调递增的列上），那意味着每当一个记录被插入（或修改）时，页拆分都会发生，因为在现存的页上没有这些数据的空间。很多的页拆分会降低SQLServer的性能。

举个例子：假定你刚刚用缺省的填充因子新创建了一个索引。当SQLServer创建它时，它把索引放在相邻的物理页面上，因为数据能够顺序的读所以这样会有最优的I/O访问。但当表随着INSERT、UPDATE、DELETE增加和改变时，发生了页拆分。当页拆分发生时，SQLServer必须在磁盘的某处分配一个新的页，这些新的页和最初的物理页不是连续的。因此，访问使用的是随机的I/O，而不是有顺序的I/O，这样访问索引页会变得更慢。

那么理想的填充因子是多少呢？它依赖于应用程序对SQLServer表的读和写的比率。首要的原则，按照下面的指导：

低更改的表（读写比率为100：1）：100％的填充因子

高更改的表（写超过读）：50-70％的填充因子

读写各一半的：80-90％的填充因子

在为应用程序找到最优的填充因子前也不得不进行试验。不要假定一个低的填充因子总比高的好。低的填充因子会减少页拆分，它也增加了SQLServer查询期间读的页数量，从而减少性能。太低的填充因子不仅增加I/O开销，也影响缓存。当数据页从磁盘移到缓存中时，整个页（包括空的空间）都移到缓存中。所以填充因子越低，不得不移到SQLServer缓存中的页面就越多，意味着同时为其他重要数据页驻留的空间就少，从而降低性能。

如果你没有指定填充因子，缺省的填充因子时0，意味着100％的填充因子（索引的叶页100％的填满，但索引的中间页有预留的空间）。

作为监控的一部分，你要决定新建索引或重建索引时的填充因子是多少。事实上，除了只读数据库，所有的情况，缺省值0都是不适合的。相反，你想要一个填充因子保留合适的自由空间，按照上面的讨论来做。