Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance Monitoring - Basic Counters Expand / Collapse
Author
Message
Posted Friday, April 30, 2004 3:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:00 PM
Points: 33,051, Visits: 15,158
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist






Follow me on Twitter:
@way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #113983
Posted Wednesday, May 5, 2004 2:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, Visits: 188

A nice concise article as an introduction to the mega-complex area of performance tuning - just the sort of thing I need.

 

Much appreciated.

 

Jamie Thomson

Conchango UK



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #114441
Posted Wednesday, May 5, 2004 4:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 2,726, Visits: 1,109

Very good guide on basic counters, looking forward to the rest of the series .

Darren Woodcock



--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #114454
Posted Wednesday, May 5, 2004 5:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 26, 2010 7:51 AM
Points: 269, Visits: 8

Very helpful list and explanations.

I wondered though if perhaps the process object should have been the processor object?

-Caleb




Post #114457
Posted Wednesday, May 5, 2004 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 27, 2008 8:48 PM
Points: 64, Visits: 2

Steve,

Great article. 

Can you elaborate on the rule of thumb for disk queue length and Processor Queue Length.  In the past, I have heard that anything (sustained) above 2 is a high number for the disk.  And that anything above 10 is a high number for processor queue length.  You say 8 and 2 respectively. 

Thanks,

Ben Reeder




Post #114479
Posted Wednesday, May 5, 2004 9:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:00 PM
Points: 33,051, Visits: 15,158

Thanks everyone, glad you liked it.

As far as the CPU %, it is the system object.  Apologies!

 

Disk Q - A tough one and honestly, I have to admit my experience with the latest and greatest is a little old. Been working with SANs quite a bit lately and the counters don't translate as well. However, I've usually gone with 8/disk thumb/view/guesstimate. When I see this, usally in conjunction with longer lock wait times but the # of reads/writes not growing I get worried. Of course, the complaints from the users is the main benchmark

For the CPU Q, the standard benchmark has been 2 per CPU over time, which for me is more than 10-15 minutes, that I get worried. If it's isolated occurrances because of some report or query, I can let that go. But if it's 4 times a day, then I need to perhaps consider upgrading the CPUs or adding more. Of course that's tough, so this metric is often more of a support for an argument for a change. If the Q stacks up, 5, 10, more per CPU and the CPUs are 100%, then you likely have a bottleneck here. If the CPU is 100% and the Q is low, then it's just busy.

Now with hyperthreading the 2 per CPU may need to be modified to 3 or 4 per physical. Honestly haven't done enough testing in comparison here. Hopefully I'll get to do some over time.

 








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #114524
Posted Wednesday, May 5, 2004 2:51 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:50 PM
Points: 292, Visits: 264

Here is the list of counters I used to use when I was a DBA. It is based on the SQL Server Operations Guide from Microsoft (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx). Many of the ones I used match the ones in Steve's article but there are some on this list that you might not see an immediate need for. The purpose of some of these is to establish a performance baseline so that when a user complains about how performance has "suddenly" gone to pot, you can compare the current counters to the baseline to see if the problem really is related to SQL Server.

Here is my list:

Memory

  • Pages/sec.

Network Interface

  • Bytes total/sec.

Physical Disk

  • Disk Transfers/sec.

Process

  • % Processor Time for sqlservr.exe

Processor

  • % Processor Time

SQL Server: Access Methods

  • Full Scans/sec.

SQL Server: Buffer Manager

  • Buffer Cache Hit Ratio

SQL Server: Cache Manager

  • Cache Hit Ratio

SQL Server: Databases (All Instances)

  • Log Growths
  • Percent Log Used
  • Transactions/sec.

SQL Server: General Statistics

  • User Connections

SQL Server: Latches

  • Average Latch Wait Time

SQL Server: Locks

  • Average Wait Time
  • Lock Timeouts/sec.
  • Lock Waits/sec.
  • Number of Deadlocks/sec.

SQL Server: Memory Manager

  • Memory Grants Pending

System

  • Processor Queue Length (Threshold: Average of five samples > 2)


Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #114589
Posted Friday, November 11, 2005 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 14, 2007 7:05 AM
Points: 1, Visits: 1

Greetings,

Nice article. After years monitoring Oracle, DB2 and SQL Server databases, I made for each of those RDBMS a three level performance monitoring check list. The first level is a day to day monitor list. I run on each server a perfmon logging into a csv file the results and doing some trending with Excel. It is a 3 minutes process per server.

When I notice something abnormal I run the second level. 95% of the time, I can identify positively there is a problem and begin to address it. If I don't see anything with the level 2, I go on level 3. I can say frankly, I went to level 3 only 7-8 times in the last 5 years.

Level 1:

Processor >> % Total Processor time

Physical Disk >> % Disk utilization

Physical Disk>>Avg. Disk Queue Length

System >> Processor Queue Length

System Object : Avg. Disk Queue Length

à Free Memory (KBs) (Task Manager)

à Total memory available (Task manager)

SQL Memory Manager >>Total server memory

Memory Object : Available Mbytes

Level 2:

Check SQL Connectivity issue

Memory >>Pages/Sec

SQL Buffer Manager>>Pages Writes/sec

SQL Buffer Manager>>Pages Reads/sec

SQL Buffer Manager>>Buffer Cache Hit Ratio

SQL Access Methods>>Full Scans/sec

SQL Access Methods>>Index Searches/sec

SQL Server Databases Methods: Transactions/Sec

SQL Access Methods >>Page Splits/sec ctr

SQL Blocking situation

SQL Error or warning message in the log

SQL Abnormal activity in current activity or Profiler (logging errors and warnings)

Level 3:

SQL Server Locks Object : Average Wait Time

SQL Locks>>Lock Waits

SQL Locks>>Number of deadlocks

Server Network Interface Object>>Bytes total/sec

SQL SQL Statistics >> SQL compilations/sec

SQL SQL Statistics >> SQL re-compilations/sec

 

Thank you

Alain Gagne OCP, DB2 expert, MCDBA, MCSE, MCSD

kestak2000@yahoo.com

Post #236543
Posted Friday, November 11, 2005 8:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 318, Visits: 350

In general I've noticed the following about diskQs.

1.  There are 2 schools of thought to follow for a general rule of thumb.

     a.  No more than 2 per CPU-- that means for every CPU on the box, you can withstand a Q up to 2.  So the Q for a 4-way box would be 8 tops.

     b.  No more than 2 per spindle-- that means for every HD on the box you can withstand a Q of up to 2.  This requires you to know how many spindles are in your raid set, but it's easy info to come by. 

In general, I've noticed that they are both valid under different circumstances.  The first rule is handy for small to mid sized boxes with direct attached storage.  The 2nd is good for SANs and larger boxes, though it will work well with mid size boxes on a SAN as well.

Of course, these are merely rules of thumb to see where you should start looking for trouble.  The tie breaker of course is fn_virtualfilestats.  This is where you'll get your most valuable info.

--Sean.



Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #236587
Posted Friday, November 11, 2005 8:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57

Nice article. You inspired me to add a few counters (to my cluttered mess!)  that I was missing.

Three Points

1.I believe the "System Object : Avg. Disk Queue Length" actually belongs to physical disk.

2.While you mention it, the importance of looking at individual disks and processors and not just the totals can not be overstated. The devil is often in the details.

3.It also helps to look at the usage of the paging file.

Terry Duffy

 




Post #236591
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse