http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
Excellent video in there, well worth watching. Yes, the video is from 2006, but even now, it is just as relevant.
A little after I saw the blog post, I got the chance to put this in practice too; A customer suspected his SQL cluster wasn't performing too well. I added the counters from the video to the perfmon counter log, and kept the perfmon running.
Then our customer came back after a half a year; he found the SQL performance had degraded even more. We had a nice list of performance statistics now that went deeper than just CPU and memory usage. Now, I got the number of User Connections, and saw that it went up 4 times.
The perfmon counters Brent Ozar was talking about:
- These are listed OBJECT first, then COUNTER
- Memory – Available MBytes
- Paging File – % Usage
- Physical Disk – Avg. Disk sec/Read
- Physical Disk – Avg. Disk sec/Write
- Physical Disk – Disk Reads/sec
- Physical Disk – Disk Writes/sec
- Processor – % Processor Time
- SQLServer: Buffer Manager – Buffer cache hit ratio
- SQLServer: Buffer Manager – Page life expectancy
- SQLServer: General Statistics – User Connections
- SQLServer: Memory Manager – Memory Grants Pending
- SQLServer: SQL Statistics – Batch Requests/sec
- SQLServer: SQL Statistics – Compilations/sec
- SQLServer: SQL Statistics – Recompilations/sec
- System – Processor Queue Length
To find out what they all mean, see the video:
No comments:
Post a Comment