使用Perfmon和PAL工具查看Server性能--从性能监视器获得更多有用信息

23ee

这节着眼于硬件、OS和SQL Server瓶颈，按照问题可能性的排序：内存、磁盘 和CPU，介绍主要的组件。你也会学习SQL Server性能计数器，用以规划使用PerfMon来识别特殊的SQL Server问题条件。
瓶颈和SQL Server
瓶颈是指资源严重制约数据库性能。总会有这样那样的瓶颈------目标是确保没有单个组件严重延迟整个事务处理系统。这节将审查一些不同类型的瓶颈，并提供一些规范的指南来帮你识别资源竞争。SQL Server的性能与服务器性能紧密相关，因为查询处理持续时间依赖充足的内存、磁盘和CPU性能。
瓶颈的类型
大部分瓶颈可归为两类：基于配置和基于架构。每一类都能在每种资源类型（CPU、内存和磁盘）上引起瓶颈。尽管有很多潜在的问题，大部分服务器范围或实例范围的瓶颈倾向于基于配置，但是数据库架构瓶颈是数据库设计问题，特别是对于单个数据库（常见问题包括架构规范化、索引选择和统计）。
基于配置的瓶颈
SQL Server安装不需要专业知识，大部分的默认值对大多数部署而言足够。当性能和可扩展性成为要紧问题时，能够对OS和SQL Server做很多优化。基于配置的瓶颈包括OS配置，如内存设置，包括/3GB和/PAE ；I/O性能调校，如磁盘扇区对齐；及HBA队列深度优化。另外，有很多SQL Server基于配置的优化，如磁盘和日志文件布局，数据库自动增长设置，及sp_configure选项。
基于架构的瓶颈
架构瓶颈是特指应用程序的，因为它们与一个特定数据库的架构相关。大部分情况下，优化架构的最佳时间是应用程序设计期间，因为架构改变对应用程序的影响最小。基于架构的瓶颈说明了为何性能测试是软件开发项目中必不可少的一环，因为对于已经上线的应用程序很难改善性能。基于架构的瓶颈包括过于或完全没有规范化、索引缺失或过剩、丢失统计或者很差的聚集键选择。
规范的指南
这里会介绍有价值的PerfMon计数器，以及健康的计数器值的规范指南。规范指南可以作为问题的基线指示器，但是你也要收集其他证据来制作行动计划。
调查CPU问题
及时服务SQL Server的CPU周期可用性对于数据库服务器的性能是关键的。基于配置的CPU瓶颈或许包括最大限度的平行度，并行度的成本阈值，及错误配置CPU超线程。改变默认配置，及配置选项的最佳设置是依场景而定的，规范的配置并覆盖所有潜在场景是有挑战的-----经常有边缘事件及意外。
内核模式和应用模式
辨认内核模式消耗和应用模式消耗之间的不同是重要的，因为这个概念将为故障排除提供重要且有用的指标。它适用于CPU和内存消耗。内核模式指的是内部Windows OS操作，凭借它，内核可以无限制地访问系统硬件，如全部的内存地址范围、外部设备等。应用模式（也被称为用户模式）负责其他一切事情，包括运行应用程序，如SQL Server。所有用户模式的应用程序通过执行程序访问硬件资源，执行程序运行在内核模式中。需要磁盘I/O的应用程序通过内核模式的执行程序来提交请求，执行程序执行请求并把结果返回到请求的用户模式进程。
CPU性能计数器
SQL Server遇到高CPU使用率引起的性能问题是常见的性能问题。使用任务管理器很容易识别高消耗的Windows进程sqlservr.exe，但是表10-3显示的计数器提供了额外的信息来辅助进一步故障排除。抓取性能数据应该至少持续几分钟，以确保样本具有代表性。如果有间歇的问题或收集基线，更长的数据抓取间隔会导致更有意义的结果。

CPU问题的常见原因
CPU高使用率有3中常见的原因：

• 缺少统计或统计过期------查询优化器依靠相关的统计来确定一个好执行计划。因此，缺少统计或统计过期会引起查询优化器选择欠佳的计划，进而引起过多的CPU消耗。
• 缺少索引------缺少有用的索引可以导致高CPU使用率。SQL Server依靠有意义的索引来高效检索数据，缺少索引常常引起过多的CPU使用。缺少有用的索引可以导致昂贵的操作，例如哈希连接和排序能够通过改善索引来避免。
• 过多的重新编译------欠佳的计划复用能够导致高CPU使用率，SQL Server生成查询计划时会消耗过多的CPU周期。ad hoc、动态查询或缺失内存（程序缓存）能够导致重新编译，从而导致计划从缓存中删除。
  

调查内存相关的问题
SQL Server的性能与充足内存的可用性及性能密切相关。SQL Server配置相关的内存设置包括如下：

• sp_configure：Min/max server memory、AWE Enabled、Min memory per query
• Windows：/3GB, /USERVA, /PAE (in 32-bit environments)
• Lock Pages in Memory privilege
  

使用Windows任务管理器不能提供SQL Server消耗内存的最佳衡量。使用PerfMon更加可靠，因为它包括SQL Server产生的各种类型的内存分配。
内存压力的类型
SQL Server会遭受内部或外部的内存压力，理解如何识别和排除压力，能够更有针对性地进行故障排除。外部内存压力通常发生在SQL Server在共享计算机上运行并且几个进程争夺内存的时候。在这种情况下，SQL Server Operating System（SQLOS）中的资源监视器会收到Windows的信号，要求SQL Server减少其提交的内存。这会导致SQL Server重新计算其目标提交级别，必要时减少内存。内部内存压力发生在多个SQL S二二资源彼此竞争内存。这常会引起SQL Server收缩数据缓存，这会影响服务器性能。使用DBCC MEMORYSTATUS命令可以获取SQL Server内存消耗的状况。
虚拟地址空间（VAS--Virtual Address Space）
每个Windows进程都有自己的VAS，VAS的大小取决于处理器架构（32位或64位）及OS版本。VAS是一个固定大小的资源，这个资源在物理内存仍然可用的时候会耗尽（特别是64为计算机）。
内存性能计数器
表10-4概括了PerfMon计数器，这些计数器是收集内存可用性及消耗相关的信息的关键。

磁盘或存储相关的问题
SQL Server读写性能与Windows有效检索及写入数据页到磁盘的能力紧密相关。高效且及时的数据访问依赖于基于配置和基于架构两方面因素，例如数据和日志文件大小及布局、有用的索引及索引碎片。磁盘和存储性能是非常复杂和拖拉的实践，常因不清楚的术语及逻辑抽象而困惑，这使得很难识别根本原因。然而，不管存储硬件、磁盘布局或路径配置，唯一真正感兴趣的是从磁盘读写所需要的时间，因为时间是磁盘访问性能是否可能引起SQL Server问题的得力指标。一旦磁盘访问被识别为瓶颈，必须使用比PerfMon更专业的工具，以提供关于瓶颈的更低级别的细节。多数SAN供应商提供性能监视工具，这有助于诊断与存储控制器、缓存性能、物理磁盘服务时间相关的问题。这些工具对过度使用的组件及性能瓶颈提供进一步的诊断。磁盘性能问题有广泛且不同的潜在解决方案，包括大量的磁盘重新配置，如改变RAID级别、磁盘组成员及磁条大小。你也可以在SQL Server内部做很多改善，包括合适大小的数据及日志文件、预分配空间，以及对于超大型数据库的表分区。表10-5描述了检查磁盘性能的主要PerfMon计数器。

SQL Server性能问题
有时，服务器硬件资源不会引起瓶颈，但是应用程序性能依然糟糕。这种情况下，可能是内部的SQL Server资源耗尽或殆尽。表10-6描述了监视SQL Server资源的主要计数器。

等待统计分析（Wait Stats Analysis）
SQL Server等待统计记录了SQL Server等待每种资源所花费的时间量。大量的等待类型可通过如下计数器展示：

• Lock waits
• Log write waits
• Network I/O waits
• Non-page latch waits
• Page I/O latch waits
• Page latch waits
• Waits for the worker
  

尽管可以很容易地通过SQL Server内部的DMV访问到这些等待统计，但是通过PerfMon收集它们作为系统范围的数据收集实践，能够最小化涉及收集信息的付出。
获得性能基线（Getting a Performance Baseline）
性能基线就是一段时间内的PerfMon日志，它表示正常的性能，保留下来供以后回顾。PerfMon日志应该包括这些计数器，即可以创建有代表性的工作负载期间的硬件及SQL Server资源的完整图片。遇到问题时，可以拿基线做比对。基线应该持续维护，否则，配置改变或调校优化能够更改输出，并使得任何对照无效。养成定期刷新基线是很有用的。