SQL Server应用模式之OLTP系统性能分析
来源:互联网 发布:淘宝应用开发者平台 编辑:程序博客网 时间:2024/04/28 23:08
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
--返回最经常运行的20条语句
SELECT TOP 20 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 statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
-qs.statement_start_offset)/2)+1) AS statement_text
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
--返回最经常被修改的30个索引
--通过它们的DataBase_id、object_id、index_id和partition_number
--可以找到他们是哪个数据库上的哪个索引
SELECT TOP 30 *
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数目最多的条语句及它们的执行计划
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)+1
, 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
对于像日志文件这样的连续写,应该在1ms以内完成
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的表格,在设计的时候要选择最小数量的索引。
2) 可以通过提高执行计划重用降低JOIN的数目降低CPU使用率。
3) 可以通过优化索引设计,降低JOIN数目和提高页面的内存里缓存生命周期,环节IO瓶颈
4) 如果Page Life Expectancy不会突然下降的话,说明内存的DataBase Page部分没有瓶颈
5) 可以通过优化索引和缩短事务大小来减少阻塞
转自:点击打开链接
- SQL Server应用模式之OLTP系统性能分析
- SQL Server应用模式之OLTP系统性能分析
- SQL Server应用模式之OLTP系统性能分析
- 好文章——SQL Server应用模式之OLTP系统性能分析
- 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题
- 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题
- SQl server性能分析
- SQL Server In Memory OLTP
- SQL Server In Memory OLTP
- sql server性能分析--DMV之(sys.dm_exec_query_stats )
- sql server性能分析--DMV之(sys.dm_exec_query_stats ) .
- Microsoft SQL Server 2008性能分析之执行计划
- [SQL Server]性能排错之系统等待:WRITELOG
- SQL Server性能分析参数
- SQL Server性能分析参数
- SQL Server性能分析参数
- SQL Server性能分析参数
- sql server 性能分析工具
- 编写简单的在线考试系统
- Android JNI调用(三)
- SQL Server 脱机 联机
- NYOJ - 104 最大和【DP】转自:飘过的小牛的博客
- MFC listCtrl选中某一项,LIST失去焦点时,该项仍然显示高亮
- SQL Server应用模式之OLTP系统性能分析
- Android - 布局(layout) 详解
- ubuntu 忘记密码
- Lock与synchronized 的区别
- HDU1394最小逆序数
- 达芬奇ICETEK-DM6446-EVMS中xdcpaths.mak
- 给定一个整数N,求N!末尾有多少个0?N!的二进制表示中最低1的位置?
- ubuntu手动安装grub
- 强引用与弱引用(不太懂,不过文章形容的不错)