SQL Q&A: Memory Configuration, Performance Profiling, Setting Your Fill Factor, and More

项目
08/17/2016

SQL 问与答内存配置、性能剖析、设置您的填充因子等等

Edited by Nancy Michell

内存配置

提示：更易于分析

您是否知道现在已能够将 PerfMon 与 SQL Server 2005 Profiler 相关联？

您可能已经注意到 SQL Server 计算机上性能监视器反映出来的 CPU 使用率出现峰值、内存消耗过度或整体性能下降，并可能很想知道导致性能异常的原因。在 SQL Server 2005 问世以前，必须使用 Profiler 来捕获跟踪，然后在 Enterprise Manager 中查看系统进程，最后捕获性能监视器日志 — 当然，这需要触发 PerfMon。使用上述所有工具完成全部任务后，还需要手动协调各个工具之间的事件来确定性能降低的原因。这就意味着需要费劲地逐个查看每个日志。虽然这个过程很乏味，但如果您希望了解出现性能问题的根本原因，就必须如此操作。

借助 SQL Server 2005，您仍需要捕获跟踪并检查 PerfMon 日志，但现在使用 Profiler 可让您直接连接这些日志。您可以滚动浏览 T-SQL 语句，Profiler 将自动以图形方式为您展示出现的问题。如果在 Profiler 的性能监视器用户界面中单击，就可跳转到与该时间戳关联的语句。这在对 SQL Server 环境进行故障排除时可节省大量时间。

让我们来看一下如何将 PerfMon 日志连接到 Profiler：

启动性能监视器并开始捕获来自数据库服务器的信息。
在“性能日志和警报”下创建新的计数器日志并输入新日志的名称。
添加新计数器，如 % Processor Time。您还希望将日志记录设置为手动启动，或者通过“计划”选项卡按计划启动。
单击“确定”，如果选择的是手动选项，请确保您要开始记录日志。
在 SQL Server 上通过 Profiler 设置跟踪。为此，您可以单击“文件”菜单上的“新建跟踪”。请确保跟踪中包括 StartTime 和 EndTime，然后命名此跟踪并将其设置为保存到某个文件。最后，必须冲击服务器以模拟某些事务活动，然后停止捕获性能监视器数据和 Profiler 数据。
在 Profiler 中，从“文件”菜单选择“导入性能数据”。接下来，选择存储 PerfMon 日志的位置，然后选择“文件”|“打开”|“跟踪”。最后，选择存储 Profiler 跟踪的位置。

完成后，您将体会到找出特定 SQL 语句对处理时间的影响是多么的容易。

问：我正在尝试寻找最适合我的 SQL Server™ 组件的内存配置。先前的管理员对 SQL Server 2000 两节点群集上每台 12GB RAM 的计算机的 Boot.ini 进行了如下设置：Yes /PAE NO /3GB（SQL Server 的设置为 No AWE）。在 12GB RAM 可用的情况下，我是否应删除 Boot.ini 中的 /3GB 开关，打开 AWE，并将这 12GB 中的 10GB 分配给 SQL Server？运行 SQL Server 的计算机上没有任何其他程序，因此没有其他应用程序需要内存。

答：是的，您应该打开地址窗口化扩展插件 (AWE)，并为 SQL Server 预分配一个 RAM 上限 — 对于有 12GB 专用 RAM 的 SQL Server 组件而言，10GB 听起来很合适。（请注意，预分配只对 SQL Server 2000 有效。自 SQL Server 2005 起，使用 AWE 已不再是静态的，可以进行动态更改。）对于应同时使用 /3GB 开关和 /PAE 开关还是只使用必要的 /PAE 开关，一直存在很大争议。尽管您真正需要的仅是设置 /PAE 和启用 AWE，但我还是建议您同时使用这两个开关；不过，需要考虑一些因素。

是否使用 /3GB 开关取决于是否会在特定情形下用到它。必须位于虚拟地址空间中前 2GB 或 3GB 的内存 MemToLeave 区域是否会不足？如果启用此开关，是否会使操作系统内存不足？（有关详细信息，请参阅 support.microsoft.com/kb/316739。）如果使用的是群集，您可以仅在其中一个节点上设置 /3GB。这样，如果正在使用 /3GB 进行测试并遇到了问题，您就可以快速将该实例故障转移到其他节点。请记住，如果您的 RAM 大于 16GB ，则不支持 /3GB 开关。

通过使用 /3GB，可以将虚拟地址空间 (VAS) 增加 50%，这样，不止数据缓存受益匪浅，导致 VAS 内存不足的应用程序也同样受益。幸运的是，64 位服务器（IA64 和 x64）消除了这一令人误解的因素。如果计算机专用于 SQL Server，则有关操作系统内存不足的考虑不适用。为操作系统保留 2GB 就显得有点多余了；如果此服务器专用于只运行标准的小型操作系统服务的 SQL Server，您会发现此服务器上还有大约 1.3GB 的可用内存，因此您不妨让 SQL Server 使用这额外的 1GB。先分配 10GB，使用 PerfMon 长时间监视可用内存以查看可用内存量，然后进行相应调整。请记住，如果在 SQL Server 2000 上过多提交，可能会导致交换的发生，因为 AWE 的动态性不像在 SQL Server 2005 中那样好。确定是否使用 /3GB 开关的主要方法是在特定环境中对其进行测试。

复制的实例名称

问：现在，我是否能够在 SQL Server 2005 复制中使用我的服务器的 IP 地址来指示要复制哪些实例？根据“如何在不受信任的域中或跨 Internet 的运行 SQL Server 的计算机之间进行复制”(support.microsoft.com/kb/321822)，我们知道在 SQL Server 2000 中这样操作会导致错误，但我不知道在 SQL Server 2005 中是否同样如此。

答：指定要参与复制的服务器实例时，必须提供 SQL Server 注册的实例名称。例如，在命令行上将 Publisher 或 Subscriber 参数指定到复制存储过程或复制代理连接设置时，必须使用 SQL Server 实例名称。如果 SQL Server 实例的网络名称与注册实例名称不同，则通过代理进行的复制连接会失败。

如果实例的网络名称与 SQL Server 实例名称不同，建议添加 SQL Server 实例名称作为有效的网络名称。设置替换网络服务名称的一种方法是将其添加到本地主机文件。默认情况下，本地主机文件位于 WINDOWS\system32\drivers\etc 或 WINNT\system32\drivers\etc 下。例如，如果计算机名称是 comp1，计算机的 IP 地址是 10.193.17.129，并且实例名称是 inst1/instname，则将下列条目添加到主机文件：

10.193.17.129 inst1

SQL Server Integration Services

问：我正在为 SQL Server 2005（64 位企业版本，两台服务器）安装主动/主动群集，这样我将拥有一共四个 SQL Server 2005 实例。所有实例都需要 SQL Server Integration Services (SSIS)。您是否能告诉我关于群集化 SSIS 的相关信息及其对维护计划的影响？

答：尽管能够群集化 SSIS 服务，但其实不必这样做，而且这样做可能还会遇到各种问题，包括不支持委派（请参阅 msdn2.microsoft.com/aa337083），而且不是多实例的 — 每个节点上一次只能运行一个实例。

以前，必须安装 SSIS（只安装但不运行）才能运行维护计划向导。不过，这种情况在 SQL Server 2005 SP1 中已经不存在了。如果 SSIS 未运行，可以由 SQL Server 代理来执行维护计划。

不必群集化 SSIS，您可以考虑将 SSIS 作为一项独立服务一直运行，并编辑 MsDtsSrvr.ini.xml，使其指向所有正在运行中的任意实例。这样，您就能够从任何节点管理程序包并提供大多数客户寻求的高可用性，而不会遇到与使服务群集化相关的任何问题。

有关创建维护计划失败的详细信息，请务必参阅知识库文章 support.microsoft.com/kb/909036。

执行时间怪异

问：在对 SQL Server 2005 SP1 组件进行加载测试期间，SQL Server Profiler 记录的存储过程 (SP) 执行时间有许多负值，并且在某些情况下，SP 执行时间与结束时间减去开始时间得出的值不匹配。

答：影响 SQL Server Profiler 中的 SP 执行时间和其他性能时间报告的因素有很多。请记住，SQL Server 2005 使用毫秒计算执行时间，如果您使用的技术更改了此计量单位，就会得到不一致的报告和不合乎情理的执行时间。

例如，如果使用其他电源使用方案，CPU stepping 或 AMD Cool 'n Quiet 技术，则会更改 CPU 频率，这样就会使计算执行时间的结果与 SQL Server Profiler 的预期值不匹配。

support.microsoft.com/kb/931279 上提供的知识库文章介绍了与此相关的症状、各种原因以及一些补救措施。

提示：检查填充因子

假如您有一个盛满水的玻璃杯，您要尝试再向这个玻璃杯中加水。结果会怎样呢？水会溢出来。

SQL Server 的情况也是如此。当索引页填充满时，如果尝试添加新行，则 SQL Server 会将大约一半的行移动到新页，以便为新添加的行腾出空间。这就是通常所说的“页面分割”。页面分割可为新记录腾出空间，但却既费时又非常耗费资源，并且会产生碎片，从而可能对 I/O 操作产生负面影响。那么，如何避免此类情况呢？

要防止此类情况的发生，必须主动确定填充因子值。创建或重新构建索引后，填充因子值会确定每个叶级页中用于填充数据的空间百分比，其余部分留作将来扩充之用。例如，将填充因子值配置为 60 就意味着每个叶级页的 40％都是空的，以便在向基础表中添加数据时为索引扩展提供空间。

默认填充因子值始终是 0，此值对大多数情况都适用。一般来讲，填充因子值为 0 意味着叶级别几乎已填满，但留出了一些空间，至少能再添加一个索引行。（请注意，填充因子为 0 和 100 是相似的。）

您可以在 CREATE INDEX 或 ALTER INDEX 语句中设置各个索引的填充因子值，也可以直接在服务器级别上配置此值，以便所有新建索引都使用默认值。

以下示例在服务器级别将填充因子值设置为 70％，这就意味着您将有 30％的可用空间供以后扩展使用。当然，在实施到生产环境前，必须先认真测试此选项。

USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO --必须重新启动 SQL Server 引擎以使更改生效。

如果您希望在单个索引级别上配置填充因子，应如何操作呢？假设您正在构建下表，并希望对名为 Col_A 的列创建唯一索引，且填充因子值为 70。此时该命令应类似于下面的命令：

--Create an Item table USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;

--Create a unique index on colum Col_A of Item table with a Fill Factor value of 70 CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO

如何识别每个索引的填充因子？可以查询 sys.Indexes 以从数据库中获取所有索引的填充因子值，如下所示：

USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

其他资源