第23学时优化和调整

第23学时优化和调整
无论数据库和应用程序设计得多好，总是还有改善性能的余地。有多种方法可以采用，
从增强服务器的物理性能，到修改服务器级的选项，还有改动物理数据库。SQL Server为你
提供了多种途径。在SQL Server 7.0中，大量服务器级的选项是自动管理的。这使得数据库管
理员的工作更为简单。
本学时的要点包括：
• SQL Server调整的改进
• 为什么要调整你的服务器
• 建立基准
• SQL Server Performance Monitor
• 使用SQL Server Profiler
• 使用Index Tuning Wi z a r d
23.1 SQL Server调整的改进
前面我已经提到过，微软在系统调整方面做出了很多的改进。如果你曾经用过S Q L
S e r v e r的早期版本或任何一种别的R D B M S来进行调整和优化(亦称为P TO或P T & O )的话，你
应该知道那是一个极其复杂的过程，需要研究成百个变量。举个例子，在SQL Server以前的
版本中，你必须告诉它你将使用多少内存。因为即使服务器有4 G B的内存它也笨得算不出它
可以使用多于3 2 M B的内存。在SQL Server 7.0 中，它将按需要自动分配和释放内存。比如
说，如果你在运行SQL Server的电脑上启动Microsoft Wo r d，它将释放足够的内存给Wo r d使
用。
这些改进对你来说意味着什么呢？首先，它减轻了你的工作量。SQL Server将自动设置
几乎所有的服务器配置选项。即使你可以修改所有这些选项，也应该让它们使用缺省值以便
让SQL Server根据服务器运行时的状况自动调整。下面列举了你可能会根据需要来设置的几
个选项：
• SQL Server内存—有时候，你必须设定供SQL Server使用的最大和最小的内存。
• I / O子系统—通常，在你建立一个SQL Server时，你将会把它安装到一个非常强大、复
杂的服务器上。这类服务器通常在一个称作R A I D的特殊设备中配置硬盘驱动器。这种
设置能使服务器的所有硬盘看起来就像一个硬盘一样，它还能提供冗余使得一个硬盘丢
失后不会丢失所有的数据。它还加快了I / O子系统的读取速度。在你的服务器是按这种
方式设置时，可能想修改一些SQL Server的选项来使计算机在处理文件时能够同时读取
更多的信息。
• Windows NT选项—当SQL Server是在Windows NT服务器上运行的唯一应用程序时，
可以改变一些选项使SQL Server运行得更快更好。这些选项包括设置服务器以使网络吞
吐量达到最大和使服务器任务运行得更快。
23.2 为什么要调整你的服务器
无论你是一个经理、一个数据库管理员或是一个数据库用户，都应该调整SQL Server及
它的性能。当正确地调整了服务器上的资源后，你将发现其性能有所提高。查询及数据修改
速度的加快将显示性能得到了提高。总之，用户将证实这一点。因为用户将最先让你知道服
务器性能是否表现得如他们所期望的那样。
在应用程序正在开发的时候，你可以做的一件大事是开始思考它的性能问题，而不是等
到这个系统已经实现的时候才开始。你将发现许多开发人员和使用人员根本不考虑性能问题，
直到真的出了问题。从用户响应时间变慢到数据库的潜在损坏，这些问题几乎无处不在，而
查找错误和排除故障都是极端困难的。按照用户的观点来看，性能总应该越变越好，而且你
对性能的每次提高都应较明显。
23.3 建立基准
就像其他任何事情一样，你必须找到开始下手的地方。在进行性能调整和优化时，这尤
其重要。在开始调整之前，你应该确定那一些区域运行情况较好，那一些区域你应该特别注
意。这些由确定一个基准来完成。可以利用该基准来确定你的SQL Server 是否运行在最优状
态。当生成一个基准时，你将随着时间变化监视服务器及其性能的统计数字，然后将它们进
行比较。确定完服务器性能基准之后，将接着用该基准来比较当前的性能。如果你发现有较
大的差异，应该研究这些区域。要建立一个基准，你应该例行监视以下方面：
• 服务器的性能。
• 用户活动。
• SQL Server错误日志和Windows NT事件日志。
为得到一个有效的基准，必须在执行各种任务的情况下监视服务器一段时间。当你创建基
准时，服务器的性能应该满足用户的需要，最重要的是要符合他们的期望。你应该记下操作的
峰值和谷值时间、查询响应时间以及数据库的转储和加载。当确定了基准之后，你可以把它和
你在这方面所收集的信息做比较。如果发现在某个方面有大的出入，你就应该对它们进行研究。
建立一个基准涉及到使用性能监视器来记录服务器的性能。你应该监测的信息包括：
• 处理器。
• 内存。
• 物理磁盘。
监视服务器性能
作为D B A，你应该创建一个日程表，并按照它来频繁测试SQL Server以确定其是否按照
你和你的用户们所期望的那样运行。SQL Server提供了一些图形工具和基于文本的方法来监
视服务器。表2 3 - 1列出了用于监视服务器的各种工具及方法。
表23-1 监测服务器的方法
功能描述
SQL Server Performance Monitor 该工具使你能利用预定义的对象和计数器来监视服务器的性能。它还
能同时监视SQL Server和Windows NT。你也可以设定一个极限，以便
到达这个极限时产生报警通知操作员并关闭应用程序。它将信息记录到
一个数据文件中去，供以后分析或在屏幕上以图形方式显示出来
2 2 2 SQL Server 7 24学时教程
下载
(续)
功能描述
Current Activity窗口该工具提供了一个对于登录用户、阻塞的进程以及正在运行的命令的
图形表示。你还可以给用户发送信息或终止进程
SQL Server Profiler Server 这是SQL Server 7.0中增加的一个功能强大的新工具。该工具提供了一
个描述服务器资源和数据库活动的方法。描述信息能够填写进S Q L
S e r v e r数据库的一个表或日志文件中，供外部应用程序进行分析
s p m o n i t o r 该系统存储过程显示了有关SQL Server自最近一次启动以来繁忙程度
的统计。它向你提供了在运行SQL Server Proformance Monitor时可能
获得的同一类信息
s p s p a c e u s e d 该系统存储过程能估计在硬盘上建立一个表或数据库需要多少物理空
间。要注意这个数据并不总是准确的
s p w h o 该系统存储过程用来核查当前用户和状态信息。它所提供的信息与你
在SQL Enterprise Manager中Current Activity窗口里看到的信息是一样
的
s p l o c k 该系统存储过程用来查看当前的锁定信息和阻塞信息。它所提供的信
息与你在SQL Enterprise Manager中Current Activity窗口里看到的信息
是一样的
DBCC 命令Database Consistency Checker简称D B C C，是一套能够检查性能统计、
表与数据库一致性以及完成许多一般性清理功能的指令
其中用来监视服务器的主要工具是SQL Server Performance Monitor。
23.4 SQL Server Performance Monitor
SQL Server Performance Monitor是Windows NT Performance Monitor的一个延伸。它能够
用来跟踪分钟级的关于SQL Server的统计和信息。你可以通过这些数据得到服务器性能的反
馈信息，并可以将它作为图表查看或存储起来以备后用。
23.4.1 监视计数器
Performance Monitor使用计数器从SQL Server中收集信息。计数器是监视中的单独项目。
为了方便使用，计数器按对象分类编组在一起。
当SQL Server Performance Monitor开始工作时，它监视五个默认计数器。这五个计数器
在下面列出。你可以通过设置SQL Server Performance Monitor来跟踪这五个计数器以外的计
数器。
• Cache Hit Ratio—取代从磁盘读数据而从数据缓冲区中读取的数据的百分比。当你发
现这个计数器值较低，即在9 0％范围以内时，通常需要再给服务器增加一些内存。如果
刚启动SQL Server就开始监测这个值时，你会发现它非常低，这是因为数据正从磁盘中
读取过来。
• I / O - Tr a n s a c t i o n / s e c—它表示每秒钟内所完成的事务数量。它将给你一个用户正向服务
器发送数据量的大约数字。
• I/O-Page Reads/sec—每秒钟物理磁盘的读取量。它告诉你SQL Server到磁盘去读取信
息的频繁程度。如果你发现大量的磁盘读取同时Cache Hit Ratio较低，这通常是需要给
服务器添加内存的信号。
• I/O-Single Page Wr i t e / s e c—每秒钟执行单页写的数量。它是SQL Server从内存中取出
第2 3学时优化和调整2 2 3
下载
一页数据并写入硬盘的次数。
• User Connections—在同一时间里从任何地点连接到SQL Serve的用户数。当你试图确
定服务器的容量时它将会派上用场。如果用户数是你预期的一半而服务器已经超负荷工
作了，那么你就应该知道当所有用户都登录时，服务器将无法正常工作。
表2 3 - 2中所概括的1 6个不同对象用来跟踪SQL Server的统计信息。每个对象至少含有一个
计数器。
表23-2 SQL Server计数器
对象名称描述
SQL Server：Access Methods 提供有关搜索和分配数据库对象的信息
SQL Server：Backup Device 提供有关备份设备状态的信息
SQL Server：B u ffer Manager 提供有关SQL Server使用内存缓冲区的信息，包括空余内存数量
SQL Server：Cache Manager 提供有关过程高速缓存的信息
SQL Server：D a t a b a s e s 提供有关用户数据库的信息，包括事务日志的大小
SQL Server：General Statistics 提供服务器范围内的统计信息，例如登录到服务器的用户数
SQL Server：L a t c h e s 提供有关SQL Server正在使用的内部资源的锁存器信息
SQL Server：L o c k s 提供有关SQL Server正在请求的锁的信息，包括超时和死锁
SQL Server：Memory Manager 提供有关SQL Server内存使用的信息
SQL Server：Replication Agents 提供有关当前运行的复制代理的信息
SQL Server：Replication Dist. 提供从分发数据库读出并送到订阅数据库的事务数量信息
SQL Server：Replication Logreader 提供从出版数据库读出并送到分发数据库的事务数量信息
SQL Server：Replication Merg e 提供有关SQL Server合并复制的信息
SQL Server：Replication Snapshot 提供有关SQL Server快照复制的信息
SQL Server：SQL Server Statistics 提供有关已送到服务器的S Q L查询的信息
SQL Server：User Settable 提供执行定制监视的能力。每个计数器可以是一个返回一个值给
Performance Monitor的查询或存储过程
23.4.2 运行SQL Server Performance Monitor
如你已经在Window NT下安装了SQL Server 7.0 ，将能访问SQL Server Performance
M o n i t o r，下面步骤将教你如何运行SQL Server Performance Monitor：
1) 按“S t a r t”按钮，从“ P r o g r a m s”进入“ Microsoft SQL Server 7.0”，并点击“ S Q L
Server Performance Monitor”，这将打开SQL Server Performance Monitor。
2) 如前所述，这里自动开始监视几个默认的计数器。如果你想增加附加的计数器，在工
具栏点击加号( + )，这将打开“Add Counters”对话框。
3) 从“O b j e c t”下拉列表框中选出你想要监视的基本对象。本例中，选择“ SQL Server：
L o c k s”。当你排查那些写得很糟糕的查询时，将用到这个计数器包括的信息。
4) 从“C o u n t e r s”框中选择“Users Blocked”。每当一个用户正阻塞另一个时，这个计数
器会让你知道。在你选好该计数器以后，按“ O K”关闭这个窗口并开始监视该计数器。
5) 当你正监视大量计数器时，很难判断哪一行属于哪一个计数器。为帮助你判断，可以
按“C t r l + H”键高亮显示你想看的计数器，这将把与该计数器相关的行变成白色。
23.5 使用SQL Server Profiler
SQL Server 7.0提供了一个强大的新工具使得D B A能够去收集数据并监视系统性能和用户
2 2 4 SQL Server 7 24学时教程
下载
活动。SQL Server Profiler能跟踪的事件是：登录企图；连接和取消连接； Tr a n s a c t - S Q L的
I N S E RT、U P D AT E和D E L E T E；远程过程调用批状态。在以跟踪形式收集了信息后，你能够
用跟踪去分析和修正服务器资源问题、监视登录以及解决加锁和阻塞问题。跟踪只是你捕捉
服务器活动和事件供以后使用的一个文件。
跟踪什么
SQL Server Profiler能够收集有关服务器活动的指定数据。虽然你刚刚接触SQL Server，
你将可能同Index Tuning Wi z a r d一起使用SQL Server Profiler ，这在“使用Index Tu n i n g
Wi z a r d”一节中要讨论。为了使Index Tuning Wi z a r d工作，你必须创建一个工作负载。工作
负载是一个文件，包含了一个脚本或一个包括一组S Q L语句或远程过程调用的SQL Server
P r o f i l e r跟踪。这个工作负载将模拟用户进行的数据库操作。下面步骤教你通过使用S Q L
Server Profiler来创建一个工作负载。
1) 启动SQL Enterprise Manager并连接到你要运行一个跟踪的服务器上。从“ To o l”菜单
中选择“SQL Server Profiler”。SQL Server Profiler应用程序打开，如图2 3 - 1所示。
2) 如果你需要打开一个预定义的跟踪，选择“ F i l e”|“O p e n”|“Trace Definition”。
3) 为创建一个新的跟踪，点击工具栏中的“ New Tr a c e”按钮，或者打开“ F i l e”菜单，
然后选择“ N e w”|“Tr a c e”。这将打开“Trace Properties”对话框，如图2 3 - 2所示。
4) 在“N a m e”框中输入你用于标识这个跟踪的名字。
5) 在“Tr a c e Ty p e”区域，选择“P r i v a t e”或是“S h a r e d”。如果这个跟踪将被创建者以外
的人使用，选择“S h a r e d”。缺省值为“P r i v a t e”。
6) “G e n e r a l”分页也能让你选择将捕获的数据放到何处。如果想记录这个数据供以后分
析，可选择存放到表中或文件中。默认的选择是将记录的事件显示在屏幕上。
7) 在如图2 3 - 3所示的“E v e n t s”分页上，你能指定要跟踪的SQL Server事件。在这个分页
中，你能选择你想记录的指定选项。为进行这个练习，选择“ SQL Operators”并点击“ A d d”
按钮。这将跟踪所有作用于你的数据库的I N S E RT、U P D AT E、S E L E C T和D E L E T E等S Q L语句。
第2 3学时优化和调整2 2 5
下载
图23-1 SQL Server Profiler
应用程序使D B A可以
收集数据并监视系统
性能和用户活动
8) 在如图2 3 - 4所示的“Data Columns”分页上，你能选择对于每一个跟踪的事件要捕获
的数据。包括关于用户和任何被访问的服务器对象的信息。大多数情况下，你不必更改该分
页中的任何设置。
9) 在如图2 3 - 5所示的“F i l t e r s”分页中，你能选择用于包含或排除的条件。举例来说，默
认情况下，任何由SQL Server Profiler生成的事件都被忽略。本例中，将保留默认设置。
10) 当设置完成以上步骤后，点击“ O K”按钮，则跟踪将自动开始。如果将跟踪输出到
2 2 6 SQL Server 7 24学时教程
下载
图23-2 在“ G e n e r a l” 分
页，定义跟踪
图23-3 在“E v e n t s”分页，
你可以选择要跟踪
的事件
图23-4 在“Data Columns”
分页，你可以选择
要收集的数据
屏幕，你将看到显示的信息。
23.6 使用Index Tuning Wizard
Index Tuning Wi z a r d是SQL Server 7.0的一个新工具。它使管理员能创建和修改索引，而
不需要知道关于数据库结构、硬件平台和部件或者用户端应用程序是如何作用于数据库的知
识。Index Tuning Wi z a r d分析那些由SQL Server Profiler收集的数据，然后给出改善索引效率
的建议，这些索引可以是系统中已有的，也可以是要新建的。这些建议以脚本的形式给出，
可以运行它们以在系统中删除并重建索引。操作步骤如下：
1) 在SQL Enterprise Manager中点击“ To o l s”下拉列表框，并选择“ Wi z a r d s”。打开
“Select Wi z a r d”对话框，如图2 3 - 6所示。点击“ M a n a g e m e n t”旁边的加号并选择“ I n d e x
Tuning Wi z a r d”。然后点击“O K”按钮。
2) 首先看到的屏幕概括了将要进行的操作。点击“ N e x t”按钮。
3) 在如图2 3 - 7所示的“ Select Server and Database”对话框中，选择你要分析的S Q L服务
器名称和数据库名称。点击“ N e x t”按钮。
4) 如果你已经用SQL Profiler创建了一个工作负载文件，从如图2 3 - 8所示的“ I n d e n t i f y
Wo r k l o a d”对话框中选择“I have a saved workload file”。若还没有，则选择“ I will create a
workload file on my own”选项。它将为你打开SQL Profiler以创建工作负载文件。本例中，
第2 3学时优化和调整2 2 7
下载
图23-5 在“F i l t e r s”分页，
你可以选择条件来
过滤要捕获的事件
图23-6 “Select Wi z a r d”
对话框
我假设你已经有了一个工作负载文件。
5) 从如图2 3 - 9所示的“ Specify Wo r k l o a d”对话框中选择包含跟踪信息的文件或S Q L
S e r v e r表。然后点击“N e x t”按钮。
6) 在如图2 3 - 1 0所示的“Select Tables to Tu n e”对话框中，SQL Server将自动选择数据库
中的所有表用于调整。可以在这里排除任何你不需要的表。
7) 当你点击“N e x t”按钮时，Index Tuning Wi z a r d将开始分析你收集的数据，并基于此
数据指定索引。当它完成了分析，将打开“ Index Recommendations”对话框，如图2 3 - 11所示，
指明哪些索引需要改变或增加。检查该信息并点击“ N e x t”按钮。
8) 下一个对话框使你能以脚本文件形式来存储这些建议的变动以便在需要的时候运行。
或者让SQL Server来为你进行这些改动。
2 2 8 SQL Server 7 24学时教程
下载
图23-7 “ Select Server
and Database”对
话框
图23-8 “Indentify Wo r k -
l o a d”对话框
图23-9 “Specify W o r k -
l o a d”对话框
23.7 课时小结
在这一学时中，我们讨论了一些有关监视和优化服务器的问题。性能的优化和调整是一
个很复杂课题。在这一学时中，仅仅介绍了基本知识。如果你想获得更多的有关优化和调整
的知识，可以去阅读《Using Microsoft SQL Server7.0》(中文书名《Microsoft SQL Server 7.0
使用详解》，书号ISBN 7-111 - 0 7 2 0 9 - X )或者《SQL Server7.0 Unleashed》。
23.8 专家答疑
问题：我应当自己去设置内存、锁和服务器上的用户数，还是让SQL Server 来处理这一
切？
解答：在多数情况下，你都应该让SQL Server动态地设置SQL Server上的大部分资源。某
些时候你将需要设置资源分配，但次数不多且间隔较长。
问题：现在我的SQL Server已经很好地运行了几个月。但最近几天来，用户们开始抱怨
响应时间太长。我应该首先做什么？
解答：首先你应该全面检查一下计算机本身的性能。这方面最好的工具是SQL Server
Performance Monitor。使用它，你就能检查出一些问题，比如用户数、处理器的使用情况以
及内存的使用情况。
23.9 课外作业
这些思考题和练习题是供你加深理解用的。答案可以在附录“答案”中找到。
第2 3学时优化和调整2 2 9
下载
图23-10 “Select Ta b l e s
to Tu n e”对话框
图2 3 - 11 Index Tuning Wi z -
a r d的建议
23.9.1 思考题
1) 为什么你需要监视和调整SQL Server？
2) 基准有什么用？
3) SQL Server Performance Monitor有什么用？
4) 什么是计数器？
5) 什么是工作负载？
6) Index Tuning Wi z a r d有什么用？
23.9.2 练习题
使用SQL Server Profiler创建一个工作负载文件，用于在对不同的表运行S E L E C T语句时，
监视p u b s数据库。当你创建了一个跟踪后，使用Index Tuning Wi z a r d来判定在这些表中是否需
要新的索引。