《MS SQL Server 2000管理员手册》系列——36. 解决常见的效能问题

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

　
 
图36-10　「处理序详细信息」对话框
4. 展开 锁定/处理序识别码 数据夹，以检视当前锁定的 SPID，如图36-11所示。
　
 
图36-11　展开的「锁定/处理序识别码」数据夹
5. 按一下左侧窗格中的一个 SPID 以检视该程序特定的锁定信息，如图36-11所示。这些信息包括锁定的类型、锁定的模式、锁定的状态和锁定的拥有者。锁定的类型包括下列之一：
o  RID 数据列锁定
　
o  KEY 索引中的数据列锁定
　
o  PAG 数据或索引页锁定
　
o  EXT Extent锁定
　
o  TAB 数据表锁定，包含该数据表的所有数据和索引页
　
o  DB 数据库锁定
　
锁定模式可以是下列之一：
o  S 共享锁定
　
o  X 独占锁定
　
o  U 更新锁定
　
o  BU 大量更新锁定
　
o  IS 意图（intent）共享
　
o  IX 意图独占
　
o  SIX 具有意图独占的共享
　
o  Sch-S 编译查询的结构描述（schema）锁定
　
o  Sch-M DDL 操作的结构描述锁定
　
锁定的状态可以是下列之一：
o  GRANT 指锁定将被授予该程序
　
o  WAIT 表示该程序由其它程序封锁，并等待一个锁定
　
o  CNVT 表示锁定被转换成另一种锁定类型
　
6. 展开 锁定/对象 数据夹，检视具有锁定的对象的清单，如图36-12所示。可以被锁定的对象有数据表、暂存数据表、数据库等等。
　
 
图36-12　展开的「锁定/对象」数据夹
7. 按一下被锁定的数据库或数据表名称，在右侧窗格中检视它的锁定信息，如图36-13所示。这些信息和在 锁定/处理序识别码 数据夹中点选 SPID 后出现的内容相同，只是观看的角度不一样而已。
　
 
图36-13　检视对象的锁定信息
sp_who 预存程序
　
您也可以在 Query Analyzer 或 OSQL 提示中执行下面指令，来检视程序的信息：
sp_who active
GO
在 Query Analyzer 中执行这个指令的结果如图36-14。如果一个程序被锁定了，blk字段将显示正在锁定程序的SPID。
当使用者抱怨交易太慢时，您可以执行这个指令来找出问题。很多时候您会发现大部份被封锁的程序是被另一个程序封锁的，一旦找出产生问题的程序所在，就能确定封锁这么久的原因。

　
 
图36-14　执行 sp_who 的结果
您应该经常监控锁定，以确定是否有些程序占有一个锁定的时间太长，以及是否程序经常被其它程序封锁（它们有 WAIT 状态）。但是通常使用者的抱怨多半由于封锁问题引起的响应时间太慢。如果封锁发生得太频繁或太长时间，您可能要确定哪个程序占有独占锁定或数据表锁定。这些锁定可能是导致封锁的原因，并监控程序执行的 SQL 陈述式。然后如果可能试着最佳化这些陈述式，使它们更快的释放锁定或避免占有数据表锁定。一般而言，一个程序用很多时间等待获得一个锁定时，该程序会使用更多的时间来完成。因此，减少锁定竞争可以提高交易响应时间。
________________________________________
相关信息
关于锁定的更多信息，请参阅< 在线丛书 >并索引 显示锁定 ，并选择在 找到的主题 对话框中的 显示锁定信息 。
________________________________________
一般效能瓶颈
　
您已经了解如何使用效能监视工具，如系统监视器、Enterprise Manager、Query Analyzer 和 Profiler，而您也准备要对付效能瓶颈了。在本节中，我们将看到一些很普通的效能瓶颈和不同的解决方案。这些瓶颈大部分是相互关联的，且一个瓶颈可能伪装成另一种瓶颈的形式。您必须寻找硬件和软件导致的瓶颈－很多效能问题发生于瓶颈的组合。可能导致瓶颈的硬件包括 CPU、内存和 I/O 子系统；可以导致瓶颈的软件包括 SQL Server 应用程序和 SQL 陈述式。下面章节我们将更详细地检验每种问题。
CPU
　
一个普通的效能问题就是缺少马力。系统的处理能力取决于系统中 CPU 的数量、类型和速度。如果您没有足够的 CPU 能力，您可能无法快速的处理交易以令使用者满意。要使用系统监视器来确定 CPU 利用的百分率，请检查 Processor 对象中的 %Processor Time 计数器（选择多处理器系统中的所有 CPU）。如果您的 CPU 长时间执行在 75% 或更高的情况下（75% 是根据 第 6 章 设定的规则所要求的最大值），您更可能遇到 CPU 瓶颈。如果您看到了低于 60% 的稳定 CPU 使用率，您仍然可以透过在系统中增加更快的 CPU 或更多的 CPU 中获益。
在决定升级您的 CPU 之前，确定监控了系统的其它特性。例如，如果您的 SQL陈述式效率很低，您可能执行了很多非必要的程序，而最佳化这些陈述式就可以帮助您降低 CPU 利用。或者假设 SQL Server 数据快取的快取命中比率低于 90%。您可能需要增加内存到数据快取中（也就是增加 max server memory 参数或增加系统的物理内存），这允许更多的数据存入快取和更少的实体磁盘 I/O 操作，导致了较低的 CPU 使用率。有时只增加处理器，您就可以获得更好的系统效能，特别是您从单处理器系统开始。然而，并不是所有应用程序都能适应多处理器系统的规模。SQL Server 本身的延展性很好，但并不是所有执行的 SQL 陈述式都需要延展性。一个程序一次只能在一个 CPU 上执行，而且对于单个 SQL 陈述式单个 CPU 已经足够了。为了效能的延展，您的 SQL Server 必须同时执行很多 SQL 陈述式－多个陈述式可以同时在不同 CPU 上执行，因此多个 CPU 能同时处理这些陈述式。
一般而言，您可以透过使用更多和更快的 CPU 来提高效能。然而，在某些情况下，在增加更快的 CPU 之同时，您必须扩大内存和 I/O 容量，否则您可能只将这个瓶颈转移到另一个位置，甚至可能是新的 CPU。例如，如果您原来有一个 CPU 瓶颈，而您透过增加一个 CPU 解决了这个问题，但您可能会发现您的系统执行了更多的工作，因此导致了更多的磁盘 I/O，然后您会发现一个 I/O 瓶颈的发生。另外，确保检查您决定增加的 CPU 的快取大小。CPU 的快取越大，效能将越好，特别是在多处理器系统中。
简单地说，如果您决定您需要更多的处理能力，您可以增加 CPU 或用更快的代替现有 CPU。例如，如果您的系统有两个 CPU，但是可以扩展为四个，那么可以增加两个同类型的 CPU，或替换四个全新更快的 CPU。如果您的系统已经具有了最大数量的 CPU，而您又需要更多的处理能力，那么就用更快的 CPU 来替换它们。例如，让我们假设您有四个 200 MHz 的 CPU。您可以用更快的 CPU 来替换它们，例如 500 MHz。更快的 CPU 将执行更快的完成程序。
内存
　
内存是 SQL Server 效能最关键性的组件之一。由于内存和 I/O 子系统的关连性，因此它是非常重要的。例如，在 I/O 密集型系统中（执行大量 I/O 的系统），可以让 SQL Server 用来储存数据的内存越多，所必须执行的实体 I/O 越少。这是因为数据可以在数据快取中找到，而不是在磁盘中。SQL Server 具有一个复杂的快取系统，透过尽可能从内存中存取数据来提高系统效能，而不存取磁盘，因为存取磁盘将提供较低的效能。您可以从内存中存取的数据越多，您的系统效能越好，这是因为执行的实体 I/O 越少。内存存取要比实体磁盘存取快得多。
在一些情况下，内存数量的不足可能导致明显的磁盘瓶颈。这是因为系统无法有效地进行快取将导致更多的实体磁盘 I/O 操作。要看 SQL Server 正在使用多少系统内存，就用系统监视器来检查 SQL Server：Memory Manager 物件中的 Total Server Memory (KB)计数器。如果 SQL Server 没有用到如您预期的内存大小，也许就要调整内存的参数设定（如本章稍后 ＜SQL Server组态设定＞ 一节所述。）。
要确定您是否有足够的快取数量，请使用效能监视器来检查 SQL Server：Buffer Manager 物件中的 Buffer Cache Hit Ratio 计数器。通常的规则是您需要有 90% 或更高的快取命中比率。如果这个命中比率低于 90%，请增加更多的内存。注意，在一些情况下，您的系统无法达到 90% 的快取是由于应用程序的特性。这可能是因为相同的数据分页很少被重新使用，而系统又频繁地刷新快取中旧的数据分页以存入新的资料分页。
________________________________________
说明
SQL Server 2000 根据系统可用内存和内存参数设定，动态地为缓冲快取分配内存。一些外部资源（例如打印程序和其它的应用程序等）可能导致 SQL Server 释放大部分的内存来为其它程序使用。仔细监控系统内存，如果可能的话，请将 SQL Server 分隔在它自己的系统中。关于使用内存参数的详细说明，请参阅 第 30 章 以及本章后面的 < SQL Server组态设定 > 一节。
________________________________________
I/O 子系统
　
在 I/O 子系统中的瓶颈是数据库系统中最容易发生的硬件问题。它仅次于撰写拙劣的 SQL 陈述式，拙劣的 SQL 陈述式是最大的效能问题。幸运的是，I/O 子系统也是最容易解决的效能问题之一。在很多情况下，增加磁盘可以完全消除这个效能瓶颈。
I/O 子系统问题来自于磁盘只能以特定速率工作的事实。例如，一个磁盘可能具有每秒85次 I/O 的处理能力。如果磁盘负荷过多，那么这些磁盘的 I/O 会产生队列，而 SQL Server 将遇到较长的 I/O 等待时间。这些较长的 I/O 等待时间可能导致过长时间的锁定占有，或执行绪在等待资源时处于空闲状态。最终结果将是整个系统的效能受损，进而导致愤怒的使用者抱怨他们的交易使用太长的时间。
在大多数情况下，I/O 子系统效能问题的发生是因为 I/O 子系统没有正确的规划它所执行的负载。关于规划的主题在 第 5 章 和 第 6 章 中已详细讨论，但让我们在这里简单的回顾一下。
大小的规划是必须的，因为单一磁盘每秒可以执行 I/O 次数受到限制。当交易记录文件执行多数循序性 I/O 时，就会像交易记录文件一样，磁盘可能达到每秒 150 次 I/O 而不会负载过量。另一方面，当数据文件执行随机性 I/O 时，就像数据文件一样，相同的磁盘可能只有每秒 85 次 I/O。如果系统需要执行更高的 I/O，那么等待时间将变得很长。
要确定是否过度使用磁盘驱动器，请使用效能监视器对象 PhysicalDisk 和 LogicalDisk监控计数器（稍后将在本节中讨论）。计数器收集关于实体和逻辑磁盘 I/O 行为的数据，例如每秒磁盘中发生的读取和写入。您必须使用 Windows NT/2000 指令 DISKPERF 启动磁盘效能计数器。这些计数器在安装操作系统时可用，但您应该知道如何启动与关闭计数器。计数器在收集数据时会占用系统资源，如 CPU 的时间，因此您应该只在要监视系统 I/O 时使用它们。您可使用 Windows NT/2000 指令 DISKPERF 来启动或关闭计数器。
要知道 DISKPERF 是否启动，请在命令提示字符下键入下列指令：
diskperf
如果 DISKPERF 已经启动，您将看到这个讯息："本系统的实体磁盘效能计数器已设为启动"。如果 DISKPERF 没有开启，您将看到这个讯息："本系统的逻辑和实体磁盘效能计数器现在设定为不启动。"
要启动 DISKPERF，请在命令提示字符下键入下列指令：
diskperf -Y
要停用 DISKPERF，请执行该指令：
diskperf -N
在 DISKPERF 生效前，您必须重新启动计算机。键入下面指令来看更多的DISKPERF 选项：
diskperf ?
有特殊重要性的计数器是 Disk Writes/Sec、Disk Reads/Sec、Avg. Disk Queue Length、Avg. Disk Sec/Write和Avg. Disk Sec/Read。这些计数器可以帮助您确定磁盘子系统是否过度使用。PhysicalDisk 和 LogicalDisk 对象都提供这些计数器。如果要详细检视这些计数器提供的信息，当增加计数器时，请在 新增计数器 对话框中按一下 说明 。
我们来看一个使用计数器的例子。假设您的系统有一个 I/O 瓶颈，您将检查PhysicalDisk 对象中的 Avg. Disk Sec/Transfer、Avg. Disk Sec/Read和Avg. Disk Sec/Write 计数器，因为它们监视磁盘需要执行读写的时间量，过长的等待时间表示您过度使用磁盘驱动器或磁盘阵列。一般的规则是这些计数器的正常读数在千分之 1 到 15 秒（0.001到0.015）之间，但是在尖峰时期可能达到千分之 20 秒（0.020）。如果您观察到高于千分之 20 秒的数值，您可能会遇到一个 I/O 子系统效能问题。
同时也检查 Disk Writes/Sec 和 Disk Reads/Sec。让我们假定这些值计数器显示每个磁盘每秒 20 次写入和 20 次读取，总共 40 次 I/O，且容量为每秒 85 次 I/O。同时如果您的磁盘等待时间较长的话，磁盘驱动器就可能故障了。另一方面，如果磁盘执行每秒 100 次 I/O，且等待时间大约千分之 20 秒或更多，则您需要增加磁盘来提高效能。
使用 RAID 数组时，如果您想确定您的系统正在执行的 I/O 有多少的话，就将在效能监视器中看到的每秒 I/O 次数除以数组中磁盘的总数量和 RAID 资源占用的因子。下表列出了使用 RAID 技术时产生读写的实体 I/O 次数。
表36-1　每个RAID层级读写执行的实体I/O次数
RAID层级  读取  写入
0 1 1
1或10 1 2
5 1 4
一般情况下，更正 I/O 子系统瓶颈的最佳途径是增加更多的磁盘。但是请记得考虑 I/O 瓶颈的其它可能原因，例如较低的快取命中速率以及执行交易的 I/O 太多。（在大多数情况下，快取命中速率低于 90% 就太低了。）如果您发现了一个 I/O 瓶颈，请回顾 第 6 章 中的说明，来确定您系统需要的磁盘数量。
故障组件
　
有时您的系统可能遇到由故障组件引起的效能问题。如果故障组件没有完全失效而仅仅是效能降低，那这可能是一个很难排除的问题，因为问题的多样性和复杂性以及它的解决方案已经超出了本书的范围。但是这里有几个辨识故障组件问题的诀窍：
•  比较磁盘和数组 当在效能监视器中检视统计资料时，比较相似的组件。如果您发现两个磁盘执行 I/O 的速率相同，却显示不同的等待时间，那么较慢的磁盘可能遇到了问题。
　
•  监控指示灯 网络集线器通常有碰撞指示灯。如果您注意到某个网络区段出现了异常的高碰撞，这时您可能有故障组件，也许是网络卡或是网络缆线。
　
•  了解您的系统 您花在系统上的时间越多，您越能了解系统的特性。当系统不正常运作时您就会发现。
　
•  使用效能监视器 这是一个监控系统行为的好方法。
　
•  阅读记录文件 养成常常检查 SQL Server 和 Windows2000 事件检视（Event Viewer）系统和应用程序记录的习惯。每天回顾这些记录文件可以在问题失控前发现问题。
　
应用程序
　
造成效能问题的另一个系统组件是 SQL Server 应用程序。这些问题可能发生在应用程序代码或在应用程序执行时的 SQL 陈述式中。本节提供一些提示和准则，用来解决和 SQL Server 应用程序有关的效能问题。
最佳化执行计划
　
正如我们在 第 35 章 所看到的，选择一个最佳的执行计划和数据存取方法对于查询是很重要的。不幸的是，并没有一定的公式来确定最佳的计划。SQL Server 自动选择查询最佳化器的计算为最佳的执行计划。随着您熟悉联结操作的不同类型，您可能可以透过分析以确定最佳执行计划。通常您需要试过各种不同的计划后，才能找到最佳方法。
使用索引
　
正如我们在 第 17 章 和 第 35 章 所看到的，正确索引的使用对于好的效能是十分重要的。使用索引来找出所需的数据，这只需要 10 到 20 个 I/O 操作，而透过数据表扫描找出所需的数据，则可能需要成千上万的 I/O 操作。不过必须谨慎地使用索引。记住，在使用 INSERT、UPDATE 或 DELETE 陈述式修改数据表的各种数据时，索引将自动更新，显示更改后的数据，因此会产生比平常更多的 I/O 操作。注意不要建立太多索引，否则用来维护这些索引的资源占用可能会影响数据修改的效能。
使用预存程序
　
正如我们在 第 21 章 中所看到的，预存程序被用来在服务器上执行预先包装和预先编译的SQL陈述式。从应用程序呼叫预存程序对效能有所帮助，因为改善了服务器上 SQL 陈述式的可重复使用性，也减少了网络的流量。由于预存程序执行在服务器上，可以减少客户端和服务器之间传送的数据数量，您可以在预存程序中将程序程序化并将数据筛选处理，而不用在应用程序中执行这些动作。
SQL Server 组态设定
　
SQL Server 2000 使用虚拟方式自行调整组态，但是有些调整参数仍然可以用来变更您的系统操作和执行的方式。在本节中，您将学到如何设定这些选项，以及它们如何影响到您系统的操作。在大多数情况下，是不需要更改这些参数的，但是了解它们是什么以及它们做什么使您有机会决定是否更改它们。您可以使用 Enterprise Manager 或 sp_configure 来设定。
要使用 Enterprise Manager，请在您要设定的服务器名称上按右键并从快捷菜单中选择 内容 ，显示 SQL Server属性 窗口。这个窗口包含9个页签，每个页签都有您可以设定的选项。下面将说明这些页签以及它们的选项。
当使用 sp_configure 设定这些选项时，某些选项是设定为进阶（advanced）的。（下面几节将说明哪些是进阶选项。）您必须将 show advanced options 选项设定为1（启用），这样才可以使用 sp_configure 来更改一个进阶选项。预设情况下该选项设定为0（禁用）。（当使用 Enterprise Manger 来设定进阶选项时，您不需要担心这个选项。）要设定 show advance options，请使用下面的陈述式：
sp_configure "show advanced options", 1
GO
通常，要使用 sp_configure 设定某个选项，请使用下面的语法：
sp configure "option name", value
affinity mask 选项
　
affinity mask 选项是用来指定在多处理器环境中，SQL Server 执行绪可以在哪些CPU 上执行。默认值为0，指定由 Windows 2000 排程算法来决定执行绪关连性。非零值设定了一个 bitmap，用来定义可以执行 SQL Server 的 CPU。十进制数值1（或二进制位屏蔽值00000001）指明只使用CPU 1、2（或00000010）指明只使用CPU 2、3（或00000011）指明使用CPU 1和CPU 2，等等。
这个选项是进阶选项，表示在使用 sp_configure 设定该选项时，必须设定 show advanced options 为1。它也可以用 Enterprise Manager 来设定。要这样做，请选择 SQL Server属性 窗口中的 处理器 页签，在 处理器控制 区域，选择您要 SQL Server 使用的 CPU 旁的复选框。按一下 套用 和 确定 来储存更改。您必须停止并重新启动 SQL Server，使该更改生效。
在指定的 SQL Server 系统上，您必须设定 affinity mask 选项，允许 SQL Server使用所有的 CPU。在没有指定的 SQL Server 系统上（包括需要 CPU 时间的其它处理程序），您可能要试着设定 affinity mask，这样 SQL Server 才会留下一个 CPU 不用而用其余的 CPU。
lightweight pooling 选项
　
lightweight pooling 选项用来设定 SQL Server 使用 lightweight 执行绪或细微模式（fibers）。使用细微模式可以减少内容切换（context switches），允许 SQL Server（而不使用 Windows NT 或 Windows 2000 的排程程序）掌控排程。如果您的应用程序在多处理器系统中执行，且您看到了大量的内容切换，您可能需要将 lightweight pooling 参数设定为1，这将启用 lightweight pooling，然后监控内容切换的数量，来核对它们是否已经减少了。默认值为 0，这样会禁用细微模式。
lightweight pooling选项也是一个进阶选项，在使用sp_configure 设定该选项时，必须将show advanced options设定为1。它也可以用Enterprise Manager来设定。选择 SQL Server属性 窗口中的 处理器 页签，在 处理器控制 区域，选取 使用Windows NT Fibers 复选框来启用该选项，或者清除这个复选框来禁用该选项。按一下 套用 ，按一下 确定 ，然后停止并重新启动SQL Server以使该选项生效。
max server memory选项
　
SQL Server 动态地分配内存。要指定 SQL Server 分配给缓冲集区的最大内存数（单位为MB），您可以设定 max server memory 选项。因为 SQL Server 需要一些时间来释放内存，所以如果您有其它的应用程序定期的需要内存，那么应该设定 max server memory，这样 SQL Server 就会为其它应用程序保留一部分内存。默认值为 2147483647，这表示 SQL Server 请求系统能尽量提供内存，而当其它应用程序需要内存和释放内存时，动态地分配和解除分配内存。建议在 SQL Server 系统中使用这个设定。如果您要更改这个设定，请计算您可以给 SQL Server 的最大内存数量，从全部的物理内存中减去 Windows 2000 需要的内存加上其它非 SQL Server 使用需要的内存。
这是一个进阶选项，在使用 sp_configure 设定该选项时，show advanced options 必须被设定为1。要使用 Enterprise Manager 来设定该选项，在 SQL Server 属性 窗口中选择 内存 页签，并调整 最大值（MB） 滑动杆。接着按一下 动态设定SQL Server的内存 。这个选项的更改将立即生效，不需要停止并重新启动 SQL Server。（如果按一下 使用定量的内存 ，您就能设定内存的固定值。这可以限制 SQL Server 分配内存的量，而在内存分配到固定值后，不再释放内存。）
min server memory 选项
　
min server memory 选项指定分配给 SQL Server 缓冲集区的最小内存数，单位为 MB。在 SQL Server 为其它应用程序保留过多内存的系统中，设定这个参数将很有用。例如，服务器当成数据库服务器的同时，还用作打印和档案服务，在这种环境中，SQL Server 可能释放太多的内存给这些其它的应用程序。这样会减慢使用者的响应时间。
min server memory 的默认值为0，允许 SQL Server 动态的分配和解除分配内存。建议使用这个设定，但是如果您的服务器不只用作 SQL Server，可能就需要更改这些设定。
这是一个进阶选项，在使用 sp_configure 设定该选项时，show advanced options 必须被设定为 1。您可以使用 Enterprise Manager 来设定该选项，请在 SQL Server 属性 窗口中选择 内存 页签，调整 最小值（MB） 滑动杆，并按一下 动态设定SQL Server的内存 。这个选项的更改将立即生效，不需要停止并重新启动 SQL Server。
recovery interval选项
　
recovery interval 选项定义了系统在故障事件中回复的最大时间，单位为分钟。SQL Server 使用这个设定以及内建的算法，决定执行自动检查点的频率，这样回复工作将在指定的分钟数内完成。根据在系统中有多少工作发生，SQL Server 决定两次检查点之间应该有多久的间隔时间。如果有很多工作要执行，检查点执行的频率将比平常多。执行的工作越少，从失效回复的时间越少。另外，回复间隔越长，检查点间允许的间隔时间越长。
透过减少检查点数量来增加回复间隔将提高效能（由于检查点导致大量的磁盘写入，这将使使用者交易时间慢几秒钟），但它也增加了回复 SQL Server 所需要的时间。默认值为 0，指定 SQL Server 确定间隔－大约1分钟的回复时间。增加 recovery interval 选项是有风险的。通常使用 5 到 15 分钟的值，但这完全取决于您在系统失效时是否能冒等待 5 到 15 分钟的风险来等待数据库的回复。一般说来，您可能要增加 recovery interval，以减少每个检查点的频率以及它们大量的写入，因此允许使用者在 I/O 子系统中更自由的执行他们的交易，而不会中断。
这是一个进阶选项，在使用 sp_configure 设定该选项时，show advanced options 必须被设定为 1。要使用 Enterprise Manager 设定这个选项，请在 SQL Server 属性 窗口中选取 数据库设定 页签，并在 复原间隔（分钟） 文字方块中键入一个值。这个选项的更改将立即生效，不需要停止并重新启动 SQL Server。
本章总结
　
在本章中，您学到了成为 DBA 将遇到的一般效能问题。您也看到如何使用系统监视器和 Enterprise Manger 来监控系统，并帮助定位效能瓶颈。另外您还学到了如何检测以及解决一般系统效能问题。
本书让您学习如何管理 SQL Server 2000、何谓管理 SQL Server 2000 和为什么要管理 SQL Server 2000。您现在应该可以有效的管理和调整您的 SQL Server 系统了，并能够顺利有效地执行日常管理任务。我们希望您能够喜欢阅读这本书，就像我们在写作本书时获得乐趣一样。