Post reply

Favorite Perf Mon Counters / Thresholds???

  • April 19, 2006 at 12:19 pm

    #114285

    I'm wondering if you guys and gals out there have "favorite" perfmon counters you use to monitor your servers.  MS only gives a brief description of each counter and rarely gives guidelines on thresholds and counter-to-counter ratios (although some are given - disk queue length comes to mind)

    Here are a list of counter categories to get your thoughts going...

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_9g6s.asp 

    Maybe someone out there has an opinion on the average ratio of (Full Scans per sec) / (Full Scans per sec + Range Scans per sec + Probe scans per sec) for a healthy OLTP server.

    I've never seen a hypothesis on such a calculation, but these are the types of ideas I'd like to hear about.

    Anecdotal...empirical...nothings to simple.  I'd like to here about it.

  • April 19, 2006 at 1:38 pm

    #633134

    I cover mine in my eBook, but it doesn't cover scans in the detail you're looking for... I was more geared towards overall health of the server. My homepage (Professional Site in my .sig) links to where you can order it off Amazon.

    K. Brian Kelley
    @kbriankelley

  • April 21, 2006 at 2:22 pm

    #633619

    Sold. RH

  • April 24, 2006 at 3:03 am

    #633711

    You might also want to look at http://www.sql-server-performance.com.  It goes into quite a bit of depth about performance monitoring.

    John

  • April 24, 2006 at 10:50 am

    #633863

    Here is a list of the counters we monitor - we collect the data every 5 minutes to a spreadsheet - as you watch them over time, you will get a feel for the counter levels in busy/slow periods - one of the most critical in our environment seems to the Avg Disk Queue Length - as this begins to approach or exceed 2 x the number of spindles it indicates the need to add addl spindles to the array.

    Memory\Pages/sec

    PhysicalDisk(_Total)\% Disk Time

    PhysicalDisk(_Total)\% Disk Read Time

    PhysicalDisk(_Total)\% Disk Write Time

    PhysicalDisk(_Total)\Avg. Disk Queue Length

    Processor(_Total)\% Processor Time

    SQLServer:Access Methods\Full Scans/sec

    SQLServer:Access Methods\Page Splits/sec

    SQLServer:Access Methods\Table Lock Escalations/sec

    SQLServer:Buffer Manager\Buffer cache hit ratio

    SQLServeratabases\Transactions/sec: Total

    SQLServer:General Connections\User Connections

    SQLServer:Locks(_Total)\Lock Wait Time (ms)

    SQLServer:Locks(_Total)\Average Wait Time (ms)

    SQLServer:Locks(_Total)\Number of Deadlocks

    SQLServer:Locks(_Total)\Lock Timeouts

    SQLServer:Memory Manager\Memory Grants Pending

    SQLServer:Memory Manager\SQL Cache Memory

    Good Luck,

    Harley

  • April 26, 2006 at 11:21 am

    #634381

    This is an excellent site, too. Information on everything from query tuning to why not to use fibers in most environments.

    K. Brian Kelley
    @kbriankelley

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply