SQL 问题与解答：轻松掌握索引
管理 SQL 索引可能是件比较棘手的工作，不过，只要您能密切关注它们并确保将问题消灭在萌芽状态，任何烦扰就都会化为无形了。
Paul S. Randal
意外的 DBA
**问：**我在公司中担任“非正式 DBA”。我们越来越多地使用 SQL Server，整个公司涌现出了大量 SQL Server 实例，所以我们深知需要一名真正的 DBA 帮助解决问题。虽然我们聘用了一些人，但我需要搞清楚是什么原因使得某些实例运行速度变慢。对于我应从哪里开始检查，您有没有一般性建议？
**答：**问得好。如果某一 SQL Server 实例执行效果不理想，但又不知从哪里开始查找原因，可能令人非常沮丧。有各种各样的因素都可能导致性能下降，因此我总是发现最简单的方法就是问 SQL Server 自己。
SQL Server 可以跟踪两组信息：I/O 统计数据和等待统计数据。通过这些信息，您应当可以对哪里出现了问题产生良好的认识。
如今大多数 SQL Server 安装都受到 I/O 限制，这意味着其性能受制于某些与读取和写入数据有关的因素。速度变慢可能是因为 I/O 子系统速度慢、将 SAN 连接到服务器的网络速度慢、服务器上用于强制 SQL Server 换入或换出页面的内存不足、索引策略较差，或者一系列其他因素。
您可使用动态管理视图 (DMV) sys.dm_io_virtual_file_stats 查看 SQL Server 对于 I/O 卷、所有 I/O 到数据和日志文件的阻塞和延迟了解哪些信息。您可能会发现 I/O 子系统本身并非热点，但 I/O 可能仍是问题所在。I/O 子系统可能不足以应对 I/O 负载。
由此引出了这一难题的另一面：等待统计数据。SQL Server 会跟踪每次执行线程必须等待可用资源的情况，以及线程必须等待多长时间。您还可以算出在通知资源可供使用之后、CPU 开始执行计算之前，线程必须等待多长时间。通过汇总这些数据，很容易看出导致 SQL Server 等待的首要原因。这会为您提供从哪里开始查找原因的线索。
这里介绍了此种方法的大致过程。有关更深入的讨论，包括您可使用的脚本，请阅读我的博客文章“Wait statistics”（等待统计数据）。该文章还包含超过 1,800 个 SQL Server 实例的读者调查结果，以及流行的等待类型及其说明。底线：不要浪费时间在 SQL Server 中翻找，直到最后才向 SQL Server 询问它对问题的意见。
缺失索引分析
**问：**我刚刚发现缺失索引 DMV。现在它们告诉我一个 SQL Server 实例上有数百个缺失索引。我是不是应该创建所有索引，这会导致问题吗？
**答：**如果没有首先执行某些分析，请不要立即创建所有索引。通过 SQL Server 2005 和更高版本中的查询处理器，您可以确定索引在何种情况下会使查询（或者批处理或存储过程）计划受益。它会在编译查询计划的同时进行此项操作。
每次确定存在缺失索引时，它都会注明。对于查询计划（其中包含在编译查询计划时就已存在的索引），它还会对每个缺失索引可能已被使用以及预计实施的次数进行计数。
您可使用三种 DMV（sys.dm_db_missing_index_groups、sys.dm_db_missing_index_group_stats 和 sys.dm_db_missing_index_details）访问所有此类信息。还有一种 DMV 可以告诉您表中的哪些列是缺失索引 (sys.dm_db_missing_index_columns)。前三种 DMV 更为常见。要获取这些数据，最简单的方法是一起查询这些 DMV。Bart Duncan 得到广泛使用的文章“Are you using SQL’s Missing Index DMVs?”（您是否正在使用 SQL 的缺失索引 DMV？）也可提供帮助。
这些信息很有价值，但您应该对其持保留态度。首先，缺失索引 DMV 中可能存在错误。它可能会告诉您某一索引缺失，而实际上该索引存在。这一错误将在下一版本 SQL Server 中修正。有关这一问题的详细信息，请阅读我的博客文章“Missing index DMVs bug that could cost your sanity”（缺失索引 DMV 错误可能耗费您的心智）。
第二，在确定查询处理器中缺失索引时，所用的机制仅考虑某一索引是否对正在编译的查询有用，它并未考虑插入/更新/删除操作对保留该索引可能产生的性能影响。如果表格的更改数量超出读取数量的程度达到一定比例，这个问题就会变得很严重。这种机制也没有考虑所创建索引的尺寸。这只能由您权衡。
最后，它会查找绝对最佳索引以帮助编译查询计划。例如，有一个包含 30 列和一个群集索引的表，某一查询请求该表的 25 列。缺失索引确定机制将会建议创建一个非集群索引，以涵盖该 25 列查询。在大多数情况下，这并不合理。
可以使用 Duncan 的脚本查看聚合的缺失索引输出。然后查看前 10 个或前 20 个索引，并进行某些分析，以确定是否确实值得创建这些索引。大多数情况下，您会发现有些索引不值得创建，因此执行这种分析始终是值得的。
难道我们就不能和平共处吗？
**问：**我是公司 DBA 团队中的一员，常与各种应用程序开发团队打交道。团队之间常常抱有敌意。这不利于工作环境。对于理顺团队之间的关系，您是否有什么建议？
**答：**由于敌意、不信任和怨恨而使工作环境变得令人不愉快，这是一个常见问题。上述问题不但无助于工作效率，还会使公司蒙受损失。幸运的是，我们有办法解决这个问题。但说起来容易，做起来难：
- 团队之间需要相互培训。每个团队都需要了解其他团队的动机，并且要知道，他们的想法就是他们的职责界限。对于每个团队认为其他团队应该完成的工作，您会感到惊讶。
- 每个团队都需要了解其他团队的难处。您可通过匿名方式这样做，而不会使事情个人化。
- 随后，每个团队都需要培训其他团队，使之了解其他团队的工作如何影响该团队。例如，假设开发团队编写了某些代码，仅使用某一小型数据集进行了测试，就将其投入了生产，最终遭遇惨重的失败。如果开发团队期望 DBA 团队对代码进行故障排除和修复，那么这明显是个残缺的流程。
要想激励双方努力制定解决方案以使工作场所的环境重新变得和谐高效，唯一的方法就是承认并了解问题。
棘手的索引
**问：**我是一名 SharePoint 管理员，并且我对 SQL Server 也了解颇多。承载我们的 SharePoint 数据库的 SQL Server 2008 包含大量索引碎片。这影响了 SharePoint 的性能。我知道不能更改索引，但除了必须重建索引之外，我是否能够采取什么其他措施？
**答：**不断重建索引会在 I/O 和 CPU 资源、事务处理日志的生成方面为 SQL Server 带来沉重负担，并有可能阻碍其他进程。即使运行 sys.dm_db_index_physical_stats DMV 以确定零碎索引也有可能造成严重的资源流失。
很多索引在 SharePoint 环境中变成碎片的原因在于 SharePoint 数据库架构使用 GUID 群集索引键。我妻子 Kimberly 在其博客文章“GUIDs as PRIMARY KEYs and/or the clustering key”（GUID 作为主键和/或群集键）中对此问题进行了讨论。
如果某一索引包含本质上的随机键，就会随机发生索引插入，进而导致出现一种称为页面分割的过程。页面分割会造成碎片，这种过程会耗费大量的资源（请参见我的博客文章“How expensive are page splits in terms of transaction log?”（页面分割在事务处理日志方面的代价有多大？））。当某一页面完全充满，但需要该页面提供空间时（例如，索引中包含必须存储在该页面上的随机键值，并且发生插入操作），就会发生页面分割。系统将分配新页面，并将大约一半的记录从已满的页面迁移到新页面，从而产生可用空间。基本过程就是如此。
您不能更改 SharePoint 数据库中的索引，因为那会打破支持协议。但您可以更改其默认填充系数。在创建或重建索引时，您可指示 SQL Server 在索引页面中留出一定数量的可用空间用于随机插入。这意味着更有可能的情况是索引页面本身已包含一定空间用于新记录，而无需进行代价高昂的页面分割。将填充系数设定为 80 表示在重建索引时将填充 80% 的容量，保留 20% 的可用空间。
随后，问题就变成了“最佳填充系数是多少？”，遗憾的是，目前还没有令人满意的答案。对于数据保持不变并且没有在线事务处理 (OLTP) 插入活动的数据仓库，最佳填充系数通常是 SQL Server 的默认值 100（意味着没有可用空间）。
对于 OLTP 环境，答案取决于碎片出现的速度有多快，以及您为了消除碎片而重建索引的频率。建议先从 70（30% 可用空间）开始，然后监视碎片情况，以查看是否需要提高或降低填充系数或者索引维护的频率。
.jpg)
Paul S. Randal是 SQLskills.com 的常务董事、Microsoft 区域总监和 SQL Server MVP。从 1999 年到 2007 年，他一直在 Microsoft 的 SQL Server 存储引擎团队工作。他曾编写过 DBCC CHECKDB/repair for SQL Server 2005，并在 SQL Server 2008 的开发过程中负责核心存储引擎部分的工作。Randal 是灾难恢复、高可用性和数据库维护方面的专家，经常在全球出席一些会议。您可以访问他的博客 SQLskills.com/blogs/paul，也可以通过 Twitter (Twitter.com/PaulRandal) 了解他。