SQLServer性能优化工具

数据和工作负荷示例
　　使用下例说明 SQL Server 性能工具的使用。首先创建下表。
　　
　　create table testtable (nkey1 int identity, col2 char(300) default 'abc', ckey1 char(1))
　　接下来，在这个表中填充 10,000 行测试数据。可以为列 nkey1 中所填充的数据创建非聚集索引。可以为列 ckey1 中的数据创建聚集索引，col2 中的数据仅仅是填充内容，将每一行增加 300 字节。
　　
　　declare @counter int
　　
　　set @counter = 1
　　
　　while (@counter <= 2000)
　　
　　begin
　　
　　insert testtable (ckey1) values ('a')
　　insert testtable (ckey1) values ('b')
　　insert testtable (ckey1) values ('c')
　　insert testtable (ckey1) values ('d')
　　insert testtable (ckey1) values ('e')
　　set @counter = @counter + 1
　　end
　　
　　数据库服务器将进行下面的两个查询：
　　
　　select ckey1,col2 from testtable where ckey1 = 'a'
　　select nkey1,col2 from testtable where nkey1 = 5000
　　Profiler
　　SQL Server Profiler 记录数据库服务器中所发生活动的详细信息。可以配置 Profiler 以便用大量的可配置性能信息监视并记录在 SQL Server 中执行查询的一个或多个用户。可在 Profiler 中记录的性能信息有：I/O 统计信息、CPU 统计信息、锁定请求、T-SQL 和 RPC 统计信息、索引和表扫描、警告和引发的错误、数据库对象的创建/除去、连接/断开、存储过程操作、游标操作等等。有关 SQL Profiler 可记录的全部信息，请在 SQL Server Books Online 中搜索字符串“Profiler”。
　　
　　将 Profiler 信息装载到 .trc 文件中以便用于 Index Tuning Wizard 中
　　Profiler 和 Index Tuning Wizard 是强大的工具组合，以帮助数据库管理员在表中创建适当的索引。Profiler 将查询所消耗的资源记录在 .trc 文件中。.trc 文件可以由 Index Tuning Wizard 读取，Index Tuning Wizard 同时考虑 .trc 信息和数据库表，然后建议应创建什么样的索引。Index Tuning Wizard 可让管理员选择是自动创建数据库的适当索引，调度索引以便在以后自动创建还是产生一个可以在以后查看和执行的 T-SQL 脚本。
　　
　　以下是分析查询负荷的步骤：
　　
　　设置 Profiler
　　从 SQL Server Enterprise Manager 菜单中选择 Tools/SQL Server Profiler 启动 Profiler。
　　按 CTRL+N 组合键新建 Profiler 跟踪。
　　键入此跟踪的名称。
　　选择 Capture to File:复选框，然后选择要将 Profiler 信息输出到其中的 .trc 文件。
　　单击 OK。
　　运行工作负荷
　　启动 Query Analyzer（从 SQL Server Enterprise Manager 菜单中选择 Tools/SQL Server Query Analyzer 或者从开始菜单中选择开始/程序/Microsoft SQL Server 7.0/Query Analyzer）。
　　连接到 SQL Server 并设定将在其中创建表的当前数据库。
　　键入或复制以下查询并将它们粘贴到 Query Analyzer 的查询窗口：
　　select ckey1,col2 from testtable where ckey1 = 'a'
　　
　　select nkey1,col2 from testtable where nkey1 = 5000
　　
　　按 CTRL+E 执行这两个查询。
　　停止 Profiler
　　
　　单击红色的正方形以停止 Profiler 跟踪。
　　将 .trc 装载到 Index Tuning Wizard
　　
　　从 Profiler 菜单中选择 Tools/Index Tuning WizardsU 启动 Index Tuning Wizard。单击 Next。
　　选择要分析的数据库。单击 Next。
　　保持 I have a saved workload file 选项按钮被选，然后单击 Next。
　　选择 My workload file 选项按钮，找到用 Profiler 创建的 .trc 文件，然后单击 Next。
　　在 Select Tables to Tune 对话框中，选择需要进行分析的表，然后单击 Next。
　　Index Tuning Wizard 将在 Index Recommendations 对话框中指出应创建的索引。单击 Next。
　　此向导可让您选择是立即创建索引，调度将在以后自动执行的索引创建任务还是创建带创建索引命令的 T-SQL 脚本。选择需要的选项，然后单击 Next。
　　单击 Finish。
　　Index Tuning Wizard 为示例数据库和工作负荷生成的 T-SQL。
　　/* Created by:Index Tuning Wizard */
　　/* Date: 9/7/98 */
　　/* Time:6:42:00 PM */
　　/* Server:HENRYLNT2 */
　　/* Database :test */
　　/* Workload file :E:/mssql7/Binn/profiler_load.sql */
　　USE [test]
　　BEGIN TRANSACTION
　　CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1])
　　if (@@error <> 0) rollback transaction
　　CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1])
　　if (@@error <> 0) rollback transaction
　　COMMIT TRANSACTION
　　Index Tuning Wizard 为示例表和数据所建议的索引就是我们预期的索引。ckey1 只有 5 个唯一值，且每一个值都有 2,000 行。假定其中的一个示例查询 (select ckey1, col2 from testtable where ckey1 = 'a') 要求根据 ckey1 中的某个值对表进行检索，那么在 ckey1 列中创建聚集索引是有意义的。第二个查询 (select nkey1, col2 from testtable where nkey1 = 5000) 根据列 nkey1 的值提取一行。Nkey1 唯一，且有 10,000 行，因此在该列创建非聚集的索引是有意义的。
　　
　　Profiler/Index Tuning Wizard 组合在涉及许多表和许多查询的实际数据库服务器环境中功能非常强大。当数据库正在进行典型查询时，请使用 Profiler 记录 .trc 文件。然后将 .trc 文件装载到 Index Tuning Wizard，以确定是否创建了正确的索引。根据 Index Tuning Wizard 中的提示自动生成并调度索引创建作业以便在非尖峰时刻运行。定期运行 Profiler/Index Tuning Wizard（比如每周）以查看数据库服务器中所执行的查询是否有较大改动，如果是，则可能需要不同的索引。定期使用 Profiler/Index Tuning Wizard 有助于数据库管理员在查询工作负荷改变以及数据库大小随着时间而增加的同时，保持 SQL Server 以最佳状态运行。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索“Index Tuning Wizard”和“Index Tuning Recommendations”。
　　
　　将 Profiler 信息加载到 SQL Server 表以进行分析
　　
　　Profiler 提供的另一个选项是将信息记录在 SQL Server 表中。完成后，就可以查询整个表以确定是否有某些查询消耗了过多资源。
　　
　　将 Profiler 信息记录在 SQL Server 表中
　　
　　从 SQL Server Enterprise Manager 菜单中选择 Tools/SQL Server Profiler 启动 Profiler。
　　按 CTRL+N 组合键新建 Profiler 跟踪。
　　键入跟踪的名称。
　　单击 Capture to Table:复选框，然后选择要将 Profiler 信息输出到其中的 SQL Server 表。
　　单击 OK。
　　结束后，单击红色的正方形停止 Profiler 跟踪。
　　用 Query Analyzer 分析 Profiler 中记录的信息
　　在将这些信息记录到 SQL Server 表中后，可以用 Query Analyzer 计算出系统中的哪些查询消耗资源最多。这样，数据库管理员就可以集中时间改进最需要帮助的查询。例如，通常用以下查询分析从 Profiler 记录到 SQL Server 表中的数据。此查询检索数据库服务器中消耗 CPU 资源最多的头 3 项。返回读和写 I/O 信息以及查询的持续时间（用毫秒计）。如果用 Profiler 记录了大量的信息，那么在这个表中创建索引以加快分析查询是有意义的。例如，如果 CPU 即将成为分析这个表的一个重要标准，那么在 CPU 列创建非聚集索引应该是一个不错的主意。
　　
　　select top 3 TextData,CPU,Reads,Writes,Duration from profiler_out_table order by cpu desc
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“Viewing and Analyzing Traces”、“Troubleshooting SQL Server Profiler”、“Tips for Using SQL Server”、“Common SQL Server Profiler Scenarios”、“Starting SQL Server Profiler”和“Monitoring with SQL Server Profiler”。
　　
　　Query Analyzer
　　I/O 统计信息
　　Query Analyzer 的 Connections Options 对话框 General 选项卡中提供了一个 Show stats I/O 选项。选择此复选框可以获取有关 Query Analyzer 中正在执行的查询所消耗 I/O 量的信息。
　　
　　例如，当选择 Show stats I/O 选项时，查询“select ckey1, col2 from testtable where ckey1 = 'a'”除返回结果集以外，还返回以下 I/O 信息：
　　
　　Table 'testtable'.Scan count 1, logical reads 400, physical reads 382, read-ahead reads 400.
　　同样，当选择 Show stats I/O 选项时，查询“select nkey1, col2 from testtable where nkey1 = 5000”除了返回结果集以外，还返回以下 I/O 信息：
　　
　　Table 'testtable'.Scan count 1, logical reads 400, physical reads 282, read-ahead reads 400.
　　使用 I/O 统计信息是一种监视查询调整效果的有效方法。例如，在此示例表中创建 Index Tuning Wizard 在上面所推荐的两个索引，然后再次运行查询。
　　
　　在“select ckey1,col2 from testtable where ckey1 = 'a'”的查询中，聚集索引改进性能的情况如下所示。假定查询需要提取该表的 20%，则性能改进应该是比较合理的：
　　
　　Table 'testtable'.Scan count 1, logical reads 91, physical reads 5, read-ahead reads 32.
　　在“select nkey1,col2 from testtable where nkey1 = 5000”的查询中，创建非聚集索引对于查询的性能有着很显著的影响。假定此查询只需要从 10,000 行的表中提取一行，那么用非聚集索引改善性能应该是比较合理的：
　　
　　Table 'testtable'.Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
　　ShowPlan
　　通过显示 Query optimizer 正在执行的任务的详细信息，使用 ShowPlan 可将注意力集中在有问题的 SQL 查询上。SQL Server 7.0 提供 ShowPlan 的文本版和图形版。通过用 CTRL+L 执行 SQL 查询，可以将 Graphical ShowPlan 的输出显示在 Query Analyzer 的 Results 窗格中。图标表示如果查询已执行，那么 Query optimizer 应该已执行的操作。箭头表示查询的数据流的方向。将鼠标放置在操作图标上，可以显示出各个操作的详细信息。执行 set showplan_all on 命令可以在基于文本的 ShowPlan 中显示出等价的信息。如果要跳过操作的详细信息的显示，以减少显示 Query optimizer 操作的基于文本的 ShowPlan 的输出，可执行命令 set showplan_text on。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“Graphical ShowPlan”、“Using ShowPlan to Monitor a Database Query”、“worktables”和“Understanding Nested Loops Joins”。
　　
　　ShowPlan 输出的示例
　　使用前面所定义的查询示例并在 Query Analyzer 中执行 set showplan_text on。
　　
　　查询：
　　select ckey1,col2 from testtable where ckey1 = 'a'
　　基于文本的 ShowPlan 输出：
　　|--Clustered Index Seek(OBJECT:([test].[dbo].[testtable].[testtable2]), SEEK:([testtable].[ckey1]='a') ORDERED)
　　上面的查询利用“Clustered Index Seek”所示 ckey1 列上的聚集索引。
　　
　　等量的 Graphical ShowPlan 输出：
　　
　　　
　　
　　图 3 使用聚集索引的查询的 Graphical ShowPlan 输出
　　
　　如果将聚集索引从表中删除，那么查询需要使用表扫描。以下 ShowPlan 输出显示了行为上的改动。
　　
　　基于文本的 ShowPlan 输出：
　　|--Table Scan(OBJECT:([test].[dbo].[testtable]), WHERE:([testtable].[ckey1]='a'))
　　等量的 Graphical ShowPlan 输出：
　　
　　　
　　
　　图 4 执行表扫描的查询的 Graphical ShowPlan 输出
　　
　　注意：在小型表中进行表扫描无须担心。对于小型表，表扫描是最有效的检索信息的方法。但是对于大型表，如果 ShowPlan 提出表扫描则是一个警告，说明该表可能需要更好的索引，或者需要对已有索引的统计信息进行更新（这可以使用 UPDATE STATISTICS 命令来完成）。SQL Server 7.0 可自动更新索引。使 SQL Server 自动维护索引统计信息是一个不错的主意，因为它有助于确保查询始终可以使用良好的索引统计信息。
　　
　　查询：
　　select nkey1,col2 from testtable where nkey1 = 5000
　　基于文本的 ShowPlan 输出：
　　|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([test].[dbo].[testtable]))
　　|--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=5000) ORDERED)
　　等量的 Graphical ShowPlan 输出：
　　
　　　
　　
　　图 5 利用非聚集索引的查询的 Graphical ShowPlan 输出（第 1 部分）
　　
　　　
　　
　　图 6 利用非聚集索引的查询的 Graphical ShowPlan 输出（第 2 部分）
　　
　　上面的查询使用 nkey1 列上的非聚集索引。这由 nkey1 列上的“Index Seek”操作指定。“Bookmark Lookup”操作表示 SQL Server 需要将指针从索引页跳到表的数据页以检索所请求的数据。因为查询要求 col2 列，而该列不是非聚集索引的一部分，所以需要指针跳动。
　　
　　查询：
　　select nkey1 from testtable where nkey1 = 5000
　　基于文本的 ShowPlan 输出：
　　|--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=[@1]) ORDERED)
　　等量的 Graphical ShowPlan 输出：
　　
　　　
　　
　　图 7 隐蔽查询的 Graphical ShowPlan 输出
　　
　　上面的查询将 nkey1 上的非聚集索引作为覆盖索引作用。请注意此查询不需要“Bookmark Lookup”（书签查找）操作。这是因为非聚集索引可提供查询（包括 SELECT 和 WHERE 子句）所需要的全部信息。这意味着指针不需要从非聚集索引页跳动到数据页。比需要书签查找操作的情况减少了 I/O。
　　
　　Performance Monitor
　　Performance Monitor 为数据库服务器中所发生的 Windows 和 SQL Server 操作提供宝贵的信息。有关 SQL Server 的特殊计数器，请在 SQL Server Books Online 中搜索字符串组合“SQL Server:”和“object”。
　　
　　在 Performance Monitor 的图形模式中，请注意 Max 和 Min 值。不要太强调平均，因为数据点的严重两极分化已使其没有太大的实际意义。研究图形形状，然后将它与 Min/Max 进行比较，以获得对此行为的准确了解。使用 键以便用白线突出显示计数器。
　　
　　可以在用 Performance Monitor 将所有可用的 Windows NT 和 SQL Server Performance Monitor 对象/计数器记录在日志文件中的同时，交互性地查看 Performance Monitor（图表模式）。采样间隔的设置确定日志文件大小的增长速度。日志文件可以很快就变得非常大（例如，如果打开所有的计数器，且采样间隔为 15 秒，则可以在 1 小时之内增加 100 MB）。但测试服务器有望提供几十亿字节的可用空间来存储这些类型的文件。但是，如果节省空间很重要，那么试着在运行时使用较大的日志记录间隔，以便 Performance Monitor 不会如此频繁地对系统进行采样。试试使用 30 或 60 秒的时间间隔。这样就可以既做到以合理的频率对所有的计数器进行采样，又能保持较小的日志文件。
　　
　　Performance Monitor 还消耗少量的 CPU 和磁盘 I/O 资源。如果系统没有大量富余的磁盘 I/O 和/或 CPU，那么可以考虑从另一台计算机中运行 Performance Monitor 以监视网络上的 SQL Server（仅图形模式 - 将性能信息记录在本机的 SQL Server 上比在局域网或 LAN 中发送信息效率高），或者仅记录最关键的计数器。
　　
　　将性能测试运行过程中所有可用的计数器记录在一个文件中以便以后分析是一个不错的主意。这样就可以在以后进一步检查任何计数器。配置 Performance Monitor 以便将所有计数器记录在日志文件中，同时以其它模式（如图形模式）监视最感兴趣的计数器。这样可记录所有的信息，但是只有最感兴趣的计数器可以在性能运行的同时呈现在整齐的 Performance Monitor 图形中。
　　
　　启动记录功能
　　打开 Performance Monitor。
　　从此菜单中选择 View/Log。
　　单击有 + 符号的按钮。
　　记录所有计数器的一个简单方法是左键单击 Add to Log 对话框中的第一个对象。
　　按住 SHIFT 键，使用 PAGE DOWN 键突出显示所有计数器。
　　单击 Add。
　　单击 Done。
　　从此菜单中选择 Options/Log。
　　在 File Name: 中为要记录的性能信息选择或创建文件名。
　　单击 Start Log。
　　停止记录功能
　　从此菜单中选择 Options/Log。
　　单击 Stop Log。
　　将记录的信息加载到 Performance Monitor 中进行分析
　　从此菜单中选择 View/Log。
　　从此菜单中选择 Options/Data FromU。
　　单击 Log File:选项按钮。
　　单击有“U”标记的按钮，然后使用 Open Input Log File 文件浏览器窗口找到并打开日志文件。双击此文件名。
　　单击 OK。
　　单击有 + 符号的按钮。
　　使用 Add to Chart 对话框将需要的计数器添加到图形显示中。选择要添加的 object/counter（对象/计数器）组合，然后单击 Add。
　　如何将 Performance Monitor 所记录的事件与时间点关联
　　此功能对于观察数据库服务器在给定的时间段内所发生的事件十分方便：
　　
　　使用前面的说明在 Performance Monitor 图表模式中选择并显示所需要的对象/计数器。
　　从此菜单中选择 Edit/Time Window。
　　Input Log File Timeframe 对话框将出现。单击所提供的时间窗口滚动条并按住鼠标左按钮，可以调整将显示在 Performance Monitor 图表中所记录数据的开始和停止时间窗口。
　　单击 OK 将图表重新设置为仅显示为所选的时间窗口记录的数据。
　　要查看的关键 Performance Monitor 计数器
　　（Physical 或 Logical）Disk Queue > 2
　　这部分要求观察几个 Performance Monitor 磁盘计数器。要启用这些计数器，从 Windows NT 命令窗口运行命令“diskperf -y”，然后重新启动 Windows NT。
　　
　　当正在进行磁盘排队的物理硬盘获得 I/O 处理时，将阻止磁盘 I/O 请求。这些驱动器的 SQL Server 响应时间延长，从而需要更多的查询执行时间。
　　
　　如果使用 RAID，必需知道有多少个物理硬盘与 Windows NT 视为一个物理驱动器的各个磁盘阵列相连，才能计算出每个物理驱动器的磁盘队列。请教硬件专家关于 SCSI 通道和物理驱动器分布的概念，以了解 SQL Server 数据如何放置在每个物理驱动器中以及每个 SCSI 通道中分布多少 SQL Server 数据。
　　
　　可以有几种方法通过 Performance Monitor 查看磁盘队列。逻辑磁盘计数器对应通过磁盘管理器分配的逻辑驱动器号，而物理磁盘计数器则对应磁盘管理器视为单个物理磁盘设备的内容。请注意磁盘管理器视为单个物理设备的既可以是单个硬盘，也可以是包含几个硬盘的 RAID 阵列。Current Disk Queue（当前磁盘队列）是磁盘队列的即时测量所得，而 Average Disk Queue（平均磁盘队列）则计算 Performance Monitor 采样期间的平均磁盘队列。请注意以下情形中的所有计数器：Logical Disk:Average Disk Queue > 2，Physical Disk:Average Disk Queue > 2，Logical Disk:Current Disk Queue > 2，或 Physical Disk:Average Disk Queue > 2。
　　
　　这里所推荐的测量数据是为每个物理硬盘所指定的。如果用这种方法测量 RAID 阵列的磁盘队列，那么这些测量数据应除以 RAID 阵列中的物理硬盘数，才能确定每个物理硬盘中的磁盘队列。
　　
　　注意 在保存 SQL Server 日志文件的物理硬盘或 RAID 阵列中，磁盘队列不是一个很有用的测量数据，因为 SQL Server Log Manager 的队列不会将 SQL Server 日志文件的多个 I/O 请求排队。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“monitoring disk activity”。
　　
　　System:Processor Queue Length > 2 (per CPU)
　　这意味着服务器的处理器正在接受超过它们作为组能够进行处理的工作请求。因此，Windows 需要将这些请求排成队列。
　　
　　某些处理器队列实际是良好的总体 SQL Server I/O 性能的指示器。如果没有处理器队列，且 CPU 利用率很低，那么可能意味着系统的其它地方出现了性能瓶颈，而最有可能的就是磁盘子系统。处理器队列中有合理的工作量意味着 CPU 没有闲置，且系统的其它部分与 CPU 保持同步。
　　
　　根据经验法则，好的处理器队列数量是数据库服务器中的 CPU 数乘以 2。
　　
　　应对明显超过此计算值的处理器队列进行调查。过多的处理器队列会消耗查询时间。可在处理器队列中分配几个不同的活动。消除强制存储分页和软内存分页有助于节省 CPU 资源。其它有助于减少处理器队列的方法包括 SQL 查询调整，提取更好的 SQL 索引以减少磁盘 I/O（并因此而减少 CPU 的工作负荷）或者在系统中添加更多的 CPU（处理器）。
　　
　　Hard Paging-Memory:Pages/sec > 0 或 Memory:Page Reads/sec > 5
　　Memory:Pages/sec > 0 或 Memory:Page Reads/sec > 5 表示 Windows 将通过磁盘解决内存引用（强制分页错误）。这需要消耗磁盘 I/O 和 CPU 资源。Memory:Pages/sec 是一个指示 Windows 正在执行的分页数量和数据库服务器当前的 RAM 配置是否充足的有效指示器。Performance Monitor 中的强制分页信息的子集是 Windows 每秒钟必须读取分页文件以解决内存引用的次数，它用“Memory:Pages Reads/sec”表示。如果“Memory:Pages Reads/sec > 5，那么这对于性能是不利的。
　　
　　自动 SQL Server 内存优化尽力动态地调整 SQL Server 内存使用以避免出现分页。每秒钟出现少量的分页是正常的，但是过多的分页则需要纠正。
　　
　　如果 SQL Server 自动调整内存，那么添加更多的 RAM 或从数据库服务器中删除其它应用程序可能会有助于将 Memory: Pages/sec 降到合理的级别。
　　
　　如果正在数据库服务器上手动配置 SQL Server 内存，那么可能有必要减少为 SQL Server 分配的内存，从数据库服务器中删除其它应用程序或者向数据库服务器中添加更多的 RAM。
　　
　　将 Memory: Pages/sec 保持在零或接近于零有助于改善数据库服务器的性能。这意味着 Windows 及其所有的应用程序（包括 SQL Server）不通过分页文件来满足内存请求中的任何数据，所以服务器中的 RAM 量足够。如果 Pages/sec 稍大于零也没有关系，但是请记住每次从分页文件而不是 RAM 中检索数据时，需要付出较高的性能代价（磁盘 I/O）。
　　
　　有必要花一点时间来了解“Memory: Pages Input/sec”和“Memory: Pages Reads/sec”之间的区别。“Memory: Pages Input/sec”表示从磁盘引入的用以解决页错误的 Windows 4 KB 页的实际数目。“Memory: Pages Reads/sec”表示每秒钟需要多少个磁盘 I/O 请求才能解决页错误，它从一个稍微不同的角度看待所发生的错误。因此，一个页读取可以包含几个 Windows 4 KB 页。当数据包的大小增加（64 KB 或更大）时，磁盘 I/O 就运行得更好，因此可能有必要从这两方面来考虑。还需记住的重要一点是对于硬盘，完成一个 4 KB 的读或写所花的时间可能与完成一个 64 KB 读或写所花的时间相同。考察以下情形；可以想象，200 个页读取（每次读取 8 个 4 KB 页）比 300 个页读取（每次仅读取一个 4 KB 页）的速度要快。并且请注意我们比较出 1,600 个 4 KB 页读取比 300 个 4 KB 页读取速度要快。这里的关键事实适用于所有的磁盘 I/O 分析：不要仅仅注意 Disk Bytes/sec（磁盘字节/秒）数，还要注意 Disk Transfers/sec（磁盘传输/秒）数，因为两者是相关的。这将在后面的磁盘 I/O 部分进行深入讨论。
　　
　　将“Memory: Pages Input/sec”和与 Windows NT 分页文件相关的所有驱动器中的“Logical Disk: Disk Reads/sec”进行比较，并且将“Memory: Page Output/sec”和与 Windows 分页文件相关的所有驱动器中的“Logical Disk: Disk Writes/sec”进行比较，因为它们提供一种关于磁盘 I/O 与分页而不是其它应用程序（即 SQL Server）的严格相关程度的测量方法。隔离分页文件 I/O 活动的另一种简单方法是确保分页文件位于与其它所有 SQL Server 文件不同的驱动器组中。将分页文件与 SQL Server 文件隔开还可以帮助改善磁盘 I/O 性能，因为它允许与分页相关的磁盘 I/O 和与 SQL Server 相关的磁盘 I/O 并行执行。
　　
　　Soft Paging-Memory: Pages Faults/sec > 0
　　Memory:Pages Faults/sec > 0 表示 Windows NT 正在分页，但是在计数器中包括强制存储分页和软内存分页。在前面的部分，我们讨论了强制存储分页。软内存分页表示数据库服务器中的某些应用程序所请求的内存分页在 RAM 内部但却在 Windows 工作集外部。Memory: Page Faults/sec 有助于得出正在发生的软内存分页的数目。没有称为 Soft Faults/sec 的计数器。而应通过公式
　　
　　“Memory: Pages Faults/sec”-“Memory: Pages Input/sec”= Soft Page Fault/sec 计算每秒钟所发生的软错误的数目。
　　
　　要确定是否是 SQL Server 而不是另一过程引发过多分页，请监视 SQL Server 过程的 Process: Page Faults/sec 计数器，并注意 sqlserver.exe 每秒页错误的数目是否与 Memory: Pages/sec 的数目接近。
　　
　　对于性能来说，软错误不如硬错误那么糟糕，因为软错误消耗的是 CPU 资源，而硬错误消耗磁盘 I/O 资源。性能最好的环境是既没有软错误，也没有硬错误。
　　
　　请注意在 SQL Server 实际首次存取它的数据高速缓存页之前，第一次存取每一页都会引起软错误。因此，不必担心 SQL Server 首次启动且首次执行数据高速缓存时所产生的初始软错误。
　　
　　有关内存优化的详细信息，请在 SQL Server Books Online 中搜索字符串“monitoring memory usage”。
　　
　　监视处理器
　　使所有的服务器处理器保持繁忙以获得最佳性能，但不要繁忙到发生处理器瓶颈的程度。性能优化的难题在于如果 CPU 不是瓶颈，那么其它部分便是瓶颈（最有可能的就是磁盘子系统），因此浪费了 CPU；CPU 通常是最难扩充的资源（超过某些特定配置级别，如当前许多系统中是 4 或 8），因此如果 CPU 利用率超过 95%，应视为好的现象。同时，应监视事务的响应时间以确保它们在合理的范围之内；如果不是，>95% 的 CPU 使用率仅仅意味着对于可用的 CPU 资源来说，工作负荷过高，要么增加 CPU，要么减少或调整工作负荷。
　　
　　查看 Performance Monitor 计数器“Processor: Processor Time %”以确保每个 CPU 上的所有处理器的利用率均低于 95%。“System:Processor Queue”是 Windows NT 系统上的所有 CPU 的处理器队列。如果每个 CPU 的“System:Processor Queue”大于 2，则表明出现 CPU 瓶颈。当检测到 CPU 瓶颈时，有必要在服务器上添加处理器或减少系统中的工作负荷。要减少工作负荷，可以调整查询或者改进索引以减少 I/O，从而减少 CPU 使用率。
　　
　　当怀疑出现 CPU 瓶颈时要查看的另一个 Performance Monitor 计数器可能是“System:Context Switches/sec”，因为它表示 Windows NT 和 SQL Server 每秒钟必须从执行一个线程转变为执行另一个线程的次数。这需要消耗 CPU 资源。环境切换是多线程、多处理器环境的正常组成部分，但是过多的环境切换将使系统停顿。解决的办法是如果有处理器队列，则仅关注环境切换。如果观察到处理器队列，那么请将环境切换级别作为调整 SQL Server 性能时的一个标准。考虑使用轻量级的池选项以便 SQL Server 切换到基于光纤的调度模式，而不是默认的基于线程的调度模式。将光纤当作轻量级线程。使用命令 sp_configure 'lightweight pooling',1 启用基于光纤的调度。查看处理器队列和环境切换以监视此效果。
　　
　　DBCC SQLPERF (THREADS) 提供映射回 spid 的有关 I/O、内存和 CPU 使用情况。执行以下 SQL 查询以调查当前消耗 CPU 时间最多的项："select * from master.sysprocesses order by cpu desc."
　　
　　磁盘 I/O 计数器
　　“Disk Write Bytes/sec”和“Disk Read Bytes/sec”计数器用每个逻辑驱动器的每秒字节数表示数据吞吐量。将这些数字与“Disk Reads/sec”和“Disk Writes/sec”一起仔细考虑。不要因为每秒的字节数较低就以为磁盘 I/O 子系统不忙！请记住一个硬盘每秒钟可以支持 75 个非连续和 150 个连续的磁盘读和磁盘写。
　　
　　监视与 SQL Server 文件相关的所有驱动器的“Disk Queue Length”，并确定哪些文件与过长的磁盘队列相关。
　　
　　如果 Performance Monitor 显示某些驱动器没有另一些驱动器繁忙，便有机会将 SQL Server 文件从出现瓶颈的驱动器中移到不忙的驱动器中。这有助于将磁盘 I/O 活动更均匀地分布在硬盘中。如果某个大型的驱动器池正在为 SQL Server 文件使用，那么磁盘队列的解决方案是通过在这个驱动器池中添加更多的物理驱动器来加大池的 I/O 容量。
　　
　　磁盘队列可能是某个 SCSI 通道已被 I/O 请求饱和的征兆。Performance Monitor 不能直接检测是否是这种情况。硬件厂商可能可以提供某些工具来帮助检测某个 RAID 控制器所服务的 I/O 数以及该控制器是否对 I/O 请求进行排队。如果许多磁盘驱动器（10 个或更多）连到了此 SCSI 通道，且它们均以全速执行 I/O，那么这种情况更有可能发生。这种情况的解决方案是取出一半磁盘驱动器，然后将它们连到另一个 SCSI 通道或 RAID 控制器以平衡这些 I/O。通常，在 SCSI 通道中重新平衡驱动器要求重建 RAID 阵列并完全备份/恢复 SQL Server 数据库文件。
　　
　　Performance Monitor 图形输出示例
　　图 8 表示可用 Performance Monitor 进行观察的典型计数器。请注意当前所观察的计数器是 Processor Queue Length。按 用亮白色突出显示当前的计数器。这有助于将当前计数器与其它正在观察的计数器区分开来，当用 Performance Monitor 同时观察许多计数器时，这种方法尤其有用。
　　
　　请注意 Processor Queue Length 的 Max（最大）值是 22.000。Performance Monitor 图形的 Max、Min 和 Average 值仅涵盖 Graph Time（图形时间）所示图形的当前时间窗口。默认情况下，Graph Time 可涵盖 100 秒。要监视更长时间，并确保获得这些时间段中有代表性的 Max、Min 和 Average 值，可使用 Performance Monitor 的记录功能。
　　
　　Processor Queue（处理器队列）图形线条的形状表示 Max 值 22 仅持续了一段很短的时间。但是在值 22 的前面有一段时间 Processor Queue Length 大于 5（这可从图上看出，图中 100% 表示 22，在值 22 的前面有一段时间图形的值超过 25%，即大约为 5。）在本例中，数据库服务器 //HENRYLNT2 只有一个处理器，不能承受 Processor Queue Length 大于 2。因此，Performance Monitor 指出这台机器上的处理器负荷有时过重，需进一步调查以减少处理器上的负荷，或者在 //HENRYLNT2 上添加更多的处理器以处理这些时间段中的较高的处理器工作负荷。
　　
　　
　　
　　图 8 Performance Monitor 图形输出
　　
　　其它性能主题
　　减少网络流量和数据库服务器的资源消耗
　　通过易于使用的接口（如 ADO/RDO/DAO 数据库 API）完成 SQL 作业的数据库程序员，依然有责任密切关注所生成的结果集。ADO/RDO/DAO 为程序员提供优秀的数据库开发接口，这些接口具有丰富的 SQL 行集功能，且不要求程序员具有丰富的 SQL 编程经验。但这同时也意味着需要付出一定的代价。如果由于程序员没有仔细考虑其应用程序返回到客户端的数据量，不注意 SQL Server 索引放置的位置以及 SQL Server 数据的排列方法，就会引起性能问题。SQL Profiler、Index Tuning Wizard 和 ShowPlan 对于找到和修复这些有问题的查询十分有帮助。
　　
　　通过消除选择列表中不需要返回的列或者仅返回所需要的行来减小返回的结果集。这有助于减少 I/O 和 CPU 的消耗。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“Optimizing Application Performance Using Efficient Data Retrieval”、“Understanding and Avoiding Blocking”和“Application Design”。
　　
　　死锁
　　如果在构造访问 SQL Server 的应用程序时，使事务按相同的时间顺序访问所有用户事务中的表，则可以避免死锁。在应用程序设计过程中，有必要尽早向 SQL 应用程序开发人员明确阐明按时序访问表的概念。这有助于避免死锁问题，以免将来需要付出更高的代价解决这个问题。
　　
　　减少 SQL 查询 I/O 并缩短事务时间：虽说这是一种防止死锁的迂回办法，但对所有的查询都应使用。它可能会有一些帮助，因为它可以加快查询的速度，从而减少将资源锁定的时间以及所有锁定的竞争（包括死锁）。使用 SQL Query Analyzer 的 SHOW STATS I/O 来确定与大型查询有关的逻辑页提取的数目。考虑选择 SQL Query Analyzer 的“Show query plan”选项时所使用的索引。考虑索引的放置或者重新设计 SQL 查询以使它更有效，从而使用更少的 I/O。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“Avoiding Deadlocks”、“Troubleshooting Deadlocking”、“Detecting and Ending Deadlocks”和“Analogy to Nonserializable Transactions”。
　　
　　只要有任何可能就应避免的 SQL
　　在 SQL 查询中使用不等号可以迫使数据库使用表扫描来求取不等式的值。如果在超大型表中定期运行这些查询，会产生较高的 I/O。
　　
　　示例：
　　
　　WHERE != some_value
　　WHERE <> some_value
　　其中带有 NOT 的任何 WHERE 表达式
　　如果需要运行这些查询，试着重新构建查询以删除 NOT 关键字。
　　
　　示例：
　　
　　不用：
　　select * from tableA where col1 != "value"
　　试着使用；
　　select * from tableA where col1 < "value" or col1 > "value"
　　如果索引创建在 col1，这使 SQL Server 得以使用这种索引（在这种情况下用聚集索引更好），而不需要求助于表扫描。
　　
　　灵巧化标准
　　在极热（存取频繁）的表中，如果有几列是 SQL 应用程序不经常需要的，则将它们移到另一个表是比较有意义的。删除的列越多，就越有利于减少 I/O 并提高性能。有关详细信息，请在 SQL Server Books Online 中搜索字符串“Logical Database Design”和“Normalization”。
　　
　　分区视图
　　SQL Server 7.0 可以通过视图对表进行水平分区。当数据库用户希望维持引用某个表名称的 SQL 查询，但数据检索的本性总是查询数据的固定分段时，使用分区可以改善 I/O 性能。例如，假定有一个记录所有销售部门一年销售情况的超大型表，并假定这个表中的所有检索都基于一个销售部门。在这种情况下，可以使用分区视图。可以为每个销售部门定义一个销售表，在每个表的销售部门列中定义一个约束，然后在所有的表中创建一个视图，以形成分区视图。销售部门列中的约束由 Query optimizer 使用。当查询视图时，与查询中所提供销售部门值不匹配的所有销售部门表都将被 Query optimizer 忽略，而不对这些基表执行 I/O。通过减少 I/O 改善了查询性能。
　　
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“Scenarios for Using Views”、“Create View”、“Using Views with Partitioned Data”、“Modifying Data Through a View”、“Copying To or From a View”和“Partitioning”。
　　
　　复制和备份性能
　　确保磁盘 I/O 子系统和 CPU 运行很好，可以改善所有 SQL Server 操作的性能。其中当然包括复制和备份。事务复制和事务日志备份涉及到读取事务日志文件。快照复制和备份执行数据库文件的连续扫描。SQL Server 7.0 的新型存储结构已经过改进，使这些操作既快速又有效（只要数据库服务器的 CPU 或磁盘子系统中没有发生排队现象）。
　　
　　有关性能优化复制和备份/恢复的详细信息，请在 SQL Server Books Online 中搜索字符串“Replication Performance”、“Optimizing Backup and Restore Performance”、“Creating and Restoring Differential Database Backups”、“Creating and Applying Transaction Log Backups”、“Using Multiple Media or Devices”、“Minimizing Backup and Recovery Times in Mission-Critical Environments”、“Backup/Restore Architecture”和“SQL Server 7.0 on Large Servers”。
　　
　　特殊的磁盘 I/O 调整方案：EMC 对称集成高速缓存磁盘队列
　　对于在 EMC Symmetrix Enterprise Storage Systems（EMC 对称企业存储系统）中执行的 SQL Server 数据库系统，应记住几种磁盘 I/O 平衡方法，因为 EMC 对称存储的独特特性有助于避免磁盘 I/O 瓶颈问题并获得最佳性能。
　　
　　对称存储系统包含高达 16 GB 的 RAM 高速缓存，并在磁盘阵列中包含内部处理器，这有助于加速数据的 I/O 处理，而无需使用托管服务器的 CPU 资源。要了解怎样平衡磁盘 I/O，请关注 Symmetrix（对称）框内的 4 个主要组件。16 GB 高速缓存是其中之一。最大可用 32 个 SA 通道将 32 个 SCSI 卡从 Windows NT 托管服务器连接到 Symmetrix，所有这些通道可以同时请求 16 GB 高速缓存中的数据。而 Symmetrix 框中最大有 32 个称为 DA 控制器的连接器，它们是内部 SCSI 控制器，用来将 Symmetrix 中的所有内部磁盘驱动器连到内部高速缓存中。这样，在 Symmetrix 中就形成了硬盘。
　　
　　关于 EMC 硬盘的备注：它们是 SCSI 硬盘，与本文中所讨论的其它 SCSI 驱动器具有相同的 I/O 能力（这里适用 75/150 规则）。EMC 技术通常使用的一个功能是“超级卷”（hyper-volumes）。超级卷定义为 EMC 硬盘的逻辑分区，对于 Windows NT 磁盘管理器来说，超级卷就像另一个物理驱动器，因此用 Windows NT 磁盘管理器可以像对其它任何磁盘驱动器一样对它们进行操作。可以在每个物理驱动器上定义多个超级卷。当在 EMC 存储中执行数据库性能优化时，很重要的一点是一定要和 EMC 域工程师密切合作，以了解超级卷是如何定义的（如果有的话），其原因在于避免物理驱动器的数据库 I/O 超载很重要。如果以为两个或多个超级卷是单独的物理驱动器，而实际上它们是同一个物理驱动器上的两个或多个超级卷，那么就极有可能发生超载。
　　
　　应该在不同的 DA 控制器中平均分配 SQL Server I/O 活动。这是因为 DA 控制器是分配给所定义的硬盘组的。本文前面已讨论过，SCSI 控制器不可能发生瓶颈。DA 控制器不大可能发生 I/O 瓶颈，但是与 DA 控制器相关的驱动器组较有可能出现瓶颈。在 DA 控制器及其相关磁盘驱动器环境中，SQL Server 磁盘 I/O 平衡的方法与其它任何厂商的磁盘驱动器和控制器的方法相同。
　　
　　如果要监视 DA 通道或单独的物理硬盘上的 I/O，可以从 EMC 技术支持人员那里获取帮助，因为这些 I/O 活动发生在 EMC 内部高速缓存的下面，Performance Monitor 无法看到。EMC 存储单元具有内部监视工具，此工具允许 EMC 技术支持工程师监视 Symmetrix 内部的 I/O 统计信息。Performance Monitor 只能通过从某个 SA 通道出来的 I/O 看到进出于 EMC 存储单元的 I/O。这一信息足以说明某个特定的 SA 通道正在对磁盘 I/O 请求进行排队，但是无法识别哪个磁盘或哪些磁盘引起磁盘排队。如果某个 SA 通道正在排队，并不一定说明瓶颈是由此 SA 通道引起的，因为引起问题的也有可能是磁盘驱动器（而且此可能性更高）。在 SA 通道和 DA 通道 + 驱动器之间隔离磁盘 I/O 瓶颈的一种方法是在托管服务器中再添加一个 SCSI 卡，并将它连接到另一个 SA 通道。如果 Performance Monitor 显示这两个 SA 通道中的 I/O 流量没有改变，并且磁盘队列依然存在，则说明瓶颈问题不是由 SA 通道引起的。隔离 I/O 瓶颈的另一种方法是使 EMC 工程师通过 EMC 监视工具监视 EMC 系统，并分析瓶颈是由哪些驱动器或 DA 通道引起的。
　　
　　将 SQL Server 活动在尽可能多的可用磁盘驱动器中平均分配。如果处理支持大量 I/O 的小型数据库，需仔细考虑超级卷的大小以使 EMC 技术工程师进行定义。假定 SQL Server 由一个 30 GB 的数据库组成，EMC 硬盘可以提供 23 GB 的容量，因此，将整个数据库放在两个驱动器上是有可能的。从易管理和节省成本的角度出发，这种方法看上去可能比较有吸引力，但是若从 I/O 性能的角度来看，则不然。一个 EMC 存储单元可能有 100 个以上的内部驱动器要处理。SQL Server 中仅涉及两个驱动器可能引起 I/O 瓶颈。可以考虑定义小型超级卷，每个大概有 2 GB。则可能有大约 12 个超级卷与给定的 23 GB 硬盘相关。假定需要 15 个 2 GB 的超级卷存储数据库。确保每个超级卷与单独的物理硬盘相关。不要在一个物理驱动器中使用 12 个超级卷，然后在另一个物理驱动器中使用另外 3 个超级卷，因为这与使用两个物理驱动器同理（两个驱动器中具有 150 个不连续 I/O 或 300 个连续 I/O）。但是，如果使用 15 个超级卷，且每个都与单独的物理驱动器相关，SQL Server 就可以用 15 个物理驱动器来提供 I/O（15 个驱动器中每秒钟有 1,125 不连续的 I/O 活动或 2,250 个连续的 I/O 活动）。
　　
　　同时考虑使用托管服务器中的几个 SA 通道以便在控制器中分配 I/O 工作。这对于支持多个 PCI 总线的托管服务器来说很有意义。本例中，考虑在每个托管服务器 PCI 总线中使用一个 SA 通道以便将 I/O 工作在 PCI 总线和 SA 通道中分配。在 EMC 存储系统中，每个 SA 通道与特定的 DA 通道相关，因此产生了特定的物理硬盘组。因为 SA 通道在 EMC 内部高速缓存中读写数据，所以不可能成为 I/O 瓶颈。记住 SCSI 控制器不可能发生瓶颈，所以最好的办法可能是集中时间在物理驱动器中平衡的 SQL Server 活动，而不必过多担心使用的 SA 通道的数量。
　　
　　查找其它信息
　　Microsoft SQL Server Books Online 提供有关 SQL Server 构架和数据库优化的信息，同时提供完整的命令语法和管理文档。可以从 SQL Server 安装媒体中将 SQL Server Books Online 安装在任何 SQL Server 客户机或服务器中。建议在频繁使用 SQL Server 的机器上将 SQL Server Books Online 安装在硬盘上以便于使用。
　　有关详细信息，请查阅 Microsoft TechNet。
　　有关 Microsoft SQL Server 的最新信息，包括有关 SQL Server 7.0 的其它白皮书，请访问 Microsoft SQL Server 的 Web 站点 http://www.microsoft.com/sql/ 。
　　Compaq 已更新它的 RAID 白皮书，其中有 50 页是关于数据库服务器性能的绝妙信息。请注意此白皮书中有 3 页针对于 Microsoft SQL Server 的信息是针对 6.5 版本的，不适用于 SQL Server 7.0。此白皮书的书名是“Configuring Compaq RAID Technology for Database Servers”（配置数据库服务器的 Compaq RAID 技术），其网址为 http://www.compaq.com/support/techpubs/whitepapers/ecg0110598.html。
　　Compaq Windows NT 集成小组名为“Disk Subsystem Performance and Scalability”（磁盘子系统性能和可伸缩性）的 30 页白皮书的网址为 http://www.compaq.com/support/techpubs/whitepapers/ecg0250997.html 。它详细讲述了 Compaq 硬盘和物理驱动器行为的硬件性能特性。此白皮书中的信息适用于 Compaq 或其它厂商所提供的 SCSI 硬盘。
　　Celko, Joe 所著的SQL for Smarties。Morgan Kaufmann Publishers，ISBN 1-55860-323-9。
　　本书中有一些非常有帮助的信息。其中包含常见问题的解决方案，如描述和查询分层结构数据。第 28 章主要讲述 SQL 查询的优化。