As an instructor I always tell my students to make a performance baseline on their SQL Server so that they have something to compare against, when (not if) the users complain about performance. But my students and customers often ask – which counters should I monitor then?
Well here is, to my experience, a good list to start with. Arguably you could narrow or widen the list – but for a start this list has in my experience proven to be sufficient.
Performance object | Counter | Warning | Alert | Notes | |||
Memory | Pages/sec | 100 | 200 | I know that many sites say 20 – but that was from the NT4 days with 128 MB RAM | |||
Memory | Available MBytes | 200 | 100 | ||||
SQL Server: Buffer Manager | Buffer Cache Hit Ratio | 90 | 85 | ||||
SQL Server: Buffer Manager | Page Life Expectancy | 350 | 300 | ||||
These should be per physical disk | |||||||
Physical Disk | Disk Reads/sec | No levels – just monitor these | |||||
Physical Disk | Disk Writes/sec | No levels – just monitor these | |||||
Physical Disk | % Disk time | No levels – just monitor these | |||||
Physical Disk | % Disk time | No levels – just monitor these | |||||
Physical Disk | Avg. Disk Queue Length | 1,5 | 2 | This is x times the number of spindles in the raid set. E.g. Raid 1 with 2 disk = 4 | |||
Processor | % Processor Time | 70 | 80 | ||||
System | Processor Queue Length | ||||||
Network Interface | Bytes Received/sec | No levels – just monitor these | |||||
Network Interface | Bytes Sent/sec | No levels – just monitor these | |||||
Network Interface | Output Queue Length | No levels – just monitor these | |||||
SQL Server: General | User Connections | No levels – just monitor these | |||||
SQL Server: Memory Manager | Connection Memory (KB) | No levels – just monitor these | |||||
SQL Server: Memory Manager | Total Server Memory (KB) | No levels – just monitor these |
You don't have to monitor the counters all the time to create a good baseline, the warning and alert levels are included for those of you who want to implement the counters in your normal performance monitoring system like MOM.
No comments:
Post a Comment