未来已来

发现并解决SqlServer性能问题的通用方法

上一篇 / 下一篇  2008-11-04 17:18:42 / 个人分类:性能测试

 

Step 1: CPU usage
首先要通过通过了解CPU使用率来诊断SQL Server性能,
通常是方法有两种:
第一:你可以使用远程桌面的察看任务管理器中CPU的使用率
第二:监控以下计数器
Processor: % Privileged Time
Processor: % Processor Time
Processor: % User Time
System: Context Switches/sec
System: Processor Queue Length

Step 2: Disk IO
监控性能计算器:
Physical Disk: % Disk Read Time
Physical Disk: % Disk Write Time
Physical Disk: % Idle Time
Physical Disk: Avg Disk Bytes/Read
Physical Disk: Avg Disk Bytes/Transfer
Physical Disk: Avg Disk Bytes/Write
Physical Disk: Avg Disk Queue Length
Physical Disk: Current Disk Queue Length

Step 3: Memory
内存指标:
Memory: Available Bytes
Memory: Pages/sec
Process: Working Set
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB)
SQLServer:Cache Manager - Cache Hit Ratio - _Total
SQLServer:Cache Manager - Cache Pages - _Total
Memory: Page Reads/sec
Memory: Page Writes/sec
Memory: Page Input/sec
Memory: Page Output/sec
Paging File: % Usage


Step 4: Network
网络监控:
Network Interface: Bytes Received/sec
Network Interface: Bytes Sent/sec
Network Interface: Bytes Total/sec
Network Interface: Current Bandwidth
Network Interface: Output Queue Length

Step 5: Transaction-level performance
通过SQL事件探查捕捉事务运行情况。
Sp_monitor :快照的SQL Server统计
Sp_who :SQL Server进程快照
Master.dbo.sysprocesses :存储SQL Server进程有关的信息的系统表
DBCC OPENTRAN
DBCC INPUTBUFFER


TAG: 性能测试

 

评分:0

我来说两句

Open Toolbar