THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

The Most Important Performance Monitor Counters for SQL Server

If you're well-versed in using PerfMon, I'd like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it's a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you've ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They'll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It's a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

P.S. I cover a variety of PerfMon counters in these screencasts.  Many of them are discussed in an excellent document put together by Microsoft PSS.  Read their blog at http://blogs.msdn.com/psssql/default.aspx.

Published Friday, March 28, 2008 3:18 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

this really requires a full week to explain, but a short version of what i wrote else where:

First, most counter values do not have absolute point in time good or bad values. If you want great values, don’t let users connect to your database. Users are evil. So are developers for that matter. So don’t let developers write code. But I digress.

Consider CPU or disk latency. It is hard to keep these values from going high. So its more important to keep the period they are high short, unless you feel suffering builds character, and your users need more character.

CPU: >70% is poor for OLTP, 50% w/HT, individual procs > 70% is also a concern. Batch and DW have no limits

SQL Compiles/sec & Recompiles/sec

<2/sec negligible, 2-20/sec could be investigated, 20-100 poor, >100 potentially serious, I would really like a means of assessing the cost of compiles, as simple statement compile cost is low, a complex query could take 1min to compile.

High logout rate is bad because true logins are expensive. The login counter includes both true logins and connection pooling logins.

SQL Batch/sec > 10K is a client application warning, along with high CPU on individual CPU with high kernel time.

System Context Switches/sec – does it track with SQL Batches/sec, network and IO?.

SQL Batches/sec, Transactions/sec, Network Packet/sec Received and Sent

I watch disk reads & writes, bytes/sec to assess disk system capability and what is happening

The best metric for quality is latency – Avg Disk sec/read, write,

Queue is not helpful in a disk array because you don’t know how disks are there

A really stupid counter people keep looking is % disk time. This has no meaningful meaning on a server other than its doing something.

A highly misinterpret counter is network bandwidth %, few situations will really use the full bandwidth. Round trip latency is for more important.

March 28, 2008 3:55 PM
 

Ranga said:

Very good presentations...saw all the three of them and were very good....Please keep them coming

April 23, 2008 3:06 PM
 

Saddiq said:

Your comments - "P.S. I cover a variety of PerfMon counters in these screencasts.  Many of them are discussed in an excellent document put together by Microsoft PSS.  Read their blog at http://blogs.msdn.com/psssql/default.aspx"

Where can I find this document?

August 19, 2008 6:30 AM
 

KKline said:

Saddiq, I can't seem to find the URL.  I wonder if they took it down.  However, if you send me an email directly, I'll send you the files.  -Kevin

September 8, 2008 11:43 AM
 

hichamkari said:

Your comments - "P.S. I cover a variety of PerfMon counters in these screencasts.  Many of them are discussed in an excellent document put together by Microsoft PSS.  Read their blog at http://blogs.msdn.com/psssql/default.aspx"

Can I have also a copy of this document, mijn email is hichamkari@hotmail.com.

Thanks

November 11, 2008 6:40 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

aspiringgeek said:

A great post indeed!  PerfMon is a powerful, vastly underutilized tool.  As I travel from customer to customer, I'm surprised at  all the really smart DBAs I work with who don't know how to use PerfMon.  I'm sympathetic--I used to be one of them. ;-)  Here's my list, which I keep up-to-date: http://blogs.msdn.com/jimmymay/archive/2008/10/15/perfmon-objects-counters-thresholds-utilities-for-sql-server.aspx

December 31, 2008 10:59 AM
 

vinay said:

Hi can you anyone suggest me how to monitor 'Server' services running on Windwos 2003 server. I want to create counter which monitor this service so that i can evaluate how often this services is stopping/starting.

I tried a lot no clue found.

Thanks in advance

December 28, 2009 2:53 AM
 

KKline said:

Hi Vinay,

You can do that thru either PowerShell or, I think, a simple check using NET STATISTICS SERVER from the command prompt.

Hope this helps,

-Kevin

January 4, 2010 2:43 PM
 

Reddy said:

Hi, I am looking for sql counters, I would like to add perfmon counters to sql server, please give advise what are the counters required in perfmon,

Thanks

Reddy

August 6, 2010 12:03 PM
 

KKline said:

Hi Reddy, I'm not sure I understand you.  Are you asking how to create your own PerfMon counters?

Or are you asking which PerfMon counters are important? If this is your question, be sure to get a copy of the new poster I created showing all of the important PerfMon counters at http://www.quest.com/backstage/promotion.aspx.

August 12, 2010 1:28 PM
 

Lynn said:

good information,thank you

October 26, 2010 10:56 AM
 

Kyle said:

You need to find out how many spindles you have even if you are using Fast Cache or something else.  If you don't know that your missing allot.

June 14, 2012 2:54 PM
 

Suman Kumar Jha said:

Realy Nice Blog to Learn Monitor Server using perfmon.

January 23, 2013 2:02 AM
 

sathish said:

How to login to this site pls tell me.

November 13, 2014 8:12 AM
 

sathish said:

i want to be a member in this site.pls tell me.awaiting for your reply

November 13, 2014 8:14 AM
 

sathish said:

Hi Can anyone help me,my weekly scheduled job is taking too much time.that is 13 hrs.my client requirement is it need to be decrease the time minumum to 10 hours.

1.what i need to be check in the system.

2.what are the steps we need to see in sqlserver.

Pls advice me.Awaiting for your reply.it will be great helpful for me for answering.

November 13, 2014 8:19 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement