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 | 2 | |
| 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.
