SQL Server: PERFMON counters to identify server utilization data

Published 18 June 08 05:21 AM | satyaskj 

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.

Comments

# Other SQL Server Blogs around the Web said on June 18, 2008 6:23 AM:

Recently I have been involved to carry over an assesment of server utilization on the SQL estate farm

# SQL Server Security, Performance & Tuning (SSQA.net) : SQL Server: PERFMON counters to identify server utilization data said on June 19, 2008 9:28 PM:

PingBack from http://sqlserver-qa.net/blogs/perftune/archive/2008/06/18/4482.aspx

Anonymous comments are disabled