It was recently brought to my attention that I neglected an important piece of information in my first post (Thanks Lance!).
I get a lot of questions about the appropriate Windows Performance counters to capture for SQL server analysis. This post is cheating; from now on I get to send out a simple link!
LogicalDisk Perfmon Object
- Average Disk sec/Read
- Average Disk sec/Write
- Disk Reads/sec
- Disk Writes/sec
- Disk Read Bytes/Sec
- Disk Write Bytes/Sec
SQL Server Buffer Manager Perfmon Object
- Checkpoint pages/sec
- Page Reads/sec
- Readahead pages/sec
Processor
- % Processor Time
- Sytem: Processor Queue Length
Memory
- Available Mbytes
Table 1: Windows Perfmon Counters
LogicalDisk Perfmon Object
The Average Disk Sec/Read and Write counters measure the time it takes for an input output (I/O) operation to get from the calling application (in this case SQL) to the storage system and back. This measure is the single biggest indicator of I/O system health. These counters are a demonstration of Little's Law; as a systems capacity reaches its maximum performance its response time approaches infinity.
An optimal system design will balance latency with performance. The optimal performance region is called the knee of the curve. The knee demonstrates the maximum throughput that can be achieved with acceptable application latency.
Note: I will have an entire series on I/O performance benchmarking, stay tuned!
Figure 1: Little's Law
The latency counters are measured in milliseconds. A reading of 0.001 is one millisecond, 0.010 is ten ms, and .100 is a measure of 100 ms. Good and bad latency changes depending on many factors (these factors are way beyond the scope of this post). A good rule of thumb is latency over 25ms (or .025) is bad.
Throughput is measured as the number of requests and physical amount of data flowing through the I/O subsystem. The counters Disk Reads and writes per second show how many requests are being made of the system. Disk read and write Bytes per second record how much physical data is being moved.
To calculate average I/O sizes simply divide Bytes per second by the number of operations per second. Knowing the size of the I/O can show application behavior. For instance a SQL data base will write 8K data pages and read 64K data extents. When SQL server 2008 needs large volumes of contiguous data it will start reading 128 or 256K I/Os. This can greatly reduce overhead and increase performance. It will also show empirical evidence that the I/O patterns are sequential.
The Disk Queue Length count is affirmation of a busy system. If the system demonstrates a high Queue and low latency then things are great! If the Queue is high and the latency is high then the I/O system may be exceeding its maximum available performance or it may have an issue.
SQL Server Buffer Manager Perfmon Object
Each SQL Server instance measures Buffer Manager Activity. These counters help the operator distinguish scan from seek activity. Scan activity is a sequential operation associated with high throughput and seek operations are usually random. The random operations are associated with lower throughput.
Checkpoint pages/sec measures the number of 8K DB pages written per second when checkpoints are issued.
Page Reads/Sec measures the number of page read operations issued by the SQL engine every second.
Readahead pages/Sec measures how many read ahead operations issued by SQL server. SQL 2005 and newer use a read ahead engine that varies I/O sizes from 8K to 512K in size. More sequential operations will usually be larger in size.
Processor
The % Processor time counter shows the processor load on the server. Busy processors are not bad (quite the opposite). Busy processors with bad SQL performance with low memory use and fast I/O are an indication that the processors are a bottleneck.
Note: On Virtual Servers (Thanks to Brent Ozar for this Tip) % Processor time does not reflect what is actually happening on the server.
Use System: Processor Queue Length to determine how many tasks are waiting on the CPU.
Memory
The Available Mbytes counter shows how much memory is in use. High memory use is not in and of itself a bad thing (especially if SQL server is configured to use a lot of RAM). High memory use can indicate a bottleneck. Use the memory counters along with the other counters listed here to identify bottlenecks.
Other Resources
The Microsoft SQL Cat team has an excellent white paper detailing SQL performance: http://sqlcat.com/whitepapers/archive/2010/05/10/analyzing-i-o-characteristics-and-sizing-storage-systems-for-sql-server-database-applications.aspx
If you ever get the chance to see Mike Ruthruff speak seize the opportunity! Besides being a co-author of this paper he is an absolute expert in SQL Server performance tuning.
