利用Performance Monitor实现SQL Server调优.docx
- 文档编号:12333578
- 上传时间:2023-04-18
- 格式:DOCX
- 页数:15
- 大小:310.72KB
利用Performance Monitor实现SQL Server调优.docx
《利用Performance Monitor实现SQL Server调优.docx》由会员分享,可在线阅读,更多相关《利用Performance Monitor实现SQL Server调优.docx(15页珍藏版)》请在冰豆网上搜索。
利用PerformanceMonitor实现SQLServer调优
利用PerformanceMonitor实现SQLServer调优
∙
性能监视的工具有很多,首先介绍MicrosoftWindowsServer自带的PerformanceMonitor.Windows性能监视器是一个很好用的工具,可以实时检查运行程序影响计算机性能的方式(CPU,ROM,IO等),并通过收集日志数据供以后分析使用.通过性能监视能了解系统loading以及这种loading对系统资源的影响,分析性能或者资源使用率的变化趋势,有效的对系统做出调整,优化或者升级.诊断系统故障或确定优化的组件或升级的步骤,也可以找出性能瓶颈.
PerformanceMonitor是一个系统内置的MMC控制台:
包括系统监视器(SystemMonitor)和性能日志和警报(PerformanceLogsandAlerts)两个部分. 通过实时和日志的方式来记录服务器性能.使用系统监视器可以取现,曲方图或者报表的方式实时查看内存,硬盘,处理器,网络等各种对象的性能数据.使用性能日志也警报可以对计数器日志进行配置,记录性能数据,设置性能警报,通过设定性能警报,可以使系统在某一特定的计数器值低于或高于指定的值时及时通知系统管理员.
下面简单介绍WindowsServer2003下的PerformanceMonitor,通过日志记录性能数据,之后分析.
1.打开:
AdministrativeTools->Performance,
或SQLServerProfiler->Tools->PerformanceMonitor,或在运行中输入"perfmon"
2.重要的性能计数器
(1).Processor
(2). PhysicalDisk
(3). Memory
(4).NetworkInterface
(5).SQLServerAccessMethods
(6).SQLServer:
SQLStatistics
(7).SQLServer:
Databases
(8).SQLServerGeneralStatistics
(9).SQLServerLocks
(10).SQLServerBufferManager
下表对重要的性能计数器做一个简要的说明:
性能计数器:
PerformanceObject
Counter
Description
Processor
%processorTime
指处理器执行非闲置线程时间的百分比,测量处理器繁忙的时间这个计数器设计成用来作为处理器活动的主要指示器,可以选择单个CPU实例,也可以选择Total
Interrupts/sec
处理器正在处理的来自应用程序或硬件的中断的数量
PhysicalDisk
%DiskTime
计数器监视磁盘忙于读/写活动所用时间的百分比.在系统监视器中,PhysicalDisk:
%DiskTime计数器监视磁盘忙于读/写活动所用时间的百分比。
如果PhysicalDisk:
%DiskTime计数器的值较高(大于90%),请检查PhysicalDisk:
CurrentDiskQueueLength计数器了解等待进行磁盘访问的系统请求数量。
等待I/O请求的数量应该保持在不超过组成物理磁盘的轴数的1.5到2倍。
大多数磁盘只有一个轴,但独立磁盘冗余阵列(RAID)设备通常有多个轴。
硬件RAID设备在系统监视器中显示为一个物理磁盘。
通过软件创建的多个RAID设备在系统监视器中显示为多个实例。
可以使用CurrentDiskQueueLength和%DiskTime计数器的值检测磁盘子系统中的瓶颈。
如果CurrentDiskQueueLength和%DiskTime计数器的值一直很高,则考虑下列事项:
1.使用速度更快的磁盘驱动器。
2.将某些文件移至其他磁盘或服务器。
3.如果正在使用一个RAID阵列,则在该阵列中添加磁盘。
计数器监视磁盘忙于读/写活动所用时间的百分比.在系统监视器中,PhysicalDisk:
%DiskTime计数器监视磁盘忙于读/写活动所用时间的百分比。
如果PhysicalDisk:
%DiskTime计数器的值较高(大于90%),请检查PhysicalDisk:
CurrentDiskQueueLength计数器了解等待进行磁
盘访问的系统请求数量。
等待I/O请求的数量应该保持在不超过组成物理磁盘的轴数的1.5到2倍。
大多数磁盘只有一个轴,但独立磁盘冗余阵列
(RAID)设备通常有多个轴。
硬件RAID设备在系统监视器中显示为一个物理磁盘。
通过软件创建的多个RAID设备在系统监视器中显示为多个实例。
可以使用CurrentDiskQueueLength和%DiskTime计数器的值检测磁盘子系统中的瓶颈。
如果CurrentDiskQueueLength和%DiskTime计数器的值一直很高,则考虑下列事项:
1.使用速度更快的磁盘驱动器。
2.将某些文件移至其他磁盘或服务器。
3.如果正在使用一个RAID阵列,则在该阵列中添加磁盘。
Avg.DiskQueueLength
指读取和写入请求(为所选磁盘在实例间隔中列队的)的平均数
CurrentDiskQueueLength
指示被挂起的磁盘I/O请求的数量。
如果这个值始终高于2,就表示产生了拥塞
Avg.DiskBytes/Transfer
写入或读取操作时向磁盘传送或从磁盘传出字节的平均数
DiskBytes/sec
在读写操作中,从磁盘传出或传送到磁盘的字节速率
Memory
Pages/sec
被请求页面的数量.
AvailableBytes
可用物理内存的数量
CommittedBytes
已分配给物理RAM用于存储或分配给页面文件的虚拟内存
PoolNonpagedBytes
未分页池系统内存区域中的RAM数量
PageFaults/sec
是每秒钟出错页面的平均数量
NetworkInterface
BytesReceived/sec
使用本网络适配器接收的字节数
BytesSent/sec
使用本网络适配器发送的字节数
BytesTotal/sec
使用本网络适配器发送和接收的字节数
Server
BytesReceived/sec
把此计数器与网络适配器的总带宽相比较,确定网络连接是否产生瓶颈
SQLServerAccessMethods
PageSplits/sec
每秒由于索引页溢出而发生的页拆分数.如果发现页分裂的次数很多,考虑提高Index的填充因子.数据页将会有更多的空间保留用于做数据的填充,从而减少页拆分
PagesAllocated/sec
在此SQLServer实例的所有数据库中每秒分配的页数。
这些页包括从混合区和统一区中分配的页
FullScans/sec
每秒不受限制的完全扫描数.这些扫描可以是基表扫描,也可以是全文索引扫描
SQLServer:
SQLStatistics
BatchRequests/Sec
每秒收到的Transact-SQL命令批数。
这一统计信息受所有约束(如I/O、用户数、高速缓存大小、请求的复杂程度等)影响。
批处理请求数值高意味着吞吐量
SQLCompilations/Sec
每秒的编译数。
表示编译代码路径被进入的次数。
包括SQLServer中语句级重新编译导致的编译。
当SQLServer用户活动稳定后,
该值将达到稳定状态
Re-Compilations/Sec
每秒语句重新编译的次数。
计算语句重新编译被触发的次数。
一般来说,这个数最好较小,存储过程在理想情况下应该只编译一次,
然后执行计划被重复使用.如果该计数器的值较高,或许需要换个方式编写存储过程,从而减少重编译的次数
SQLServer:
Databases
LogFlushes/sec
每秒日志刷新数目
ActiveTransactions
数据库的活动事务数
Backup/RestoreThroughput/sec
每秒数据库的备份和还原操作的读取/写入吞吐量。
例如,并行使用多个备份设备或使用更快的设备时,可以测量数据库备份操作性能的变化情况。
数据库的备份或还原操作的吞吐量可以确定备份和还原操作的进程和性能
SQLServerGeneralStatistics
UserConnections
系统中活动的SQL连接数.该计数器的信息可以用于找出系统的最大并发用户数
TempTablesCreationRate
每秒创建的临时表/表变量的数目
TempTablesForDestruction
等待被清除系统线程破坏的临时表/表变量数
SQLServerLocks
NumberofDeadlocks/sec
指每秒导致死锁的锁请求数.死锁对于应用程序的可伸缩性非常有害,并且会导致恶劣的用户体验.该计数器必须为0
AverageWaitTime(ms)
每个导致等待的锁请求的平均等待时间
Lockrequests/sec
锁管理器每秒请求的新锁和锁转换数.通过优化查询来减少读取次数,可以减少该计数器的值
SQLServer:
MemoryManager
TotalServerMemory(KB)
从缓冲池提交的内存(这不是SQLServer使用的总内存)
TargetServerMemory(KB)
服务器能够使用的动态内存总量
SQLCacheMemory(KB)
服务器正在用于动态SQL高速缓存的动态内存总数
MemoryGrantsPending
指每秒等待工作空间内存授权的进程数.该计数器应该尽可能接近0,否则预示可能存在着内存瓶颈
SQLServerBufferManager
BufferCacheHitRatio
缓存命中率,在缓冲区高速缓存中找到而不需要从磁盘中读取(物理I/O)的页的百分比.如果该值较低则可能存在内存不足或不正确的索引
PageReads/sec
每秒发出的物理数据库页读取数。
此统计信息显示的是所有数据库间的物理页读取总数。
由于物理I/O的开销大,可以通过使用更大的数据缓存、智能索引、更有效的查询或更改数据库设计等方法,将开销降到最低
PageWrites/sec
每秒执行的物理数据库页写入数
PageLifeExpectancy
页若不被引用将在缓冲池中停留的秒数
LazyWrites/Sec
每秒被缓冲区管理器的惰性编写器写入的缓冲区数
CheckpointPages/Sec
由要求刷新所有脏页的检查点或其他操作每秒刷新到磁盘的页数
提示:
当监视WindowsServer或SQLServer以调查与性能有关的问题时,请首选关注一下硬件的三方面:
(1)CPU(处理器使用率)
(2)RAM(内存使用率)
(3)HDD(磁盘活动即IO)
3.建立监视
下面要建立监视(我监视的HPServer配置为:
Intel4x4x3.0GHz/RAM16.0G,业务系统为OLTP).
(1)在performance->PerformanceLogsandAlerts->NewLogSetting...
(2)GeneralTab->AddCounters,添加需要监测的计数器(可参考如上的计数器列表)
(3)GeneralTab->Interval,设置监测的时间间隔(默认是15s)
(4)LogFilesTab->Logfiletype,选择LogFile保存的方式(textFile,BinaryFile,SQLDatabase),这里我选择textFile(Tabdelimited).
(5)ScheduleTab,设置监测的开始时间及结束时间.
4.分析(我做测试监测的时间段(2010/7/710:
30-23:
59))
在监测一段时间之后,你就会得到Server重要的性能计数器信息,接下来就可以分析Server的性能. 我是借助数据透视图来做的,看起来会比较直观.
4.1CPU使用率.分析%ProcessorTime(_Total)(所用时间的百分比,横轴取时间,竖轴取%ProcessorTime)
如下图在2010/7/710:
30-12:
40和2010/7/716:
44-18:
48这两段时间内CPU的使用率很高基本上都在50%以上.尤其在17:
00-17:
12,17:
53-18:
00CPU很繁忙,在这段时间会有大量的事务需要处理(T-SQL查询,SP,后台job,User操作等等).
如果CUP使用率一直居高不下(持续80%到90%的状态),就要考虑升级CPU,增加更多的处理器或者系统调优(建议先做系统调优,升级硬件需要增加额外的成本).
4.2磁盘I/O(%DiskTime,磁盘忙于读/写活动所用时间的百分比)
监视磁盘活动涉及到两个主要方面:
(1)监视磁盘I/O及检测是否有过度换页
(2)隔离SQLServer产生的磁盘活动
从做的数据透视图来看,磁盘I/O的读写很清闲,只在11:
58,15:
00,18:
00,23:
45左右(图上没有截出来)会有较大的IO.
如果磁盘I/O很高(>90%),则考虑更换快速磁盘(如固态硬盘等).
请参考微软给出的解决方案:
监视磁盘I/O及检测过度换页
可以对下面两个计数器进行监视以确定磁盘活动:
1.PhysicalDisk:
%DiskTime
2.PhysicalDisk:
Avg.DiskQueueLength
在系统监视器中,PhysicalDisk:
%DiskTime 计数器监视磁盘忙于读/写活动所用时间的百分比。
如果 PhysicalDisk:
%DiskTime 计数器的值较高(大于90%),请检查PhysicalDisk:
CurrentDiskQueueLength 计数器了解等待进行磁盘访问的系统请求数量。
等待I/O请求的数量应该保持在不超过组成物理磁盘的轴数的1.5到2倍。
大多数磁盘只有一个轴,但独立磁盘冗余阵列(RAID)设备通常有多个轴。
硬件RAID设备在系统监视器中显示为一个物理磁盘。
通过软件创建的多个RAID设备在系统监视器中显示为多个实例。
可以使用 CurrentDiskQueueLength 和 %DiskTime 计数器的值检测磁盘子系统中的瓶颈。
如果 CurrentDiskQueueLength 和 %DiskTime 计数器的值一直很高,则考虑下列事项:
1.使用速度更快的磁盘驱动器。
2.将某些文件移至其他磁盘或服务器。
3.如果正在使用一个RAID阵列,则在该阵列中添加磁盘。
如果使用RAID设备,%DiskTime 计数器会指示大于100%的值。
如果出现这种情况,则使用 PhysicalDisk:
Avg.DiskQueueLength 计数器来确定等待进行磁盘访问的平均系统请求数量。
I/O依赖的应用程序或系统可能会使磁盘持续处于活动状态。
监视 Memory:
PageFaults/sec 计数器可以确保磁盘活动不是由分页导致的。
在Windows中,换页的原因包括:
1.配置进程占用了过多内存。
2.文件系统活动。
如果在同一硬盘上有多个逻辑分区,请使用 LogicalDisk 计数器而非 PhysicalDisk 计数器。
查看逻辑磁盘计数器有助于确定哪些文件被频繁访问。
当发现磁盘有大量读/写活动时,请查看读写专用计数器以确定导致每个逻辑卷负荷增加的磁盘活动类型,例如,LogicalDisk:
DiskWriteBytes/sec。
隔离SQLServer产生的磁盘活动
可以进行监视以确定由SQLServer组件生成的I/O活动量的两个计数器为:
1.SQLServer:
BufferManager:
Pagereads/sec
2.SQLServer:
BufferManager:
Pagewrites/sec
在系统监视器中,这些计数器通过检查以下操作的性能监视由SQLServer组件生成的I/O活动量。
1.向磁盘写入页
2.从磁盘读取页
如果这些计数器的值达到硬件I/O子系统的容量限制,则需要减小这些值,方法是调整应用程序或数据库以减少I/O操作(如索引覆盖、索引优化或规范化),增加硬件的I/O容量或添加内存
4.3缓存命中率(BufferCacheHitRatio)
根据检测的数据来看,缓存命中率基本上在99.99%-100%之间,表示数据缓存几乎满足所有的数据请求.
4.4页拆分(PageSplits/sec,每秒由于索引页益处而发生的页拆分数)
如果页拆分很频繁,可以考虑增加填充因子(我设置的Indexfillfactor为85,也就是每个页会留有15%的空间做数据填充).
从我做的检测来看,只有在很少的时间段内会有较大的页拆分,此时可能会有大量的数据事务操作.总体来看性能还好.
4.5每秒日志刷新数目(LogFlushes/sec)
日志刷新发生在当transaction提交,数据从日志缓存写入磁盘日志文件时.应该尽可能的减少日志刷新.
如果检测到数值一直很高的话,说明transaction非常活跃,就要减少transaction数.
这里有一个简单的示例来说明:
比如说要向Table中Insert1w条数据
做法1:
一条一条的Insert,一个transaction一条.会产生1w个logflushes
做法2:
1w条数据在一个transactionInsert.只产生1个logflushes
明显的第二种产生的日志刷新会大大减少,相应的磁盘I/O也大大减少.从而有助于提高性能.
总结:
(1). 还有很多的日志记录没有做一一的简单分析.
(2).PerformanceMonitor只是提供一个方法来帮助发现问题,提供一个性能优化的方向.一旦影响性能的问题找到了,就可以从这个方向来着手处理.
(3).网上有很多性能检测的工具,大抵应该是把如上所做的工作封装起来,并且UI上面已经分析好,更加的直观.
(4). 如果写的有不当之处,欢迎指出指正,谢谢!
另:
参考文章:
http:
//www.sql-server-
微软对计数器更详细的描述:
本文标题:
SQLServer性能调教系列
(2)--ServerPerformanceMonitor(Perfmon)
链接:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 利用Performance Monitor实现SQL Server调优 利用 Performance Monitor 实现 SQL Server 调优