36. 解决常见的效能问题
什么是瓶颈
发现问题
一般效能瓶颈
SQL Server组态设定
本章总结
在整本书中，您看到了可以使用的工具和可以修改的参数，来帮助您发现与解决效能问题。例如，前一章学到如何利用 SQL 陈述式和预存程序来辨识问题，以及如何调整那些陈述式和程序达成最佳化效能。本章则帮助您简单地找到需要的信息，以便解决各种效能问题，并检视一些其它章节讨论过跟效能有关的主题，提供检验效能问题的参考方案，与说明一些与效能监视和系统调整有关的附加信息。
我们将从复习 瓶颈 （bottleneck）的定义开始。然后看看如何使用 Windows 2000 系统监视器（在 Windows NT 中称为效能监视器），以及如何使用 Microsoft SQL Server Enterprise Manager 来确定效能问题是否存在。接着是如何解决一般效能问题，该问题发生在好几种层级中，包括应用程序、SQL Server、操作系统和硬件等层级。用在系统容量计划的主要规则已经在 第 6 章 说明过，但本章还会再复习一次，因为它们可以用来分析现有的系统，以确定是否需要附加硬件来改善效能。最后将复习前几章提过的好几种 SQL Server 设定参数，用来调整系统效能的改变方式。
在本章的结尾，您将可以识别效能瓶颈，并确定导致瓶颈的原因。虽然不是每次都能解决问题，但是只要肯花时间以及资源来处理这些问题，大部分问题是可以解决的。
什么是瓶颈
瓶颈 一词通常用来讨论软件和硬件效能问题，也就是系统中一个组件或一组组件可能限制系统效能的状况。例如，缺少足够容量的 I/O 子系统会导致一个严重的瓶颈－它会使整个系统变慢。（在本章的 ＜I/O子系统＞ 一节中会详细的讨论这种情况。）
几乎所有系统中活动的组件都可能导致瓶颈。瓶颈可以由一个组件所引起，例如一个磁盘，或由一组组件所引起，例如I/O子系统，或由不同组件的组合所引起。例如，您可能首先检测到一个 I/O 子系统瓶颈。解决这个问题的方法是透过增加更多的磁盘来支持发生问题系统中的 I/O 数量（硬件解决方案），或透过最佳化低效率查询来减少 I/O 的发生（软件解决方案），或两者都使用。当 I/O 问题被解决时，可能会发现现在产生 CPU 瓶颈，并需要增加 CPU 的速度或 CPU 的数量。
发现问题
要确定系统是否有问题存在，首先要观察系统效能。例如，使用者是否在执行数据库查询或修改时，受到比预期更慢的反应时间。这是效能问题或瓶颈的一般状况。也许您会注意到，当执行某种查询时，系统上所有其它的操作会执行得比平常慢。因此将着重于造成问题的最佳化查询，或者在较少使用者存取系统时，试着同时执行查询来找出原因。
另一个确定是否有问题存在的途径是定期的测试和监控系统。可以使用的工具包括 Windows 2000 系统监视器和 SQL Server Enterprise Manager。本节会学习如何使用这两种工具检查系统的状况，也将学习用在监视 SQL Server 过程的 sp_wh 0-7356-1266-8预存程序。
________________________________________
说明
如果需要使用 SQL Profiler和SQL Query Analyzer 来侦测和 SQL 陈述式有关的效能问题，请参阅 第 35 章 。
________________________________________
系统监视器
Windows2000 系统监视器不仅支持 Windows2000 计数器，也支持 SQL Server计数器。这些计数器监控系统功能随时间的变化以确定系统中的状态，例如 CPU 利用的百分率或 SQL Server 快取命中比率。（本章也提供关于特定效能计数器的信息。）您可以随时观察监视器，或将数据记录到档案中，以后再检视。
要使用系统监视器监视系统，请依下列步骤进行：
1. 按 开始 / 程序集 / 系统管理工具 / 效能 ，进入系统监视器窗口。
2. 指定您是否以图表格式、报表格式或记录数据格式来检视计数器数据，或是在工具列上点选合适的按钮，检视之前存在数据记录文件的数据。图36-1显示在 系统监视器 中的图表检视。如果选择检视记录文件，对话框会显示在您开启的档案上。
图36-1 Windows 2000 效能监视器
3. 要在 效能 窗口中加入一个检视的计数器，请在工具列中按一下加号按钮，会出现 新增计数器 对话框，如图36-2所示。点选 本机计算机计数器 ，检视本机系统的计数器，或者点选 从下列计算机选择计数器 ，并从下拉式清单上选择一个远程计算机名称，检视该计算机的计数器。
图36-2 「新增计数器」对话框
4. 从 效能对象 下拉式清单上选择 System 对象。这些对象代表系统组件。您选择的对象计数器将显示在对话框左边角落的清单上。要观察所有对象的计数器，按一下 所有计数器 。如果想要监视某些特定的计数器，点选 从清单选取计数器 ，然后选择清单上的计数器。有些计数器不只一种，这些例子都将显示在对话框右边角落的清单上。要选择一个或多个特定例子来检视的话，就点选 从清单选取例项 ，或者点选 所有例项 来检视全部的例子。
5. 按一下 新增 ，所选择的计数器就会新增到 效能 窗口。（如果选择一个计数器的多种例项，它们会全部新增进去。）您可以继续新增计数器。准备要回到 效能 窗口时按一下 关闭 ，您就能看到计数器提供的效能数据。图36-3显示三种计数器回传的结果：Context Switches/Sec、Total Server Memory (KB)和% Processor Time。
图36-3 实时系统监视器
要将效能数据存入记录文件中，请跟着下面程序：
1. 在 效能 窗口左边窗格中展开 效能记录文件及警示 。在 计数器记录文件 上按一下右键，并从快捷菜单中选择 新增记录文件设定 。出现 新记录文件设定 对话框后，在这里输入您的记录文件设定名称，如图36-4所示，最后按一下 确定 。
图36-4 「新记录文件设定」对话框
2. 出现新的记录档案后窗口就被命名了。在 一般 签页中，按一下 新增 。 新增计数器 对话框出现，选择您要记录的计数器，就像之前步骤3~5所描述的一样。 一般 签页也让您可以变更记录文件名称，并且指定效能数据多久检查一次。
3. 按一下 记录文件 页签，设定记录档案的附加属性。图36-5显示这些设定，该档案将在它的名称之后加入日期，并且设定成二位档案型态。
图36-5 新的记录档案窗口中的「记录文件」签页
4. 按一下 排程 页签。这个签页中，您为记录文件指定一个开始和结束时间。您也可以选择开启一个新的记录文件，或者当目前记录文件关闭时执行指令。
5. 按一下 确定 关闭窗口，并将记录档案信息存盘。如果您选择立即开启记录，当您点选 确定 时，记录就会开始启动。这个记录档案的项目将显示在 效能 窗口，如图36-6所示。
您可以在有规律的基础上使用效能监视器来检查系统的状态。每日或每星期的监控是个好主意，这样您可以了解您的系统，以便于当异常的现象发生时，您能够识别这个现象的原因。将效能数据保存到记录档案以备稍后的检查，这也是建议的方法－记录档案中的数据总会派上用场，您可以比较系统更改前后的效能数据，确定这个更改是否是好的。您也可以使用这些记录文件来比较使用者和系统活动是如何从一种状态变更到另一种状态的。例如，您可能注意到，在本月的最近几天，使用者活动比其它时间要多。这样您将确保您的系统能够处理在这些尖峰值时的负载。
图36-6 效能 窗口，显示一个新计数器记录的项目
Enterprise Manager
除了自动的日常管理功能以外，Enterprise Manager 可以用来协助监控 SQL Server 过程与锁定。（请参阅 第 19 章 关于锁定的信息。）例如，您可以收集关于程序锁定和对象锁定的数据－在这些情况下，一个对象可以是数据表、数据库或暂存数据表。要检视这些信息，请按照下列步骤操作。
1. 在 Enterprise Manager 窗口展开 SQL Server/SQL Server群组/管理资料夹/目前活动 ，如图36-7所示。 目前活动 数据夹包含三个数据夹： 处理器信息 、 锁定/处理序识别码 和 锁定/对象 。
图36-7 展开 Enterprise Manager 中的「目前活动」资料夹
2. 按一下 处理器信息 来检视当前连接到 SQL Server 的使用者名称和他们的程序 ID；这些使用者程序状态（执行、睡眠、或是背景）；他们连接了哪些数据库；他们执行了哪些指令和应用程序；等待时间（使用者花在等待可用资源的时间）；CPU、实体 I/O、和每个程序的内存使用量；以及每个程序的封锁状态（程序封锁他人或是被他人封锁）。要看这些信息，必须将窗口滚动条往右移。图36-8显示了这些信息。
3. 按一下 锁定/处理序识别码 ，在右侧窗格中检视系统程序识别码（SPID）的清单，如图36-9所示。在右侧窗格的一个 SPID 上按二下，以显示 处理序详细信息 对话框，如图36-10所示。这个对话框显示了程序执行的最后一个 T-SQL 指令。
图36-8 Enterprise Manager 中的「处理器信息」
图36-9 显示在「锁定/处理序识别码」窗格的 SPID
