原文出自:
http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/
问题:
我们可能经常会遇到SQLServer数据库频繁关闭的情况。在分析了内存和CPU使用情况后,我们需要继续调查根源是否在I/O。我们应该如何识别SQLServer是否有I/O相关的瓶颈?
解决:
当数据页经常从缓冲池中移进移出的时候,I/O子系统就会成为SQLServer性能问题的关键因素之一。事务日志和tempdb同样也会产生重大的I/O压力。因此,你必须确保你的I/O子系统能按照预期运行。否则你将会成为响应时间增长和频繁超时的受害者。在这篇文章中,将描述如何使用内置工具识别I/O相关瓶颈,并提供一些磁盘配置的方法:
性能计数器(Performance Monitor):
可以使用性能计数器来检查I/O子系统的负荷。下面的计数器可用于检查磁盘性能:
PhysicalDisk Object:Avg.DiskQueue Length:计算从物理磁盘中的平均读和写的请求队列。过高的值代表磁盘操作处于等待状态。当这个值在SQLServer峰值时长期超过2,证明需要注意了。如果有多个硬盘,就需要把这些数值除以2。比如,有4个硬盘,且队列为10,那么平均值就是10/4=2.5,虽然也证明需要关注,但不能使用10这个值。
Avg.Disk Sec/Read和Avg.Disk Sec/Write:显示从磁盘读或者写入磁盘的平均时间。10ms内是很好的表现,20以下还算能接受。高于此值证明存在问题。
Physical Disk:%Disk Time:在磁盘忙于读或者写请求的时候持续时间的比率。根据拇指定律,此值应该小于50%。
Disk Reads/Sec和Disk Writes/Sec计数器显示出在磁盘中读写操作的速率。这两个值应该小于磁盘能力的85%。当超过此值,磁盘的访问时间将以指数方式增长。
可以通过以下方式来计算逐渐增长的负载的能力。一种方法是使用SQLIO。你应该找到吞吐量比较稳定,但缓慢增长。
可以使用以下公式来计算RAID配置:
Raid 0:I/O per disk = (reads + writes) / number ofdisks
Raid 1:I/O per disk = [reads + (writes*2)] / 2
Raid 5:I/O per disk = [reads + (writes*4)] / number of disks
Raid 10:I/O per disk = [reads + (writes*2)] / number of disks
比如:对于RAID 1,如果得到下面的计数器:
Disk Reads/sec = 90
Disk Writes/sec=75
根据公式:[reads + (writes*2)] / 2or[90 + (75*2)] / 2= 120I/Os每个磁盘。
动态管理视图(DMVs):
有很多游泳的DMVs可以用于检查I/O瓶颈:
当一个页面被用于读或者写访问且页面在缓冲池中不存在或不可用时,会引发一个I/O闩锁等待(I/O latch),它会在PAGEIOLATCH_EX/PAGEIOLATCH_SH(具体根据请求类型而定)。这些等待表明一个I/O瓶颈。可以使用sys.dm_os_wait_stats找到闩锁等待的信息。如果你保存了SQLServer正常运行下的waiting_task_counts和wait_time_ms值,并且于此次的值做对比,可以识别出I/O问题:
select *
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type asc
挂起的I/O请求可以在下面查询中查到,并且用于识别那个磁盘负责的这个瓶颈:
select database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
sys.dm_io_pending_io_requests as iopior
where iovfs.file_handle = iopior.io_handle
磁盘碎片(Disk Fragmentation):
建议你检查磁盘碎片和配置用于SQLServer实例的磁盘。在NTFS文件系统中的碎片会产生严重的性能影响。磁盘需要经常整理碎片并且指定整理碎片计划。研究表明,一些情况下SAN在整理碎片后性能更差。因此,SAN必须根据实际情况对待。
NTFS上的索引碎片同样能引起高I/O好用。但是这和在SANs中的效果是不一样的。
磁盘配置/最佳实践:
常规情况,你应该把日志文件和数据文件分开存放以获得更好的性能。对于重负载的数据文件(包括tempdb)的I/O特性是随机读取。对于日志文件,是顺序访问的,除非事务需要回滚。
对于内置磁盘仅仅可以用于数据库日志文件,因为它们对顺序I/O有很好的性能,但是对随机I/O性能低下。
数据库的数据和日志文件应该放在对应专用的磁盘中。确保良好的性能。建议日志文件放在两个内置磁盘,并配置为RAID 1。数据文件驻留在仅用于给SQLServer访问的SAN系统中,并只被查询和报表控制。特殊访问应该被禁止。
写缓冲在可能的情况下应该被允许,并保证断电也能使用。
为了尽可能保证对于OLTP系统的I/O瓶颈影响最小化,不应该把OLAP和OLTP环境混合。并且保证你的代码优化及有合适的索引来避免不必要的I/O。
分享到:
相关推荐
这是一个相当高级的SQL Server监控工具,全面监控SQL Server的活动与性能,分析性能瓶颈,给出优化建议。 监控SQL Server的活动:进程、任务,详细查看当前执行的语句与实际变量值,终止进程 IO/CPU/网络等性能...
此版本被更新,以涵盖查询存储中等待统计信息的分析、CXCONSUMER等待事件以及SQLServer 2019年的最新情况。无论您是刚刚开始等待统计,还是已经熟悉这些统计信息,这本书提供了关于等待统计信息是如何生成的以及它们...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
--查看缓存中重用次数少,占用内存大的查询语句(当前缓存中未释放的)--全局 SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_...
Sql2008集群搭建。Window2008+Sqlserver R2 搭建集群环境,解决大并发,IO瓶颈问题,带图详解,看图操作。
每个实例仅仅允许有一个TempDb,但需要用到TempDB的地方却有很多,所以TempDB很容易成为性能瓶颈,我想大家数人都了解这一点,而大多数人所不了解的应该是在什么情况下才需要额外的TempDB文件。 当你看到PA
使用Toad,非常容易检测到存储过程的错误,开发人员可以一步一步运行PL/SQL语句来识别问题。调试会话可以和其他程序会话同时进行。 SQLab Xpert Option: 帮助开发人员优化SQL,为他们提供各种优化模式下SQL执行...
MyCAT还是一种新型的数据库,它看起来像是与内存缓存技术,NoSQL技术和HDFS大数据集成的SQL Server。作为一种新型的现代企业数据库产品,MyCAT与传统的数据库和新的分布式数据仓库相结合。总之,MyCAT是一种全新的...
分布式系统的前世今生 应用驱动 数据 硬件驱动 突破约束 人的驱动 聪明人 懒人 高效、容错、易用 1980s,个人电脑普及 分布式系统采用Client-Server架构 分布式SQL数据库(1979) 网络文件系统(1984) 1990s,数据中心...
程序上存在数据库的问题,那就必须采用更加专业的工具来抓取出现问题的程序,主要是程序中执行效率很低的sql语句,weblogic可以采用introscope定位,期间可以注意观察一下jvm的垃圾回收情况看是否正常,我在实践中...