http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx
http://www.sql-server-performance.com/tips/monitor_io_counters_p1.aspx
http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx
Issue | Performance Counter | Monitor Type | Normal Value | Solution |
the number of pages per second that are paged out of RAM to disk, or paged into RAM from disk. | Memory Object: Pages/Sec | Memory | paging will average near zero. | The more RAM a server has, the less paging it has to perform. |
check to see if your SQL Server has enough physical RAM | Memory Object: Available Bytes counter. | Memory |
| This value should be greater than 5MB. If not, then your SQL Server needs more physical RAM. |
how much the mssqlserver service is currently using | SQLServer:Memory Manager: Total Server Memory (KB) | Memory |
|
|
how much memory SQL Server would like to have in order to operate efficiently | SQLServer:Memory Manager: Target Server Memory (KB) . | Memory |
|
|
each disk array
if you have an array of 6 physical disks, and the Avg. Disk Queue Length is 10 for a particular array, then the actual Avg. Disk Queue Length for each drive is 1.66 (10/6=1.66), which is within the recommended 2 per physical disk. | PhysicalDisk Object: Avg. Disk Queue Length | I/O | If the Avg. Disk Queue Length exceeds 2 for continuous periods (over 10 minutes or so) for each individual disk drive in an array, then you probably have an I/O bottleneck for that array | If your server has an I/O bottleneck, consider these potential solutions: adding drives to an array (if you can), getting faster drives, adding cache memory to the controller card (if you can), using a different version of RAID, getting a faster controller, or reducing the load on the server. |
how busy a physical array is (not a logical partition or individual disks in an array) | Physical Disk Object: % Disk Time counter
% Disk Read Time counter
% Disk Write Time | I/O | the % Disk Time counter should run less than 55%. If this counter exceeds 55% for continuous periods (over 10 minutes or so), then your SQL Server may be experiencing an I/O bottleneck. |
|
the utilization of each individual CPU | Process Object: % Processor Time | CPU | if the total CPU utilization of your server exceeds 80% for continuous periods (over 10 minutes or so), then you may have a CPU bottleneck. |
|
Processor Queue Length | System Object: Processor Queue Length | CPU | If the Processor Queue Length exceeds 2 per CPU for continuous periods (over 10 minutes or so), then you probably have a CPU bottleneck. |
|
| System: % Total Privileged Time |
| If you notice that the System: % Total Privileged Time counter is running at greater than 20%, this is an indication that your server's I/O may be bottlenecked. |
|