SQL的性能监控优化参考

　　最近需要查两台业务应用完全相同的数据库服务器，但执行性能却相差甚远的原因。突然想起来从前在SNDA的时候PT也发生过的性能问题。现在回头再想想，当时的分析还很不足。

 

　　数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。当一台服务器的执行性能出现问题，准确的找到问题来源就非常关键。合理利用操作系统自带的性能计数器可以帮助你定位故障的原因。

 

　　可惜我没有Patton的开发能力，所以只能定时取数据做日后分析，否则用他开发的monitor监控，还可以实现即时报警，那就更好了。

服务器上新建性能监控的日志，取所需计数器，设定计划任务定时启动或建立SQL JOB定时执行命令：logman start 计数器名 

添加计数器

计数器	描述
Memory: Available Bytes	内存可用字节数
Memory: Page Faults / sec	处理器硬/软页错误处理速率
Process: Working Set	进程占用内存量
Memory / Pages/sec	每秒磁盘读写页数
Physical Disk: Avg.Disk Queue Length	读取和写入请求(磁盘在实例间隔中列队的)平均数。
Physical Disk: Reads/sec	每秒磁盘读取操作速率
Physical Disk: Writes/sec	每秒磁盘写入操作速率
Processor: % Privileged Time	处理器执行内核命令所用时间百分比
Process: % Processor Time	处理器时间百分比（活跃程度）
Processor: %User Time	处理器执行用户进程百分比
SQL Server: Access Methods: Full Scans/sec	每秒完全扫描次数
SQL Server: Access Methods: Page splits/sec	每秒页分割数量
SQL Server: Buffer Manager: Buffer Cache Hit Ratio	缓冲区缓存命中率
SQL Server: Buffer Manager: Lazy Writes/sec	惰性写进程每秒写缓冲区数量
SQLServer: Cache Manager: Cache Hit Ratio	SQL快取中找到请求资料分页的时间比率
SQL Server: Latches: Latch Waits/sec	每秒闩锁等待数量
SQL Server: Locks: Average Wait Time	每个导致等待的锁请求的平均等待时间（毫秒）
SQLServer: Locks: Lock Requests/sec	每秒请求的锁个数
SQLServer: Locks: Lock Wait Time (ms)	SQL每秒锁等待
SQL Server: Memory Manager: Total Server Memory	服务器分配SQL可用内存总量
SQLServer: General Statistics/User Connections	SQL Server用户连接数
SQLServer: SQL Statistics/SQL Re-Compilations	每秒SQL重编译数

 

Memory: Page Faults / sec处理器硬/软页错误处理速率
如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

 

Process : Working Set进程占用内存量
该值保持在min server memory （0）和 max server memory （2147483647）服务器选项设置的内存量之间，则不需要调整工作集大小。

 

Memory / Pages/sec每秒磁盘读写页数
这会导致硬页故障，从而导致SQL Server依赖页文件而不是依赖内存。如果这个计数器的平均值为20，你可能需要添加额外的RAM来停止内存分页。

Physical Disk:Avg.Disk Queue Length读取和写入请求(磁盘在实例间隔中列队的)平均数。
该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。
 
Processor: % Privileged Time处理器执行内核命令所用时间百分比
值越小越好

 

Process:%Processor Time处理器时间百分比（活跃程度）
如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

 

Processor: %User Time处理器执行用户进程百分比
Processor: %User Time计数器值高于Processor: % Privileged Time 计数器值，表明该CPU 使用率可能来自执行用户进程（例如 SQL Server）。优化应用程序可以降低 CPU 的使用率。

 

SQL Server : Access Methods: Full Scans/sec每秒完全扫描次数
合理范围：<5

SQL Server : Access Methods: Page splits/sec每秒页分割数量
合理范围：越小越好，可降低填满因子的设定值或重建索引还减少每秒的页分割数量。

 

SQL Server : Buffer Manager:Buffer Cache Hit Ratio缓冲区缓存命中率
比率最好为90% 或更高。表示90% 以上的数据请求可以从数据缓冲区中获得所需数据。

 

SQL Server : Buffer Manager: Lazy Writes/sec惰性写进程每秒写缓冲区数量
合理范围：0

 

SQLServer : Cache Manager: Cache Hit RatioSQL快取中找到请求资料分页的时间比率
该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

 

SQL Server : Latches: Latch Waits/sec每秒闩锁等待数量
合理范围：越小越好，值过大表明服务器可能存在对资源的竞争问题。

 

SQL Server : Memory Manager: Total Server Memory服务器分配SQL可用内存总量
如果内存等于机器上的物理内存总量，则可能存在内存争用，因为没有给windows留下任何RAM以执行通常的操作

 

SQLServer:General Statistics/User Connections  SQL Server用户连接数
由于每个用户连接都消耗一些内存，配置的用户连接数过高会影响吞吐量。这个数字跳到基线的500％以上，可能使系统速度变慢。

 

SQLServer:SQL Statistics/SQL Re-Compilations每秒SQL重编译数
如果这个数字过高，存储过程执行计划可能无法适当地缓存。

 

　　影响性能的因素很多，而应用又各不相同，找出一个通用的优化方案是很困难的，只能是在系统开发和维护的过程中针对运行的具体情况，不断加以调整。

　　以上只是我平时去取的计数器，还有非常多的计数器可以帮助管理员更好的分析性能，有需要可以再Google。