SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Monitoring - Basic Counters


Performance Monitoring - Basic Counters

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145705 Visits: 19425
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
My Blog: www.voiceofthedba.com
Jamie Thomson
Jamie Thomson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2693 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
Silverfox
Silverfox
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6400 Visits: 1161

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
ccadman
ccadman
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 8

Very helpful list and explanations.

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

-Caleb





timingskey
timingskey
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 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





Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145705 Visits: 19425

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
My Blog: www.voiceofthedba.com
Tatsu
Tatsu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1626 Visits: 307

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
Alain Gagne
Alain Gagne
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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


KenpoDBA
KenpoDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2504 Visits: 634

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.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


TDuffy
TDuffy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search