数据库的性能优化和设置

转贴]配置SQL   Server   2000选项
SQL   Server服务器的配置选项属于那种人们了解较少且经常误用的选项。当一个技术支持人员要求你按照某种方式调整一个选项、而另一个技术支持人员却要求你按照另一种完全对立的方式调整同一个选项时，你可能对这些选项的真正含义感到困惑。有关这些选项的资料很缺乏，至少可以说不够详细和清楚。在SQL   Server   2000中，Microsoft减少了几个配置选项，让SQL   Server动态配置它们，从而减少了几个容易混淆的地方。同时，Microsoft又为SQL   Server   2000新增了两个服务器配置选项，调整了一些数据库选项，从而稍许简化了数据库管理员的工作。
新增的服务器选项
就象访问大多数企业版服务器的属性一样，我们不能在SQL   Server   Enterprise   Manager中通过服务器属性窗口访问SQL   Server   2000新增的两个服务器选项。作为防止用户由于不小心而错误配置服务器的一个安全措施，Microsoft没有把这些高级配置选项放入Enterprise   Manager。相反，我们必须使用T-SQL/sp_configure系统存储过程去访问这些高级选项。我们可以用不带参数运行sp_configure的方法查看服务器的当前配置。在执行结果中，config_value是SQL   Server从Master数据库syscurconfigs表提取出来的数据，它显示了服务器的当前配置；run_value列显示了执行sp_configure时SQL   Server正在使用的选项，SQL   Server在sysconfigures表中存储这些数据。修改某个选项之后，我们必须执行RECONFIGURE命令（在大多数情况下，还要重新启动SQL   Server）才能让新的run_value显示出来。本文所讨论的所有选项都要求重新启动SQL   Server。
服务器选项总共有36个，默认情况下，sp_configure存储过程只显示其中的10个，显示结果中不包含高级选项，而且所有新的SQL   Server配置选项都不会出现在这个精简的清单中。然而，我们可以使用show   advanced   options命令参数让SQL   Server显示出所有选项。要启用show   advanced   options，我们使用如下命令格式：
EXEC   sp_configure   ‘show   advanced   options‘,   ‘1‘   RECONFIGURE
要安装一个选项，我们必须在使用sp_configure配置服务器之后运行RECONFIGURE命令。上面命令的输出结果如下：
Configuration   option   ‘show   advanced   options‘   changed   from   0   to   1.   Run   the   RECONFIGURE   command   to   install.
一旦能够查看高级选项，我们就可以看到两个新的服务器选项。其中最重要的一个新选项是awe   enabled选项，它能够让SQL   Server企业版提高服务器的内存访问能力。默认情况下，SQL   Server能够使用的最大RAM是3GB。在Windows   2000上，应用程序可以   使用Address   Windowing   Extensions（AWE）API访问更多的RAM。例如，在Windows   2000   Advanced   Server中，我们能够使用多达8GB的内存，只有Windows   2000   Datacenter   Server支持64GB内存才超过它。显然，当SQL   Server拥有更多的可用内存，它将能够缓冲更多的数据，改善查询的响应时间。
不过，启用awe   enabled选项也有副作用。启用awe   enabled选项之后，SQL   Server不再动态地分配内存。由于缺乏内存动态分配功能，管理负担随之增加，因为我们必须仔细地监视RAM使用情况。另外，设置awe   enabled选项之后，我们还必须设置max   server   memory选项。如果我们不设置max   server   memory选项，服务器RAM又等于最低要求3GB，SQL   Server将在启动的时候占据机器上几乎所有的RAM，只给Windows和其他应用留下128   MB的RAM。通过设置max   server   memory选项，我们可以限制SQL   Server使用的内存总量。
awe   enabled选项只能在SQL   Server   2000   Enterprise   Edition上使用，操作系统必须是Windows   2000   Advance   Server或Datacenter。如果你在SQL   Server的其他版本上使用这个选项（或者操作系统是WinNT），SQL   Server将忽略这个选项。在某些服务器配置组合下，不适当地配置这个选项将导致不可预知的结果。例如，如果我们在Windows   98操作系统、运行SQL   Server   Personal   Edition的机器上设置这个选项，SQL   Server可能报告它已经停止（甚至是在它正在运行的时候），而且它将拒绝停止SQL   Server实例。
在SQL   Server   Enterprise   Edition服务器上启用AWE包括三个步骤。首先，我们必须确保启动SQL   Server实例的帐号具有在内存中锁定页的权限。SQL   Server安装时自动把页锁定权限授予我们指定用来启动SQL   Server服务的Windows帐号；但是，如果后来这个帐号已经改变，你应该检查一下已经把哪些权限授予了启动SQL   Server的用户。检查帐号的权限可以使用Windows   2000的组策略工具。第二个步骤是运行sp_configure存储过程，把awe   enabled选项设置为1。然后，我们必须执行RECONFIGURE，用手工方式重新启动SQL   Server。配置命令的语法为：
EXEC   sp_configure   ‘awe   enabled‘,   ‘1‘   RECONFIGURE
注意，在Windows   2000或者NT上，如果要访问高于4GB的物理内存，我们还必须采取其他一些措施，即修改boot.ini文件，加入/pae选项。
第二个新的SQL   Server   2000选项用来启用C2级安全审核模式。C2是一个政府安全等级，它保证系统能够保护资源并具有足够的审核能力。C2模式允许我们监视对所有数据库实体的所有访问企图。启用SQL   Server的C2审核功能的命令如下：
EXEC   sp_configure   ‘c2   audit   mode‘,   ‘1‘   RECONFIGURE
（要实现完整的C2级安全保证，Windows操作系统也必须提供相应的支持）启用C2审核模式并重新启动之后，SQL   Server自动在\MSSQL\Data目录下面创建跟踪文件。我们可以使用SQL   Server   Profiler查看这些监视服务器活动的跟踪文件。
SQL   Server以128KB大小的块为单位把数据写入跟踪文件。因此，当SQL   Server非正常停止时，我们最多可能丢失128   KB的日志数据。可以想象，包含审核信息的日志文件将以很快的速度增大。例如，某次试验只访问了三个表，跟踪文件已经超过了1MB。当跟踪文件超过200MB时，C2审核将关闭旧文件并创建新文件。每次SQL   Server启动的时候，它会创建一个新的跟踪文件。如果磁盘空间不足，SQL   Server将停止运行，直至我们为审核日志释放出足够的磁盘空间并重新启动SQL   Server实例。在SQL   Server启动的时候，我们可以使用-f参数禁用审核。
减少的服务器选项
在SQL   Server   2000中，Microsoft减少了原有的几个选项，让SQL   Server   2000自动配置这些选项。减少的选项中最引人注目的是max   async   IO选项。这个选项允许数据库管理员指定在单一的数据库文件上可以出现多少异步的磁盘读取和写入操作。SQL   Server   7.0中的max   async   IO选项是人们了解最少的选项之一，它的默认值是32，但很少有管理员去调整这个值。在SQL   Server   2000中，这个异步IO选项随着SQL   Server接收的适配器反馈信息动态地上升或者下降，SQL   Server利用反馈算法确定服务器负载以及SQL   Server系统能够控制的数量。
数据库选项
在SQL   Server   2000中，如果你曾经查看过Enterprise   Manager中数据库的Options选项卡，你可能会对一些通用选项的消失感到困惑（要访问Options选项卡，在Enterprise   Manager中右击数据库然后选择Properties）。Options选项卡中减少了trunc.   log   on   chkpt.以及Select   Into/Bulk   Copy这两个选项，如图1所示。为了清楚和向后兼容起见，这些通用选项现在称为recovery   model（恢复模型）选项。如果用SQL   Server   2000的Enterprise   Manager连接SQL   Server   7.0数据库，我们仍旧可以看到这些老选项。以前，我们使用下面的命令为Northwind数据库开启trunc.   log   on   chkpt.选项：
SP_DBOPTION   Northwind   ,‘trunc.   log   on   chkpt.‘,   true
设置好选项之后，我们可以通过Options选项卡或者下面的查询检查Northwind数据库上这些选项设置是否成功：
SELECT   DATABASEPROPERTY   (‘Northwind‘,   ‘IsTruncLog‘)
结果为1表示选项设置成了true；结果为0表示选项设置成了false。如果结果为NULL，它表示我们或者选择了一个错误的选项，或者数据库不存在。
为了便于使用，Microsoft把trunc.   log   on   chkpt.和Select   Into/Bulk   Copy选项换成了恢复模型设置。这种选项改变的目的在于确保数据库管理员能够充分理解在灾难恢复策略中恢复模型选项的意义。SQL   Server   2000为我们提供了三种数据库恢复模型：simple（简单恢复），full（完全恢复），bulk_logged（大容量日志记录恢复）。
简单恢复模型最容易操作，但它是最缺乏灵活性的灾难恢复策略。选择简单恢复模型等同于把trunc.   log   on   chkpt.设置成true。在这种恢复模型下，我们只能进行完全备份和差异备份（differential   backup）：这是因为事务日志总是被截断，事务日志备份不可用。一般地，对于一个包含关键性数据的系统，我们不应该选择简单恢复模型，因为它不能够帮助我们把系统还原到故障点。使用这种恢复模型时，我们最多只能把系统恢复到最后一次成功进行完全备份和差异备份的状态。进行恢复时，我们首先要恢复最后一次成功进行的完全备份，然后在此基础上恢复差异备份（差异备份只能把自从数据库最后一次完全备份之后对数据库的改动施加到数据库上）。
完全恢复模型把trunc.   log   on   chkpt.选项和Select   Into/Bulk   Copy选项都设置成false。完全恢复具有把数据库恢复到故障点或特定即时点的能力。对于保护那些包含关键性数据的环境来说，这种模型很理想，但它提高了设备和管理的代价，因为如果数据库访问比较频繁的话，系统将很快产生庞大的事务日志记录。由于在这种模型中Select   Into/Bulk   Copy设置成了false，SQL   Server将记录包括大容量数据装入在内的所有事件。
最后一种恢复模型是大容量日志记录恢复，它把trunc.   log   on   chkpt.设置成false，把Select   Into/Bulk   Copy设置成true。在大容量日志记录恢复模型中，大容量复制操作的数据丢失程度要比完全恢复模型严重。完全恢复模型记录大容量复制操作的完整日志，但在大容量日志记录恢复模型下，SQL   Server只记录这些操作的最小日志，而且无法逐个控制这些操作。在大容量日志记录恢复模型中，数据文件损坏可能导致要求手工重做工作。   下表比较了三种恢复模型的特点。
恢复模型   优点   工作损失表现   能否恢复到即时点？
简单   允许高性能大容量复制操作。
收回日志空间，使得空间要求最小。   必须重做自最新的数据库或差异备份后所发生的更改。   可以恢复到任何备份的结尾处。随后必须重做更改。
完全   数据文件丢失或损坏不会导致工作损失。
可以恢复到任意即时点（例如，应用程序或用户错误之前）。   正常情况下没有。
如果日志损坏，则必须重做自最新的日志备份后所发生的更改。   可以恢复到任何即时点。
大容量日志记录   允许高性能大容量复制操作。
大容量操作使用最少的日志空间。   如果日志损坏，或者自最新的日志备份后发生了大容量操作，则必须重做自上次备份后所做的更改。   否则不丢失任何工作。   可以恢复到任何备份的结尾处。随后必须重做更改。
在数据库的Options选项卡中，我们可以从Model下拉列表框选择Simple把恢复模型改成简单模型。另外，Microsoft扩展了ALTER   DATABASE命令，我们可以用它设置数据库属性。例如，用下面这个T-SQL命令可以把恢复模型设置为完全恢复模型：
ALTER   DATABASE   Northwind   SET   RECOVERY   FULL
SQL   Server   2000提供了把数据库转入单用户模式的许多选项，它们都属于那种最令人感兴趣的隐藏选项。为了修正讹误或其他数据问题，数据库管理员常常要把数据库转入单用户模式。当数据库处于这种模式时，其它用户将不能再访问数据，从而使得管理员能够在用户访问损坏的数据之前修正数据问题。在SQL   Server   7.0中，在把数据库转入单用户模式之前，我们必须确保所有用户都已经断开连接。对于一个高速OLTP数据库系统，比如电子商务系统，断开所有用户的连接非常困难，因为就在我们断开某个用户的连接时，其他用户还会连接数据库。SQL   Server   2000极大地改进了这个操作过程，我们可以给用户一个指定的时间去完成他们的事务，然后由SQL   Server自动断开他们的连接。另外，我们也可以在不提供任何延迟时间的情况下断开所有的连接。
把数据库转入单用户模式的方法之一是在数据库的Options选项卡选中Restrict   Access检查框，然后选择Single   user。另外，Microsoft扩展了ALTER   DATABASE命令，使它能够把数据库转入单用户模式，语法如下：
ALTER   DATABASE   Northwind   SET   SINGLE_USER
执行这个命令之后，SQL   Server等待所有的数据库连接，让它们完成各自的事务。在这种状态下，所有请求连接数据库的用户都将接收到图2显示的错误信息，并被重定向到他们各自的默认数据库（通常是Master数据库）。图2的错误信息意味着数据库处于冻结状态，直至所有用户断开连接。如果目标服务器或者发出命令的用户没有设置query   timeout参数，客户端可能无限期地等待查询完成，直至所有的连接被断开。在Query   Analyzer中，我们可以在Options屏幕（选择菜单Tools，Options）的Connections选项卡里面指定超时秒数。在单用户模式下，只有发出ALTER   DATABASE命令的用户可以保持连接。
另外，我们还可以用ROLLBACK   IMMEDIATE命令断开所有打开数据库连接的用户。但我们不能在Enterprise   Manager中使用这个命令，而是应该用Query   Analyzer执行，例如：
ALTER   DATABASE   Northwind   SET   SINGLE_USER   WITH   ROLLBACK   IMMEDIATE
执行这个命令之后，SQL   Server立即断开所有的连接并回退它们的事务。所有正在执行事务的用户都会接收到一个连接错误，而且他们不能再连接数据库。
我们可以指定一个时间选项，让SQL   Server在断开用户的连接之前等待用户完成他们的事务。这个选项是可选的，它用ROLLBACK   AFTER关键词指定，如下面的命令所示：
ALTER   DATABASE   Northwind   SET   SINGLE_USER   WITH   ROLLBACK   AFTER   20   SECONDS
执行这个命令之后，SQL   Server先等待20秒钟，然后断开所有的连接并回退它们的事务。在这个过程中，SQL   Server不再接受新的连接请求，它是一个数据库级的服务器暂停。在这个20秒之内，所有企图连接数据库的新用户都将接收到图2显示的错误信息。如果执行这个命令的时候不存在已经连接的用户，数据库将立即转入单用户模式。
Enterprise   Manager的数据库Options选项卡中，最后一个新的配置选项是Compatibility   Level，如图1所示。要设置这个选项，我们只需从Compatibility   Level下拉框选择一个合适的兼容级别。在这个下拉框中，SQL   Server   2000由80代表，7.0由70代表，6.5由65代表。兼容级别选项决定了某些数据库查询操作的执行方法。由于SQL   Server的关系引擎在发展变化，某些查询的结果在不同的版本之间可能不同。例如，如果我们执行下面这个查询：
SELECT   DATALENGTH(‘‘)
依赖于我们为数据库设置的兼容级别是SQL   Server   2000、7.0或者6.5，上述查询可能得到两个不同的结果。对于SQL   Server   2000或7.0数据库，返回结果是0，因为SQL   Server   2000和7.0把空字符串视为真正的空；在SQL   Server   6.5兼容模式下，返回结果是1，因为SQL   Server   6.5把空字符串视为一个空格。SQL   Server   7.0也有这个兼容级别选项，但它只能通过sp_dbcmptlevel存储过程访问。
综上所述，在SQL   Server   2000中，Microsoft对服务器和数据库配置方法进行了几个实质性的改动。不要轻视所有本文讨论的选项和其他SQL   Server配置选项——即使是一个小小的改动，它也可能对性能产生重大的正面或负面影响。在调整SQL   Server的配置选项时，你最好使用Performance   Monitor之类的服务器监视工具，确保选项调整不会对服务器性能产生负面影响。