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

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

Darren Woodcock

Very helpful list and explanations.

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

-Caleb

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

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.

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)

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:

Level 2:

Level 3:

Thank you

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

kestak2000@yahoo.com

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.

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