SQL Server 性能优化工具(3)

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 首次启动且首次执行数据高速缓存时所产生的初始软错误。
监视处理器

使所有的服务器处理器保持繁忙以获得最佳性能，但不要繁忙到发生处理器瓶颈的程度。性能优化的难题在于如果 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."