SQL Server应用模式之OLTP系统性能分析

OLTP系统的最大特点，是这类应用里有大量的，并发程度比较高的小事务，包括SELECT、INSERT、UPDATE和DELETE。 这些操作都比较简单，事务时间也不会很长，但是要求的返回时间很严格，基本上需要在几秒钟内必须返回。

支持生产流水线的数据库应用，是很典型的OLTP系统。一件产品从原材料到组装成最后的产品，中间会有很多道工序。每道工序本身不复杂，不会花很多时间。工厂需要使用数据库应用记录和监督每一道工序。在流水线上，工人可以扫描产品上的条形码，快速的输入产品加工、处理或检验结果。这些输入和修改过程都会很简单，而且很多在数据库里会是INSERT、UPDATE或DELETE动作。但是应用的响应速度要求非常高，最后等待的时间可以忽略不计。如果工人输入一个条形码以后要等几秒钟，那么他在处理每一件产品的时候，都会多花几秒钟。如果他要花几十秒，那么整个流水线的运转就会很慢。如果系统出了问题，他每处理一个产品都要花几分钟，那么流水线就会瘫痪，工人们都可以去喝茶了。数据库管理员这时将面对的是心急如焚的管理高层。

所以OLTP系统在设计的时候，要非常小心，像那种由于一条语句而导致整个服务器范围的阻塞，是绝对要避免的。

OLTP系统要注意避免出现的问题主要体现在以下几个方面。

数据库设计

规则	性能计数器值	阈值	检查目标	问题描述
1	经常运行的语句超过4个表格Join	>4张表	sys.dm_exec_sql_text sys.dm_exec_cached_plans （建议查询1.1）	如果经常运行的语句要做多张表的Join，可以考虑降低数据库设计范式级别，增加一些冗余字段，用空间换取数据库效率。
2	经常更新的表格有超过3个索引	>3个索引	sys.indexes sys.dm_db_index_operational_stats （建议查询1.2）	索引太多会影响更新效率
3	语句会做大量IO Table Scans Range Scans	>1	a. 性能计数器SQLServer：Access Methods - Full Scans/sec 和 Range Scans/sec 比较高。 b. sys.dm_exec_query_stats 里显示有语句经常做大量IO动作。 (建议查询1.3)	语句缺少合适的索引
4	未被使用的索引		所有没有在sys.dm_db_index_usage_stats这个DMV里出现的索引	避免定义没有用的索引，凭空增加SQL Server的维护负担

建议查询1.1

--返回最经常运行的100条语句

SELECT TOP 100
    cp.cacheobjtype 
　　,cp.usecounts 
　　,cp.size_in_bytes 
　　,qs.statement_start_offset 
　　,qs.statement_end_offset
　　,qt.dbid ,qt.objectid 
　　,SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1
       then len(convert(nvarchar(max),qt.text)) * 2
       else qs.statement_end_offset end - qs.statement_start_offset) / 2) AS statement 

FROM 
　　sys.dm_exec_query_stats qs 
CROSS APPLY 　
　　sys.dm_exec_sql_text(qs.sql_handle) as qt
INNER JOIN 
　　sys.dm_exec_cached_plans as cp
ON qs.plan_handle = cp.plan_handle
WHERE 
　　cp.plan_handle = qs.plan_handle
AND cp.usecounts>4
ORDER BY
    [dbid],[Usecounts]
DESC

建议查询1.2

--返回最经常被修改的100个索引

--通过它们的DataBase_id、object_id、index_id和partition_number

--可以找到他们是哪个数据库上的哪个索引

SELECT TOP 100 *
FROM 
　　sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) 
ORDER BY 
　　leaf_insert_count + leaf_delete_count + leaf_update_count DESC

建议查询1.3

--返回做I/O数目最多的50条语句及它们的执行计划

SELECT TOP 50 (total_logical_reads/execution_count) AS avg_logical_reads
　　, (total_logical_writes/execution_count) AS avg_logical_writes
　　, (total_physical_reads/execution_count) AS avg_phys_reads, Execution_count
　　, statement_start_offset AS stmt_start_offset
　　, statement_end_offset AS stmt_end_offset
　　, SUBSTRING(sql_text.text,(statement_start_offset/2)
　　, CASE WHEN (statement_end_offset - statement_start_offset)/2 <=0
    THEN
       64000
    ELSE (statement_end_offset
    - statement_start_offset)/ 2
    END) AS exec_statement, sql_text.text, plan_text.*
FROM 
　　sys.dm_exec_query_stats
CROSS APPLY 
　　sys.dm_exec_sql_text(sql_handle) AS sql_text 
CROSS APPLY 
　　sys.dm_exec_query_plan(plan_handle)  as plan_text 
ORDER BY (total_logical_reads + total_logical_writes) / Execution_count 
DESC

CPU

规则	性能计数器值	阈值	检查目标	问题描述
1	Signal Waits	>25%	sys.dm_os_wait_stats（建议查询2.1）	指令等待CPU资源的时间占总时间的百分比。如果超过25%，说明CPU资源紧张
2	执行计划重用率	<90%	性能计数器SQLServer:Statistics下（计算方法2.1）	OLTP系统的核心语句，必须有大于95%的执行计划重用率
3	并行运行的Cxpacket等待状态	>5%	sys.dm_os_wait_stats（建议查询2.2）	首先，并行运行意味着SQL Server在处理一句代价很大的语句，要不就是没有合适的索引，要不就是筛选条件没能够筛选掉足够的记录，使得语句要返回大量的结果。这个在OLTP系统里都是不容许的。 其次，并行运行会影响OLTP系统整体相应速度，也是不推荐的。

建议查询2.1

-- 计算signal wait占整wait时间百分比

SELECT
 　　convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms)) 
FROM 
　　sys.dm_os_wait_stats

计算方法2.1

性能计数对象SQLServer:SQL Statistics 下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是：

Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec

执行计划重用率 = （Batch request/sec – Initial Compilations/sec）/Batch requests/sec

建议查询2.2

--计算'Cxpacket'占整wait时间的百分比

DECLARE @Cxpacket bigint
DECLARE @Sumwaits bigint
SELECT @Cxpacket = wait_time_ms 
FROM 
　　sys.dm_os_wait_stats
WHERE wait_type = 'Cxpacket'
SELECT
    @Sumwaits = sum(wait_time_ms) 
FROM sys.dm_os_wait_stats
SELECT CONVERT(numeric(5,4),@Cxpacket/@Sumwaits)

内存

规则	性能计数器值	阈值	检查目标	问题描述
1	Page Life Expectancy	<300 sec	性能计数器 SQLServer:Buffer Manager SQLServer:Buffer Nodes （建议查询2.1）	OLTP系统的操作都比较简单，所以它们不应该要访问太多的数据。如果数据也不能长时间的缓存在内存里，势必会影响性能，同事也说明了某些语句没有合适的索引
2	Page Life Expectancy	经常会下降50%	性能计数器SQL Server Buffer Manager	问题同上
3	Memory Grants Pending	>1	性能计数器 SQL Server Memory Manager （建议查询2.2）	等待内存分配的用户数目，如果大于1，一定有内存压力
4	SQL cache hit ratio	<90%	性能计数器 SQL Server:Plan Cache	这个值不能长时间（例如，60秒钟）地小于90%。否则常常意味着有内存压力

I/O

规则	性能计数器值	阈值	检查目标	问题描述
1	Average Disk sec/read	>20ms	性能计数器 Physical Disk	在没有I/O压力的情况下，读操作应该在4～8ms以内完成
2	Average Disk sec/write	>20ms	性能计数器 Physical Disk	–
3	Big Ios Table Scans Range Scans	>1	性能计数器 SQLServer:Access Methods - Full Scans/sec 和Range Scans/sec比较高	语句缺少合适的索引
4	排在前两位的等待状态有下面几个： ASYNCH_IO_COMPLETION, IO_COMPLETION LOGMGR,WRITELOG, PAGEIOLATCH_x	Top2	SELECT TOP 2 wait_type FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC	这些等待状态意味着有I/O等待

阻塞
阻塞问题在OLTP系统里危害巨大，是要严格避免的。

规则	性能计数器值	阈值	检查目标	问题描述
1	阻塞发生频率	>2%	sys.dm_db_index_operational_stats(建议查询5.1)	阻塞发生频率
2	阻塞事件报告	30s	sp_configure ‘blocked process threshold’	在SQL Trace里自动报告超过30秒钟的阻塞语句
3	平均阻塞时间	>100ms	sys.dm_db_index_operational_stats(建议查询5.1)	阻塞发生的长短
4	排在前两位的等待状态以这样开头LCK_M_??	Top2	SELECT TOP 2 wait_type FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC	说明系统经常有阻塞
5	经常有死锁	每个小时超过5个	打开Trace Flag 1204，或者在SQL Trace里跟踪相关时间	死锁往往伴随着阻塞同时发生

建议查询5.1

--查询当前数据库上所有用户表格在Row Lock上发生阻塞的频率

DECLARE @dbid int
SELECT @dbid = db_id() 
SELECT 
　　dbid=database_id
　　,objectname = object_name(s.object_id) 
　　,indexname = i.name ,i.index_id
　　--,partition_number
　　,row_lock_count 
　　,row_lock_wait_count 
　　,[block %] = CAST(100.0 * row_lock_wait_count/(1+row_lock_count) AS numeric(15,2)) 
　　,row_lock_wait_in_ms 
　　,[avg row lock wait in ms] = CAST(1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS numeric(15,2))
FROM 
　　sys.dm_db_index_operational_stats(@dbid,NULL,NULL,NULL) s
　　, sys.indexes i 
WHERE
    objectproperty(s.object_id,'IsUserTable') = 1
AND i.object_id = s.object_id
AND i.index_id = s.index_id 
ORDER BY 
　　row_lock_wait_count
DESC

网络传输

规则	性能计数器值	阈值	检查目标	问题描述
1	网络有延时，或者应用太频繁地和数据库交互	Output queue length >2	性能计数器 Network Interface	网络不能支持应用和数据库服务器的交互流量
2	网络带宽用尽	Packets Outbound Discarded; Packets Outbound Errors Packets Received Discarded; Packets Received Errors	性能计数器 Network Interface	由于网络太忙，有packet在传输中丢失

总之，对于一个要处理大量小型事务请求的OLTP系统，其事务请求的相应速度与资源配置优化可以从下面几方面着手。

1）对于会经常发生INSERT、UPDATE和DELETE的表格，在设计的时候要选择最小数量的索引。

1）可以通过提高执行计划重用降低JOIN的数目降低CPU使用率。

2）可以通过优化索引设计，降低JOIN数目和提高页面的内存里缓存生命周期，环节IO瓶颈。

3）如果Page Life Expectancy不会突然下降的话，说明内存的DataBase Page部分没有瓶颈。

4）可以通过优化索引和缩短事务大小来减少阻塞。