http://sqlcat.com/whitepapers_chinese-simplified/archive/2008/10/17/oltp-sql-server-2005.aspx
面向 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。
About Lindsey.allen
Lindsey Allen has been working with SQL Server and Business Intelligence for the past 15+ years. She joint Microsoft Consulting Services summer of 1995 and transitioned to SQL Server Product Group summer of 2005. Lindsey is currently managing SQLCAT Best Practice and Customer Lab team.
871
到【灌水乐园】发言
Comments
lidong said:
Hi, 朱老师好啊, 很高兴在这里看到您的文章. 您上面提到SQL 2005 SP2 中不缓存零成本计划, 您指的零成本是trivial plan吗? 如果是的话,我做了个小测试:
use tempdb
go
if exists(select * from sys.objects where name = 'table_ld')
drop table table_ld
create table table_ld (col int)
go
dbcc freeproccache
go
select * from sys.dm_exec_query_optimizer_info where counter ='trivial plan'
go
insert table_ld (col) values (1)
go
select * from sys.dm_exec_query_optimizer_info where counter ='trivial plan'
GO
Select st.text,cp.*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text like '%insert%into%[[]table_ld%'
结果显示trivial plan计数器加1, 但cache中还是能找到这个语句的查询计划. 我的SQLSERVER版本是9.0.3239.
还想请教您第二个问题, 因为存储执行计划的cache的内存也大都是由buffer manager 分配(steal)的. 而Page Life Expectancy和SQL Cache hit ratio 这两个计数器也是位于sql server: buffer manager对象下面的. 那么这两个计数器是否也统计除了数据页面之外的其它页面呢? 比如存储query plan的proc cache的页面.
顺祝圣诞快乐!
Lindsey.allen said:
圣诞快乐!
Zero cost plan is not the same as trivial plan. We do cache some trivial plan for reuse. If you insert a couple of more rows into the same table, you will see the plan use count increases with each insert.
There are other memory object and object caches in buffer pool in addition to plan cache.
lidong said:
谢谢, 回复的真快:)
我在网上找到了一些zero cost query plan 的信息:
Trace flag 2861 instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction and others).
关于第二个问题,我可能没说清楚,不好意思. 是的, 除了数据页外, 其它许多只需要一个page的内存对象都可能由buffer manager分配.
那么在计算Page Life Expectancy和SQL Cache hit ratio这两个计数器时, 是否会考虑除除数据页以外的其它页的呢? 还是只考虑数据页?
比如query plan 的生命周期是否会计入Page Life Expectancy, 在proc cache中为请求找到一个query plan, 那是否是递增SQL Cache hit ratio的值呢?
Lindsey.allen said:
Buffer Manager/Page life expectancy counter and Buffer Manager/Buffer Cache Hit Ratio counter are for data pages. Plan Cache/Cache Hit Ratio is for plan cache.
Buffer Pool is utilized by many functions of SQL server, including database pages, plan cache, locks, workspace memory. Workspace memory is the memory required by SQL Server to execute queries, including memory for hash space, index build, sorting etc. Memory Manager/Total server memory (KB) is the counter you want to monitor.
lidong said:
谢谢, 前段时间在研究SQL server的内存管理, 一直有这么个困惑, 现在终于搞明白了.