SQL Server: PERFMON counters to identify server utilization data
Recently I have been involved to carry over an assesment of server utilization on the SQL estate farm (nearly 150+) instances, as it stands it is not an easy job to obtain the results when you have no influence on accessing required information on a shared platform. So in order to get the results quickly as you might guess using PERFMON *(SYSMON) tool is best to go with for initial assesment. As it defines within the SYSMON tool getting Collection of counter logs with Performance Logs and Alerts option instantly we created set of jobs against business importance servers that are having multiple SQL Server instances.
I was told that there may be third party tools available from the vendor such as Quest or IDERA with a built-in data-collection model process that can configured for automated data collection against multiple servers across multiple domains, and also Systems Management Server (SMS) from Microsoft also helps to some extent. Neither of these tools were available at our end so the obvious choice is to go with manual precision of using PERFMON tool.
I would like to share the required collection of counters that will be useful to obtain server utilization information if you have similar exercise or process to obtain.
|
Object |
Counter |
|
Processor |
%Processor Time |
|
Processor Queue Length |
|
Memory |
Available Bytes |
|
Page Faults/Sec |
|
Physical disk |
% Disk Time |
|
Avg. Disk sec/Read |
|
Avg. Disk sec/Write |
|
Current Disk Queue Length |
|
Disk Reads/sec |
|
Disk Writes/sec |
|
Avg. Disk Queue Length |
|
Server |
Server Sessions |
|
SQL Server: cache manager |
Cache Hit Ratio |
|
SQL Server: databases |
Transactions/Sec |
|
SQL Server: general statistics |
User Connections |
|
System |
File Read Operations/sec |
|
File Write Operations/sec |
|
Processor Queue Length |
I hope this will help others too.