Perfmon for SQL Server Analysis
At the moment I'm using Perfmon to gather information on an SQL server and application I'm testing.
To know what are the best counters to use, I headed over to this great post by Brent Ozar where he explains in detail what perfmon counters you need and how you setup them up.
Here's what he recommends:
Performance Monitor Counters for SQL Server Analysis
These are listed OBJECT first, then COUNTER
Of course, that's the easy part. The real difficulty is being able to parse the log file and know what to do with the data.
Thankfully, Brent describes how to format the results in excel. He also describes how to analyze the data, starting with the the CPU, then Memory and finally Disk Metrics.
Personally, I'm going to go to my client's DBA and ask them what he thinks of the data, as he knows what I'm testing. It's not in my clients best interest to spend excessive amounts of time analysing this type of data when some-one internally is able to do so far quicker.
To know what are the best counters to use, I headed over to this great post by Brent Ozar where he explains in detail what perfmon counters you need and how you setup them up.
Here's what he recommends:
Performance Monitor Counters for SQL Server Analysis
These are listed OBJECT first, then COUNTER
- Memory - Available MBytes
- Paging File - % Usage
- Physical Disk - % Disk Time
- Physical Disk - Avg. Disk Queue Length
- 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
- System - Processor Queue Length
Of course, that's the easy part. The real difficulty is being able to parse the log file and know what to do with the data.
Thankfully, Brent describes how to format the results in excel. He also describes how to analyze the data, starting with the the CPU, then Memory and finally Disk Metrics.
Personally, I'm going to go to my client's DBA and ask them what he thinks of the data, as he knows what I'm testing. It's not in my clients best interest to spend excessive amounts of time analysing this type of data when some-one internally is able to do so far quicker.