利用PerformanceMonitor实现SQLServer调优.docx

利用Performance Monitor实现SQL Server调优性能监视的工具有很多,首先介绍Microsoft Windows Server自带的Performance Monitor. Windows性能监视器是一个很好用的工具,可以实时检查运行程序影响计算机性能的方式(CPU,ROM,IO等),并通过收集日志数据供以后分析使用. 通过性能监视能了解系统loading以及这种loading对系统资源的影响, 分析性能或者资源使用率的变化趋势, 有效的对系统做出调整, 优化或者升级. 诊断系统故障或确定优化的组件或升级的步骤, 也可以找出性能瓶颈.Performance Monitor是一个系统内置的MMC控制台: 包括系统监视器(System Monitor)和性能日志和警报(Performance Logs and Alerts)两个部分.通过实时和日志的方式来记录服务器性能. 使用系统监视器可以取现, 曲方图或者报表的方式实时查看内存, 硬盘, 处理器, 网络等各种对象的性能数据. 使用性能日志也警报可以对计数器日志进行配置, 记录性能数据, 设置性能警报, 通过设定性能警报, 可以使系统在某一特定的计数器值低于或高于指定的值时及时通知系统管理员.下面简单介绍Windows Server 2003下的Performance Monitor, 通过日志记录性能数据, 之后分析.1. 打开:Administrative Tools-Performance,或SQL Server Profiler-Tools-Performance Monitor,或在运行中输入perfmon2.重要的性能计数器(1). Processor(2).PhysicalDisk(3).Memory(4). Network Interface(5). SQL Server Access Methods(6). SQL Server: SQL Statistics(7). SQL Server: Databases(8). SQL Server General Statistics(9). SQL Server Locks(10). SQL Server Buffer Manager下表对重要的性能计数器做一个简要的说明: 性能计数器:Performance ObjectCounterDescriptionProcessor%processor Time指处理器执行非闲置线程时间的百分比,测量处理器繁忙的时间 这个计数器设计成用来作为处理器活动的主要指示器,可以选择单个CPU实例,也可以选择TotalInterrupts/sec处理器正在处理的来自应用程序或硬件的中断的数量PhysicalDisk% Disk Time计数器监视磁盘忙于读/写活动所用时间的百分比.在系统监视器中，PhysicalDisk: % Disk Time 计数器监视磁盘忙于读/写活动所用时间的百分比。如果 PhysicalDisk: % Disk Time 计数器的值较高（大于 90%），请检查 PhysicalDisk: Current Disk Queue Length 计数器了解等待进行磁盘访问的系统请求数量。等待 I/O 请求的数量应该保持在不超过组成物理磁盘的轴数的 1.5 到 2 倍。大多数磁盘只有一个轴，但独立磁盘冗余阵列 (RAID) 设 备通常有多个轴。硬件 RAID 设备在系统监视器中显示为一个物理磁盘。通过软件创建的多个 RAID 设备在系统监视器中显示为多个实例。可以使用 Current Disk Queue Length 和 % Disk Time 计数器的值检测磁盘子系统中的瓶颈。如果 Current Disk Queue Length 和 % Disk Time 计数器的值一直很高，则考虑下列事项：1.使用速度更快的磁盘驱动器。2.将某些文件移至其他磁盘或服务器。3.如果正在使用一个 RAID 阵列，则在该阵列中添加磁盘。计数器监视磁盘忙于读/写活动所用时间的百分比.在系统监视器中，PhysicalDisk: % Disk Time 计数器监视磁盘忙于读/写活动所用时间的百分比。如果 PhysicalDisk: % Disk Time 计数器的值较高（大于 90%），请检查 PhysicalDisk: Current Disk Queue Length 计数器了解等待进行磁盘访问的系统请求数量。等待 I/O 请求的数量应该保持在不超过组成物理磁盘的轴数的 1.5 到 2 倍。大多数磁盘只有一个轴，但独立磁盘冗余阵列 (RAID) 设备通常有多个轴。硬件 RAID 设备在系统监视器中显示为一个物理磁盘。通过软件创建的多个 RAID 设备在系统监视器中显示为多个实例。可以使用 Current Disk Queue Length 和 % Disk Time 计数器的值检测磁盘子系统中的瓶颈。如果 Current Disk Queue Length 和 % Disk Time 计数器的值一直很高，则考虑下列事项：1.使用速度更快的磁盘驱动器。2.将某些文件移至其他磁盘或服务器。3.如果正在使用一个 RAID 阵列，则在该阵列中添加磁盘。Avg.Disk Queue Length指读取和写入请求(为所选磁盘在实例间隔中列队的)的平均数Current Disk Queue Length指示被挂起的磁盘 I/O 请求的数量。如果这个值始终高于 2， 就表示产生了拥塞Avg.Disk Bytes/Transfer写入或读取操作时向磁盘传送或从磁盘传出字节的平均数Disk Bytes/sec在读写操作中，从磁盘传出或传送到磁盘的字节速率MemoryPages/sec被请求页面的数量.Available Bytes可用物理内存的数量Committed Bytes已分配给物理 RAM 用于存储或分配给页面文件的虚拟内存Pool Nonpaged Bytes未分页池系统内存区域中的 RAM 数量Page Faults/sec是每秒钟出错页面的平均数量Network InterfaceBytes Received/sec使用本网络适配器接收的字节数Bytes Sent/sec使用本网络适配器发送的字节数Bytes Total/sec使用本网络适配器发送和接收的字节数ServerBytes Received/sec把此计数器与网络适配器的总带宽相比较，确定网络连接是否产生瓶颈SQL Server Access MethodsPage Splits/sec每秒由于索引页溢出而发生的页拆分数.如果发现页分裂的次数很多,考虑提高Index的填充因子.数据页将会有更多的空间保留用于做数据的填充,从而减少页拆分Pages Allocated/sec在此 SQL Server 实例的所有数据库中每秒分配的页数。这些页包括从混合区和统一区中分配的页Full Scans/sec每秒不受限制的完全扫描数. 这些扫描可以是基表扫描，也可以是全文索引扫描SQL Server: SQL StatisticsBatch Requests/Sec每秒收到的 Transact-SQL 命令批数。这一统计信息受所有约束（如 I/O、用户数、高速缓存大小、请求的复杂程度等）影响。批处理请求数值高意味着吞吐量SQL Compilations/Sec每秒的编译数。表示编译代码路径被进入的次数。包括 SQL Server 中语句级重新编译导致的编译。当 SQL Server 用户活动稳定后，该值将达到稳定状态Re-Compilations/Sec每秒语句重新编译的次数。计算语句重新编译被触发的次数。一般来说，这个数最好较小,存储过程在理想情况下应该只编译一次，然后执行计划被重复使用. 如果该计数器的值较高，或许需要换个方式编写存储过程，从而减少重编译的次数SQL Server: DatabasesLog Flushes/sec每秒日志刷新数目Active Transactions数据库的活动事务数Backup/Restore Throughput/sec每秒数据库的备份和还原操作的读取/写入吞吐量。例如，并行使用多个备份设备或使用更快的设备时,可以测量数据库备份操作性能的变化情况。数据库的备份或还原操作的吞吐量可以确定备份和还原操作的进程和性能SQL Server General StatisticsUser Connections系统中活动的SQL连接数. 该计数器的信息可以用于找出系统的最大并发用户数Temp Tables Creation Rate每秒创建的临时表/表变量的数目Temp Tables For Destruction等待被清除系统线程破坏的临时表/表变量数SQL Server LocksNumber of Deadlocks/sec指每秒导致死锁的锁请求数. 死锁对于应用程序的可伸缩性非常有害, 并且会导致恶劣的用户体验. 该计数器必须为0Average Wait Time (ms)每个导致等待的锁请求的平均等待时间Lock requests/sec锁管理器每秒请求的新锁和锁转换数. 通过优化查询来减少读取次数, 可以减少该计数器的值SQL Server:Memory ManagerTotal Server Memory (KB)从缓冲池提交的内存(这不是 SQL Server 使用的总内存)Target Server Memory (KB)服务器能够使用的动态内存总量SQL Cache Memory(KB)服务器正在用于动态 SQL 高速缓存的动态内存总数Memory Grants Pending指每秒等待工作空间内存授权的进程数. 该计数器应该尽可能接近0,否则预示可能存在着内存瓶颈SQL Server Buffer ManagerBuffer Cache Hit Ratio缓存命中率,在缓冲区高速缓存中找到而不需要从磁盘中读取(物理I/O)的页的百分比. 如果该值较低则可能存在内存不足或不正确的索引Page Reads/sec每秒发出的物理数据库页读取数。此统计信息显示的是所有数据库间的物理页读取总数。由于物理 I/O 的开销大，可以通过使用更大的数据缓存、智能索引、更有效的查询或更改数据库设计等方法，将开销降到最低Page Writes/sec每秒执行的物理数据库页写入数Page Life Expectancy页若不被引用将在缓冲池中停留的秒数Lazy Writes/Sec每秒被缓冲区管理器的惰性编写器写入的缓冲区数Checkpoint Pages/Sec由要求刷新所有脏页的检查点或其他操作每秒刷新到磁盘的页数提示: 当监视Windows Server或SQL Server以调查与性能有关的问题时,请首选关注一下硬件的三方面:(1) CPU(处理器使用率)(2) RAM(内存使用率)(3) HDD(磁盘活动即IO)3.建立监视下面要建立监视(我监视的HP Server配置为:Intel 4x4 x 3.0 GHz/RAM 16.0G,业务系统为OLTP).(1) 在performance-Performance Logs and Alerts-New Log Setting.(2) General Tab-Add Counters,添加需要监测的计数器(可参考如上的计数器列表)(3) General Tab-Interval,设置监测的时间间隔(默认是15s)(4) Log Files Tab-Log file type,选择Log File保存的方式(text File,Binary File,SQL Database),这里我选择text File(Tab delimited).(5) Schedule Tab,设置监测的开始时间及结束时间.4.分析(我做测试监测的时间段(2010/7/7 10:30-23:59)在监测一段时间之后,你就会得到Server重要的性能计数器信息,接下来就可以分析Server的性能.我是借助数据透视图来做的,看起来会比较直观.4.1 CPU使用率.分析%Processor Time(_Total)(所用时间的百分比,横轴取时间,竖轴取%Processor Time)如下图在2010/7/7 10:30-12:40和2010/7/7 16: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(%Disk Time,磁盘忙于读/写活动所用时间的百分比)监视磁盘活动涉及到两个主要方面:(1)监视磁盘I/O及检测是否有过度换页(2)隔离SQL Server产生的磁盘活动从做的数据透视图来看,磁盘I/O的读写很清闲,只在11:58,15:00,18:00,23:45左右(图上没有截出来)会有较大的IO.如果磁盘I/O很高(90%),则考虑更换快速磁盘(如固态硬盘等).请参考微软给出的解决方案:监视磁盘 I/O 及检测过度换页可以对下面两个计数器进行监视以确定磁盘活动：1. PhysicalDisk: % Disk Time2. PhysicalDisk: Avg. Disk Queue Length在系统监视器中，PhysicalDisk:% Disk Time计数器监视磁盘忙于读/写活动所用时间的百分比。如果PhysicalDisk: % Disk Time计数器的值较高（大于 90%），请检查PhysicalDisk: Current Disk Queue Length计数器了解等待进行磁盘访问的系统请求数量。等待 I/O 请求的数量应该保持在不超过组成物理磁盘的轴数的 1.5 到 2 倍。大多数磁盘只有一个轴，但独立磁盘冗余阵列 (RAID) 设备通常有多个轴。硬件 RAID 设备在系统监视器中显示为一个物理磁盘。通过软件创建的多个 RAID 设备在系统监视器中显示为多个实例。可以使用Current Disk Queue Length和% Disk Time计数器的值检测磁盘子系统中的瓶颈。如果Current Disk Queue Length和% Disk Time计数器的值一直很高，则考虑下列事项：1. 使用速度更快的磁盘驱动器。2. 将某些文件移至其他磁盘或服务器。3. 如果正在使用一个 RAID 阵列，则在该阵列中添加磁盘。如果使用 RAID 设备，% Disk Time计数器会指示大于 100% 的值。如果出现这种情况，则使用PhysicalDisk: Avg.Disk Queue Length计数器来确定等待进行磁盘访问的平均系统请求数量。I/O 依赖的应用程序或系统可能会使磁盘持续处于活动状态。监视Memory: Page Faults/sec计数器可以确保磁盘活动不是由分页导致的。在 Windows 中，换页的原因包括：1. 配置进程占用了过多内存。2. 文件系统活动。如果在同一硬盘上有多个逻辑分区，请使用Logical Disk计数器而非Physical Disk计数器。查看逻辑磁盘计数器有助于确定哪些文件被频繁访问。当发现磁盘有大量读/写活动时，请查看读写专用计数器以确定导致每个逻辑卷负荷增加的磁盘活动类型，例如，Logical Disk: Disk Write Bytes/sec。隔离 SQL Server 产生的磁盘活动可以进行监视以确定由 SQL Server 组件生成的 I/O 活动量的两个计数器为：1. SQL Server:Buffer Manager:Page reads/sec2. SQL Server:Buffer Manager:Page writes/sec在系统监视器中，这些计数器通过检查以下操作的性能监视由 SQL Server 组件生成的 I/O 活动量。1. 向磁盘写入页2. 从磁盘读取页如果这些计数器的值达到硬件 I/O 子系统的容量限制，则需要减小这些值，方法是调整应用程序或数据库以减少 I/O 操作（如索引覆盖、索引优化或规范化），增加硬件的 I/O 容量或添加内存4.3 缓存命中率(Buffer Cache Hit Ratio)根据检测的数据来看,缓存命中率基本上在99.99%-100%之间,表示数据缓存几乎满足所有的数据请求.4.4 页拆分(Page Splits/sec,每秒由于索引页益处而发生的页拆分数)如果页拆分很频繁,可以考虑增加填充因子(我设置的Index fill factor为85,也就是每个页会留有15%的空间做数据填充).从我做的检测来看,只有在很少的时间段内会有较大的页拆分,此时可能会有大量的数据事务操作.总体来看性能还好.4.