OLTP系统的最大特点，是这类应用里有大量的，并发程度比较高的小事务，包括SELECT、INSERT、UPDATE和DELETE。 这些操作都比较简单，事务时间也不会很长，但是要求的返回时间很严格，基本上需要在几秒钟内必须返回。
支持生产流水线的数据库应用，是很典型的OLTP系统。一件产品从原材料到组装成最后的产品，中间会有很多道工序。每道工序本身不复杂，不会花很多时间。工厂需要使用数据库应用记录和监督每一道工序。在流水线上，工人可以扫描产品上的条形码，快速的输入产品加工、处理或检验结果。这些输入和修改过程都会很简单，而且很多在数据库里会是INSERT、UPDATE或DELETE动作。但是应用的响应速度要求非常高，最后等待的时间可以忽略不计。如果工人输入一个条形码以后要等几秒钟，很多他在处理每一件产品的时候，都会多花几秒钟。如果他要花几十秒，那么整个流水线的运转就会很慢。如果系统出了问题，他每处理一个产品都要花几分钟，那么流水线就会瘫痪，工人们都可以去喝茶了。数据库管理员这时将面对的是心急如焚的管理高层。
所以OLTP系统在设计的时候，要非常小心，像那种由于一条语句而导致整个服务器范围的阻塞，是绝对要避免的。
OLTP系统要注意避免出现的问题主要提现在以下几个方面。
|
规则 |
性能计数器值 |
阈值 |
检查目标 |
问题描述 |
|
1 |
经常运行的语句超过4个表格Join |
>4张表 |
sys.dm_exec_sql_text |
如果经常运行的语句要做多张表的Join，可以考虑降低数据库设计范式级别，增加一些冗余字段，用空间换取数据库效率。 |
|
2 |
经常更新的表格有超过3个索引 |
>3个索引 |
sys.indexes |
索引太多会影响更新效率 |
|
3 |
语句会做大量IO |
>1 |
a. 性能计数器SQLServer：Access Methods - Full Scans/sec 和 Range Scans/sec 比较高。 |
语句缺少合适的索引 |
|
4 |
未被使用的索引 |
所有没有在sys.dm_db_index_usage_stats这个DMV里出现的索引 |
避免定义没有用的索引，凭空增加SQL Server的维护负担 |
建议查询1.1 返回最经常运行的100条语句
--返回最经常运行的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+1 ,(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个索引
--返回最经常被修改的100个索引，通过它们的DataBase_id、object_id、index_id和partition_number 可以找到他们是哪个数据库上的哪个索引 SELECT TOP 100 db_name(database_id) as [DB Name], object_name(a.object_id) as [Table Name], b.type, -- b.type_desc, i.name as [Index Name], leaf_insert_count, leaf_delete_count, leaf_update_count FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) a INNER JOIN sys.objects b on a.object_id = b.object_id INNER JOIN sys.indexes i on i.object_id = a.object_id and i.index_id = a.index_id WHERE B.type = 'U' ORDER BY leaf_insert_count + leaf_delete_count + leaf_update_count DESC
建议查询1.3 返回IO代价最大的语句与执行计划
select top 10 (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, a.last_execution_time, a.last_elapsed_time/1000/1000, --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, db_name(sql_text.dbid), plan_text.* from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text cross apply sys.dm_exec_query_plan(plan_handle) as plan_text where a.last_execution_time > getdate()-0.5 order by --(total_logical_reads + total_logical_writes) /Execution_count Desc (total_physical_reads) Desc
建议语句1.4 查询没有被使用到的索引
--查看数据库中没有用到的索引 select db_name(database_id) as N'数据库名称', object_name(a.object_id,a.database_id) as N'表名', b.name N'索引名称', user_seeks N'用户索引查找次数', user_scans N'用户索引扫描次数', last_user_seek N'最后查找时间', last_user_scan N'最后扫描时间', c.[rows] as N'表中的行数' from sys.dm_db_index_usage_stats a inner join sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id inner join sysindexes c on c.id = b.object_id where (user_seeks+user_scans)=0 and c.rows > 100000 order by user_seeks,user_scans,object_name(a.object_id)
|
规则 |
性能计数器值 |
阈值 |
检查目标 |
问题描述 |
|
1 |
Signal Waits |
>25% |
sys.dm_os_wait_stats |
指令等待CPU资源的时间占总时间的百分比。如果超过25%，说明CPU资源紧张 |
|
2 |
执行计划重用率 |
<90% |
性能计数器SQLServer:Statistics下 |
OLTP系统的核心语句，必须有大于95%的执行计划重用率 |
|
3 |
并行运行的Cxpacket等待状态 |
>5% |
sys.dm_os_wait_stats |
首先，并行运行意味着SQL Server在处理一句代价很大的语句，要不就是没有合适的索引，要不就是筛选条件没能够筛选掉足够的记录，使得语句要返回大量的结果。这个在OLTP系统里都是不容许的。 |
建议查询2.1
-- 计算signal wait占整wait时间的百分比 -- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明 CPU 紧张 select convert(numeric(5,4),(sum(signal_wait_time_ms)*1.0)/sum(wait_time_ms)) *100 AS 'CPU Busy Ratio' from Sys.dm_os_wait_stats
建议查询2.2
-- 计算'Cxpacket'占整wait时间的百分比 -- Cxpacket:Sql Server 在处理一句代价很大的语句，要不有并行作业，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当 >5% 说明有问题 declare @Cxpacket bigint declare @PageIOLATCH_EX bigint declare @PageIOLATCH_SH bigint declare @Sumwaits bigint select @Cxpacket = wait_time_ms from Sys.dm_os_wait_stats where wait_type = 'Cxpacket'; select @PageIOLATCH_EX = wait_time_ms from sys.dm_os_wait_stats where wait_type = 'PageIOLATCH_EX'; select @PageIOLATCH_SH = wait_time_ms from sys.dm_os_wait_stats where wait_type = 'PageIOLATCH_SH'; --这个值大一般表示内存不够用 select @Sumwaits = sum(wait_time_ms) from Sys.dm_os_wait_stats select convert(numeric(5,4),@Cxpacket/(@Sumwaits * 1.0)) *100 As 'Cxpacket Ratio%', convert(numeric(5,4),@PageIOLATCH_EX/(@Sumwaits * 1.0)) *100 As 'PageIOLATCH_EX Ratio%', convert(numeric(5,4),@PageIOLATCH_SH/(@Sumwaits * 1.0)) *100 As 'PageIOLATCH_SH Ratio%'
--性能计数对象SQLServer:SQL Statistics 下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是： --Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec --执行计划重用率 = （Batch request/sec – Initial Compilations/sec）/Batch requests/sec declare @batch_requests_sec bigint; declare @sql_compilations_sec bigint; declare @sql_recompilations_sec bigint; select @batch_requests_sec = cntr_value from sys.sysperfinfo b where b.object_name = 'SQLServer:SQL Statistics' and counter_name = 'Batch Requests/sec' select @sql_compilations_sec = cntr_value from sys.sysperfinfo b where b.object_name = 'SQLServer:SQL Statistics' and counter_name = 'SQL Compilations/sec' select @sql_recompilations_sec = cntr_value from sys.sysperfinfo b where b.object_name = 'SQLServer:SQL Statistics' and counter_name = 'SQL Re-Compilations/sec' select 100.0*(@batch_requests_sec - (@sql_compilations_sec-@sql_recompilations_sec))/@batch_requests_sec as [SQL PLAN Reuse Ratio%] go
|
规则 |
性能计数器值 |
阈值 |
检查目标 |
问题描述 |
|
1 |
Page Life Expectancy |
<300 sec |
性能计数器 |
OLTP系统的操作都比较简单，所以它们不应该要访问太多的数据。如果数据也不能长时间的缓存在内存里，势必会影响性能，同事也说明了某些语句没有合适的索引 |
|
2 |
Page Life Expectancy |
经常会下降50% |
性能计数器SQL Server Buffer Manager |
问题同上 |
|
3 |
Memory Grants Pending |
>1 |
性能计数器 SQL Server Memory Manager |
等待内存分配的用户数目，如果大于1，一定有内存压力 |
|
4 |
SQL cache hit ratio |
<90% |
性能计数器 （建议查询3.3） |
这个值不能长时间（例如，60秒钟）地小于90%。否则常常意味着有内存压力 |
建议查询3.1
select * from sys.sysperfinfo b where b.object_name = 'SQLServer:Buffer Manager' and counter_name = 'Page life expectancy'
select * from sys.sysperfinfo b where b.object_name = 'SQLServer:Buffer Node' and counter_name = 'Page life expectancy'
建议查询3.2
--等待内存分配的用户数目，如果大于1，一定有内存压力 select * from sys.sysperfinfo b where b.object_name = 'SQLServer:Memory Manager' and counter_name = 'Memory Grants Pending'
建议查询3.3
select * from sys.sysperfinfo a where a.object_name = 'SQLServer:Plan Cache' and a.counter_name like 'Cache Hit Ratio%'
|
规则 |
性能计数器值 |
阈值 |
检查目标 |
问题描述 |
|
1 |
Average Disk sec/read |
>20ms |
性能计数器 |
在没有I/O压力的情况下，读操作应该在4～8ms以内完成 |
|
2 |
Average Disk sec/write |
>20ms |
性能计数器 |
对于像日志文件这样的连续写，应该在1ms以内完成 |
|
3 |
Big Ios |
>1 |
性能计数器 |
语句缺少合适的索引 |
|
4 |
排在前两位的等待状态有下面几个： |
Top2 |
SELECT TOP 2 wait_type |
这些等待状态意味着有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 |
说明系统经常有阻塞 |
|
5 |
经常有死锁 |
每个小时超过5个 |
打开Trace Flag 1204，或者在SQL Trace里跟踪相关时间 |
死锁往往伴随着阻塞同时发生 |
建议查询5.1
--查询阻塞发生频率与平均阻赛时间
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 |
性能计数器 |
网络不能支持应用和数据库服务器的交互流量 |
|
2 |
网络带宽用尽 |
Packets Outbound Discarded; |
性能计数器 |
由于网络太忙，有packet在传输中丢失 |
总之，对于一个要处理大量小型事务请求的OLTP系统，其事务请求的相应速度与资源配置优化可以从下面几方面着手。
1) 对于会经常发生INSERT、UPDATE和DELETE的表格，在设计的时候要选择最小数量的索引。
2) 可以通过提高执行计划重用降低JOIN的数目降低CPU使用率。
3) 可以通过优化索引设计，降低JOIN数目和提高页面的内存里缓存生命周期，环节IO瓶颈。
4) 如果Page Life Expectancy不会突然下降的话，说明内存的DataBase Page部分没有瓶颈。
5) 可以通过优化索引和缩短事务大小来减少阻塞
