面向 OLTP 应用程序的重要 SQL Server 2005 性能问题
OLTP 工作负荷的特征是存在大量相似的小型事务。
在检查数据库设计、资源利用和系统性能的重要性时,请务必牢记这些特征。下面简述面向 OLTP
应用程序的重要性能瓶颈或缺陷。
数据库设计问题
· 常用查询存在过多的表联接。在 OLTP 应用程序中过多使用联接将导致查询运行缓慢,
浪费系统资源。通常,应该重新设计数据库,避免需要 5 个或 5 个以上表联接的频繁操作。
· 频繁更新(包括插入、更新和删除)的表中存在过多的索引导致额外的索引维护开销。通常,OLTP 数据库的设计中应该将索引数保持为正常运转前提下的最小值,这同样是由于大量相似事务与索引维护成本共同作用的结果。
· 由于缺失索引,导致大量 IO,如表和范围的扫描。根据定义,OLTP 事务不应要求大量 IO,并且应该接受检查。
· 未使用的索引在插入、更新和删除中导致索引维护成本,而对任何用户都没有用。
应该清除未使用的索引。任何使用过的索引(通过选择、更新或删除操作)都出现在 sys.dm_db_index_usage_stats 中。因此,任何已定义但不包括在此 DMV 中的
索引自上次重新启动 SQL Server 以来尚未使用过。
CPU 瓶颈
· 信号等待时间超过总等待时间的 25%。请参阅 sys.dm_os_wait_stats 中的信号等待时间和总等待时间。信号等待时间用于度量可运行队列等待 CPU 时所花费的时间。信号等待时间值很高说明出现 CPU 瓶颈。
· 计划重用小于 90%。查询计划用于执行某个查询。OLTP 工作负荷建议使用计划重用,因为重新创建相同的计划(用于相似或相同的事务)是对 CPU 资源的浪费。比较 SQL Server SQL Statistics:batch requests/sec 与 SQL compilations/sec。按照如下公式计算计划重用:计划重用 =(批处理请求数 — SQL 编译数)/批处理请求数。计划重用规则的特例:SQL 2005 SP2 中不缓存(而不是重用)零成本计划。使用零成本计划的应用程序具有较低的计划重用,但这不是性能问题。
· 并行等待类型 cxpacket 超过总等待时间的 10%。并行牺牲 CPU 资源换取执行速度。如果有大量 OLTP,并行查询通常会降低 OLTP 吞吐量,所以应尽量避免。有关等待统计信息,
请参阅 sys.dm_os_wait_stats。
内存瓶颈
· Average Page Life Expectancy 持续较低。请参阅 Perfmon 对象 SQL Server Buffer Manager 中的 Average Page Life Expectancy 计数器(表示一页在缓存中停留的平均
秒数)。对于 OLTP,Average Page Life Expectancy 为 300 表示 5 分钟。只要时间
缩短就表示可能出现内存压力、缺失索引或缓存刷新的情况。
· Page Life Expectancy 突然大幅下降。OLTP 应用程序(如小型事务)应该有稳定(或缓慢增长)的 Page Life Expectancy。请参阅 Perfmon 对象 SQL Server Buffer Manager。
· 挂起的内存授予。请参阅 Perfmon 对象 SQL Server Memory Manager 中的 Memory Grants Pending 计数器。小型 OLTP 事务应该不需要大的内存授予。
· SQL Cache hit ratio 突然下降或持续较低。OLTP 应用程序(如小型事务)应具有较高的 Cache hit ratio。由于 OLTP 事务较小,不应出现以下情况:(1) SQL 缓存命中率大幅下降或 (2) 缓存命中率持续低于 90%。缓存命中率下降或较低可能表示出现内存压力或缺失索引的情况。
IO 瓶颈
· 较高的每次读取磁盘平均秒数。IO 子系统在队列中排队时,每次读取磁盘的时间会增加。
请参阅 Perfmon Logical disk 或 Physical disk(disk seconds/read 计数器)。通常,
在没有 IO 压力的情况下,完成一次读取需要 4 — 8 毫秒。当 IO 子系统由于高 IO 请求而
面临压力时,完成一次读取的平均时间会增加,这是在磁盘队列中排队的结果。对于许多应用程序,disk seconds/read 出现周期性的较高值是可接受的。对于高性能的 OLTP 应用程序,复杂的 SAN 子系统在处理 IO 活动峰值方面可以提供更强的 IO 伸缩性和复原性。持续的高 disk seconds/read(大于 15 毫秒)值明确指示出现磁盘瓶颈。
· 较高的每次写入磁盘平均秒数。请参阅 Perfmon Logical disk 或 Physical disk。高容量的 OLTP 应用程序的吞度量取决于快速顺序的事务日志写入。高性能 SAN 环境中的事务日志写入可能只需要 1 毫秒(或更短)。对于许多应用程序,考虑到复杂 SAN 子系统的不菲成本,每次写入磁盘平均秒数出现周期性的峰值是可接受的。但是,持续的高 Average disk seconds/write 值是磁盘瓶颈的可靠指示器。
· 由于缺失索引,导致大量 IO,如表和范围的扫描。
sys.dm_os_wait_stats 中居前等待统计信息与 IO 相关,如 ASYNCH_IO_COMPLETION、IO_COMPLETION、LOGMGR、WRITELOG 或 PAGEIOLATCH_x
阻塞瓶颈
· 索引争用。在 sys.dm_db_index_operational_stats 中查找锁等待和闩锁等待。
比较锁请求和闩锁请求。
· 较长的平均行锁等待时间或闩锁等待时间。平均行锁等待时间或闩锁等待时间的计算方法是:
锁等待时间和闩锁等待时间(毫秒)除以锁等待数和闩锁等待数。从 sys.dm_db_index_ operational_stats 计算出的平均锁等待毫秒数表示每次阻塞的平均时间。
· 阻塞进程报表列出长时间的阻塞。请参阅 Errors and Warnings 事件下的 sp_configure“blocked process threshold”和事件探查器“Blocked process Report”。
· 居前等待统计是 LCK_x。请参阅 sys.dm_os_wait_stats。
· 大量死锁。请参阅 Locks 事件下的事件探查器“Graphical Deadlock”,以找出死锁涉及的语句。
网络瓶颈
· 导致多次往返数据库的应用程序会使网络滞后时间加倍。
· 网络带宽全部占用。请参阅性能监视器的网络接口对象中的 packets/sec 计数器和当前带宽计数器。TCP/IP 帧的实际带宽计算方法为:packets/sec * 1500 * 8 /1000000 Mbps。